MySQL REPLACE 與 REGEXP 完全指南：字符串替換、正則匹配與中文模糊查詢優化

MySQL 提供 REPLACE 字符串替換函數與 REGEXP 正則表達式匹配，是數據清洗、模糊搜索、文本處理的利器。本文系統整理語法、實戰案例、中文編碼陷阱與性能優化建議。

一、REPLACE 相關用法

1. REPLACE INTO（插入或替換行）

REPLACE INTO table (id, name) VALUES ('1', 'aa'), ('2', 'bb');

邏輯：若主鍵/唯一鍵衝突 → 刪除舊行 → 插入新行。等效於 DELETE + INSERT。

⚠ 需表有主鍵或唯一索引，否則退化為普通 INSERT。

2. REPLACE(str, from_str, to_str)（字符串替換函數）

SELECT REPLACE('www.163.com', 'w', 'Ww');  -- 返回 'WwWwWw.163.com'

-- 批量更新表字段
UPDATE `table` SET `name` = REPLACE(`name`, 'aa', 'bb');

將字段中所有 aa 替換為 bb，區分大小寫。

二、REGEXP 正則表達式完整語法

元字符速查表

字符類快捷方式

單詞邊界

• [[:<:]]：單詞開頭
• [[:>:]]：單詞結尾

SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';  -- 1 (匹配)
SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]'; -- 0 (不匹配)

實戰查詢案例

-- 以 b 開頭
SELECT * FROM pet WHERE name REGEXP '^b';

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

-- 包含 w
SELECT * FROM pet WHERE name REGEXP 'w';

-- 精確 5 字符
SELECT * FROM pet WHERE name REGEXP '^.....$';
-- 或
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

三、中文 LIKE 模糊查詢亂碼/誤匹配問題

現象

SELECT * FROM contact WHERE name LIKE '%a%';
-- 結果竟包含中文「新」！

根因

MySQL 默認字符集 latin1/utf8 且 不區分大小寫，中文 UTF-8 編碼中某些字節恰好落在 ASCII 字母範圍，導致大小寫轉換邏輯誤判。

解決方案對比

最佳實踐

-- 不區分大小寫、正確處理中文
SELECT id, title FROM news 
WHERE BINARY UCASE(title) LIKE CONCAT('%', UCASE('搜索詞'), '%');

四、REGEXP 特殊字符轉義

正則中使用字面量特殊字符需雙反斜杠：

SELECT '1+2' REGEXP '1\\+2';  -- 1 (匹配)
SELECT '1+2' REGEXP '1+2';    -- 0 (+ 被視為元字符)
SELECT '1+2' REGEXP '1\+2';   -- 0 (單反斜杠被 SQL 消費)

五、性能優化建議

1. 避免前導通配符：LIKE '%abc' / REGEXP 'abc' 導致全表掃描
2. 優先用 LIKE 前綴：LIKE 'abc%' 可用索引
3. 全文搜索用 Fulltext 索引：大文本字段建 FULLTEXT，配合 MATCH...AGAINST
4. REGEXP 不走索引：MySQL 8.0 前 REGEXP 完全不使用索引，慎用於大表
5. 應用層預處理：複雜文本分析考慮 Elasticsearch

總結：REPLACE 適合精確替換；REGEXP 功能強但性能弱；中文模糊查詢用 BINARY UCASE(...) 組合；大規模搜索上專業搜索引擎。