MySQL 查询在逗号分隔的字符串中查找值
我的表 SHIRTS 中有一个字段 COLORS (varchar(50))，它包含一个逗号分隔的字符串，例如 1,2,5，12,15，.每个数字代表可用的颜色.
I have a field COLORS (varchar(50)) in a my table SHIRTS that contains a comma delimited string such as 1,2,5,12,15,. Each number representing the available colors.
当运行查询 select * from shirts where colours like '%1%' 以获取所有红色衬衫(颜色 = 1)时，我还会得到颜色为灰色( = 12) 和橙色 (=15).
When running the query select * from shirts where colors like '%1%' to get all the red shirts (color=1), I also get the shirts whose color is grey (=12) and orange (=15).
我应该如何重写查询，以便仅选择颜色 1 而不是所有包含数字 1 的颜色?
How should I rewrite the query so that is selects ONLY the color 1 and not all colors containing the number 1?
推荐答案
经典的方法是在左右添加逗号:
The classic way would be to add commas to the left and right:
select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
但是find_in_set也有效:
select * from shirts where find_in_set('1',colors) <> 0
相关文章