1.创建索引
ALTER TABLE test ADD FULLTEXT INDEX ft_index (text) WITH PARSER ngram;
2.使用索引
-- IN NATURAL LANGUAGE MODE 自然语言全文索引
SELECT
*, MATCH (text) AGAINST ('李 ' IN NATURAL LANGUAGE MODE) AS score
FROM test WHERE MATCH (text) AGAINST ('李 ' IN NATURAL LANGUAGE MODE)
ORDER BY
score DESC;
-- IN BOOLEAN MODE 布尔全文索引
SELECT id,text,
MATCH (text) AGAINST ('+李' IN BOOLEAN MODE) AS score
FROM test
ORDER BY score DESC;
3.查看词距 默认2 取值1-10
show variables like '%ngram_token_size%';
4.调整词距
vim /etc/my.cnf
[mysqld]
ngram_token_size=2
5.配置停用词
--查看默认停用词
select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;
-- 自定义停用词库
CREATE TABLE my_stopwords(value VARCHAR(18));
-- 配置自定义停用词库
SET GLOBAL innodb_ft_server_stopword_table = 'test211008/my_stopwords';