一、業務場景我們在實際生產環境中遇到了這樣一種需求，即需要檢索一個父子關係的子樹數據估計大家也遇到過類似的場景，最典型的就是省市數據，其中path欄位是按層級關係生成的行政區路徑：如果我們已知某市名，想查出同級和高一級的省名，如保定市同級和上級河北省，那麼我們有什麼實現方式呢給大家10秒鐘，快速搶答...
一、業務場景
我們在實際生產環境中遇到了這樣一種需求，即需要檢索一個父子關係的子樹數據
估計大家也遇到過類似的場景，最典型的就是省市數據，其中path欄位是按層級關係生成的行政區路徑：
如果我們已知某市名，想查出同級和高一級的省名，如保定市同級和上級河北省，那麼我們有什麼實現方式呢
給大家10秒鐘，快速搶答
.
.
.
時間到
大概有以下幾種方法：
a.2008中新添加的層級數據類型
b.cte遞歸方式
c.直接程式處理
d.比較傻的方式：全文索引。。。
層級關係是實現這種業務邏輯的最好方式，比較經典的方式是遞歸方式，但是，我們不講這兩個方法內容和實現，因為今天的主題是：全文索引中的坑，因為這是我實實在在遇到的案例
我們的業務邏輯是這樣的，已知一個或者幾個ID，但並不瞭解這個ID的層次關係，需要的數據是提取對應ID的所有層次，比如按上圖來說，我傳入id in(9,910)那麼就要所有河北省的數據，如果我傳入id in(910)，那麼所取的數據就是保定市以及下屬所有行政區
二、問題出現
為了簡化說明問題，我把數據精簡為表pathtest，數據為下圖形式
按照上段說明的業務需求，在path需要建立一個全文索引方便檢索，這個過程相信大家都知道如何建，就不描寫過程了
我們先看一下實際包含66的數據行有多少？（或者說ID=66及以下的子樹數據集）
select * from pathtest where path like '%,66,%'
(49 行受影響)
結果省略一部分，但可以看出是49行
之後我運行如下的查詢，查詢路徑下包含66的數據集，也就是ID=66以及之下的子樹
select * from pathtest where freetext(path,'"66"')
(17 行受影響)
顯示是17行結果？？
差這麼多啊？就說是全文索引不精確，這也差太多了吧
OK，我們繼續
select * from pathtest where freetext(path,'"66,480"')
同樣，按邏輯上說，這個查詢同樣是檢索66層次ID下的子樹（PS:這裡480是在66子樹下）
但是：
1行？？？這是什麼情況
我們乾脆再檢索一下ID=480的數據吧
select * from pathtest where freetext(path,'"480"')
這下更方便看了，根本就沒有數據，這是怎麼回事？
三、問題研究
有些經驗的同學肯定已經想到這是全文索引建立的時候分詞出現了問題，那麼我們就看一下
sys.dm_fts_parser 這個函數是對字元串按全文索引方式進行關鍵字拆分，具體使用請查閱MSDN
我分別取兩個路徑串
1.path=’,66,73,’
2.path=’,66,480,’
我們來看一下拆分的結果
select * from sys.dm_fts_parser ('",66,73,"',1033,0,1)
嗯，把66,73分別拆分成字元串關鍵字“66”、“73”以及數值(nn開頭)的66、73一共四條記錄，沒有問題
select * from sys.dm_fts_parser ('",66,480,"',1033,0,1)
這下問題出現了，字元串並沒有按我們想的那樣拆分成“66”與“480”的關鍵字，而是生成了”66,480”與66480的兩個關鍵字
所以我們在進行freetext(path,'"66,480"')檢索的時候返回了一條滿足條件的記錄，且freetext(path,'"480"')的時候沒有滿足關鍵字的記錄。
這裡涉及到一個全文索引關鍵詞拆分演算法的問題，sqlserver把類似66,480這種字元串當成了有千分位分隔符的純數字，所以不再進行拆分
但是像66,73這種字元串，不是數字的千分位分隔表示方法，所以進行了拆分
假如我們把66,480這個字元串改寫成66,480,1,則會進行字元串方式的拆分，如下：
select * from sys.dm_fts_parser ('",66,480,1,"',1033,0,1)
同時我們知道freetext這個函數也是以建立全文索引的方式進行檢索詞拆分後與全文索引進行比較，所以在進行where freetext(path,'"66,480"')檢索的時候，也是把字元串進行了處理，但同樣是將字元串以千分位分隔數字進行了處理，所以可以檢索出一條path=’66,480’記錄，但是在進行where freetext(path,'"480"')的時候，由於全表的全文索引中，並沒有拆分出480這個字元串關鍵詞，所以沒有滿足條件的記錄
如果我們將檢索串改寫為where freetext(path,'"66 480"'),那麼對於這個邏輯來說，就是要檢索全文索引中滿足66或者480關鍵字的結果集，在本例中，結果集為17條，即與where freetext(path,'"66"')相同(因為空格符做為了斷字元將字元拆為了“66”與“480”，表中滿足66的結果集為17條，480為0條，所以結果總數為17條)
四、解決方案
既然知道了是”,”引起的字元串誤判斷，我們就把這個替換掉
update pathtest set path=replace(path,',',';')
等全文索引重新填充完成後，執行
select * from pathtest where freetext(path,'"66"')
(49 行受影響)
好！與直接like的結果一致
之後我們再運行
select * from pathtest where freetext(path,'"66,480"')
(0 行受影響)
！！！什麼情況 ！？
之前我們說了，“66,480”會被誤判斷拆分，所以這裡的檢索詞也不能這麼寫了，可以寫空格，也可以寫分號，反正就是不能用逗號，將查詢改為
select * from pathtest where freetext(path,'"66 480"')
(49 行受影響)
select * from pathtest where freetext(path,'"480"')
(1 行受影響)
好了，這下與期待結果一致了
五、後記
這個問題解決前還出過一段插曲，把“,”替換成了“/”,但是在進行拆分的時候/66/480/拆分成了”66”與”480/”，後來將/加入了stoplist，這個問題就解決掉了，本次測試我沒有再現這個情況，應該是當時設置的斷字語言導致的，有興趣的同學可以自己玩一下。
切記如果你要建的全文索引中有類似的欄位，清註意逗號問題，同時在freetext檢索的時候，也同樣要註意“,”的問題。
更多深入sqlserver文章，請關註微信號：insidesqlserver 或者掃描二維碼
