MySQL MySQL查询在逗号分隔的字符串中查找值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5033047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL query finding values in a comma separated string
提问by bikey77
I have a field COLORS (varchar(50))in a my table SHIRTSthat contains a comma delimited string such as 1,2,5,12,15,. Each number representing the available colors.
我的表中有一个字段COLORS (varchar(50))，SHIRTS其中包含逗号分隔的字符串，例如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 * from shirts where colors like '%1%'以获取所有红色衬衫 (color=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 的颜色？
回答by Andomar
The classic way would be to add commas to the left and right:
经典的方法是在左侧和右侧添加逗号：
select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
But find_in_setalso works:
但find_in_set也有效：
select * from shirts where find_in_set('1',colors) <> 0
回答by Shakti Singh
FIND_IN_SETis your friend in this case
在这种情况下，FIND_IN_SET是你的朋友
select * from shirts where FIND_IN_SET(1,colors)
回答by Joe Stefanelli
Take a look at the FIND_IN_SETfunction for MySQL.
查看MySQL的FIND_IN_SET函数。
SELECT *
FROM shirts
WHERE FIND_IN_SET('1',colors) > 0
回答by RolandoMySQLDBA
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 !!!
试一试 ！！！
回答by ColinM
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 SETtype with FIND_IN_SET('red',colors)in your queries. When using the SETtype 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.
如果颜色集或多或少是固定的，最有效和最易读的方法是在您的应用程序中使用字符串常量，然后在您的查询中使用 MySQL 的SET类型FIND_IN_SET('red',colors)。当使用FIND_IN_SETSET类型时，MySQL 使用一个整数来存储所有值，并使用二进制运算来检查值的存在，这比扫描逗号分隔的字符串更有效。"and"
In SET('red','blue','green'), 'red'would be stored internally as 1, 'blue'would be stored internally as 2and '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）。
回答by CanSpice
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
回答by KOGI
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'
回答by Deepak Bhatta
select * from shirts where find_in_set('1',colors) <> 0
Works for me
为我工作
回答by Delickate
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');
回答by Saranga kapilarathna
1. For MySQL:
1. 对于 MySQL：
SELECT FIND_IN_SET(5, columnname) AS result
FROM table
2.For Postgres SQL :
2.对于 Postgres SQL：
SELECT *
FROM TABLENAME f
WHERE 'searchvalue' = ANY (string_to_array(COLUMNNAME, ','))
Example
例子
select *
from customer f
where '11' = ANY (string_to_array(customerids, ','))
