簡要概述

<i id='v9e5z'></i>

• MySQL支持全文索引和搜索。
• MySQL中的全文索引是FULLTEXT類型的索引。
• 全文索引只能用于InnoDB或MyISAM表，并且只能為CHAR、VARCHAR或TEXT列創建。
• MySQL5.7提供了一個內置的全文ngram解析器，支持中文，日文和韓文(CJK)，以及一個可安裝的MeCab日文全文解析器插件。
• FULLTEXT索引定義可以在創建表時在CREATE TABLE語句中給出，也可以稍后使用ALTER TABLE或CREATE INDEX添加。
• 對于大型數據集，將數據加載到一個沒有FULLTEXT索引的表中，然后在此之后創建索引，比將數據加載到一個已有FULLTEXT索引的表中要快得多。

使用示例

創建索引

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刷新全文索引

ALTER TABLE your_table_name ALGORITHM=INPLACE, DROP INDEX your_ngram_index_name, ADD FULLTEXT INDEX your_ngram_index_name(your_ngram_column_name) WITH PARSER ngram;

原因2：建立的全文檢索失效，如先建立全文索引，再關閉停用詞，此時停用詞仍然會生效

解決方案2：

（1）建一個新表新索引，將原始數據拷貝到新表

（2）重新設置存儲引擎，將存儲引擎修改為innodb，ALTER TABLE your_table_name ENGINE = InnoDB;