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 |
+----------+

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net