Mysql LIKE子句和字段中的单独单词 [英] Mysql LIKE clause and separate words in a field

问题描述

我目前正在使用如下所示的mysql语句来搜索帖子标题.

I currently use a mysql statement like the one below to search post titles.

select * from table where title like %search_term%

但是问题是，如果标题类似于:Acme launches 5 pound burger并且用户搜索了Acme，它将返回结果.但是，如果用户搜索Acme burger或Acme 5 pound，则不会返回任何内容.

But problem is, if the title were like: Acme launches 5 pound burger and a user searched for Acme, it'll return a result. But if a user searched for Acme burger or Acme 5 pound, it'll return nothing.

当用户搜索多个单词时，是否有办法使它返回结果? LIKE是在这里使用正确的东西还是还有其他可以使用的东西?

Is there a way to get it to return results when a users searches for more than one word? Is LIKE the correct thing to use here or is there something else that can be used?

推荐答案

您可以使用REGEXP来匹配搜索字符串中的任何单词:

You could use a REGEXP to match any of the words in your search string:

select *
from tbl
where
  title REGEXP CONCAT('[[:<:]](', REPLACE('Acme burger', ' ', '|'), ')[[:>:]]')

请注意，这将不是很有效.请参见小提琴此处.

Please notice that this will not be very efficient. See fiddle here.

如果您需要匹配字符串中的每个单词，则可以使用如下查询:

If you need to match every word in your string, you could use a query like this:

select *
from tbl
where
  title REGEXP CONCAT('[[:<:]]', REPLACE('Acme burger', ' ', '[[:>:]].*[[:<:]]'), '[[:>:]]')

小提琴此处.但是单词必须以正确的顺序排列(例如，"Acme burger"将匹配，"burger Acme"将不匹配).有一个REGEXP可以按任何顺序匹配每个单词，但是MySql不支持它，除非您安装支持Perl regexp的UDF.

Fiddle here. But words have to be in the correct order (es. 'Acme burger' will match, 'burger Acme' won't). There's a REGEXP to match every word in any order, but it is not supported by MySql, unless you install an UDF that supports Perl regexp.

这篇关于Mysql LIKE子句和字段中的单独单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！