說到全文檢索的分詞，多半講到的是中(日韓)文分詞，少有英文等拉丁文系語言，因為英語單詞天然就是分詞的。
但更少講到阿拉伯數字。比如金額，手機号碼，座機号碼等等。
以下不是傳統的從0開始針對mysql全文索引前世今生講起。
我更喜歡從一個小問題入手，見縫插針的将相關的知識點，以非時間線性順序零散穿插起來。
從一個線上的BUG說起
我們有一張人口表，裡面的資料有多種資料源合并而來，是以每個使用者的手機号可能有多個。
這也很好了解，有的人就是有多個手機号，有的人就是經常換手機号，對吧。
現在有個功能需要通過手機号去關聯使用者。
因為手機号有多個，是以要麼使用like進行模糊比對。使用者表有上千萬條記錄，這樣的效率肯定是不能接受的。
sql複制代碼select * from t_user where phone like '%13112345678%'
要麼使用另一個折中的方案，将手機号單獨成表，使用者表對手機号表一對多關聯。
這種方式效率上能接受，但需要改變現有資料結構，故放棄。
sql複制代碼select u.id,u.username,u.phone from t_user u LEFT JOIN t_user_phone p on u.id = p.user_id where p.phone = '13112345678'
最終選用全文索引。（mysql 5.7.6+）
先在使用者表針對手機号建立一個全文索引。
使用内置分詞引擎ngram。
sql複制代碼CREATE FULLTEXT INDEX idx_full_text_phone ON t_user (phone) WITH PARSER ngram;
當使用手機模糊查詢關聯使用者時可使用以下語句。
- 布爾模式模糊檢索
sql複制代碼select * from t_user where match(phone) AGAINST('13996459860' in boolean mode)
- 自然語言模式。mysql預設為此模式，是以第2條sql沒有顯式指定時，仍然為自然語言模式。
sql複制代碼select * from t_user where match(phone) AGAINST('13996459860' in NATURAL LANGUAGE mode)
或
select * from t_user where match(phone) AGAINST('13996459860')
根據我們的需求，查詢手機号需要全比對才算命中。是以選擇布爾模式。
自然語言模式做不到。
關于布爾模式和自然語言模式的差別，後面做介紹。
以上算是簡單的背景介紹。
但是
萬惡的但是，雖遲但到
有一天産品過來告訴我，某個手機号關聯出來上百個人。
他問，這種情況是正常的嗎？
他如果直接說你這裡有個bug,我可能直接就怼回去了(bushi
但是他說得這麼委婉，我反而沒底了。
不要對一個程式員說：你的代碼有Bug。他的第一反應是：①你的環境有問題吧；②S13你會用嗎？
如果你委婉地說：你這個程式和預期的有點不一緻，你看看是不是我的使用方法有問題？
他本能地會想：woco！是不是出Bug了！
直覺告訴我這不正常，不然這個人是搞電詐或者海王嗎？
我拿手機号去資料庫裡查詢。使用布爾模式全文檢索，确實關聯出來多個人。
但也确實是個BUG.
我們來完整地模拟一下。
先建立一張測試使用者表。
phone字段加上全文索引，使用ngram分詞器。
sql複制代碼CREATE TABLE `t_user` (
`id` int(11) NOT NULL,
`username` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`phone` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_full_text_phone` (`phone`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
插入幾條測試資料
sql複制代碼-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('1', '張三', '13996459860,15987569874,0797-12345');
INSERT INTO `t_user` VALUES ('2', '李四', '0797-6789');
INSERT INTO `t_user` VALUES ('3', '王五', '0797-94649');
正常情況下
sql複制代碼select * from t_user where match(phone) AGAINST('13996459860' in boolean mode)
select * from t_user where match(phone) AGAINST('13996459860' in NATURAL LANGUAGE mode)
select * from t_user where match(phone) AGAINST('13996459860')
都能得到
異常情況
sql複制代碼select * from t_user where match(phone) AGAINST('0797-12345' in boolean mode)
得到結果
可以看到後面兩條記錄不是預期的結果。
也是産品經理反映的問題。
大家應該都猜到了，就是座機号的原因。
嗯，使用者有個座機，這很河狸嘛。
都是廣義上的聯系方式嘛。
看起來，這條SQL是将包含0797的資料行都傳回了，但我使用的是布爾模式，要求全部比對上0797-12345才傳回。
我猜可能是'-'導緻分詞的問題,将其分成了兩部份。
分詞器
分詞就是對需要進行搜尋的關鍵詞進行拆分。MySQL最初支援全文索引時，使用的是parser （拉丁文法分詞器，通過空格來分詞）， 如英文I am programmer ,天然可以通過空格拆分成I am programmer3個單詞，這也就是前文說的英語天然沒有分詞的問題。
但對于像中文這類不以空格拆分詞語的語言來說無法适用。 是以MYSQL5.7.6後提供了n_gram parser（字元長度分詞器) ，對中文的全文索引支援更友好，分詞器的使用也很簡單，建立索引時添加 WITH PARSER ngram即為使用n_gram parser（字元長度分詞器)，不加則預設使用傳統parser（拉丁文法空格分詞器）。
注意字元長度分詞器這幾個字，故名思義，它就是按字元的長度來分詞的，之是以單獨提出來，是差別于基于NLP自然語義的分詞，如複旦分詞等。
比如我是程式員這個短句，如果按照自然語義分析來進行分詞的話，它可能會分成我 是 程式 程式員等。
斷不可能分出來序員。除非分詞器有問題。
但n_gram parser分詞器就有可能。 mysql預設分詞長度為2，可在my.cnf裡進行配置，ngram_token_size = 2指定分詞長度。
針對不同的分詞長度，我是程式員這個短句可以有以下多種分詞效果。
ini複制代碼
ngram_token_size=1: '我', '是', '程', '序', '員'
ngram_token_size=2: '我是', '是程', '程式' , '序員'
ngram_token_size=3: '我是程', '是程式' , '程式員'
...
ngram_token_size=5: '我是程式員'
...
最大ngram_token_size=10
我的測試庫ngram_token_size為2，加個字段簡單測試一下。
單個字搜不到，因為最小分詞機關為2。
搜尋程式和序員都能得到正确的結果。
以上是漢字的分詞，回到今天的正題，對于阿拉伯數字呢？ 如金額23.45元，手機号13912345678，座機号0797-12345678，日期2023-01-01等等。
針對上面說到的BUG,座機号0797-12345678關聯出來了多個帶0797但-後面不相同的号碼，
我一開始以為是-的問題。它将0797-12345678分成了0797和12345678兩部份。
但通過這一小節的n_gram parser的介紹，我們知道它是基于長度的分詞器，那麼原因肯定就不是這樣的。
通過以下兩句SQL可以證明它是兩兩拆分的。
sql複制代碼select * from t_user where match(phone) AGAINST('7-' in NATURAL LANGUAGE mode)
select * from t_user where match(phone) AGAINST('07' in boolean mode)
以7-和07都能将3條記錄全部比對出來。
但是在布爾模式下，7-搜尋不出來。
為什麼呢？
這裡mysql把7-中的-當成邏輯運算符了，而不是整體當作一個搜尋關鍵詞。
stopword
内置的MySQL全文解析器将單詞與stopword 清單中的條目進行比較。如果一個單詞在stopword清單當中，則該單詞将從索引中排除。
對于ngram解析器，stopword處理的執行方式不同。ngram解析器不排除與stopword中的條目相等的令牌，而是排除包含stopword的令牌。
例如，假設ngram_token_size=2，包含a,b的文檔将被解析為a，和，b。
如果逗号，被定義為stopword，則a，和，b都将從索引中排除，因為它們包含逗号。
同理，如果stopword當中包含-，同時ngram_token_size=4，那麼座機号0797-1789就被拆分成兩個大的部份，0797和1789。
其中 797-1 97-17 7-178 等都将被排除。
如此以上猜想成立的話，就有可能導緻開頭的BUG。 前提是wordstop當中包含-。
在innodb當中，stopword可以通過INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD表來檢視。
可以通過此表來自定義删除或添加stopword，進而改變分詞規則。
通過檢視，可以發現'-'并不在stopword當中，是以上面的猜想是錯誤的，并不是這個原因導緻的BUG。
sql複制代碼mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
+-------+
36 rows in set (0.00 sec)
布爾模式的邏輯運算符
mysql全文檢索有兩種最常用的方式。自然語言模式和布爾模式。
自然語言模式
對于自然語言模式搜尋，搜尋項被轉換為ngram項的并集。例如，字元串abc(假設ngram_token_size=2)被轉換為ab bc。給定兩個文檔，一個包含ab，另一個包含abc，搜尋詞ab bc比對這兩個文檔。
可以簡單的了解為，将搜尋關鍵詞再拆分，與文檔進行模式比對。
上圖所示，文檔中包含12和'0997'都被命中了。
布爾模式
對于布爾模式搜尋，搜尋項被轉換為ngram短語搜尋。例如，字元串abc(假設ngram_token_size=2)被轉換為ab bc。給定兩個文檔，一個包含ab，另一個包含abc，搜尋短語ab bc隻比對包含abc的文檔。
可以了解為不會對關鍵詞進行再拆分，相當于對搜尋關鍵詞進行全比對。
使用相同的測試資料和相當的搜尋關鍵詞，使用布爾模式搜尋。
結果為空。 沒有資料被命中。
但是
在布爾模式下搜尋0797-12345命中了0797-94649和0797-1789。
但不會命中'07','09','12'等。
我隻能解釋為，布爾模式下，搜尋關鍵詞0797-12345中的'-'被當成文法了，導緻無形中被拆分成了0797和12345兩部份。
但是，我從mysql官網沒有找到證據。 是以此點存疑。各位看官要有自己的思考，不要被我誤導！
跟上一小節當中'7-'沒有命中任何記錄一樣，也是布爾模式下文法的原因。
現在我們來讨論一下布爾模式下的邏輯運算符問題。
布爾模式的邏輯運算符
-
+ select * from t_user where match(phone) AGAINST('a +b' in boolean mode)
其中 + 會被識别成邏輯運算符，而不是将a +b作為一個整體，以下同理。
'a +b' 指'a'和'b'必須同時出現才滿足搜尋條件。
-
- select * from t_user where match(phone) AGAINST('0797 -12345' in boolean mode)
0797 -12345指0797必須包含，但不包含12345才能滿足搜尋條件。
以下查詢排除了包含0797-12345的記錄。
注意-前後空格 0797 -12345才表示包含0797 同時不包含12345. 0797-12345等于0797 - 12345，它并不等于0797 -12345。
有圖為證：
-
> <
提高/降低該條比對資料的權重值。不管使用>還是 <，其權重值均大于沒使用其中任何一個的。
select * from t_user where match(phone) AGAINST('0797(>94649 <12345)' in boolean mode)
表示比對0797,同時包含94649的列往前排，包含12345的往後排 select * from t_user where match(phone) AGAINST('a > b' in NATURAL LANGUAGE mode)
- () 相當于表達式分組，參考上一個例子。
-
*
通配符，隻能在字元串後面使用
- " 完全比對，被雙引号包起來的單詞必須整個被比對。 select * from t_user where match(phone) AGAINST('"0797-1789"' in boolean mode) "0797-1789"中不可再分。其它包含0797-1234等記錄就不再比對。
解決方案
現在，讓我們回到最初的美好。
我們遇到了一個問題，一個座機号0979-1789全文檢索傳回了不完全比對的記錄。
那麼，想要完全比對，需要怎麼做呢。
經過上面的旅程，我們有了兩種方案。
- 使用 "" 将座機号包起來，"0979-1789"，表示此搜尋關鍵詞不可再分。自然就能全比對。
-
主動拆分，再使用+
我們知道，之是以座機号能将不完全比對的記錄查詢出來，是因為将座機号當中的"-"當成了邏輯運算符，進而導緻了座機号被拆分成了兩部份。
那我們先主動将座機号拆分兩部份，再使用邏輯運算符"+"，表示兩部份都必須包含才能傳回。
建議使用第一種方法。
其它的電話号碼表示方法，比如區号+電話号碼，023+12345678，國際長途0086-10-1234567或+86-573-82651630，610-643-4567等。
這裡面涉及到+-等邏輯運算符，用第一種方法最安全。
反向索引
全文索引即是反向索引。
好像這種說法，在lucene或者elasticsearch更流行。
文末還是簡單說一下它的原理。
傳統資料庫索引的方式是，【表->字段】。而反向索引的方式是先将字段進行分詞，然後将單詞跟文檔進行關聯，變為【文檔 -> 單詞】，并将記錄其它更為強大的資訊（文檔編号、詞項頻率、詞項的位置、詞項開始和結束的字元位置可以被存儲）。
有兩篇文章：
1 我是程式員
2 我熱愛寫程式
先分詞(這裡假設以自然語義分詞)
1 【我】【是】【程式】【程式員】
2 【我】【熱愛】【寫】【程式】
前面文章對關鍵字，經倒排後變成關鍵字對文章
關鍵字文章号
我1，2
是1
程式1，2
程式員1
熱愛2
寫2
為了快速定位和節省存儲大小，還需要加上關鍵字出現頻率和位置。
關鍵字文章号（頻率）位置
我1（1）1
2（1）1
是1（1）2
程式1（1）3
2（1）4
程式員1（1）4
熱愛1（1）1
寫1（1）3
如果我要對“程式”進行搜尋，能就能快速定位到文檔1，2，并且能直接知道它在文檔當中出現了多少次，分别出現在哪裡。
小結
關于分詞，mysql有兩種引擎，一種是基于空格的拉丁語系模式，預設就是這種。如'i love you'拆分為i love you三部份。 在5.7.6以後，針對中日韓文字内置了一種基于長度的分詞器，n_gram parser。
此分詞器并不區分中文和阿拉伯數字，兩種文本分詞的标準是一樣的。
但一些特殊的文本裡面帶有布爾模式下的邏輯運算符(+-><*())的時候需要特别注意。
同時，mysql全文索引本身有很多限制，該用elasticsearch的時候也該大膽上：
1：隻支援char、varchar、text類型。
2：MySQL的全文索引隻有全部在記憶體中的時候，性能才非常好。如果記憶體無法裝載全部索引，那麼性能可能會非常慢（可以為全文索引設定單獨的鍵緩存（key cache），保證不會被其他的索引緩存擠出記憶體）
3：相比其它的索引類型，當insert、update和delete操作進行時，全文索引的操作代價非常大。而且全文索引會有更多的碎片，可能需要做更多的optimize table操作。
4：全文索引優先級在索引中最高，即便這時有更合适的索引可用，MySQL也會放棄性能比較，優先使用全文索引。
5：全文索引不存儲索引列的實際值，也就不可能用作索引覆寫掃描。
6：除了相關性排序，全文索引不能用作其他的排序。如果查詢需要做相關性以外的排序操作，都需要使用檔案排。