Database Reference
In-Depth Information
mysql>
SELECT COUNT(*) FROM kjv
->
WHERE MATCH(vtext) AGAINST('David Goliath');
+----------+
| COUNT(*) |
+----------+
| 898 |
+----------+
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 |
+----------+
An easier 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 permit you to exclude words by preceding each one with
a
-
character. The following queries select
kjv
rows containing the name David but not
Goliath, and vice versa:
mysql>
SELECT COUNT(*) FROM kjv
->
WHERE MATCH(vtext) AGAINST('+David -Goliath' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 892 |
+----------+
mysql>
SELECT COUNT(*) FROM kjv
->
WHERE MATCH(vtext) AGAINST('-David +Goliath' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
