MySQL选择LIKE或REGEXP以匹配一条记录中的多个单词

The field table.name contains 'Stylus Photo 2100' and with the following query

字段表.name包含“手写笔照片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

谢谢

5 个解决方案

#1

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%'

#2

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.

我认为最好的解决办法是使用正则表达式。它是最清洁的，可能也是最有效的。所有常用的DB引擎都支持正则表达式。

In MySql there is RLIKE operator 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 bucket，而bucketname RLIKE 'Stylus|2100'我在regexp中不是很强大，所以我希望表达式是正确的。

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 regexp支持的内容:http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp。

#3

You can just replace each space with %

您可以用%替换每个空格

SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus%2100%'

#4

have a look on http://www.techonthenet.com/sql/like.php

查看http://www.techonthenet.com/sql/like.php

with LIKE '%Stylus 2100%' you ask a string containing exactly 'Stylus 2100', and 'Stylus Photo 2100' do not contain that string, there's 'Photo' inside;

像'%Stylus 2100%'你会问一个恰好包含'Stylus 2100'的字符串，而'Stylus Photo 2100'不包含那个字符串，里面有'Photo';

#5

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”，这并不好。

#1