mysql 全文搜索 MySQL全文检索使用及踩坑

简要概述使用示例创建索引

CREATE FULLTEXT INDEX your_ngram_index_name ON your_table_name (your_ngram_column_name) WITH PARSER ngram;ALTER TABLE your_table_name ADD FULLTEXT INDEX your_ngram_index_name (your_ngram_column_name) WITH PARSER ngram;

两者区别：

1. ALTER TABLE your_table_name ADD FULLTEXT INDEX: 这个命令会在已经存在的数据表上添加一个全文索引。它将表中的全部文本列包括在索引中，并将其作为一个整体被索引。这种索引适合对文本进行全文搜索，但在对较长的文本进行索引时，会导致索引大小变得非常庞大，从而降低查询性能。

2. CREATE FULLTEXT INDEX your_ngram_index_name: 这个命令创建的是 ngram 索引，它将文本分解成多个部分，然后将每个部分作为一个独立的词条进行索引。在这种索引中，每个文本列的每个单词都会被索引，并且在查询时，可以使用通配符或模糊搜索来查找特定的单词或短语。相对于 ALTER TABLE your_table_name ADD FULLTEXT INDEX，它的索引大小较小，查询更高效，但需要占用更多的存储空间。

使用

使用模板：MATCH (col1, col2,...) AGAINST (expr [search_modifier])search_modifier:{    IN NATURAL LANGUAGE MODE  | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION  | IN BOOLEAN MODE  | WITH QUERY EXPANSION}示例：select * from table_name where match(field_name) against ('test');select match(field_name) against ('test') score from table_name where match(field_name) against ('test'); //查出匹配得分

遇坑

1.部分单词检索不到，如“pay”检索不出来

原因1：当输入单词”pay“时，解析器会对单词进行分词，分词是使用n-gram算法[1]，使用innodb存储引擎时默认N=innodb_ft_min_token_size[2]，由于mysql的停用词[3]中存在”a“，所以检索时会被忽略。

[1] n-gram算法：在MySQL中，ngram解析器将文本分解为一个或多个N个字符的长度的片段，这些片段被称为”n-gram”或”token”。对于”pay”这个单词，当N=2时，ngram解析器会将它划分为”pa”和”ay”两个token；当N=3时，ngram解析器会将它划分为”p”, “pa”, “ay”三个token。通过划分单词为多个token，可以使全文检索更加灵活和高效。

[2]innodb_ft_min_token_size：innodb_ft_min_token_size和innodb_ft_max_token_size都是InnoDB存储引擎的全文检索参数，它们用于控制InnoDB在进行全文检索时生成的单词（token）的最小和最大长度。其中，innodb_ft_min_token_size表示最小长度，默认值为3，即InnoDB在解析文本时会跳过不足3个字符的单词；innodb_ft_max_token_size表示最大长度，默认值为84，即InnoDB在解析文本时会将长度超过84的单词截断成84个字符。这两个参数可以用于调整全文检索的性能和精度，但是需要根据实际情况进行调整。例如，将innodb_ft_min_token_size参数调整为1可以提高搜索精度，但是检索性能可能会下降。而将innodb_ft_max_token_size参数调整为更小的值可以减少搜索索引的大小，提高检索性能，但是可能会影响一些长单词的检索结果。需要根据具体业务需求进行调整。

[3] 停用词（stop words）：是在进行全文检索时被排除在索引之外的一些常见词汇，如”a”，”an”，”the”等，这些词汇在搜索中的作用不大，占据了索引空间，因此可以被认为是噪音。在进行全文检索时，需要将停用词从搜索索引中删除，从而减小搜索索引的大小并提高搜索效率。通常，MySQL提供内置的停用词列表，可以在需要的时候启用。此外，也可以根据实际需求自定义停用词列表。对于需要搜索的文本，MySQL会将其划分为单词（token），然后进行匹配，但如果这些单词中含有停用词，会被忽略掉，从而达到优化检索性能的目的。

解决方案1：

（1）将innodb_ft_min_token_size设置成1

（2）关闭停用词，在mysql配置文件my.ini中添加innodb-ft-enable-stopword=OFF然后重启mysql，再通过执行如下SQL刷新全文索引

ALTERTABLEyour_table_nameALGORITHM=INPLACE,DROPINDEXyour_ngram_index_name,ADDFULLTEXTINDEXyour_ngram_index_name(your_ngram_column_name)WITHPARSERngram;

原因2：建立的全文检索失效，如先建立全文索引，再关闭停用词，此时停用词仍然会生效

解决方案2：

（1）建一个新表新索引，将原始数据拷贝到新表

（2）重新设置存储引擎，将存储引擎修改为innodb，ALTER TABLE your_table_name ENGINE = InnoDB;