MYSQL FULLTEXT search for multi kewords

Question

I have field called keywords and the stored info is a keywords separated with |

For example:

sky|Banana is good|apple|apple is red|sky is blue|green

What i'm try to do is search in that field and find results that contains at least one of the keywords.

For that i used MATCH AGAINST in BOOLEAN MODE

SELECT * FROM mytable 
WHERE MATCH(kewords) 
      AGAINST ('+sky is blue +Banana is good +otherword' IN BOOLEAN MODE)

But results is goes wrong, for example it will select results that contains +sky is blue and also with a single word sky

I know how to solve that in PHP, but i'm looking for MYSQL solution, i thought Match AGAINST is what i'm looking for.

Answer 1

One way to do this is with LIKE, fencing the search words with the delimiter:

SELECT * FROM mytable
WHERE CONCAT('|', keywords, '|') LIKE '%|sky is blue|%' OR
      CONCAT('|', keywords, '|')LIKE '%|Banana is good|%' OR
      CONCAT('|', keywords, '|') LIKE '%|otherword|%' 

This ensures that the search phrase must match exactly (i.e. sky is blue will not match sky).

If you want all the phrases to be present (instead of any), just change the OR to AND.