MySQL 8 中文全文检索、统计搜索文字出现次数

1、配置MySQL

# my.cnf
[mysqld]
# 输入搜索的长度，默认为4
ft_min_word_len=1
innodb_ft_min_token_size=1

# 全文检索分词数，默认为2
ngram_token_size=1

2、创建数据表

DROP TABLE IF EXISTS `t_article`;
CREATE TABLE `t_article`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT INDEX `fulltext_title_content`(`title`, `content`) WITH PARSER `ngram`
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `t_article` VALUES (1, '八荣八耻 1', '以热爱祖国为荣、以危害祖国为耻');
INSERT INTO `t_article` VALUES (2, '八荣八耻 2', '以服务人民为荣、以背离人民为耻');
INSERT INTO `t_article` VALUES (3, '八荣八耻 3', '以崇尚科学为荣，以愚昧无知为耻');
INSERT INTO `t_article` VALUES (4, '八荣八耻 4', '以辛勤劳动为荣，以好逸恶劳为耻');
INSERT INTO `t_article` VALUES (5, '八荣八耻 5', '以团结互助为荣，以损人利己为耻');
INSERT INTO `t_article` VALUES (6, '八荣八耻 6', '以诚实守信为荣，以见利忘义为耻');
INSERT INTO `t_article` VALUES (7, '八荣八耻 7', '以遵纪守法为荣，以违法乱纪为耻');
INSERT INTO `t_article` VALUES (8, '八荣八耻 8', '以艰苦奋斗为荣，以骄奢淫逸为耻');
INSERT INTO `t_article` VALUES (9, '满江红', '靖康耻，尤未雪');
INSERT INTO `t_article` VALUES (10, '第一生产力', '科学技术是第一 生产力');
INSERT INTO `t_article` VALUES (11, '团结互助', '团结就是力量');
INSERT INTO `t_article` VALUES (12, 'Blue Red', 'Red Black');
INSERT INTO `t_article` VALUES (13, '我是奇迹 1', '你好，我是奇迹2');
INSERT INTO `t_article` VALUES (14, '恭喜发财', '你好');

3、全文检索查询

3.1、查询 title 或者 content 中包含“祖国”的记录

select *, MATCH (title, content) AGAINST ('祖国') as score
from t_article where MATCH (title, content) AGAINST ('祖国' IN NATURAL LANGUAGE MODE);

3.2、查询 title 或者 content 中包含“团结劳动”的记录

select *, MATCH (title, content) AGAINST ('团结劳动') as score
from t_article where MATCH (title, content) AGAINST ('团结劳动' IN NATURAL LANGUAGE MODE);

3.3、查询 title 或者 content 中包含“为荣”的记录

select *, MATCH (title, content) AGAINST ('为荣') as score
from t_article where MATCH (title, content) AGAINST ('为荣' IN NATURAL LANGUAGE MODE);

4、特殊情况

4.1、查询单个汉字

#  my.cnf 需要设置如下 全文检索分词数
ngram_token_size=1


select *, MATCH (title, content) AGAINST ('力') as score
from t_article where MATCH (title, content) AGAINST ('力' IN NATURAL LANGUAGE MODE);

4.2、查询单个字段

# 需要增加索引，之前增加是联合索引
alter table `t_article` add fulltext index fulltext_content(`content`) WITH PARSER ngram;
alter table `t_article` add fulltext index fulltext_title(`title`) WITH PARSER ngram; 

select *, MATCH (content) AGAINST ('诚实') as score from t_article where MATCH (content) AGAINST ('诚实' IN NATURAL LANGUAGE MODE);

5、全文检索模式

5.1、自然语言模式(NATURAL LANGUAGE MODE) 

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

5.2、BOOLEAN 模式(BOOLEAN MODE)

BOOLEAN 模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。
BOOLEAN 模式，举例如下：
'apple banana' 
无操作符，表示或，要么包含apple，要么包含banana

'+apple +juice'
必须同时包含两个词

'+apple macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性会更高。

'+apple -macintosh'
必须包含apple，同时不能包含macintosh。

'+apple ~macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。

'+apple +(>juice <pie)'
查询必须包含apple和juice或者apple和pie的记录，但是apple juice的相关性要比apple pie高。

'apple*'
查询包含以apple开头的单词的记录，如apple、apples、applet。

'"some words"'
使用双引号把要搜素的词括起来，效果类似于like '%some words%'，
例如“some words of wisdom”会被匹配到，而“some noise words”就不会被匹配。

5.3、查询

5.3.1、查询字段 content 中包含 “团结”和“力量”的语句

select *, MATCH (content) AGAINST ('+团结 +力量') as score
from t_article where MATCH (content) AGAINST ('+团结 +力量' IN BOOLEAN MODE);

5.3.2、查询字段 content 中包含 “团结”，但不包含“力量”的语句

select *, MATCH (content) AGAINST ('+团结 -力量') as score
from t_article where MATCH (content) AGAINST ('+团结 -力量' IN BOOLEAN MODE);

5.3.3、查询字段 conent 中包含“团结”或者“力量”的语句

select *, MATCH (content) AGAINST ('团结 力量') as score
from t_article where MATCH (content) AGAINST ('团结 力量' IN BOOLEAN MODE);

6、统计搜索关键字出现次数的SQL

SELECT
    *,
    LENGTH( title ) - LENGTH( REPLACE ( title, '为荣', '' ) ) AS title_count,
    TRUNCATE((LENGTH( content ) - LENGTH( REPLACE ( content, '为荣', '' )))/LENGTH('为荣'),0) AS content_count,
    MATCH ( title, content ) AGAINST ( '为荣' ) AS score 
FROM
    t_article 
WHERE
    MATCH ( title, content ) AGAINST ( '为荣' IN NATURAL LANGUAGE MODE );

7、注意

1）只能在类型为 CHAR、VARCHAR 或者 TEXT 的字段上创建全文索引。

2）全文索引只支持 InnoDB 和 MyISAM 引擎。

3）MATCH (columnName) AGAINST ('keywords')。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。

     如上面的示例，MATCH (title,content)使用的字段名与全文索引t_article(title,conent)定义的字段名一致。

     如果要对 title 或者 content 字段分别进行查询，就需要在 title 和 content 字段上分别创建新的全文索引。

4）MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。

5）如果要导入大数据集，使用先导入数据，再在表上创建全文索引的方式，要比先在表上创建全文索引再导入数据的方式快很多。

来源：https://www.cnblogs.com/lianyiu/p/14325045.html