一、LIKE 模糊查詢基礎

通配符

• %：匹配任意長度字元（含 0 個）
• _：匹配**單個**任意字元

常用範例

-- 以 'a' 開頭
SELECT * FROM student WHERE name LIKE 'a%';

-- 以 'e' 結尾
SELECT * FROM student WHERE name LIKE '%e';

-- 包含 'a' 任意位置
SELECT * FROM student WHERE name LIKE '%a%';

-- 固定長度 5 字元
SELECT * FROM student WHERE name LIKE '_____';

-- 轉義特殊字元 (搜尋實際的 % 或 _)
SELECT * FROM student WHERE name LIKE '%\%%' ESCAPE '\\';

大小寫敏感度

• 預設 不區分大小寫 (依字元集排序規則，如 utf8mb4_general_ci)
• 強制區分：WHERE name LIKE BINARY 'A%' 或 COLLATE utf8mb4_bin

二、REGEXP 正則表達式匹配

常用元字元

元字元	說明	範例
.	任意單字元	'a.c' 匹配 'abc', 'axc'
[...]	字元類別	'[abc]' 匹配 a/b/c; '[a-z]' 小寫字母
[^...]	排除字元類別	'[^0-9]' 非數字
*	前一元素 0~n 次	'ab*c' 匹配 'ac', 'abc', 'abbc'
+	前一元素 1~n 次	'ab+c' 匹配 'abc', 'abbc'
?	前一元素 0~1 次	'ab?c' 匹配 'ac', 'abc'
{n},{n,m}	精確 n 次 / n~m 次	'a{3}' = 'aaa'; 'a{2,4}' = 'aa'~'aaaa'
^	字串開頭	'^abc' 以 abc 開頭
$	字串結尾	'abc$' 以 abc 結尾
|	或 (OR)	'cat|dog' 匹配 cat 或 dog
(...)	分組	'(ab)+' 匹配 'ab', 'abab'

實戰範例

-- 以 b/B 開頭
SELECT * FROM pet WHERE name REGEXP '^[bB]';

-- 以 fy 結尾
SELECT * FROM pet WHERE name REGEXP 'fy$';

-- 包含 w/W
SELECT * FROM pet WHERE name REGEXP '[wW]';

-- 正好 5 個字元
SELECT * FROM pet WHERE name REGEXP '^.....$';
-- 或
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

REGEXP vs LIKE 效能

• REGEXP **無法使用索引**（全表掃描），大表慎用
• LIKE 'prefix%' 可用索引 (範圍掃描)，LIKE '%suffix' 不能
• MySQL 8.0 REGEXP 採用 ICU 庫，支援 Unicode 屬性 \p{Han} 匹配中文

三、中文全文搜尋方案

方案一：MySQL 內建 ngram Parser (5.7.6+)

-- 建表指定 ngram parser
CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT (content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 搜尋 (自動按 bigram 切分)
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH(content) AGAINST('+数据库 -教程' IN BOOLEAN MODE);

優點：零依賴、支援 InnoDB 事務。缺點：bigram 切分精度有限、不支援布林模式通配符/片語。

方案二：外部分詞器 + 儲存分詞結果

-- 應用層分詞 (jieba, SCWS, IK Analyzer 等)
-- 寫入時同步更新分詞表
CREATE TABLE article_keywords (
    article_id INT PRIMARY KEY,
    keywords TEXT,  -- 空格分隔的分詞
    FULLTEXT (keywords)
);

-- 搜尋
SELECT a.* FROM articles a
JOIN article_keywords k ON a.id = k.article_id
WHERE MATCH(k.keywords) AGAINST('+数据库 +优化' IN BOOLEAN MODE);

方案三：專用搜尋引擎（生產環境推薦）

• Elasticsearch / OpenSearch：分散式、近即時、豐富分析器、中文分詞插件 (ik, pinyin)
• Typesense / MeiliSearch：輕量、易部署、開箱即用中文支援
• Sphinx / Manticore Search：MySQL 協議相容、增量索引快

四、選擇建議決策樹

資料量 < 10萬、簡單前綴搜尋 → LIKE 'prefix%' + 索引
資料量 < 100萬、需包含/後綴/中文 → ngram 全文索引 (MySQL 5.7+)
資料量 > 100萬、高併發、複雜查詢 → Elasticsearch / Typesense
需精確正則匹配、低頻 → REGEXP (接受全表掃描)

五、效能優化 Tips

• 避免 LIKE '%...%' 開頭通配符 — 全表掃描
• 前綴索引：INDEX idx_name_prefix (name(10)) 配合 LIKE 'prefix%'
• 大表正則查詢：導出到應用層或 ClickHouse 處理
• 全文索引：定期 OPTIMIZE TABLE 整理碎片
• 監控 Handler_read_rnd_next / Com_select (表掃描比率) 超過 4000 需優化