CODEWITHSUNDEEP
mysqlfull-text-search
風吟月
風吟月

Reputation: 243

mysql match against multiple words

Below is an example sql I am stuck with, it will not return a product named "iphone 4s", It returns 10 other result. Any help would be great thanks

1st sql example

SELECT * FROM products 
          WHERE match(desc) against('+iphone +4s' IN BOOLEAN MODE) LIMIT 10";

result: contains the words 'iphone' and '4s'

2nd sql example

SELECT * FROM products 
           WHERE match(desc) against('+iphone 4s' IN BOOLEAN MODE) LIMIT 10";

result: contains the words 'iphone', but rank rows higher if they also contain '4s'

3rd sql example

SELECT * FROM products 
           WHERE match(desc) against('iphone 4s' IN BOOLEAN MODE) LIMIT 10";

result: contains the words 'iphone' or '4s'

What I want to search is 'iphone 4s', but it comes with other result, like 'iphone is good, but 4s...', 'new iphone 5 has published...',...

Can anyone help me to solve it? thanks.

Upvotes: 16

Views: 23154

Answers (2)

valex
valex

Reputation: 24134

Use REGEXP

SELECT * FROM products 
WHERE desc REGEXP 'iphone[[. .]]*4s'
LIMIT 10;

SQLFiddle demo

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

To match an exact phrase, just use double quotes to surround the phrase to match;

SELECT * 
FROM products 
WHERE MATCH(desc) 
      AGAINST('"iphone 4s"' IN BOOLEAN MODE) 
LIMIT 10

More info at the manual pages.

Upvotes: 19

Related Questions