MySQL 模糊搜索實戰：LIKE 模式匹配、全文索引原理、中文分詞限制與避坑指南

一、LIKE 模式匹配四大通配符

注意：[]、[^] 為 SQL Server / Access 語法，MySQL 不支持，MySQL 僅支持 % 和 _；如需字符集合匹配請用 REGEXP。

二、LIKE 性能陷阱與優化

• 前導通配符 %abc 無法使用索引，全表掃描。
• 後置通配符 abc% 可走索引（前綴索引）。
• 大表模糊搜索必須：
   1. 全文索引（MyISAM/InnoDB 5.6+）
   2. 搜索引擎

三、MySQL 內建全文索引

1. 創建測試表與數據

CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data TEXT
) ENGINE=MyISAM DEFAULT CHARSET=utf8;  -- 5.6 前僅 MyISAM 支持

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...');

2. 添加全文索引

ALTER TABLE reviews ADD FULLTEXT INDEX ft_data (data);
-- 或建表時指定
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data TEXT,
    FULLTEXT KEY ft_data (data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  -- 5.6+ InnoDB 支持

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

SELECT id, MATCH(data) AGAINST('single') AS relevance
FROM reviews
WHERE MATCH(data) AGAINST('single');
-- 返回 relevance > 0 的行，按相關性降序

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

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

-- 通配符 *：包含 hot 的詞（hotter, hot）
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('hot*' IN BOOLEAN MODE);

-- 至少包含 hell 或 rocks 之一
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('hell rocks' IN BOOLEAN MODE);

四、中文全文搜索的致命限制

• MySQL 內建全文索引按空格/標點分詞，中文無分隔符 → 無法正確分詞。
• 5.7+ 引入 ngram 解析器（按 n-gram 切分），但精度一般，不支持停用詞、同義詞。
• 生產環境中文搜索強烈建議接入專業搜索引擎：
   - Elasticsearch + IK Analyzer（最主流）
   - Solr + mmseg4j
   - 算術/向量檢索

五、LIKE 查詢中通配符轉義

-- 查詢包含字面 % _ \ 的記錄
SELECT * FROM t WHERE col LIKE '%\%%' ESCAPE '\';  -- 包含 %
SELECT * FROM t WHERE col LIKE '%\_%' ESCAPE '\';  -- 包含 _

-- 應用層轉義函數
FUNCTION sql_escape(str):
    RETURN REPLACE(REPLACE(REPLACE(str, '\', '\\'), '%', '\%'), '_', '\_')

六、總結選型表