问题描述
我目前正在使用如下所示的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子句和字段中的单独单词的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!