MySQL -- 全文检索(自然语言全文检索)
2017-09-12 16:33 abce 阅读(1441) 评论(0) 编辑 收藏 举报
自然语言全文本检索
缺省或者modifier被设置为in natural language mode,都是进行自然语言检索。对于表中的每一行,match()都会返回一个关联值。
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 | 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子句的时候,返回的行会被自动排序,根据检索关联度进行排序。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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> |
可以查看一下匹配的次数:
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 -> 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。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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()调用是一样的,所以只会执行全文检索一次。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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列表。也可以重新定义。
每个正确的单词在查询时都被会加权,根据其在集合和查询中的重要性。所以出现频率越高,权重就越低。单词的权重会被用来计算行的关联值。
全文检索如果本生行数就比较少,可能检索不出正确的结果。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· .NET10 - 预览版1新功能体验(一)