Mysql INNODB和MyIsAM实现全文检索

我不是女神ヾ 2022-09-02 03:50 143阅读 0赞

**目录**

SQL整个流程

参考文章

--------------------

全文搜索引擎实现数据库级别的一直没有测试，今天有空试了一把做下记录。注意INNODB和MyIsAM 对中文的支持力度不一样。

## SQL整个流程 ##

-- *****************************MySQL 从 5.7.6 版本开始，MySQL就内置了ngram全文解析器，用来支持中文、日文、韩文分词*********************
    -- 参考地址：https://www.cnblogs.com/jstarseven/p/13558612.html
    -- 当前数据库版本号
    select version();
    -- INNODB
    DROP TABLE IF EXISTS article_plus;
    
    CREATE TABLE `article_plus` (
      `id` varchar(255) DEFAULT NULL,
      `title` varchar(255) DEFAULT NULL,
      `body` longtext,
      FULLTEXT KEY `content_index`(`title`,`body`) WITH PARSER `ngram`
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    
    -- MyIsAM
    DROP TABLE IF EXISTS article_plus;
    
    CREATE TABLE `article_plus` (
      `id` varchar(255) DEFAULT NULL,
      `title` varchar(255) DEFAULT NULL,
      `body` longtext,
      FULLTEXT KEY `content_index`(`title`,`body`) WITH PARSER `ngram`
    ) ENGINE=MyisAM DEFAULT CHARSET=utf8;
    
    -- 查看全文搜索的变量 ft_max_word_len  ft_min_word_len  关键词的最大最小取值范围
    show VARIABLES like 'ft%';
    
    
     -- 插入内容
    INSERT INTO `dbsearch`.`article_plus`(`id`, `title`, `body`) VALUES (1, '中国女队斩获首金', '杨倩为中国女队斩获2020东京奥运会首金。');
    INSERT INTO `dbsearch`.`article_plus`(`id`, `title`, `body`) VALUES (2, '中国男子73公斤举重冠军打破世界纪录', '中国(China)男子73公斤举重冠军石智勇，打破世界纪录。');
    INSERT INTO `dbsearch`.`article_plus`(`id`, `title`, `body`) VALUES (3, 'Apple', 'Apple');
    INSERT INTO `dbsearch`.`article_plus`(`id`, `title`, `body`) VALUES (4, 'Huawei', 'Huawei');
    INSERT INTO `dbsearch`.`article_plus`(`id`, `title`, `body`) VALUES (5, 'Huawei 抢占Apple中国市场', 'Huawei 抢占Apple中国市场');
    
    -- 通过 alter table 方式
    ALTER TABLE article_plus ADD FULLTEXT INDEX content_index(title,body) WITH PARSER ngram;
    
    -- 通过 create index 方式
    CREATE FULLTEXT INDEX content_index ON article_plus(title,body) WITH PARSER ngram;
    
     -- 删除全文索引
     DROP INDEX content_index ON article_plus;
     
     -- 重建索引
     repair table article_plus quick;
    
    -- 全文搜索中国--MYISAM查不出来
    select id,title, MATCH(title,body) AGAINST ('中国') as score 
    from article_plus where MATCH(title,body) AGAINST ('中国' IN NATURAL LANGUAGE MODE);
    
    -- 全文搜索中国--MYISAM可以查出来
    select id,title, MATCH(title,body) AGAINST ('中国') as score 
    from article_plus where MATCH(title,body) AGAINST ('中国' IN BOOLEAN MODE);
    
    -- 全文搜索Apple
    select id,title, MATCH(title,body) AGAINST ('Apple') as score 
    from article_plus where MATCH(title,body) AGAINST ('Apple' IN NATURAL LANGUAGE MODE);
    
    
    -- 使用全文索引
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('2020');
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('中国'  IN BOOLEAN MODE );
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('+China');
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('-China');
    -- 至少包含一个
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('Apple');
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('Apple Huawei');
    -- 两词均在包含
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('+Apple+Huawei');
    -- 包含Apple但如果包含Huawei它的排名会更高
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('+Apple Huawei');
    -- 包含Apple但不包含Huawei
    SELECT * FROM article_plus WHERE MATCH(title,body) AGAINST('+Apple -Huawei');

## 参考文章 ##

[Mysql 如何实现全文检索，关键词跑分 ][Mysql _]

[Mysql使用全文索引(FullText index)][Mysql_FullText index]

[MySQL 5.7 中文全文检索使用教程][MySQL 5.7]

[Mysql _]: https://www.cnblogs.com/jstarseven/p/13558612.html
[Mysql_FullText index]: https://zhuanlan.zhihu.com/p/88275060
[MySQL 5.7]: https://www.jianshu.com/p/c48106149b6a