全文檢索(Full-Text Search)是SQL中用于高效搜索文本數據的技術，與傳統的LIKE操作或簡單字符串比較相比，它能提供更強大、更靈活的文本搜索能力。

---

基本概念

全文檢索的核心思想是將文本內容分解為可索引的單元(通常是詞或詞組)，然后建立倒排索引(Inverted Index)來快速定位包含特定詞匯的文檔。

工作原理

1. 文本分析與分詞(Tokenization)

• 將文本分解為詞元(tokens)或詞項(terms)

• 移除停用詞(stop words)如"a", "the", "and"等

• 應用詞干提取(stemming)將單詞還原為詞根形式(如"running"→"run")

• 可能還包括大小寫轉換、特殊字符處理等

2. 索引構建

• 創建倒排索引：記錄每個詞項出現在哪些文檔中

• 存儲詞項的位置信息(用于短語搜索)

• 可能包括詞頻(TF)和逆文檔頻率(IDF)等統計信息

3. 查詢處理

• 解析用戶查詢(可能包括布爾操作符AND/OR/NOT)

• 擴展查詢(如同義詞、拼寫糾正)

• 使用索引快速定位相關文檔

• 計算相關性得分并對結果排序

---

SQL中的全文檢索實現

不同數據庫系統的全文檢索實現略有不同：

MySQL (MyISAM/InnoDB)

-- 創建全文索引
CREATE FULLTEXT INDEX idx_name ON table_name(column_name);-- 使用全文搜索
SELECT * FROM table_name 
WHERE MATCH(column_name) AGAINST('search term');

SQL Server

-- 創建全文目錄和索引
CREATE FULLTEXT CATALOG ft_catalog AS DEFAULT;
CREATE FULLTEXT INDEX ON table_name(column_name) 
KEY INDEX pk_index_name ON ft_catalog;-- 使用CONTAINS或FREETEXT搜索
SELECT * FROM table_name 
WHERE CONTAINS(column_name, '"search term"');

PostgreSQL

-- 創建全文搜索列和索引
ALTER TABLE table_name ADD COLUMN tsv_column tsvector;
UPDATE table_name SET tsv_column = to_tsvector('english', text_column);
CREATE INDEX idx_gin ON table_name USING GIN(tsv_column);-- 使用搜索
SELECT * FROM table_name 
WHERE tsv_column @@ to_tsquery('english', 'search & term');

---

高級特性

1. 相關性排序：根據匹配程度對結果排序

2. 模糊搜索：處理拼寫錯誤或近似匹配

3. 短語搜索：查找精確的短語而不僅是單個詞

4. 同義詞擴展：自動包含同義詞搜索結果

5. 加權搜索：為特定字段或詞項分配更高權重

性能考慮

• 全文索引通常比傳統索引占用更多空間

• 索引更新可能影響寫入性能

• 復雜查詢可能需要更多處理時間

• 需要定期優化索引以保持性能