MySQL 全文检索的使用示例

吾爱主题阅读：126 2024-04-02 08:03:06 评论：0

1. 环境准备

MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。 MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。本文使用的MySQL 版本是5.7.22，InnoDB数据库引擎。

所以这里需要MySQL的版本大于5.7.6

1 2 3 4 5 6 7 8 -- 查看mysql的版本 mysql> select version(); + -----------+ | version() | + -----------+ | 5.7.33 | + -----------+ 1 row in set (0.02 sec)

在 mysql 配置文件中添加分词以及最小词语长度（如果已经配置可以忽略）

ft_min_word_len 最小字符长度默认为 4，在英文条件下确实比较合理中文情况下需要修改；

ngram_token_size 分词的最小长度举个例子不同长度对你好世界的分词

1 2 3 4 n=1: '你' , '好' , '世' , '界' n=2: '你好' , '好世' , '世界' n=3: '你好世' , '好世界' n=4: '你好世界'

1 2 3 4 5 6 7 8 9 10 11 12 # /etc/mysql/mysql.conf.d/mysqld.cnf ft_min_word_len = 2 ngram_token_size = 2 # 如果没有则新增配置 echo 'ft_min_word_len = 2 ngram_token_size = 2' >> mysqld.cnf # 重启服务 /etc/init .d /mysql restart

2. 数据准备

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 -- mysql 于全文检索的demo mysql> CREATE TABLE `articles` ( `id` int (10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar (50) DEFAULT NULL COMMENT '主题' , `content` longtext NOT NULL COMMENT '内容' , PRIMARY KEY (`id`), FULLTEXT KEY `title_content_index` (`content`,`title`) /*!50100 WITH PARSER `ngram` */ ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.20 sec) mysql> INSERT INTO articles (`title`, `content`) VALUES ( '如果' , '今生今世永不再将你想起 除了 除了在有些个 因落泪而湿润的夜里如果 如果你愿意' ), ( '爱情' , '有一天路标迁了希望你能从容 有一天桥墩断了希望你能渡越 有一天栋梁倒了希望你能坚强 有一天期待蔫了希望你能理解' ), ( '远和近' , '你一会看我 一会看云 我觉得 你看我时很远 你看云时很近' ), ( '断章' , '你站在桥上看风景， 看风景人在楼上看你。 明月装饰了你的窗子， 你装饰了别人的梦。' ), ( '独语' , '我向你倾吐思念 你如石像 沉默不应 如果沉默是你的悲抑 你知道这悲抑 最伤我心' ); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * from articles where match(content, title) against( '风景' in NATURAL LANGUAGE MODE) LIMIT 10; + ----+--------+--------------------------------------------------------------------------------------------------------------------------+ | id | title | content | + ----+--------+--------------------------------------------------------------------------------------------------------------------------+ | 10 | 断章 | 你站在桥上看风景， 看风景人在楼上看你。 明月装饰了你的窗子， 你装饰了别人的梦。 | + ----+--------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec)

3. 开始表演

自然语言模式(NATURAL LANGUAGE MODE)

自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。

布隆模式(BOOLEAN MODE)

BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

查询扩展(QUERY EXPANSION)

查询的结果不仅匹配出结果同时可以联想出其他你需要的结果。（类似关联查询，但是官网推荐仅支持短语查询否则会出现很多脏数据）

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 32 33 34 -- 自然语言模式（NATURAL LANGUAGE MODE）查询并得到评分 mysql> SELECT id, title, MATCH ( content, title ) against ( '风景' IN NATURAL LANGUAGE MODE ) AS score FROM articles; + ----+-----------+--------------------+ | id | title | score | + ----+-----------+--------------------+ | 7 | 如果 | 0 | | 8 | 爱情 | 0 | | 9 | 远和近 | 0 | | 10 | 断章 | 0.9771181344985962 | | 11 | 独语 | 0 | + ----+-----------+--------------------+ 5 rows in set (0.02 sec) -- 布隆模式(BOOLEAN MODE) 可以组合查询 mysql> SELECT id, title FROM articles where MATCH ( content, title ) against ( '+风景 -爱情' IN BOOLEAN MODE ); + ----+--------+ | id | title | + ----+--------+ | 10 | 断章 | + ----+--------+ 1 row in set (0.01 sec) -- 查询扩展(QUERY EXPANSION) 可以联想出其他结果 mysql> SELECT id, title FROM articles where MATCH ( content, title ) against ( '风景' WITH QUERY EXPANSION ); + ----+--------+ | id | title | + ----+--------+ | 10 | 断章 | | 11 | 独语 | + ----+--------+ 2 rows in set (0.02 sec)

4. 分词引擎

目前官网 MeCab Full-Text Parser 有支持日语的分词插件（可以更好的理解语义）

内置的 full-text parser 因为英文中单词的边界默认是空格，所以在处理英文文本时可以简单的使用空格作为分隔符。但是在处理中文时需要理解语义的基础上进行有效的分词，所以在处理中文、日文、韩文MySQL 提供了 ngram full-text （本文的配置就是基于ngram的中文分词）

总结

优点

对比 like 查询效率有提升（具体提升的测试没有做）
全文搜索可以同时对多个字段做索引，like只能对单一字段搜索

对于中文的分词可能需要在理解语义的基础上才能有效的分词；比如上文中的你好世界（hello world）对于英文按空格切分就可以，中文则需要理解语义的基础才能分成你好/世界。

这里分享一下python中jieba分词，有助于理解中文分词的魅力

结巴分词利用一个中文词库，通过词库计算汉字之间构成词语的关联概率，所以通过计算汉字之间的概率，就可以形成分词的结果。

1 2 3 4 5 6 7 8 9 10 11 In [ 1 ]: import jieba In [ 2 ]: jieba.lcut( "你好世界" ) Building prefix dict from the default dictionary ... Dumping model to file cache / var / folders / st / b16fyn3s57x_5vszjl599njw0000gn / T / jieba.cache Loading model cost 0.937 seconds. Prefix dict has been built successfully. Out[ 2 ]: [ '你好' , '世界' ] In [ 3 ]: jieba.lcut( "hello world" ) Out[ 3 ]: [ 'hello' , ' ' , 'world' ]