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 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 statement finds rows 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 | +----------+
For a complete list of Boolean FULLTEXT operators, see the MySQL Reference Manual.
