farzad farzad - 18 days ago 9
MySQL Question

how select row in MySQL by check when column data include delimiter

I have question . If i have user table and a column name be

freinds
.
I want use some user id inside a column friends with this value : 1|2|45|18 .
This means for example user id =5 have 4 freinds with these id 1|2|45|18.
I don't want use two table for this situation.

How can select each users data when friends with user id = 5 ?
In php we have explode function for slice each delimeter .
Can i set delimeter for this target or i should use two table user and friends in MySQL ?

Answer

It's an awful case, especially if you inherit some DB with such a scheme, but to find friends you can use:

SELECT friend.*
FROM user u 
left join user friend on  concat('|', o.friends, '|') like concat('%|', friend.id, '|%')

where u.id = 5;
Comments