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如'％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 who's 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 comma's to the left and right:

select * from shirts where ',' + colors + ',' like '%,1,%'

但是 find_in_set 也工作：

select * from shirts where find_in_set('1',colors) <> 0

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