mysql like 表字段,Mysql LIKE子句和字段中的单独单词

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

select * from table where title like %search_term%

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.

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?

解决方案

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

select *

from tbl

where

title REGEXP CONCAT('[[:<: replace 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 burger>:]].*[[:<:>:]]')

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.