Boolean mode searches also allow you to exclude words.
mysql>
mysql> CREATE TABLE mytable
-> (
-> bsect ENUM('O','N') NOT NULL, # book section (testament)
-> bname VARCHAR(20) NOT NULL, # book name
-> bnum TINYINT UNSIGNED NOT NULL, # book number
-> cnum TINYINT UNSIGNED NOT NULL, # chapter number
-> vnum TINYINT UNSIGNED NOT NULL, # verse number
-> vtext TEXT NOT NULL # text of verse
-> ) TYPE = MyISAM;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> ALTER TABLE mytable ADD FULLTEXT (vtext);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT COUNT(*) FROM mytable WHERE MATCH(vtext) AGAINST('+David -Goliath' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT COUNT(*) FROM mytable WHERE MATCH(vtext) AGAINST('-David +Goliath' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)
Related examples in the same category