mysql fulltext_MySQL(FullText)

fulltext變量:

show variables like "%FT%"

+---------------------------------+----------------+

| ft_boolean_syntax | + ->

| ft_max_word_len | 84 |

| ft_min_word_len | 4 |

| ft_query_expansion_limit | 20 |

| ft_stopword_file | (built-in) |

| innodb_ft_aux_table | |

| innodb_ft_cache_size | 8000000 |

| innodb_ft_enable_diag_print | OFF |

| innodb_ft_enable_stopword | ON |

| innodb_ft_max_token_size | 84 |

| innodb_ft_min_token_size | 3 |

| innodb_ft_num_word_optimize | 2000 |

| innodb_ft_result_cache_limit | 2000000000 |

| innodb_ft_server_stopword_table | |

| innodb_ft_sort_pll_degree | 2 |

| innodb_ft_total_cache_size | 640000000 |

| innodb_ft_user_stopword_table | |

+---------------------------------+----------------+

(MySQL5.7.6開始支持中日韓的分詞器ngram parser)

show variables like "%ngram%";

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| ngram_token_size | 2 |

+------------------+-------+

fulltext tables(INNODB information_schema):

INNODB_SYS_INDEXES:提供了InnoDB索引的狀態信息

INNODB_SYS_TABLES:提供了InnoDB表的狀態信息

INNODB_FT_CONFIG:顯示一個InnoDB表的FULLTEXT索引及其相關處理的元數據

INNODB_FT_INDEX_TABLE:轉化后的索引信息用于處理基于InnoDB表FULLTEXT索引的文本搜索。一般用于調試診斷目的

INNODB_FT_INDEX_CACHE:向含FULLTEXT索引的InnoDB表插入數據后新插入數據轉后的索引信息

INNODB_FT_DEFAULT_STOPWORD:在InnoDB表上創建FULLTEXT索引所使用的默認停止字表

INNODB_FT_DELETED:記錄了從InnoDB表FULLTEXT索引中刪除的行

INNODB_FT_BEING_DELETED:用于記錄正在被刪除的信息，用于監控和調試目的

fulltext分詞處理:

默認的parser和限定的ngram parser

默認的parser適合latin

限定的ngram parser適合中日韓

默認的parser使用nnodb_ft_min_token_size和innodb_ft_max_token_size決定詞的大小

限定的ngram parser的ngram_token_size, 決定詞的大小, 它的合法的取值范圍是1到10

若用了ngram_token_size，所以innodb_ft_min_token_size和innodb_ft_max_token_size將不適用于n-gram

對于一個新的詞，默認的parser會查找stopwords表，看是否有匹配的詞，如果匹配，這個詞就不會加入到全文索引中

對于一個新的詞，限定的ngram parser會查找stopwords表，看是否包含里面的詞，如果包含，就不會把原詞加入到全文索引中

如果mysql內置的分詞器效果不理想，可以使用第三方插件或者自己分完詞后存儲和索引

fulltext查詢處理:

文本查詢,在自然語言模式(NATURAL LANGUAGE MODE)下，文本的查詢被轉換為n-gram分詞查詢的并集

文本查詢,在布爾模式(BOOLEAN MODE)，文本查詢被轉化為n-gram分詞的短語查詢

通配符查詢,如果前綴的長度比ngram_token_size小，那么查詢結果將返回在全文索引中所有以這個詞作為前綴的n-gram的詞

通配符查詢,如果前綴的長度大于等于ngam_token_size，那么這個查詢則轉換為一個短語(phrase search)，通配符則被忽略

短語查詢,被轉換為n-gram分詞的短語查詢

一般來說，查詢正好等于ngram_token_size的詞，速度會更快，但是查詢比它更長的詞或短語，則會變慢

fulltext搜索語法:

MATCH (col1,col2,…) AGAINST (expr[search_modifier])

col1,col2,…為已建立FULLTEXT索引并要從中查找數據的列

expr為要查找的文本內容

search_modifier的每個取值代表一種類型的全文搜索

search_modifier取值:

IN NATURAL LANGUAGE MODE：自然語言全文搜索(默認)

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION：帶查詢擴展的自然語言全文搜索

IN BOOLEAN MODE：布爾全文搜索

WITH QUERY EXPANSION：查詢擴展全文搜索

fulltext例子:

(建立索引)

use xxxxx;

alter table company add fulltext ( `co_name`);

(索引查詢)

select co_id,co_name from company where match (`co_name`) against ('最淘科技') limit 10;

select count(*) from company where match (`co_name`) against ('科技');

select count(*) from company where match (`co_name`) against ('科技*');

select count(*) from company where match (`co_name`) against ('*科技');

select count(*) from company where match (`co_name`) against ('*科技*');

(全表掃描)

select count(if(match (`co_name`) against ('最淘科技'))) as count from company;

fulltext搜索模式:

1.自然語言模式NATURAL LANGUAGE MODE。

MySQL全文搜索的默認搜索方式. 把搜索字符串解釋為一系列單詞并查找包含這些單詞的數據行。

2.布爾全文搜索BOOLEAN MODE

把搜索字符串解釋為一系列單詞，但允許使用一些操作符字符來"修飾"這些單詞以表明特定的要求，如某給定單詞必須出現(或不出現)在匹配數據行里，某個數據行必須包含一個精確的短語，等等。

“+”表明結果中必須包含

“-”表明所得結果中不能含有

空字符表明后跟的單詞是可選的,但出現的話會增加該行的相關性

“@distance”用于指定兩個或多個單詞相互之間的距離(以單詞度量)需在指定的范圍內

“>”用于增加后跟單詞對其所在行的相關性的貢獻

“

“()”用于將單詞分組為子表達式且可以嵌套

