MySQL WHERE子句和分隔字符串的问题

Question

I have a database table which stores products. Each product can have multiple colours. Each colour is represented by its ID rather than a textual description like 'Red', 'Yellow', etc. When the $_POST['colour'] array is imploded into a string (delimited by commas) it is then stored in the table:

product_id | colour
----------------------
1          | 1,2
2          | 10
3          | 7,9

Recently I've tried to create a search form which could select a number of colours and search the database table to see if there are any products which contain at least one of the colours in the search array. So if a visitor wanted to see products for colours 1 and 9, I need to search the 'colour' column for these two values.

I can't use WHERE colour IN (1,9) because I think that only works if you have one value in the column (rather than a delimited array of multiple values). Nor can I use WHERE colour LIKE 1 OR WHERE colour LIKE 9 because it would return products which have a colour ID of 10 or 11 or 12, etc.

Does anybody know how I can do this?

Answer 1

This table need to be normalized if you wanna get good performance, create a productColor table with a one-to-many relation with the product table.

That being said:

SELECT *
FROM Product
WHERE Colour LIKE '%,1,%' OR Colour LIKE '1,% OR COLOUR LIKE '%,1'
   OR Colour LIKE '%,9,%' OR Colour LIKE '9,% OR COLOUR LIKE '%,9'