Best search ways in comma separated values for comma separated option

Question

I have user tables which contains column city_id and inside this column city ids are stored as comma separated values as following:

user_id  user_name     city_id    
1        abc1           1,2,3
2        abc2           15,2,9
3        abc5           1,2,13

Now I want to search rows which contains city id = 1,9,13 from city_id column.

How it can be done?

Expected Ouput:

abc, abc1, abc3

Answer 1

You should avoid storing CSV data in the city_id column as you are currently doing. Ideally, one record should have just one data point for city_id. That being said, MySQL offers a function FIND_IN_SET which can cope with CSV data, at least to a point. Try the following query:

SELECT user_name
FROM yourTable
WHERE
    FIND_IN_SET('1', city_id) > 0 OR
    FIND_IN_SET('9', city_id) > 0 OR
    FIND_IN_SET('13', city_id) > 0;

We can make the query even tighter by using REGEXP with the regex alternation \b(1|9|13)\b:

SELECT user_name
FROM yourTable
WHERE city_id REGEXP '[[:<:]](1|9|13)[[:>:]]';