MySQL 查询在逗号分隔的字符串中查找值 [英] MySQL query finding values in a comma separated string

问题描述

我的表 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

这篇关于MySQL 查询在逗号分隔的字符串中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！