56.笔记 MySQL学习——布尔模式全文搜索
通过使用布尔模式的全文搜索,可以获得对多单词搜索的更多控制。要在搜索字符串后面加上IN BOOLEAN MODE。
布尔模式有以下特点:
l 即使找到的单词会出现在一半以上的行里,仍然会把它们搜搜出来
l 查询结果不再按相关程序排序
l 搜索可以要求短语里的所有单词都必须是按某种特定的顺序出现
l 可以对未包括在FULLTEX索引里的那些列,进行布尔模式全文搜索
如果要匹配一个短语,那么需要把它用双引号引起来。只有在行包含的那些单词及其顺序与短语里列出的内容一致时,才会被认为是匹配上了。
如下:
mysql> select * from apothegm wherematch(attribution,phrase) against ( '"bell book and candle"' inboolean mode);
+---------------------+------------------------+
| attribution | phrase |
+---------------------+------------------------+
| Miguel de Cervantes | Bell, book, andcandle |
+---------------------+------------------------+
1 row in set (0.00 sec)
mysql> select * from apothegm wherematch(attribution,phrase) against ( '"book bell and candle"' inboolean mode);
Empty set (0.00 sec)
对于布尔模式搜索,还可以为搜索字符串里的单词加上些修饰符,例如加号,减号。
搜索字符串’bell –candle’只会与那些包含了‘bell’但不包含candle的行
mysql> select * from apothegm wherematch(attribution,phrase) against ('bell');
+-----------------------+------------------------------------+
| attribution | phrase |
+-----------------------+------------------------------------+
| Alexander Graham Bell | Mr. Watson, comehere. I want you! |
| Miguel de Cervantes | Bell, book, and candle |
+-----------------------+------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from apothegm wherematch(attribution,phrase) against ('+bell -candle' in boolean mode);
+-----------------------+------------------------------------+
| attribution | phrase |
+-----------------------+------------------------------------+
| Alexander Graham Bell | Mr. Watson, comehere. I want you! |
+-----------------------+------------------------------------+
1 row in set (0.00 sec)