MYSQL 搜索/通配符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8168333/
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 Search/Wildcards
提问by user1051894
I trying to search a MySQL database using PHP, The search works fine but I'm looking for a little help with wildcards:
我试图使用 PHP 搜索 MySQL 数据库，搜索工作正常，但我正在寻找通配符的一些帮助：
The data in the field (Model) I am searching is: "A4" (215 results)
我搜索的字段（Model）中的数据是：“A4”（215个结果）
My search string is:
我的搜索字符串是：
SELECT * FROM `temp_comp`.`mvl` WHERE `Model` LIKE '%A4 Avant%'
Is there a way I can still search 'A4 Avant' but this will return any fields that contain 'A4' or 'Avant'
有没有办法我仍然可以搜索“A4 Avant”，但这将返回包含“A4”或“Avant”的任何字段
The search term is taken from a csv file so I wanted to try and do this without having to split the two words first and search for 'A4' and/or 'Avant', I have tried the following but get no results:
搜索词取自一个 csv 文件，所以我想尝试这样做，而不必先拆分这两个词并搜索“A4”和/或“Avant”，我尝试了以下操作但没有得到任何结果：
SELECT * FROM `temp_comp`.`mvl` WHERE `Model` LIKE '%A4%Avant%'
As you may have guessed this is not my normal field so any help would be very much appreciated.
您可能已经猜到这不是我的常规领域，因此非常感谢任何帮助。
回答by Johan
SELECT * FROM temp_comp.mvl WHERE (Model LIKE '%A4%') OR (Model LIKE '%Avant%')
If you want to avoid splitting up the test you can use a regexp:
如果您想避免拆分测试，您可以使用正则表达式：
SELECT * FROM temp_comp.mvl WHERE Model REGEXP 'A4|Avant' <<-- Either '%A4% or %Avant%
SELECT * FROM temp_comp.mvl WHERE Model REGEXP 'A4*Avant' <<-- '%A4%Avant%
See the reference
见参考
回答by Jim H.
MySQL doesn't have a built-in way to split strings in a query. Splitting them beforehand and then adding n predicates to the where clause is trivial.
MySQL 没有内置的方法来拆分查询中的字符串。事先拆分它们，然后将 n 个谓词添加到 where 子句中是微不足道的。
SELECT * FROM `temp_comp`.`mvl`
WHERE `Model` LIKE '%A4%' OR `Model` LIKE '%Avant%';
回答by itsols
Here's how you can achieve this.
以下是您如何实现这一目标。
Modify your query like this:
像这样修改您的查询：
SELECT * FROM `temp_comp`.`mvl` WHERE `Model` LIKE '%A4%' AND Model LIKE '%Avant%'
The above example will look for elements of the search term in the query in such a manner that they should all be present in the record. And if you want to search for records with either of the search terms, you can replace the word ANDwith ORand it does the job.
上面的示例将在查询中查找搜索词的元素，以便它们都应该出现在记录中。如果你想与任何搜索词的搜索记录，可以替换单词AND与OR和它的工作。
And ideally, you would follow these steps to prepare for the query:
理想情况下，您将按照以下步骤准备查询：
- Take the input string to search.
- Split it by spaces so you have an array of words
- Loop through the array and build your search string.
- Use the search string in your query.
- 取输入字符串进行搜索。
- 用空格分割它，这样你就有了一个单词数组
- 遍历数组并构建搜索字符串。
- 在查询中使用搜索字符串。
