mysql boolean 检索_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"