MySQL LIKE IN()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1127088/
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 LIKE IN()?
提问by Michael Wales
My current query looks like this:
我当前的查询如下所示：
SELECT * FROM fiberbox f WHERE f.fiberBox LIKE '%1740 %' OR f.fiberBox LIKE '%1938 %' OR f.fiberBox LIKE '%1940 %'
I did some looking around and can't find anything similar to a LIKE IN() - I envision it working like this:
我环顾四周，找不到任何类似于 LIKE IN() 的东西-我设想它的工作方式如下：
SELECT * FROM fiberbox f WHERE f.fiberbox LIKE IN('%140 %', '%1938 %', '%1940 %')
Any ideas? Am I just thinking of the problem the wrong way - some obscure command I've never seen.
有任何想法吗？我是否只是以错误的方式思考问题 - 一些我从未见过的晦涩命令。
MySQL 5.0.77-community-log
MySQL 5.0.77-社区日志
回答by Paul Dixon
回答by David Carroll
Paul Dixon's answer worked brilliantly for me. To add to this, here are some things I observed for those interested in using REGEXP:
保罗迪克森的回答对我来说非常有效。除此之外，对于那些对使用 REGEXP 感兴趣的人，我观察到了一些事情：
To Accomplish multiple LIKE filters with Wildcards:
要使用通配符完成多个 LIKE 过滤器：
SELECT * FROM fiberbox WHERE field LIKE '%1740 %'
OR field LIKE '%1938 %'
OR field LIKE '%1940 %';
Use REGEXP Alternative:
使用 REGEXP 替代方案：
SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |1940 ';
Values within REGEXP quotes and between the | (OR) operator are treated as wildcards. Typically, REGEXP will require wildcard expressions such as (.*)1740 (.*) to work as %1740 %.
REGEXP 引号内和 | 之间的值 (OR) 运算符被视为通配符。通常，REGEXP 将需要通配符表达式，例如 (.*)1740 (.*) 以用作 %1740 %。
If you need more control over placement of the wildcard, use some of these variants:
如果您需要更多地控制通配符的位置，请使用以下一些变体：
To Accomplish LIKE with Controlled Wildcard Placement:
使用受控通配符放置完成 LIKE：
SELECT * FROM fiberbox WHERE field LIKE '1740 %'
OR field LIKE '%1938 '
OR field LIKE '%1940 % test';
Use:
用：
SELECT * FROM fiberbox WHERE field REGEXP '^1740 |1938 $|1940 (.*) test';
Placing ^ in front of the value indicates start of the line.
Placing $ after the value indicates end of line.
Placing (.*) behaves much like the % wildcard.
The . indicates any single character, except line breaks. Placing . inside () with * (.*) adds a repeating pattern indicating any number of characters till end of line.
将 ^ 放在值前面表示行的开始。
将 $ 放在值之后表示行尾。
放置 (.*) 的行为很像 % 通配符。
这 。表示除换行符外的任何单个字符。放置。inside () with * (.*) 添加一个重复模式，指示任意数量的字符直到行尾。
There are more efficient ways to narrow down specific matches, but that requires more review of Regular Expressions. NOTE: Not all regex patterns appear to work in MySQL statements. You'll need to test your patterns and see what works.
有更有效的方法来缩小特定匹配的范围，但这需要更多地正则表达式。注意：并非所有正则表达式模式似乎都适用于 MySQL 语句。你需要测试你的模式，看看什么是有效的。
Finally, To Accomplish Multiple LIKE and NOT LIKE filters:
最后，要完成多个 LIKE 和 NOT LIKE 过滤器：
SELECT * FROM fiberbox WHERE field LIKE '%1740 %'
OR field LIKE '%1938 %'
OR field NOT LIKE '%1940 %'
OR field NOT LIKE 'test %'
OR field = '9999';
Use REGEXP Alternative:
使用 REGEXP 替代方案：
SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |^9999$'
OR field NOT REGEXP '1940 |^test ';
OR Mixed Alternative:
或混合替代：
SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 '
OR field NOT REGEXP '1940 |^test '
OR field NOT LIKE 'test %'
OR field = '9999';
Notice I separated the NOT set in a separate WHERE filter. I experimented with using negating patterns, forward looking patterns, and so on. However, these expressions did not appear to yield the desired results. In the first example above, I use ^9999$ to indicate exact match. This allows you to add specific matches with wildcard matches in the same expression. However, you can also mix these types of statements as you can see in the second example listed.
请注意，我在单独的 WHERE 过滤器中分隔了 NOT 设置。我尝试使用否定模式、前瞻性模式等。然而，这些表达似乎并没有产生预期的结果。在上面的第一个示例中，我使用 ^9999$ 表示完全匹配。这允许您在同一表达式中添加具有通配符匹配项的特定匹配项。但是，您也可以混合使用这些类型的语句，如在列出的第二个示例中所见。
Regarding performance, I ran some minor tests against an existing table and found no differences between my variations. However, I imagine performance could be an issue with bigger databases, larger fields, greater record counts, and more complex filters.
关于性能，我对现有表进行了一些小测试，发现我的变体之间没有差异。但是，我认为性能可能是更大的数据库、更大的字段、更多的记录数和更复杂的过滤器的问题。
As always, use logic above as it makes sense.
一如既往，使用上面的逻辑，因为它是有道理的。
If you want to learn more about regular expressions, I recommend www.regular-expressions.info as a good reference site.
如果你想了解更多关于正则表达式的知识，我推荐 www.regular-expressions.info 作为一个很好的参考站点。
回答by Quassnoi
You can create an inline view or a temporary table, fill it with you values and issue this:
您可以创建内联视图或临时表，用您的值填充它并发出以下命令：
SELECT *
FROM fiberbox f
JOIN (
SELECT '%1740%' AS cond
UNION ALL
SELECT '%1938%' AS cond
UNION ALL
SELECT '%1940%' AS cond
) с
ON f.fiberBox LIKE cond
This, however, can return you multiple rows for a fiberboxthat is something like '1740, 1938', so this query can fit you better:
但是，这可以为fiberbox类似于 的a 返回多行'1740, 1938'，因此此查询更适合您：
SELECT *
FROM fiberbox f
WHERE EXISTS
(
SELECT 1
FROM (
SELECT '%1740%' AS cond
UNION ALL
SELECT '%1938%' AS cond
UNION ALL
SELECT '%1940%' AS cond
) с
WHERE f.fiberbox LIKE cond
)
回答by user136379
Regexp way with list of values
带有值列表的正则表达式方式
SELECT * FROM table WHERE field regexp concat_ws("|",
"111",
"222",
"333");
回答by gahooa
Sorry, there is no operation similar to LIKE INin mysql.
抱歉，LIKE IN在mysql中没有类似的操作。
If you want to use the LIKE operator without a join, you'll have to do it this way:
如果你想在没有连接的情况下使用 LIKE 运算符，你必须这样做：
(field LIKE value OR field LIKE value OR field LIKE value)
You know, MySQL will not optimize that query, FYI.
您知道，MySQL 不会优化该查询，仅供参考。
回答by Shaakir
Just note to anyone trying the REGEXP to use "LIKE IN" functionality.
请注意任何尝试 REGEXP 使用“LIKE IN”功能的人。
IN allows you to do:
IN 允许您执行以下操作：
field IN (
'val1',
'val2',
'val3'
)
In REGEXP this won't work
在 REGEXP 这行不通
REGEXP '
val1$|
val2$|
val3$
'
It has to be in one line like this:
它必须像这样在一行中：
REGEXP 'val1$|val2$|val3$'
回答by Shaakir
Flip operands
翻转操作数
'a,b,c' like '%'||field||'%'
回答by Raúl Moreno
Just a little tip:
只是一个小技巧：
I prefer to use the variant RLIKE(exactly the same command as REGEXP) as it sounds more like natural language, and is shorter; well, just 1 char.
我更喜欢使用变体RLIKE（与REGEXP完全相同的命令），因为它听起来更像自然语言，而且更短；好吧，只有 1 个字符。
The "R" prefix is for Reg. Exp., of course.
“R”前缀用于 Reg。经验，当然。
回答by Edmhs
This would be correct:
这是正确的：
SELECT * FROM table WHERE field regexp concat_ws("|",(
"111",
"222",
"333"
));
回答by Z.I.J
You can get desired result with help of Regular Expressions.
您可以在正则表达式的帮助下获得所需的结果。
SELECT fiberbox from fiberbox where fiberbox REGEXP '[1740|1938|1940]';
We can test the above query please click SQL fiddle
我们可以测试上面的查询请点击SQL fiddle
SELECT fiberbox from fiberbox where fiberbox REGEXP '[174019381940]';
We can test the above query please click SQL fiddle
我们可以测试上面的查询请点击SQL fiddle
