MySQL查询在逗号分隔的字符串中查找值

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.

在我的t恤中有一个字段颜色(varchar(50))，其中包含一个逗号分隔的字符串，如1,2,5,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).

当运行查询select *时，从“%1%”等颜色的衬衫中获得所有的红色衬衫(颜色=1)，我还得到颜色为灰色(=12)和橙色(=15)的衬衫。

How should I rewrite the query so that is selects ONLY the color 1 and not all colors containing the number 1?

我应该如何重写查询，以便只选择颜色1而不是包含数字1的所有颜色?

10 个解决方案

#1

138

The classic way would be to add comma's to the left and right:

经典的做法是在左边和右边加逗号:

select * from shirts where CONCAT(',', colors, ',') like '%,1,%'

But find_in_set also works:

但find_in_set也适用:

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

#2

FIND_IN_SET is your friend in this case

在本例中，FIND_IN_SET是您的朋友。

select * from shirts where FIND_IN_SET(1,colors)

#3

Take a look at the FIND_IN_SET function for MySQL.

看看MySQL的FIND_IN_SET函数。

SELECT * 
    FROM shirts 
    WHERE FIND_IN_SET('1',colors) > 0

#4

This will work for sure, and I actually tried it out:

这肯定行得通，我试过了

lwdba@localhost (DB test) :: DROP TABLE IF EXISTS shirts;
Query OK, 0 rows affected (0.08 sec)

lwdba@localhost (DB test) :: CREATE TABLE shirts
    -> (<BR>
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ticketnumber INT,
    -> colors VARCHAR(30)
    -> );<BR>
Query OK, 0 rows affected (0.19 sec)

lwdba@localhost (DB test) :: INSERT INTO shirts (ticketnumber,colors) VALUES
    -> (32423,'1,2,5,12,15'),
    -> (32424,'1,5,12,15,30'),
    -> (32425,'2,5,11,15,28'),
    -> (32426,'1,2,7,12,15'),
    -> (32427,'2,4,8,12,15');
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

lwdba@localhost (DB test) :: SELECT * FROM shirts WHERE LOCATE(CONCAT(',', 1 ,','),CONCAT(',',colors,',')) > 0;
+----+--------------+--------------+
| id | ticketnumber | colors       |
+----+--------------+--------------+
|  1 |        32423 | 1,2,5,12,15  |
|  2 |        32424 | 1,5,12,15,30 |
|  4 |        32426 | 1,2,7,12,15  |
+----+--------------+--------------+
3 rows in set (0.00 sec)

Give it a Try !!!

试一试!!!

#5

If the set of colors is more or less fixed, the most efficient and also most readable way would be to use string constants in your app and then use MySQL's SET type with FIND_IN_SET('red',colors) in your queries. When using the SET type with FIND_IN_SET, MySQL uses one integer to store all values and uses binary "and" operation to check for presence of values which is way more efficient than scanning a comma-separated string.

如果一组颜色或多或少是固定的，那么最有效也是最易读的方法是在应用程序中使用字符串常量，然后在查询中使用带有FIND_IN_SET('red'，colors)的MySQL的set类型。当使用FIND_IN_SET使用SET类型时，MySQL使用一个整数存储所有值，并使用二进制“和”操作检查是否存在值，这比扫描逗号分隔的字符串更有效。

In SET('red','blue','green'), 'red' would be stored internally as 1, 'blue' would be stored internally as 2 and 'green' would be stored internally as 4. The value 'red,blue' would be stored as 3 (1|2) and 'red,green' as 5 (1|4).

在SET('red'，'blue'，'green')中，' red'将在内部存储为1，'blue'将在内部存储为2，'green'将在内部存储为4。值“red,blue”将存储为3(1|2)和“red,green”存储为5(1|4)。

#6

If you're using MySQL, there is a method REGEXP that you can use...

如果您正在使用MySQL，有一个方法REGEXP可以使用……

http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

http://dev.mysql.com/doc/refman/5.1/en/regexp.html operator_regexp

So then you would use:

然后你会用:

SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'

#7

You should actually fix your database schema so that you have three tables:

实际上，您应该修复您的数据库模式，以便您有三个表:

shirt: shirt_id, shirt_name
color: color_id, color_name
shirtcolor: shirt_id, color_id

Then if you want to find all of the shirts that are red, you'd do a query like:

如果你想找到所有红色的衬衫，你可以这样问:

SELECT *
FROM shirt, color
WHERE color.color_name = 'red'
  AND shirt.shirt_id = shirtcolor.shirt_id
  AND color.color_id = shirtcolor.color_id

#8

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

Works for me

适合我

#9

You can achieve this by following function.

您可以通过以下函数实现此功能。

Run following query to create function.

运行以下查询来创建函数。

DELIMITER ||
CREATE FUNCTION `TOTAL_OCCURANCE`(`commastring` TEXT, `findme`     VARCHAR(255)) RETURNS int(11)
NO SQL
-- SANI: First param is for comma separated string and 2nd for string to find.
return ROUND (   
    (
        LENGTH(commastring)
        - LENGTH( REPLACE ( commastring, findme, "") ) 
    ) / LENGTH(findme)        
);

And call this function like this

这个函数是这样的

msyql> select TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB', 'A');

Hope it'd help.

希望它会有帮助。

#10

-4

All the answers are not really correct, try this:

所有的答案都不是真的正确，试试这个:

select * from shirts where 1 IN (colors);

#1

138