mysql全文索引

文章主目录

• 建立索引语句
• 索引占用空间
• 使用

回到顶部

建立索引语句

1.需要字段字符集一致utf8mb4 根据自己字段定义

ALTER TABLE user ADD FULLTEXT(name, email, phone, external_id) WITH PARSER ngram CHARSET utf8mb4;

回到顶部

索引占用空间

user表大约80M，创建的全文索引占用空间约100M (不含表大小)

回到顶部

使用

全文索引两种查法：

select * from user where MATCH(name, email, phone, external_id) AGAINST ('要查的关键字' IN NATURAL LANGUAGE MODE);

select * from user where MATCH(name, email, phone, external_id) AGAINST ('+包含的关键字 -不包含的关键' IN BOOLEAN MODE);

还有其他用法，但是不推荐使用 比如 WITH QUERY EXPANSION 选项，作用是搜索"database"字符串，相关的 mysql mssql oracle也会命中，但使用该方法将会使 CPU 内存消耗急剧增大，使用后有宕机风险。

ngram全文索引默认按两个中文分词， 比如查询 "花生壳" 而只查了"花" 则下面的语句无法命中：

select * from user where MATCH(name, email, phone, external_id) AGAINST ('花' IN NATURAL LANGUAGE MODE);
select * from user where MATCH(name, email, phone, external_id)AGAINST ('花' IN BOOLEAN MODE);

以下查询皆可命中：

select * from user where MATCH(name, email, phone, external_id) AGAINST ('花生' IN NATURAL LANGUAGE MODE);
select * from user where MATCH(name, email, phone, external_id) AGAINST ('花生壳' IN NATURAL LANGUAGE MODE);
select * from userwhere MATCH(name, email, phone, external_id) AGAINST ('生壳' IN NATURAL LANGUAGE MODE);

可以使用布尔模式，支持通配符(截断符)：
https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html
以下结果可以命中：

select * from oray.cs_question where MATCH(`Title`,`Keyword`,`Description`,`Content`) AGAINST ('花*' IN BOOLEAN MODE);
select * from oray.cs_question where MATCH(`Title`,`Keyword`,`Description`,`Content`) AGAINST ('生*' IN BOOLEAN MODE);

使用全文索引两种查法，基本都可以在0.1秒内返回结果。

具体关联注意事项可以查看官网：

https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html