blocks|key|1775452|text|经典的方法是在左边和右边加逗号：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1775453|select+*+from+shirts+where+CONCAT(',',+colors,+',')+like+'%25,1,%25'|code-block|syntax|javascript|1775454|但是find_in_set也可以工作：|offset|length|1775455|select+*+from+shirts+where+find_in_set('1',colors)+<>+0|1775456|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set^0|0|0|2|B|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

The classic way would be to add commas to the left and right:

<pre><code>select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
</code></pre>

But <a href="http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set" rel="noreferrer">find_in_set</a> also works:

<pre><code>select * from shirts where find_in_set('1',colors) &lt;&gt; 0
</code></pre>

blocks|key|1989277|text|在这种情况下，FIND_IN_SET是你的朋友|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1989278|select+*+from+shirts+where+FIND_IN_SET(1,colors)+|code-block|syntax|javascript|1989279|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set^0|7|B|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

<a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set" rel="noreferrer">FIND_IN_SET</a> is your friend in this case

<pre><code>select * from shirts where FIND_IN_SET(1,colors) 
</code></pre>

blocks|key|1989212|text|看一下MySQL的FIND_IN_SET函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1989213|SELECT+*+
++++FROM+shirts+
++++WHERE+FIND_IN_SET('1',colors)+>+0|code-block|syntax|javascript|1989214|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set^0|9|B|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Take a look at the <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set" rel="noreferrer">FIND_IN_SET</a> function for MySQL.

<pre><code>SELECT * 
 FROM shirts 
 WHERE FIND_IN_SET('1',colors) &gt; 0
</code></pre>

blocks|key|1989295|text|这肯定能行得通，我真的试过了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1989296|lwdba@localhost+(DB+test)+::+DROP+TABLE+IF+EXISTS+shirts;
Query+OK,+0+rows+affected+(0.08+sec)

lwdba@localhost+(DB+test)+::+CREATE+TABLE+shirts
++++->+( 
++++->+id+INT+NOT+NULL+AUTO_INCREMENT+PRIMARY+KEY,
++++->+ticketnumber+INT,
++++->+colors+VARCHAR(30)
++++->+); 
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;
%2B----%2B--------------%2B--------------%2B
%7C+id+%7C+ticketnumber+%7C+colors+++++++%7C
%2B----%2B--------------%2B--------------%2B
%7C++1+%7C++++++++32423+%7C+1,2,5,12,15++%7C
%7C++2+%7C++++++++32424+%7C+1,5,12,15,30+%7C
%7C++4+%7C++++++++32426+%7C+1,2,7,12,15++%7C
%2B----%2B--------------%2B--------------%2B
3+rows+in+set+(0.00+sec)|code-block|syntax|javascript|1989297|试一试吧！|1989298|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

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

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

lwdba@localhost (DB test) :: INSERT INTO shirts (ticketnumber,colors) VALUES
 -&gt; (32423,'1,2,5,12,15'),
 -&gt; (32424,'1,5,12,15,30'),
 -&gt; (32425,'2,5,11,15,28'),
 -&gt; (32426,'1,2,7,12,15'),
 -&gt; (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,',')) &gt; 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)
</code></pre>

Give it a Try !!!

blocks|key|1989312|text|如果颜色集或多或少是固定的，那么最有效也最具可读性的方法是在应用程序中使用字符串常量，然后在查询中使用带有FIND_IN_SET('red',colors)的MySQL的SET类型。当将SET类型与FIND_IN_SET一起使用时，MySQL使用一个整数来存储所有值，并使用二进制"and"操作来检查值的存在，这比扫描逗号分隔的字符串要高效得多。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1989313|在SET('red','blue','green')中，'red'将在内部存储为1，'blue'将在内部存储为2，'green'将在内部存储为4。值'red,blue'将存储为3+(1%7C2)，'red,green'将存储为5+(1%7C4)。|1989314|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set^0|1H|P|2D|3|2L|3|3W|5|2R|B|0|0|1|P|S|5|14|1|16|6|1J|1|1L|7|1Z|1|22|A|2G|1|2J|3|2O|B|33|1|36|3|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|F|3|G|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]|$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]|$9|1G|A|1H|B|C]|$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]|$9|1M|A|1N|B|C]|$9|1O|A|1P|B|C]|$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|1S|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

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 <code>SET</code> type with <code>FIND_IN_SET('red',colors)</code> in your queries. When using the <code>SET</code> type with <a href="http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set" rel="nofollow noreferrer">FIND_IN_SET</a>, MySQL uses one integer to store all values and uses binary <code>"and"</code> operation to check for presence of values which is way more efficient than scanning a comma-separated string.

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

