如何编写包含 A 的 MySQL 查询（“a”或“b”）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6121630/
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
How to write MySQL query where A contains ( "a" or "b" )
提问by Hao
I must use this format where A operand B. A is the field; I want B to be either "text 1" or "text 2", so if A has data like "text 1 texttext" or "texttext 2" , the query will have result.
我必须使用这种格式where A operand B。A 是场；我希望 B 是 "text 1" 或 "text 2"，所以如果 A 有像 "text 1 texttext" 或 "texttext 2" 这样的数据，查询就会有结果。
But how do I write this? Does MySQL support something like
但是我该怎么写呢？MySQL是否支持类似的东西
where A contains ('text 1' OR 'text 2')? `
回答by Spudley
Two options:
两种选择：
Use the
LIKEkeyword, along with percent signs in the stringselect * from table where field like '%a%' or field like '%b%'.(note: If your search string contains percent signs, you'll need to escape them)
If you're looking for more a complex combination of strings than you've specified in your example, you could regular expressions (regex):
See the MySQL manual for more on how to use them: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
使用
LIKE关键字以及字符串中的百分号select * from table where field like '%a%' or field like '%b%'.（注意：如果您的搜索字符串包含百分号，则需要对它们进行转义）
如果您正在寻找比您在示例中指定的更复杂的字符串组合，您可以使用正则表达式（regex）：
有关如何使用它们的更多信息，请参阅 MySQL 手册：http: //dev.mysql.com/doc/refman/5.1/en/regexp.html
Of these, using LIKEis the most usual solution -- it's standard SQL, and in common use. Regex is less commonly used but much more powerful.
其中， usingLIKE是最常用的解决方案——它是标准 SQL，并且很常用。正则表达式不太常用，但功能更强大。
Note that whichever option you go with, you need to be aware of possible performance implications. Searching for sub-strings like this will mean that the query will have to scan the entire table. If you have a large table, this could make for a very slow query, and no amount of indexing is going to help.
请注意，无论选择哪个选项，您都需要了解可能的性能影响。像这样搜索子字符串意味着查询必须扫描整个表。如果您有一个大表，这可能会导致查询速度非常慢，并且没有多少索引会有所帮助。
If this is an issue for you, and you'r going to need to search for the same things over and over, you may prefer to do something like adding a flag field to the table which specifies that the string field contains the relevant sub-strings. If you keep this flag field up-to-date when you insert of update a record, you could simply query the flag when you want to search. This can be indexed, and would make your query much much quicker. Whether it's worth the effort to do that is up to you, it'll depend on how bad the performance is using LIKE.
如果这对您来说是个问题，并且您将需要一遍又一遍地搜索相同的内容，您可能更喜欢做一些事情，例如向表中添加一个标志字段，指定字符串字段包含相关子字符串。如果在插入或更新记录时保持此标志字段是最新的，则可以在要搜索时简单地查询该标志。这可以被索引，并且会使您的查询更快。是否值得为此付出努力取决于您，这取决于使用LIKE.
回答by alex
You can write your query like so:
您可以像这样编写查询：
SELECT * FROM MyTable WHERE (A LIKE '%text1%' OR A LIKE '%text2%')
The %is a wildcard, meaning that it searches for all rows where column A contains either text1or text2
该%是通配符，这意味着搜索，其中A列中包含了要么全部行文本1或文本2
回答by tanovellino
I've used most of the times the LIKE option and it works just fine. I just like to share one of my latest experiences where I used INSTR function. Regardless of the reasons that made me consider this options, what's important here is that the use is similar: instr(A, 'text 1') > 0 or instr(A, 'text 2') > 0 Another option could be: (instr(A, 'text 1') + instr(A, 'text 2')) > 0
我大部分时间都使用 LIKE 选项，它工作得很好。我只想分享我使用 INSTR 函数的最新经验之一。不管让我考虑这个选项的原因是什么，这里重要的是用法是相似的： instr(A, 'text 1') > 0 或 instr(A, 'text 2') > 0 另一个选项可能是：（ instr(A, 'text 1') + instr(A, 'text 2')) > 0
I'd go with the LIKE '%text1%' OR LIKE '%text2%' option... if not hope this other option helps
我会选择 LIKE '%text1%' OR LIKE '%text2%' 选项......如果不是希望这个其他选项有帮助
回答by Oscar Fernando
I user for searching the size of motorcycle :
我用于搜索摩托车尺寸的用户：
For example : Data = "Tire cycle size 70 / 90 - 16"
例如：数据 =“轮胎循环尺寸 70 / 90 - 16”
i can search with "70 90 16"
我可以用“70 90 16”搜索
$searchTerms = preg_split("/[\s,-\/?!]+/", $itemName);
foreach ($searchTerms as $term) {
$term = trim($term);
if (!empty($term)) {
$searchTermBits[] = "name LIKE '%$term%'";
}
}
$query = "SELECT * FROM item WHERE " .implode(' AND ', $searchTermBits);
