MySQL Cookbook, 2nd Edition

Requiring or Excluding FULLTEXT Search Words

Problem

You want to specifically require or disallow words in a FULLTEXT search.

Solution

Use a Boolean mode search.

Discussion

Normally, FULLTEXT searches return rows that contain any of the words in the search string, even if some of them are missing. For example, the following statement finds rows that contain either of the names David or Goliath:

mysql>SELECT COUNT(*) FROM kjv
    -> WHERE MATCH(vtext) AGAINST('David Goliath');
+----------+
| COUNT(*) |
+----------+
|      934 |
+----------+

This behavior is undesirable if you want only rows that contain both words. One way to do this is to rewrite the statement to look for each word separately and join the conditions with AND:

mysql>SELECT COUNT(*) FROM kjv
    -> WHERE MATCH(vtext) AGAINST('David')
    -> AND MATCH(vtext) AGAINST('Goliath');
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

Another way to require multiple words is with a Boolean mode search. To do this, precede each word in the search string by a + character and add IN BOOLEAN MODE after the string:

mysql>SELECT COUNT(*) FROM kjv
    -> WHERE MATCH(vtext) AGAINST('+David +Goliath' IN BOOLEAN MODE)
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

Boolean mode searches also allow you to exclude words. Just precede any disallowed word by a - character. The following queries select kjv rows containing the name David but not Goliath, or vice versa:

mysql>SELECT COUNT(*) FROM kjv
    -> WHERE MATCH(vtext) AGAINST('+David -Goliath' IN BOOLEAN ...