blocks|key|1989186|text|如果你使用的是MySQL，有一种方法你可以使用...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1989187|http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp|offset|length|1989188|因此，您可以使用：|1989189|SELECT+*+FROM+`shirts`+WHERE+`colors`+REGEXP+'\b1\b'|code-block|syntax|javascript|1989190|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1U|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|Z|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|C]]]]

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

<a href="http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp</a>

So then you would use:

<pre><code>SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'
</code></pre>

blocks|key|1775418|text|您实际上应该修复您的数据库模式，这样您就有了三个表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1775419|shirt:+shirt_id,+shirt_name
color:+color_id,+color_name
shirtcolor:+shirt_id,+color_id|code-block|syntax|javascript|1775420|然后，如果您想要查找所有红色的衬衫，您可以执行如下查询：|1775421|SELECT+*
FROM+shirt,+color
WHERE+color.color_name+=+'red'
++AND+shirt.shirt_id+=+shirtcolor.shirt_id
++AND+color.color_id+=+shirtcolor.color_id|1775422|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>shirt: shirt_id, shirt_name
color: color_id, color_name
shirtcolor: shirt_id, color_id
</code></pre>

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

<pre><code>SELECT *
FROM shirt, color
WHERE color.color_name = 'red'
 AND shirt.shirt_id = shirtcolor.shirt_id
 AND color.color_id = shirtcolor.color_id
</code></pre>

blocks|key|1989372|text|select+*+from+shirts+where+find_in_set('1',colors)+<>+0|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1989373|对我很管用|unstyled|1989374|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select * from shirts where find_in_set('1',colors) &lt;&gt; 0
</code></pre>

Works for me

blocks|key|1775556|text|您可以通过以下函数来实现此功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1775557|运行以下查询以创建函数。|1775558|DELIMITER+%7C%7C
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)++++++++
);|code-block|syntax|javascript|1775559|并像这样调用这个函数|1775560|msyql>+select+TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB',+'A');|1775561|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can achieve this by following function.

Run following query to create function.

<pre><code>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) 
);
</code></pre>

And call this function like this

<pre><code>msyql&gt; select TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB', 'A');
</code></pre>

blocks|key|1775514|text|并不是所有的答案都是正确的，试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1775515|select+*+from+shirts+where+1+IN+(colors);|code-block|syntax|javascript|1775516|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

All the answers are not really correct, try this:

<pre><code>select * from shirts where 1 IN (colors);
</code></pre>

I have a field <code>COLORS (varchar(50))</code> in a my table <code>SHIRTS</code> that contains a comma delimited string such as <code>1,2,5,12,15,</code>. Each number representing the available colors.
When running the query <code>select * from shirts where colors like '%1%'</code> to get all the red shirts (color=1), I also get the shirts whose color is grey (=12) and orange (=15).
How should I rewrite the query so that is selects ONLY the color 1 and not all colors containing the number 1?

MySQL query finding values in a comma separated string

数据库

MySQL

我的表SHIRTS中有一个字段COLORS (varchar(50))，其中包含一个逗号分隔的字符串，比如1,2,5,12,15,。每个数字表示可用颜色。在运行查询select * from shirts where colors like '%1%'以获取所有红色衬衫(color=1)时，我还获得了颜色为灰色(=12)和橙色(=15)的衬衫。我应该如何重写查询，以便只选择颜色1，而不是所有包含数

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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