MySQL 全文搜索實戰：FULLTEXT 索引、自然語言模式與布爾模式精準檢索

為什麼不用 LIKE？

SELECT ... LIKE '%keyword%' 全表掃描，大數據量性能極差，且無相關性排序。

MySQL 內建全文搜索方案

• 標記字段 FULLTEXT 索引
• 使用 MATCH(col) AGAINST('keyword') 語法
• 自動建立倒排索引，自然語言相關性評分，智能過濾噪音

實戰步驟演示

1. 建表與數據

CREATE TABLE reviews (
  id INT(5) PRIMARY KEY AUTO_INCREMENT,
  data TEXT
) ENGINE=MyISAM;  # 5.6+ InnoDB 也支持

INSERT INTO reviews (data) VALUES
('Gingerboy has a new single out called Throwing Rocks. It\'s great!'),
('Hello all, I really like the new Madonna single. One of the hottest tracks...'),
('Have you heard the new band Hotter Than Hell? They have five members and burn instruments...');

2. 添加全文索引

ALTER TABLE reviews ADD FULLTEXT INDEX (data);
SHOW INDEXES FROM reviews;
# 顯示 Index_type = FULLTEXT

3. 自然語言模式（默認）

# 查找包含 "single" 的記錄
SELECT id FROM reviews WHERE MATCH(data) AGAINST('single');
# 返回 id=1,2

# 查看相關性評分
SELECT id, MATCH(data) AGAINST('rock') AS score FROM reviews;
# id=3 score=1.386，id=1,2 score=0

評分越高相關性越強，MySQL 內部基於詞頻/文檔頻率/長度歸一化計算。

4. 布爾模式（精確控制）

# 必須包含 single，排除 madonna
SELECT id FROM reviews WHERE MATCH(data) AGAINST('+single -madonna' IN BOOLEAN MODE);
# 返回 id=1

# 通配符前綴匹配：查找以 hot 開頭的詞
SELECT id FROM reviews WHERE MATCH(data) AGAINST('hot*' IN BOOLEAN MODE);
# 返回 id=3 (Hotter), id=2 (hottest)

# OR 邏輯：至少包含 hell 或 rocks 之一
SELECT id FROM reviews WHERE MATCH(data) AGAINST('hell rocks' IN BOOLEAN MODE);
# 返回 id=1 (Rocks), id=3 (Hell)

布爾操作符速查

限制與注意事項

• 最小詞長：默認 4 字符（ft_min_word_len），中文需分詞或調整參數
• 停用詞表：默認英文停用詞，可自定義 ft_stopword_file
• 50% 閾值：自然語言模式下，出現在超過 50% 文檔的詞被忽略（布爾模式不受影響）
• 引擎限制：< 5.6 僅 MyISAM；5.6+ InnoDB 支持
• 中文分詞：MySQL 內建不支持中文分詞，生產環境需結合 ngram (5.7.6+) 或外部分詞器 (SCWS/jieba) + 應用層索引