Full Text Searches and Strings - 5.17 Requiring or Excluding FULLTEXT Search Words

(Page 2 of 4 )

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

Comments On: Full Text Searches and Strings

· This article is an excerpt from the book "MySQL Cookbook, Second Edition," published...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.