MySQL 模糊查詢完全指南：LIKE、REGEXP、全文搜尋三大方案

MySQL 模糊查詢完全指南：LIKE、REGEXP、全文搜尋三大方案

MySQL 提供 LIKE、REGEXP、FULLTEXT 三種模糊匹配機制，適用場景、效能特性、語法能力截然不同。本文系統對比三大方案，含轉義規則、正則元字元、全文搜尋閾值配置、實戰選型建議。

方案一：標準 SQL LIKE / NOT LIKE

通配符

• %：匹配任意長度字串 (含 0 字元)
• _：匹配任意單一字元

常用模式

WHERE col LIKE 'm%'       -- 以 m/M 開頭 (不區分大小寫，依照 collation)
WHERE col LIKE '%m%'      -- 包含 m/M
WHERE col LIKE '%m'       -- 以 m/M 結尾
WHERE col LIKE '_m_'      -- 長度 3 且中間為 m/M

轉義機制

-- 預設反斜線轉義
WHERE col LIKE '%50\%%'        -- 包含 "50%"
WHERE col LIKE '%\_get%'       -- 包含 "_get"

-- 自定義轉義字元 (ESCAPE)
WHERE col LIKE '%50/%%' ESCAPE '/'  -- '/' 轉義下一個 '%'
WHERE col LIKE '%/_get%' ESCAPE '/' -- '/' 轉義 '_'

效能特性

• 前綴匹配 (col LIKE 'abc%') 可用索引 (Range Scan)。
• 前導通配符 (%abc%) 無法使用索引，全表掃描。
• 大小寫敏感度由 COLLATION 決定 (如 utf8mb4_general_ci 不敏感，utf8mb4_bin 敏感)。

方案二：正則表達式 REGEXP / RLIKE

核心元字元

範例

WHERE col REGEXP '^50%{1,3}'  -- 以 50%、50%%、50%%% 開頭
WHERE col REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'  -- 日期格式 YYYY-MM-DD

效能警示

• REGEXP 無法使用索引 (MySQL 8.0 前)，全表掃描 + 正則引擎計算，極慢。
• 8.0 引入 REGEXP_LIKE() 並支援函數索引，但仍不建議高頻核心查詢使用。
• 複雜正則易導致 ReDoS (正則拒絕服務)，需嚴格限制輸入長度與複雜度。

方案三：全文搜尋 FULLTEXT (MyISAM 5.6+ / InnoDB 5.7+)

建表與索引

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

查詢模式

1. 自然語言模式 (預設)：

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

   計算相關性分數，按分數降序返回。50% 閾值規則：若詞項出現在超過 50% 行中，視為噪音詞自動忽略 (導致搜「the」無結果)。
2. 布林模式 (BOOLEAN MODE)：

   SELECT * FROM articles 
   WHERE MATCH(title, body) AGAINST('+mysql -oracle' IN BOOLEAN MODE);
   -- + 必須包含, - 必須排除, * 萬用字元, "精確短語"

   繞過 50% 閾值，支援邏輯運算，不計算相關性分數。
3. 查詢擴展模式：

   SELECT * FROM articles 
   WHERE MATCH(title, body) AGAINST('mysql' WITH QUERY EXPANSION);
   -- 先自然語言搜尋，取高分文檔關鍵詞再二次搜尋

關鍵配置參數

-- 最小索引詞長度 (預設 4，導致搜 "you" 無果)
SET GLOBAL innodb_ft_min_token_size = 2;  -- InnoDB
SET GLOBAL ft_min_word_len = 2;           -- MyISAM
-- 修改後需 REPAIR TABLE / ALTER TABLE ... FORCE 重建索引，並重啟生效

三大方案選型決策表

避坑指南

• FULLTEXT 最小詞長預設 4，中文單字 (1-2 字) 無法搜尋 → 必須調小 innodb_ft_min_token_size 並重建索引。
• 中文分詞： MySQL 內建不支援中文分詞 (5.7+ ngram parser 實驗性)，生產環境**強制使用 Elasticsearch + IK Analyzer**。
• LIKE '%...%' 大表必死，若無法改用 FULLTEXT/ES，考慮冗餘欄位/觸發器維護關鍵字索引表。

架構建議： 業務搜尋功能上線即接入 Elasticsearch/OpenSearch，MySQL FULLTEXT 僅作為極簡單場景或原型驗證備選。REGEXP 僅用於 DDL/遷移腳本/低頻管理查詢，嚴禁核心業務高頻路徑。