您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 字串替換實戰：REPLACE 函數與 LIKE 條件精準清洗髒資料

編輯：MySQL 綜合教學

典型場景：批量修正欄位中特定字符

業務常遇到歷史資料格式不統一，如參考範圍欄位 reference_range 存儲「0-300」需統一為「0~300」，或清洗全形/半形字符、移除不見字符等。MySQL 內建 REPLACE() 函數配合 WHERE 條件可高效完成。

核心語法與執行範例

-- 將 reference_range 中的全形長破音號 '—' (U+2014) 替換為波浪號 '~'
UPDATE tj_item_result
SET reference_range = REPLACE(reference_range, '—', '~')
WHERE reference_range LIKE '%—%';

關鍵點解析

REPLACE(str, from_str, to_str)：將 str 中所有 from_str 替換為 to_str，區分大小寫、全量替換 (非正則)。
WHERE ... LIKE '%—%'：僅更新包含目標字符的行，避免全表掃描寫入、減少 Binlog 體積、降低主從延遲風險。
字符編碼一致性：確保連接字符集 (SET NAMES utf8mb4)、表字符集、SQL 檔案編碼三者一致，否則全形字符可能匹配失敗。

進階清洗模式

多字符鏈式替換

UPDATE products
SET description = REPLACE(REPLACE(REPLACE(description, '　', ' '), '、', ','), '。', '.')
WHERE description LIKE '%　%' OR description LIKE '%、%' OR description LIKE '%。%';

正則替換 (MySQL 8.0+ REGEXP_REPLACE)

-- 移除所有非數字字符
UPDATE orders
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '')
WHERE phone REGEXP '[^0-9]';

批次處理大表避免鎖表

-- 分塊更新，每次 5000 行，配合主鍵範圍
DELIMITER //
CREATE PROCEDURE batch_replace()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE min_id, max_id BIGINT;
  SELECT MIN(id), MAX(id) INTO min_id, max_id FROM tj_item_result;
  WHILE min_id <= max_id DO
    UPDATE tj_item_result
    SET reference_range = REPLACE(reference_range, '—', '~')
    WHERE id BETWEEN min_id AND min_id + 4999
      AND reference_range LIKE '%—%';
    SET min_id = min_id + 5000;
    DO SLEEP(0.1); -- 降低主從壓力
  END WHILE;
END//
DELIMITER ;
CALL batch_replace();

效能與安全檢查清單

檢查項	建議
影響行數預估	先執行 `SELECT COUNT(*) FROM tbl WHERE col LIKE '%target%'` 確認規模
索引可用性	LIKE '%xxx%' 無法使用索引，大表必須分批或用全文索引/外部搜尋引擎預篩選
事務大小	單次事務 < 5000 行，避免 Undo Log 膨脹、鎖升級
主從延遲	生產庫採用 pt-online-schema-change 模式或分批低峰執行
回滾方案	更新前備份主鍵+原值至臨時表：`CREATE TABLE bak AS SELECT id, col FROM tbl WHERE col LIKE '%target%'`
字符集陷阱	全形/半形、零寬空格、BOM 頭等隱形字符用 `HEX(col)` 檢視確認 Unicode 碼位

常見錯誤與修正

錯誤寫法	問題	正確寫法
`UPDATE t SET c = REPLACE(c, '-', '~')`	無 WHERE 條件，全表寫入，Binlog 暴增	加上 `WHERE c LIKE '%-%'`
`REPLACE(c, '—', '~')` 但 SQL 檔為 UTF-8、資料庫為 GBK	字符不匹配，0 行受影響	統一字符集或用 `UNHEX('E28094')` 明確指定 Unicode
在 WHERE 條件用 `c = REPLACE(c, '—', '~')`	邏輯錯誤，WHERE 不支援賦值	WHERE 用 LIKE，SET 用 REPLACE

總結

REPLACE() + LIKE 過濾 是 MySQL 字串清洗的黃金組合：語法簡單、語義明確、配合分批處理可安全應用於千萬級大表。MySQL 8.0 引入 REGEXP_REPLACE 進一步擴展了複雜模式替換能力，但核心原則不變——精準定位目標行、最小化寫入範圍、預留回滾通道。