前置檢查
-- 1. 確認資料庫是否已啟用全文檢索
SELECT DATABASEPROPERTY('pubs', 'IsFulltextEnabled'); -- 1=啟用, 0=未啟用
啟用/停用全文檢索
-- 啟用
EXEC sp_fulltext_database 'enable';
-- 停用
EXEC sp_fulltext_database 'disable';
建立全文索引步驟 (SSMS 圖形化)
- 展開資料庫 → 資料表 → 右鍵目標表 (如
Products) → 全文索引 → 定義全文索引。
- 選擇唯一索引鍵欄位 (必須為單欄、非 NULL、唯一，通常是 PK)。
SSMS 中：索引/鍵值 → 新增 → 類型選 Unique Key、欄位選 PK。
- 勾選要全文索引的文字欄位 (如
ProductName, QuantityPerUnit)。可指定語言斷詞器。
- 選擇/建立全文目錄 (如
proCatalog)。目錄是全文索引的實體容器。
- 設定填充排程：完整填充 (建立初始索引)、增量填充 (追蹤變更)、自動填充 (背景自動)。初次建議「立即執行完整填充」。
- 完成精靈。在「全文目錄」節點可見新目錄，右鍵屬性查看狀態、填充進度。
手動填充與監控
-- 立即啟動完整填充
ALTER FULLTEXT CATALOG proCatalog REBUILD;
-- 或
EXEC sp_fulltext_catalog 'proCatalog', 'start_full';
-- 檢查填充狀態
SELECT * FROM sys.fulltext_catalogs WHERE name = 'proCatalog';
SELECT * FROM sys.dm_fts_index_population WHERE database_id = DB_ID('pubs');
全文查詢實戰
| 需求 | T-SQL 範例 | 說明 |
|---|
| 包含特定字詞 (前綴比對) | SELECT * FROM Products WHERE CONTAINS(ProductName, '"Chai*"') | 雙引號包字詞，* 為前綴萬用字元 |
| AND/OR/NEAR 邏輯組合 | WHERE CONTAINS(ProductName, '"Chai" AND "Chang"')
WHERE CONTAINS(ProductName, '"Chai" NEAR "Chang"') | NEAR 表示鄰近 (預設 50 字內) |
| 自由文字查詢 (詞幹/同義詞) | SELECT * FROM Products WHERE FREETEXT(ProductName, 'chinese tea') | 自動展開詞幹、同義字，適合使用者自由輸入 |
| 取得排名分數 (相關性) | SELECT *, rank FROM CONTAINSTABLE(Products, ProductName, 'Chai') AS ft JOIN Products p ON p.ProductID = ft.[KEY] ORDER BY rank DESC | 回傳 KEY 與 RANK (0-1000)，可排序顯示最相關 |
| 多欄位搜尋 | WHERE CONTAINS((ProductName, QuantityPerUnit), 'Chai') | 括號列出多欄位，任一符合即符合 |
系統預存程序對照 (T-SQL 腳本化部署用)
-- 1. 啟用資料庫全文
EXEC sp_fulltext_database 'enable';
-- 2. 建立全文目錄
EXEC sp_fulltext_catalog 'proCatalog', 'create';
-- 3. 註冊資料表
EXEC sp_fulltext_table 'Products', 'create', 'proCatalog', 'PK_Products'; -- PK_Products 為唯一索引名
-- 4. 加入全文索引欄位
EXEC sp_fulltext_column 'Products', 'ProductName', 'add';
EXEC sp_fulltext_column 'Products', 'QuantityPerUnit', 'add';
-- 5. 啟用資料表全文索引
EXEC sp_fulltext_table 'Products', 'activate';
-- 6. 完整填充
EXEC sp_fulltext_catalog 'proCatalog', 'start_full';
效能與維護提醒
- 全文索引屬於非同步填充，資料異動後有延遲 (秒~分鐘) 才可搜尋到。
- 大量匯入資料前，建議停用全文索引 (
sp_fulltext_table 'Products', 'deactivate')，匯入完再 activate + start_full，避免逐筆填充拖慢匯入。
- 定期
REBUILD 或 REORGANIZE 全文目錄碎片 (ALTER FULLTEXT CATALOG ... REORGANIZE)。
- 斷詞器選擇影響中文分詞效果：中文建議使用 Chinese (Simplified/Traditional) Word Breaker。
