在 MySQL 的 LIKE Query 中使用 OR 来比较多个字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11106888/
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
Using OR in LIKE Query in MySQL to compare multiple fields
提问by RCNeil
I always thought that you could use ORin a LIKEstatment to query things in MySQL. So, if I wanted to compare multiple fields in a row to 1 keyword or term:
我一直认为您可以OR在LIKE语句中使用在 MySQL 中查询事物。因此，如果我想将一行中的多个字段与 1 个关键字或术语进行比较：
SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword%';
and if I had an array of words to compare:
如果我有一组要比较的单词：
SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword1%'
AND Column1 OR Column2 LIKE '%keyword2%';
I don't believe that syntax is correct, however. Is there an efficient method of writing this aside from something like:
但是，我不相信语法是正确的。除了以下内容之外，是否有一种有效的方法可以编写此内容：
SELECT * FROM MyTable WHERE Column1 LIKE '%keyword1%' OR Column2 LIKE
'%keyword1%' AND Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%';
Am I going about this correctly?
我这样做正确吗？
回答by Sashi Kant
Use this::
用这个：：
SELECT * FROM MyTable WHERE (Column1 LIKE '%keyword1%' OR Column2 LIKE
'%keyword1%') AND (Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%');
回答by Mazrick
The closest to the syntax you are desiring is:
最接近您想要的语法是：
SELECT * FROM MyTable
WHERE (CONCAT(Column1, Column2) LIKE '%keyword1%')
AND (CONCAT(Column1, Column2) LIKE '%keyword2%')
Note: that the "%" at the start of your search string precludes the use of indexes. If there are any large number of records to search, it would be best to rethink the implementation.
注意：搜索字符串开头的“%”排除了索引的使用。如果要搜索大量记录，最好重新考虑实现。
If you cannot guarantee that each column is not NULL, then use CONCAT_WS instead:
如果您不能保证每一列都不是 NULL，那么请改用 CONCAT_WS：
SELECT * FROM MyTable
WHERE (CONCAT_WS("-", Column1, Column2) LIKE '%keyword1%')
AND (CONCAT_WS("-", Column1, Column2) LIKE '%keyword2%')
This CONCAT_WS solution also has the possible benefit of assuring that matches of your "keyword" where in only in Column1 OR Column2, if you select a separator character that is never present in your keywords.
这个 CONCAT_WS 解决方案还有一个可能的好处，那就是确保你的“关键字”只在 Column1 或 Column2 中匹配，如果你选择了一个从不出现在你的关键字中的分隔符。
