MySQL 'LIKE ('%this%' OR '%that%') 和 something=else' 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6526949/
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
'LIKE ('%this%' OR '%that%') and something=else' not working
提问by Kevin Ohashi
I have a select query where I am trying to search strings for multiple patterns
我有一个选择查询，我试图在其中搜索多个模式的字符串
LIKE ('%this%' or '%that%' ) and something=else
Returns zero results
返回零结果
However
然而
LIKE '%this%' and something=else
returns results and
返回结果和
LIKE '%that%' and something=else
returns result
返回结果
Is it possible to get all my results into one query? If a string matches both, how will it handle that?
是否可以将我的所有结果合并到一个查询中？如果一个字符串两者都匹配，它将如何处理？
回答by Bohemian
It would be nice if you could, but you can't use that syntax in SQL.
如果可以的话会很好，但是您不能在 SQL 中使用该语法。
Try this:
尝试这个：
(column1 LIKE '%this%' OR column1 LIKE '%that%') AND something = else
Note the use of brackets! You need them around the ORexpression.
Without brackets, it will be parsed as A OR (B AND C),which won't give you the results you expect.
注意括号的使用！您需要它们围绕OR表达式。
如果没有括号，它将被解析为A OR (B AND C)，这不会给您预期的结果。
回答by Nijat Asad
Instead of using `LIKE` use `REGEXP`.
For example:
REGEXP 'THIS|THAT'
For example:
REGEXP 'THIS|THAT'
mysql> SELECT 'pi' REGEXP 'pi|apa'; -> 1
mysql> SELECT 'axe' REGEXP 'pi|apa'; -> 0
mysql> SELECT 'apa' REGEXP 'pi|apa'; -> 1
mysql> SELECT 'apa' REGEXP '^(pi|apa)$'; -> 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$'; -> 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$'; -> 0
Refer:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html
回答by Dirk
Try something like:
尝试类似：
WHERE (column LIKE '%this%' OR column LIKE '%that%') AND something = else
WHERE (column LIKE '%this%' OR column LIKE '%that%') AND something = else
回答by Ryan
Break out the LIKEclauses into 2 separate statements, i.e.:
将LIKE子句分成 2 个单独的语句，即：
(fieldname1 LIKE '%this%' or fieldname1 LIKE '%that%' ) and something=else
回答by josh.trow
Do you have something against splitting it up?
你有什么反对把它分开吗？
...FROM <blah>
WHERE
(fieldA LIKE '%THIS%' OR fieldA LIKE '%THAT%')
AND something = else
回答by BumbleB2na
Have you tried:
你有没有尝试过：
(column LIKE '%this%' and something=else) or (column LIKE '%that%' and something=else)
回答by Michal_Szulc
I know it's a bit old question but still people try to find efficient solution so instead you should use FULLTEXT index(it's available from MySQL 5.6.4).
我知道这是一个有点老的问题，但人们仍然试图找到有效的解决方案，因此您应该使用FULLTEXT 索引（它可从 MySQL 5.6.4 获得）。
Query on table with +35mil records by triple likein where block took ~2.5sbut after adding index on these fields and using BOOLEAN MODEinside match ... against ...it took only 0.05s.
上表查询与三联+ 35mil记录like在块了〜2.5秒，但在这些领域增加指标，并使用后BOOLEAN MODE里面match ... against ...只用了0.05秒。
