MySQL -- 全文检索(布尔全文检索)

modifier的值为in boolean mode的时候，可以使用布尔全文检索。在布尔全文检索中，有些字符在检索字符串的开头或结尾会有特殊含义。
在下面的示例中，+和-操作符表明在匹配的时候，单词必须存在和不存在。所以是检索含有"MySQL"但是不含有"YourSQL"的行。

mysql> SELECT 
    ->     *
    -> FROM
    ->     articles
    -> WHERE
    ->     MATCH (title , body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  6 | MySQL Security        | When configured properly, MySQL ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
+----+-----------------------+-------------------------------------+
5 rows in set (0.00 sec)

mysql>

布尔全文检索的操作符：

+ 

　　表示该word必须存在

- 

　　表示该word必须不被排除

(no operator) 

　　表示word是可选的，如果出现，其相关性会更高

@distance  

　　仅仅适用于innodb。测试两个或多个word出现的距离是否在distance的值之内，distance的单位是word个数。

　　下面测试的结果是多个单词之间最远的距离要在distance之间。(和姜承饶的书结果不一致)   

mysql> select * from articles where id=12;
+----+-----------------+--------------------------------------------------------------+
| id | title           | body                                                         |
+----+-----------------+--------------------------------------------------------------+
| 12 | BOOLEN Tutorial | word1,word2,word3,word4,word5,word6,word7,word8,word9,word10 |
+----+-----------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title , body) AGAINST ('"word2 word4 word9" @9' IN BOOLEAN MODE);
+----+-----------------+--------------------------------------------------------------+
| id | title           | body                                                         |
+----+-----------------+--------------------------------------------------------------+
| 12 | BOOLEN Tutorial | word1,word2,word3,word4,word5,word6,word7,word8,word9,word10 |
+----+-----------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title , body) AGAINST ('"word2 word4 word9" @8' IN BOOLEAN MODE);
+----+-----------------+--------------------------------------------------------------+
| id | title           | body                                                         |
+----+-----------------+--------------------------------------------------------------+
| 12 | BOOLEN Tutorial | word1,word2,word3,word4,word5,word6,word7,word8,word9,word10 |
+----+-----------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title , body) AGAINST ('"word2 word4 word9" @7' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title , body) AGAINST ('"word2 word4 word9" @6' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql>

　

> 

　　表示出现该单词时增加相关性

< 

　　表示出现该单词时降低相关性

 ( ) 

　　子表达式

~ 

　　表示允许出现该单词，但是出现时相关性为负

* 

　　表示以该单词开头的单词，如lik*表示lik、like等

” 

　　匹配双引号中的字面值，全文检索引擎会将双引号中的内容按照单词分开，非单词字符不用匹配，比如"test phrase"匹配"test,phrase"