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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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>

布尔全文检索的操作符：

?

1

+

　　表示该word必须存在

?

1

-

　　表示该word必须不被排除

?

1	(no operator)

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

?

1

@distance

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

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

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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>

　

?

1

>

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

?

1

<

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

?

1

( )

　　子表达式

?

1

~

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

?

1

*

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

?

1

”

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