How to select an item in a comma separated list field in MySQL

I have a table named product, where in the category field I have inserted around 5 to 6 categories.

For example 2,3,4,5,12 just like that.

But when I use this query it doesn't work:

SELECT * FROM product WHERE categ开发者_StackOverflow中文版ory in '3' 

SELECT * FROM `product` WHERE `category` LIKE '%3%' 

can anyone help to fix it

SELECT * FROM product WHERE FIND_IN_SET('3',category); 

See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

If you don't have find_in_set this code would be equivalent:

SELECT * FROM product 
WHERE category = '3' or category LIKE '%,3,%' 
      or category LIKE '3,%' or category LIKE '%,3'; 

Otherwise you will get a false positive for 3 on '13, 23, 40'.

Warning
Do note however that it's very bad practise to put a CSV list in a field.
You'll get much better performance by extracting the list out of the field and putting it in another table in a 1-to-N relation.

the best practice would be to create another table!

i.e.: product_categories int productid int category

SELECT * FROM product p 
INNER JOIN product_categories pc ON pc.productid = p.productid 
WHERE pc.category = '3'

with the way you want to implement it, you would also have a problem when a category '13' is available, but you want to have '3'

I'm sorry to say it but you should redesign your database.
Using a single field to store multiple connections will always be a pain in the butt.

It is much easier to create an additional table that stores the relations.

The IN statement wont work because you are asking for the category to be an exact match of "3" when it is in fact "2,3,4,5,12". I think it should also be in round brackets.

As user737767 said you should really normalise your database so you have a table of categories, a table of products and another table to show which categories each product belongs to.

Your LIKE statement is also a bad idea because searching for "3" will also bring back results like "23"