Full Text Searches and Strings - 5.17 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 |
  +----------+

Next: Another Solution >>
 

More Database Articles Articles
More By O'Reilly Media