MySQL 全文搜尋實戰：拒絕 LIKE 全表掃描，擁抱 FULLTEXT 索引與自然語言排序

為什麼不用 LIKE？

SELECT * FROM articles WHERE body LIKE '%keyword%' 在大表上會觸發全表掃描，效能隨資料量線性惡化，且無法按相關度排序。MySQL FULLTEXT 索引 專為此場景設計：建立倒排索引、支援自然語言相關度評分、布林邏輯查詢。

一、建立全文索引

建表時定義

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title, body)
) ENGINE=MyISAM;  -- 5.6+ InnoDB 亦支援

事後新增

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);

驗證索引

SHOW INDEXES FROM articles WHERE Index_type = 'FULLTEXT';

二、核心查詢語法

自然語言模式（預設）

SELECT title, body
FROM articles
WHERE MATCH(title, body) AGAINST('search keywords');

• 自動按相關度降序
• 忽略大小寫
• 50% 門檻：出現於超過半數文檔的詞被視為停用詞

顯式取得相關度分數

SELECT id, title,
       MATCH(title, body) AGAINST('MySQL tutorial') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL tutorial');

布林模式（精準控制）

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -deprecated' IN BOOLEAN MODE);

• 不受 50% 門檻限制
• 不自動排序（需自行 ORDER BY）
• 支援 + - > < () ~ * "..." 運算子

三、效能與限制提醒

• MATCH() 欄位必須完全匹配某個 FULLTEXT 索引的欄位組合（布林模式除外）。
• AGAINST() 參數必須為常數字串，不可用變數或欄位。
• 最小索引詞長 ft_min_word_len 預設 4，中文需配合分詞器或調為 2。
• 停用詞表 ft_stopword_file 可自訂。

四、微調建議

-- 修改最小詞長（需重建索引）
SET GLOBAL ft_min_word_len=2;
-- 自訂停用詞表
SET GLOBAL ft_stopword_file='/etc/mysql/stopwords.txt';

結語

對於中小型專案、單機部署、維運資源有限的場景，MySQL 內建全文搜尋是「零依賴、零成本」的優質選擇。善用 FULLTEXT 索引 + MATCH...AGAINST，即可以 SQL 原生語法實現相關度排序、布林邏輯與片語搜尋，大幅提升使用者搜尋體驗。