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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.