FIND_IN_SET with multiple value

I want to search multiple values from database field. below is my query.

SELECT * FROM `tablename` 
WHERE FIND_IN_SET('12,13,15,15',category_id) 

How i search its not working for me.

Solution 1:

FIND_IN_SET() can only be used to search for a single value in a comma-separated list, it doesn't work with two lists.

You'll need to call it separately for each value.

SELECT * FROM tablename
WHERE FIND_IN_SET('12', category_id) OR FIND_IN_SET('13', category_id) OR FIND_IN_SET('15', category_id)

It would be better if you normalized your schema instead of using comma-separated lists. If you create a many-to-many table with the category IDs, you could do:

SELECT t1.*
FROM tablename AS t1
JOIN item_categories AS c ON t1.id = c.table_id
WHERE c.category_id IN (12, 13, 15)

Solution 2:

FIND_IN_SET is not the solution. Try to use REGEXP:

SELECT * FROM `tablename` 
WHERE CONCAT(',', `category_id`, ',') REGEXP ',(12|13|15),';

But even if it's less pretty, it's better to use LIKE for performance reasons :

SELECT * FROM `tablename` 
WHERE CONCAT(',', `category_id`, ',') LIKE '%,12,%' OR CONCAT(',', `category_id`, ',') LIKE '%,13,%' OR CONCAT(',', `category_id`, ',') LIKE '%,15,%';