4.14 Requiring or Excluding FULLTEXT Search Words
4.14.1 Problem
You want to specifically require or disallow words in a
FULLTEXT search.
4.14.2 Solution
Use a Boolean mode search.
4.14.3 Discussion
Normally, FULLTEXT searches return records that
contain any of the words in the search string, even if some of them
are missing. For example, the following query finds records 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 records that contain
both words. One way to do this is to rewrite the query 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 |
+----------+
As of MySQL 4.0.1, another way to require multiple words is with a
Boolean mode search. To do this, precede each word in the search
string with 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 with a - character. The
following queries select kjv records containing
the name David but not Goliath, or vice versa:
mysql> SELECT COUNT(*) FROM kjv
-> WHERE MATCH(vtext) AGAINST('+David -Goliath' IN BOOLEAN MODE)
+----------+
| COUNT(*) |
+----------+
| 928 |
+----------+
mysql> SELECT COUNT(*) FROM kjv
-> WHERE MATCH(vtext) AGAINST('-David +Goliath' IN BOOLEAN MODE)
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
Another useful special character in Boolean searches is
*; when appended to a search word, it
acts as a wildcard operator. The following query finds records
containing not only whirl, but also words such as
whirls, whirleth, and
whirlwind:
mysql> SELECT COUNT(*) FROM kjv
-> WHERE MATCH(vtext) AGAINST('whirl*' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 28 |
+----------+
|
