LIKE 模式匹配
基本通配符
-- % 匹配任意長度字符 (含 0 個)
SELECT * FROM users WHERE name LIKE 'Zhang%'; -- 以 Zhang 開頭
SELECT * FROM users WHERE name LIKE '%San'; -- 以 San 結尾
SELECT * FROM users WHERE name LIKE '%Zhang%'; -- 包含 Zhang
-- _ 匹配單個字符
SELECT * FROM users WHERE name LIKE 'Zh_ng'; -- Zhang, Zhong 等
SELECT * FROM users WHERE name LIKE '_____'; -- 正好 5 個字符
-- 轉義特殊字符
SELECT * FROM products WHERE code LIKE '20\%%' ESCAPE '\'; -- 字面匹配 '20%'
SELECT * FROM products WHERE code LIKE '20[%]%'; -- 標準 SQL 轉義
大小寫敏感控制
-- 表/列定義 COLLATE 決定默認行為
-- utf8mb4_general_ci / utf8mb4_0900_ai_ci : 不區分大小寫 (CI)
-- utf8mb4_bin / utf8mb4_0900_as_cs : 區分大小寫 (CS)
-- 強制大小寫敏感
SELECT * FROM users WHERE name LIKE BINARY 'Zhang%'; -- 僅匹配 'Zhang' 開頭
SELECT * FROM users WHERE name COLLATE utf8mb4_bin LIKE 'Zhang%';
-- 強制不敏感 (列默認 CS 時)
SELECT * FROM users WHERE name COLLATE utf8mb4_general_ci LIKE 'zhang%';
LIKE 性能鐵律
| 模式 |
能否用索引 |
性能 |
備註 |
LIKE 'prefix%' |
✅ Range Scan |
快 |
前綴固定,走 B+Tree 範圍 |
LIKE '%suffix' |
❌ Full Scan |
慢 |
反轉列 + 正向 LIKE 可優化 |
LIKE '%middle%' |
❌ Full Scan |
極慢 |
必須用全文索引/搜索引擎 |
LIKE '_abc%' |
❌ Full Scan |
慢 |
首字符通配符同 % 開頭 |
REGEXP 正則表達式
常用元字符
-- 基礎
'.' 任意單字符
'^' 字符串開頭
'$' 字符串結尾
'*' 前面字符 0 次或多次
'+' 前面字符 1 次或多次
'?' 前面字符 0 次或 1 次
'|' 或 (分支)
'()' 分組
'[]' 字符類 [abc] [a-z] [0-9]
'[^]' 排除字符類 [^abc]
'{n}' 精確 n 次
'{n,m}' n 到 m 次
-- 字符類簡寫 (MySQL 8.0+)
[:digit:] = [0-9]
[:alpha:] = [a-zA-Z]
[:alnum:] = [a-zA-Z0-9]
[:space:] = 空白字符
實戰範例
-- 以 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}$'; -- 更清晰
-- 包含數字
SELECT * FROM pet WHERE name REGEXP '[0-9]';
SELECT * FROM pet WHERE name REGEXP '[[:digit:]]';
-- 只包含字母數字下劃線
SELECT * FROM users WHERE username REGEXP '^[a-zA-Z0-9_]+$';
-- 手機號簡易驗證
SELECT * FROM users WHERE phone REGEXP '^1[3-9][0-9]{9}$';
-- 郵箱簡易驗證
SELECT * FROM users WHERE email REGEXP '^[^@]+@[^@]+\.[^@]+$';
REGEXP vs LIKE 性能
REGEXP 不走普通 B+Tree 索引 (8.0+ 支持函數索引間接支持)
- 複雜正則比
LIKE '%...%' 稍快 (內部優化),但同樣需全表掃描
- 大數據量**必須**用全文索引或 Elasticsearch
全文索引:專業級模糊搜索
ngram 解析器 (MySQL 5.7.6+ 內置,無需安裝)
[mysqld]
ngram_token_size = 2 -- 中文雙字分詞
CREATE TABLE articles (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
三種查詢模式
-- 1. 自然語言模式 (默認,按相關性排序)
SELECT *, MATCH(title, content) AGAINST('MySQL 優化') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 優化')
ORDER BY score DESC;
-- 2. 布爾模式 (精確控制)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle 優化*' IN BOOLEAN MODE);
-- +MySQL: 必須包含
-- -Oracle: 必須不包含
-- 優化*: 前綴匹配
-- 3. 查詢擴展 (自動發掘相關詞)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('數據庫調優' WITH QUERY EXPANSION);
中文分詞效果對比
-- 數據: '真的不會吧'
-- ngram (token_size=2): '真的', '的不', '不會', '會吧' -- 機械切分
-- MeCab (詞級): '真的', '不會吧' -- 語義切分
-- 測試查詢
SELECT * FROM t3 WHERE MATCH(name2) AGAINST('不會吧' IN BOOLEAN MODE);
-- ngram: 能查到 '不會吧'、'不會吧1'、'真的 不會吧' (按雙字匹配)
-- MeCab: 更精準匹配詞邊界
全文索引優化關鍵
- 大量導入:先導數據,後
ALTER TABLE ADD FULLTEXT INDEX (快 10x)
- 定期 OPTIMIZE:
OPTIMIZE TABLE articles 合併碎片段、回收刪除空間
- 避免混合查詢:全文索引列與普通索引列混用 WHERE 會導致全表掃描,用子查詢/臨時表分離
- 停用詞:ngram 無停用詞;MeCab 需配置
mecabrc 排除「的、了、在、是」等高頻詞
PHP 應用層最佳實踐
prepare("
SELECT id, title, LEFT(content, 200) AS snippet
FROM articles
WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
ORDER BY MATCH(title, content) AGAINST(? IN BOOLEAN MODE) DESC
LIMIT 20
");
$stmt->execute([$keyword, $keyword]);
// 2. 關鍵字預處理:轉義布爾操作符
function escape_boolean($str) {
return preg_replace('/([+\-><~*()"])/', '\\\\$1', $str);
}
$safe_keyword = escape_boolean($keyword);
$stmt->execute(["+$safe_keyword*", "+$safe_keyword*"]);
// 3. 高亮顯示 (應用層實現)
function highlight($text, $keyword) {
return preg_replace('/(' . preg_quote($keyword, '/') . ')/i', '$1', $text);
}
?>
選型決策:何時用什麼
| 場景 |
推薦方案 |
理由 |
| 前綴匹配 (自動完成、下拉提示) |
LIKE 'prefix%' + 索引 |
極快、原生支持、無額外維護 |
| 固定格式驗證 (手機、郵箱、身份證) |
REGEXP / 應用層驗證 |
精確匹配,數據量小不影響性能 |
| 文章/商品/日志全文搜索 (< 1000 萬行) |
MySQL Fulltext + ngram/MeCab |
零依賴、事務一致、運維簡單 |
| 複雜搜索 (高亮、聚合、多租戶、拼音/同義詞) |
Elasticsearch / OpenSearch |
專業搜索引擎,功能完整、水平擴展 |
| 超大規模 (> 5000 萬行) / 實時寫入查詢 |
ES + Canal 同步 / ClickHouse |
MySQL 全文索引寫入性能瓶頸 |
核心建議:
1. 能用 LIKE 'prefix%' 解決的,絕不上 REGEXP/全文索引
2. 中文全文搜索,**MySQL 5.7+ 必須用 InnoDB + ngram/MeCab**,別再改 ft_min_word_len
3. 搜索是獨立業務域,超出 MySQL 能力邊界時,果斷引入 Elasticsearch,MySQL 做主存儲、ES 做搜索索引,Canal 同步 Binlog 保證最終一致