全文搜尋基礎

MySQL 3.23.23+ 支援全文索引（FULLTEXT），僅適用於 MyISAM 表的 CHAR、VARCHAR、TEXT 欄位。

建立全文索引

-- 建表時
CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title, body)
) ENGINE=MyISAM;

-- 事後新增
ALTER TABLE articles ADD FULLTEXT (title, body);
-- 或
CREATE FULLTEXT INDEX ft_idx ON articles (title, body);

效能提示：大量資料先匯入、再建立索引（ALTER TABLE）遠快於先建索引再匯入。

自然語言模式搜尋

使用 MATCH() ... AGAINST()，預設為自然語言模式，不分大小寫，按相關性遞減排序。

基本查詢

SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('database');

取得相關性分數

SELECT id, MATCH (title, body) AGAINST ('Tutorial') AS score
FROM articles;

分數為非負浮點數，0 表示不相關。計算依據：詞頻、唯一詞數、全集詞頻、文檔頻率（TF-IDF 變體）。

同時顯示分數並排序

SELECT id, body,
       MATCH (title, body) AGAINST ('Security implications of running MySQL as root') AS score
FROM articles
WHERE MATCH (title, body) AGAINST ('Security implications of running MySQL as root');

優化器會合併雙重 MATCH() 呼叫，無額外開銷。

詞語解析與停用詞

• 「詞」定義：字母、數字、'、_ 組成的序列。
• 忽略：停用詞表、長度 ≤ 3 字元的詞。
• 權重機制：出現在多文檔的詞權重低（語義值低），稀有詞權重高。

50% 閾值現象

SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('MySQL');

若搜尋詞出現在超過 50% 的行中，視為停用詞，回傳空集合。這是設計使然，避免高頻詞汙染結果。

布林模式搜尋（IN BOOLEAN MODE）

MySQL 4.0.1+ 支援，不受 50% 閾值限制，不自動按相關性排序。

操作符完整表

操作符	含義	範例
+	必須包含	+MySQL
-	必須不包含	-YourSQL
(無)	可選，包含者排序較前	MySQL
> / <	增加/降低基礎權重	+apple +(>pie <strudel)
()	分組	+apple +(pie strudel)
~	負向貢獻（噪聲詞），降低排序但不排除	~noise
*	截斷符（置於詞尾）	apple* 匹配 apple, apples, applesauce
"..."	片語精確匹配	"some words"

布林模式範例

-- 必須含 MySQL，排除 YourSQL
SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

全文搜尋限制

• MATCH() 參數必須來自同一張表，且屬於同一個 FULLTEXT 索引（布林模式除外）。
• MATCH() 欄位列表須與索引定義完全一致（布林模式除外）。
• AGAINST() 參數必須為常數字串。

微調全文搜尋（進階）

可調參數

• ft_min_word_len：最小索引詞長度（預設 4）。修改後需重建索引。
• ft_stopword_file：自訂停用詞檔案路徑。修改後需重建索引。

關閉 50% 閾值（需重編譯）

修改 myisam/ftdefs.h：

#define GWS_IN_USE GWS_PROB
→ #define GWS_IN_USE GWS_FREQ

重編譯 MySQL 後生效，但會嚴重降低相關性判斷能力，建議改用布林模式。

重建索引最快方式

REPAIR TABLE tbl_name QUICK;

全文搜尋待辦功能

• 鄰近操作符
• "always-index words" 支援（如 C++、TCP/IP）
• MERGE 表全文搜尋
• 多位元組字元支援（中文分詞）
• 按語言的停用詞表、詞幹提取
• 使用者自定義 UDF 前置解析器
• 更靈活的 CREATE/ALTER TABLE 參數