MySQL SELECT LIKE 或 REGEXP 匹配一条记录中的多个单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9099469/
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 SELECT LIKE or REGEXP to match multiple words in one record
提问by Alessio Firenze
The field table.namecontains 'Stylus Photo 2100' and with the following query
场table。name包含“Stylus Photo 2100”和以下查询
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus 2100%'
I get no results. Of course i would if i searched
我没有结果。当然，如果我搜索
SELECT `name` FROM `table` WHERE `name` LIKE '%Photo 2100%'
How can I select the record by searching 'Stylus 2100' ?
如何通过搜索“Stylus 2100”来选择记录？
Thanks
谢谢
回答by SERPRO
Well if you know the order of your words.. you can use:
好吧，如果您知道单词的顺序.. 您可以使用：
SELECT `name` FROM `table` WHERE `name` REGEXP 'Stylus.+2100'
Also you can use:
你也可以使用：
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%' AND `name` LIKE '%2100%'
回答by Ondrej Bozek
I think that the best solution would be to use Regular expressions. It's cleanest and probably the most effective. Regular Expressions are supported in all commonly used DB engines.
我认为最好的解决方案是使用正则表达式。它是最干净的，也可能是最有效的。所有常用的数据库引擎都支持正则表达式。
In MySql there is RLIKEoperator so your query would be something like:SELECT * FROM buckets WHERE bucketname RLIKE 'Stylus|2100'
I'm not very strong in regexp so I hope the expression is ok.
在 MySql 中有RLIKE运算符，因此您的查询将类似于：SELECT * FROM buckets WHERE bucketname RLIKE 'Stylus|2100'
我不是很擅长正则表达式，所以我希望表达式没问题。
Edit
The RegExp should rather be:
编辑
RegExp 应该是：
SELECT * FROM buckets WHERE bucketname RLIKE '(?=.*Stylus)(?=.*2100)'
More on MySql regexp support:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
更多关于 MySql 正则表达式支持：http:
//dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
回答by mdprotacio
You can just replace each space with %
您可以将每个空格替换为 %
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%2100%'
回答by Martin
The correct solution is a FullText Search (if you can use it) https://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
正确的解决方案是全文搜索（如果可以使用）https://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
This nearly does what you want:
这几乎可以满足您的要求：
SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus|2100)+.*(Stylus|2100)+';
SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus|2100|photo)+.*(Stylus|2100|photo)+.*(Stylus|2100|photo)+.*';
But this will also match "210021002100" which is not great.
但这也将匹配“210021002100”，这不是很好。
回答by Mohideen bin Mohammed
you need to do something like this,
你需要做这样的事情，
SELECT * FROM buckets WHERE bucketname RLIKE 'Stylus.*2100';
or
或者
SELECT * FROM buckets WHERE bucketname RLIKE '(Stylus)+.*(2100)+';
回答by Peter Darmis
Assuming that your search is stylus photo 2100. Try the following example is using RLIKE.
假设您的搜索是stylus photo 2100. 尝试以下示例使用RLIKE.
SELECT * FROM `buckets` WHERE `bucketname` RLIKE REPLACE('stylus photo 2100', ' ', '+.*');
EDIT
编辑
Another way is to use FULLTEXTindex on bucketnameand MATCH ... AGAINSTsyntax in your SELECTstatement. So to re-write the above example...
另一种方法是在语句中使用FULLTEXT索引bucketname和MATCH ... AGAINST语法SELECT。所以要重写上面的例子......
SELECT * FROM `buckets` WHERE MATCH(`bucketname`) AGAINST (REPLACE('stylus photo 2100', ' ', ','));
回答by Gopalakrishnan
SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus % 2100%'