“~”是后跟單詞對其所在行的相關性的貢獻值為負

“*”為普通的通配符，若為單詞指定了通配符，那么即使該單詞過短或者出現在了停止字列表中它也不會被移除

“””，括在雙引號中的短語指明行必須在字面上包含指定的短語，全文搜索將短語分割為詞后在FULLTEXT索引中搜索

非字字符無需完全匹配

3.查詢擴展全文搜索WITH QUERY EXPANSION

這種搜索分兩階段進行。第一階段是自然語言搜索，第二階段使用原來的搜索字符串加上在第一次搜索中找到的相關度最高的匹配數據行再進行一次搜索。

這擴大了搜索范圍，它可以把與原來的搜索字符串相關、但用原來的搜索字符串匹配不到的數據行也找出來

fulltext索引提示限定:

1.對于自然語言模式的全文搜索，索引提示會被忽略而不給出任何提示，比如雖明確在查詢語句中給出了IGNORE INDEX(i)指明不使用i索引，但是該索引提示會被忽略掉，最終的查詢中仍會使用索引i

2.對于布爾模式的全文搜索，FOR ORDER BY和FOR GROUP BY的索引提示會被忽略，FOR JOIN和不帶FOR修飾符的索引提示不被忽略

fulltext vs like

LIKE搜索的耗時隨著記錄數的增加而線性增長，但對于10萬行記錄以下的表搜索時間基本上能保持在1秒以內

FULLTEXT搜索耗時也隨表中記錄數的增長而線性增加。對于10萬行記錄以下的表搜索時間基本上能保持在0.01秒以內

無論是LIKE搜索還是FULLTEXT搜索，其性能都會隨著記錄數的增長而下降，項目中的文本搜索數據庫記錄數龐大到一定規模后，需要考慮其他文本搜索解決方案

fulltext Note:

目前可對MyISAM表和InnoDB表的CHAR、VARCHAR、TEXT類型的列創建全文索引

分區表不支持全文搜索

表意型語言如漢語、日語沒有諸如空格之類的單詞定界符。因此FULLTEXT解析器不能確定此類語言中詞的起止。對于此種情況要特殊處理(比如將中文轉換成一種單字節類似英文習慣的存儲方式)

全文索引同其他索引一樣，可在創建表是由CREATE TABLE語句創建也可以在表創建之后用ALTER TABLE或者CREATE INDEX命令創建

對于要導入大量數據的表先導入數據再創建FULLTEXT索引比先創建索引后導入數據會更快

默認情況下全文搜索大小寫不敏感

FULLTEXT解析器自動移除首尾的“’”

FULLTEXT解析器用“ ”(空格)、“,”(逗號)“.”(點號)作為默認的單詞分隔符，因此對于不使用這些分隔符的語言如漢語來說FULLTEXT解析器不能正確的識別單詞，對于這種情況需做額外處理

FULLTEXT索引可以為一個或多個數據列而創建。如果它涉及多個數據列，基于該索引的搜索將在所有數據列上同時進行。

全文搜索時，MATCH()函數給出的數據列清單必須和某個FULLTEXT索引所匹配的那些數據列精確匹配

全文搜索對"單詞"的定義是：由字母、數字、撇號(如"it's"中的"'")和下劃線字符構成的字符序列

全文搜索匹配整個單詞，而不是單詞的一部分

只要在一個數據行里找到了搜索字符串里的任何單詞，FULLTEXT引擎就會認為這個數據行與搜索字符串是匹配的

全文搜索將忽略stopword列表中的單詞, stopword列表包含諸如“the”、“or”、“and”等常用單詞，這些詞通常被認為沒有什么語義價值

全文搜索還將忽略一些常用單詞，如"the"、"after"和"other"等，這些單詞被稱為"休止單詞"，MySQL在進行全文搜索時總是會忽略它們

全文搜索還將忽略太短的單詞。在默認的情況下，"太短"指少于4個字符, 但可以通過重新配置服務器的辦法把這個最小長度設置為其他值

文本集合和查詢語句中的單詞的權重由該單詞在集合或語句中的重要性確定。單詞在越多的行中出現則該單詞的權重越低，反之權重越高

全文搜索返回的行是按與所查找內容的相關度由高到低的順序排列的。

全文搜索查詢內容與結果相關度的值由WHERE語句中的MATCH (…) AGAINST (…)計算所得，是一個非負浮點數。

全文搜索查詢內容與結果相關度的值越大表明相應的行與所查找的內容越相關，0值表明不相關。

全文搜索查詢內容與結果相關度的值基于行中的單詞數、行中不重復的單詞數、文本集合中總單詞數以及含特定單詞的行數計算得出

多數全文搜索相關的變量不能在Server運行的時候修改。需在Server啟動時指定這些參數，或者修改完參數之后重新啟動Server

某些變量修改后需要重建FULLTEXT索引

InnoDB表重建FULLTEXT索引可以通過帶DROP INDEX和ADD INDEX從句的ALTER TABLE語句完成，先刪除舊的再創建新的

MyISAM表重建FULLTEXT索引可以通過帶DROP INDEX和ADD INDEX從句的ALTER TABLE語句完成，先刪除舊的再創建新的，也可以通過QUICK repair操作來重建

helper:

同表復制

UPDATE 表名S SET b列名=源a列名

UPDATE 表名S SET b列名=CONCAT(b列名,",",a列名)

UPDATE 表名S SET b列名=源A列名 WHERE XXX

UPDATE 表名S SET b列名=CONCAT(b列名,",",a列名) WHERE XXX

異表復制

update T set b列名 = S.源A列名 from S join T on (A.id = T.id);

update T set T.b列名 = (select S.源A列名 from S where S.id = T.id)