SQL仅获取与逗号分隔的完整数字匹配的行

Question

I'm working on something that shows shops under a specific category, however I have an issue because I store the categories of a shop like this in a record with the id of a category. "1,5,12". Now, the problem is if I want to show shops with category 2, it "mistakens" 12 as category 2. This is the SQL right now.

SELECT * FROM shops WHERE shop_cats LIKE '%".$sqlid."%' LIMIT 8

Is there a way to split the record "shop_cats" by a comma in SQL, so it checks the full number? The only way I can think of is to get all the shops, and do it with PHP, but I don't like that as it will take too many resources.

Answer 1

This is a really, really bad way to store categories, for many reasons:

• You are storing numbers as strings.
• You cannot declare proper foreign key relationships.
• A (normal) column in a table should have only one value.
• SQL has poor string functions.
• The resulting queries cannot take advantage of indexes.

The proper way to store this information in a database is using a junction table, with one row per shop and per category.

Sometimes, we are stuck with other people's really bad design decisions. If this is your case, then you can use FIND_IN_SET():

WHERE FIND_IN_SET($sqlid, shop_cats) > 0

But you should really fix the data structure.