MySQL -- 全文检索(自然语言全文检索)

 

自然语言全文本检索
缺省或者modifier被设置为in natural language mode,都是进行自然语言检索。对于表中的每一行，match()都会返回一个关联值。

mysql> CREATE TABLE articles (
    ->     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     title VARCHAR(200),
    ->     body TEXT,
    ->     FULLTEXT ( title , body )
    -> )  ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO articles (title,body) VALUES
    ->         ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ->         ('How To Use MySQL Well','After you went through a ...'),
    ->         ('Optimizing MySQL','In this tutorial we will show ...'),
    ->         ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ->         ('MySQL vs. YourSQL','In the following database comparison ...'),
    ->         ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from articles
    -> where match(title,body)
    -> against('database' in natural language mode);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

缺省情况下，检索是大小写不敏感的。如果要想进行大小写敏感的检索，对于索引的列要进行二进制collation。比如字符集类型为latin1的列可以修改为Latin1_bin。
当match()被作为where子句的时候，返回的行会被自动排序，根据检索关联度进行排序。

mysql> INSERT INTO articles (title,body) VALUES
    ->         ('oracle Tutorial','DBMS stands for DataBase ...DataBase');
Query OK, 1 row affected (0.00 sec)

mysql> select * from articles
    ->  where match(title,body)
    ->  against('database' in natural language mode);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  7 | oracle Tutorial   | DBMS stands for DataBase ...DataBase     |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

mysql>

　　

可以查看一下匹配的次数：

#使用索引
mysql> SELECT 
    ->     COUNT(*)
    -> FROM
    ->     articles
    -> WHERE
    ->     MATCH (title , body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> 

#使用全表扫描
mysql> SELECT 
    ->     COUNT(IF(MATCH (title , body) AGAINST ('database' IN NATURAL LANGUAGE MODE),
    ->         1,
    ->         NULL)) AS count
    -> FROM
    ->     articles;
+-------+
| count |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

mysql> 

对于自然语言全文本检索，match()中的列名必须和全文索引中的列相同。上例中，如果想对title或body分开检索，就需要分别创建全文索引。

 

上面的例子中，基本展示了如何使用match()。返回的结果根据关联值的降序排列。
下面的例子，展示如何显式输出关联值得大小。返回的行不是有序的，因为select语句既不包含where也没有order by。

mysql> SELECT 
    ->     id,
    ->     MATCH (title , body) AGAINST ('database' IN NATURAL LANGUAGE MODE) AS score
    -> FROM
    ->     articles;
+----+---------------------+
| id | score               |
+----+---------------------+
|  1 | 0.22764469683170319 |
|  2 |                   0 |
|  3 |                   0 |
|  4 |                   0 |
|  5 | 0.22764469683170319 |
|  6 |                   0 |
+----+---------------------+
6 rows in set (0.00 sec)

mysql> 

 

下面的例子更复杂，查询返回关联值得具体值，同时进行降序排序。为了实现这个目的，使用了match()两次。这样的语句不会有额外的开销，优化器会注意到两次match()调用是一样的，所以只会执行全文检索一次。

mysql> SELECT 
    ->     id,
    ->     body,
    ->     MATCH (title , body) AGAINST ('Security implications of running MySQL as root' IN NATURAL LANGUAGE MODE) AS score
    -> FROM
    ->     articles
    -> WHERE
    ->     MATCH (title , body) AGAINST ('Security implications of running MySQL as root' IN NATURAL LANGUAGE MODE);
+----+------------------------------------------+----------------------------+
| id | body                                     | score                      |
+----+------------------------------------------+----------------------------+
|  4 | 1. Never run mysqld as root. 2. ...      |         0.6055193543434143 |
|  6 | When configured properly, MySQL ...      |         0.6055193543434143 |
|  1 | DBMS stands for DataBase ...             | 0.000000001885928302414186 |
|  2 | After you went through a ...             | 0.000000001885928302414186 |
|  3 | In this tutorial we will show ...        | 0.000000001885928302414186 |
|  5 | In the following database comparison ... | 0.000000001885928302414186 |
+----+------------------------------------------+----------------------------+
6 rows in set (0.00 sec)

mysql> 

用双引号引起来的词组，检索匹配的结果只能是双引号中的字面值。全文检索会将双引号中的内容分解成单词，然后执行检索匹配。非单词字符是不需要匹配的，只是按照其中的单词顺序进行匹配，比如"test phrase"和"test, phrase"是匹配的。
全文检索会将字母、数字、下划线的组合当成一个word。但是也会将'当成一个word序列，不过一行只能有一个'，比如会将aaa'bbb当成一个单词，但是aaa''bbb就不是一个单词了，而是两个。如果'放在开头或者结果，会被丢弃。

内嵌的文本解释器决定单词的开头和结尾，根据delimiter符号进行判断，比如逗号、空格、点号。如果不是根据delimiter分割的，比如中文，解释器就无法判断出单词的开头和结尾了。
所以，用户必须使用某些delimiter对文本进行处理后再检索。在5.7.6中可以使用插件ngram解释器来实现对中文、日文、韩文的支持，或者使用MeCab解释器来支持日文。

也可以自己编写插件解释器。示例代码位于plugin/fulltext目录。

在全文检索中，有些单词是被忽略的：
--太短的单词。默认最小长度是3个字符(innodb)、4个字符(myisam)。可以设置innodb中的innodb_ft_min_token_size、myisam中的ft_min_word_len
--stopword中的单词会被忽略。stopword是指那些类似the、some一样太普通以致被认为是没有什么语义值得单词。有一个内嵌stopword列表。也可以重新定义。

 每个正确的单词在查询时都被会加权，根据其在集合和查询中的重要性。所以出现频率越高，权重就越低。单词的权重会被用来计算行的关联值。

全文检索如果本生行数就比较少，可能检索不出正确的结果。