blocks|key|877256|text|(a,b,c)列表仅适用于in。对于like，您必须使用or|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|877257|WHERE+interests+LIKE+'%25sports%25'+OR+interests+LIKE+'%25pub%25'|code-block|syntax|javascript|877258|entityMap^0|0|7|D|2|I|4|S|2|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

The <code>(a,b,c)</code> list only works with <code>in</code>. For <code>like</code>, you have to use <code>or</code>:

<pre><code>WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'
</code></pre>

blocks|key|877316|text|执行此操作的更快方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|877317|WHERE+interests+LIKE+'%25sports%25'+OR+interests+LIKE+'%25pub%25'|code-block|syntax|javascript|877318|是这样的吗：|877319|WHERE+interests+REGEXP+'sports%7Cpub'|877320|在这里找到了这个解决方案：http://forums.mysql.com/read.php?10,392332,392950#msg-392950|offset|length|877321|有关REGEXP的更多信息，请访问：http://www.tutorialspoint.com/mysql/mysql-regexps.htm|877322|entityMap|0|LINK|mutability|MUTABLE|url|http://forums.mysql.com/read.php?10,392332,392950#msg-392950|1|http://www.tutorialspoint.com/mysql/mysql-regexps.htm^0|0|0|0|0|D|1O|0|0|I|1H|1|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|14|8|@]|9|@$M|15|N|16|1|17]]|A|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@$M|19|N|1A|1|1B]]|A|$]]|$1|Q|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]|Y|$5|T|U|V|A|$W|Z]]]]

Faster way of doing this: 
<pre><code>WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'
</code></pre>
is this: 
<pre><code>WHERE interests REGEXP 'sports|pub'
</code></pre>
Found this solution here: <a href="http://forums.mysql.com/read.php?10,392332,392950#msg-392950" rel="noreferrer">http://forums.mysql.com/read.php?10,392332,392950#msg-392950</a>
More about REGEXP here: <a href="http://www.tutorialspoint.com/mysql/mysql-regexps.htm" rel="noreferrer">http://www.tutorialspoint.com/mysql/mysql-regexps.htm</a>

blocks|key|877376|text|为什么不试试REGEXP呢？像这样试一下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|877377|SELECT+*+FROM+table+WHERE+interests+REGEXP+'sports%7Cpub'|code-block|syntax|javascript|877378|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|$]]

Why not you try REGEXP. Try it like this:

<pre><code>SELECT * FROM table WHERE interests REGEXP 'sports|pub'
</code></pre>

blocks|key|1092054|text|您也可以使用REGEXP的同义词RLIKE。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1092055|例如：|1092056|SELECT+*
FROM+TABLE_NAME
WHERE+COLNAME+RLIKE+'REGEX1%7CREGEX2%7CREGEX3'|code-block|syntax|javascript|1092057|entityMap^0|6|6|G|5|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

You can also use <code>REGEXP</code>'s synonym <code>RLIKE</code> as well.
For example:
<pre class="lang-sql prettyprint-override"><code>SELECT *
FROM TABLE_NAME
WHERE COLNAME RLIKE 'REGEX1|REGEX2|REGEX3'
</code></pre>

blocks|key|1091908|text|您的查询应为SELECT+*+FROM+`table`+WHERE+find_in_set(interests,+"sports,pub")>0|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1091909|我所理解的是，您将兴趣存储在表的一个字段中，这是一种误解。你一定要有一个“利息”表。|1091910|entityMap^0|6|1U|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

Your query should be <code>SELECT * FROM `table` WHERE find_in_set(interests, "sports,pub")&gt;0</code>

What I understand is that you store the interests in one field of your table, which is a misconception. You should definitively have an "interest" table.

blocks|key|1091975|text|如果在AND参数后使用此函数，请不要忘记使用括号|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1091976|如下所示：|1091977|WHERE+id=123+and(interests+LIKE+'%25sports%25'+OR+interests+LIKE+'%25pub%25')|code-block|syntax|javascript|1091978|entityMap^0|3|3|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Don't forget to use parenthesis if you use this function after an <code>AND</code> parameter

Like this:

<pre><code>WHERE id=123 and(interests LIKE '%sports%' OR interests LIKE '%pub%')
</code></pre>

blocks|key|1092159|text|或者，如果您只需要匹配单词的开头：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1092160|WHERE+interests+LIKE+'sports%25'+OR+interests+LIKE+'pub%25'|code-block|syntax|javascript|1092161|您可以使用regexp插入符号匹配：|1092162|WHERE+interests+REGEXP+'%5Esports%7C%5Epub'|1092163|https://www.regular-expressions.info/anchors.html|offset|length|1092164|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|1D|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|W|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|X|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Z|8|@]|9|@$M|10|N|11|1|12]]|A|$]]|$1|O|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|L]]]]

Or if you need to match only the beginning of words:

<pre><code>WHERE interests LIKE 'sports%' OR interests LIKE 'pub%'
</code></pre>

you can use the regexp caret matches:

<pre><code>WHERE interests REGEXP '^sports|^pub'
</code></pre>

<a href="https://www.regular-expressions.info/anchors.html" rel="noreferrer">https://www.regular-expressions.info/anchors.html</a>

blocks|key|877422|text|就像@Alexis+Dufrenoy提议的那样，查询可以是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|877423|SELECT+*+FROM+`table`+WHERE+find_in_set('sports',+interests)>0+OR+find_in_set('pub',+interests)>0|code-block|syntax|javascript|877424|有关更多信息，请参阅manual。|offset|length|877425|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set^0|0|0|A|6|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Like @Alexis Dufrenoy proposed, the query could be:

<pre><code>SELECT * FROM `table` WHERE find_in_set('sports', interests)&gt;0 OR find_in_set('pub', interests)&gt;0
</code></pre>

More information in the <a href="https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set" rel="nofollow noreferrer">manual</a>.

blocks|key|877495|text|更多工作示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|877496|SELECT+COUNT(email)+as+count+FROM+table1+t1+
JOIN+(
++++++SELECT+company_domains+as+emailext+FROM+table2+WHERE+company+=+'DELL'
+++++)+t2+
ON+t1.email+LIKE+CONCAT('%25',+emailext)+WHERE+t1.event='PC+Global+Conference";|code-block|syntax|javascript|877497|任务是如果电子邮件扩展名等于多个公司域，则对事件参与者进行计数。|877498|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|$]]

More work examples:

<pre><code>SELECT COUNT(email) as count FROM table1 t1 
JOIN (
 SELECT company_domains as emailext FROM table2 WHERE company = 'DELL'
 ) t2 
ON t1.email LIKE CONCAT('%', emailext) WHERE t1.event='PC Global Conference";
</code></pre>

Task was count participants at an event(s) with filter if email extension equal to multiple company domains.

I have this MySQL query.

I have database fields with this contents

<pre><code>sports,shopping,pool,pc,games 
shopping,pool,pc,games 
sports,pub,swimming, pool, pc, games 
</code></pre>

Why does this like query does not work?
I need the fields with either sports or pub or both?

<pre><code>SELECT * FROM table WHERE interests LIKE ('%sports%', '%pub%')
</code></pre>

MySQL Like multiple values

数据库

MySQL

我有一个MySQL查询。我有包含此内容的数据库字段sports,shopping,pool,pc,games shopping,pool,pc,games sports,pub,swimming, pool, pc, games   为什么这个like查询不起作用？我需要有运动或酒吧的场地，或者两者都有？SELECT * FROM table WHERE interests LIKE ('%spo

问MySQL喜欢多个值
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL喜欢多个值EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL喜欢多个值
EN