Full Text Searches and Strings - Another Solution
(Page 3 of 4 )
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.
Next: 5.18 Performing Phrase Searches with a FULLTEXT Index >>
More Database Articles Articles
More By O'Reilly Media
|
 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.
|
|
