MySQL REPLACE() 函數實戰：字串批量替換指南

前言

MySQL 內建的 REPLACE() 函數是處理字串批量替換的利器，常用於數據清洗、內容修正、編碼轉換等場景。本文通過實例詳細說明其語法、用法及注意事項。

語法結構

REPLACE(str, from_str, to_str)

• str：原始字串或欄位名
• from_str：要被替換的子字串
• to_str：替換後的新子字串

回傳值：替換後的新字串。若任一參數為 NULL，回傳 NULL。

核心實例

1. 基礎更新：修正配置表中的錯誤值

UPDATE users_settings 
SET ConfigValue = REPLACE(ConfigValue, 'fromstr', 'tostr')
WHERE ConfigName = 'Accesslist';

將 ConfigValue 欄位中所有 fromstr 替換為 tostr，條件為 ConfigName = 'Accesslist'。

2. 數據清洗：移除多餘空格與特殊字符

UPDATE products
SET description = TRIM(REPLACE(REPLACE(description, CHAR(13), ''), CHAR(10), ''))
WHERE description LIKE '%' + CHAR(13) + '%' OR description LIKE '%' + CHAR(10) + '%';

巢狀 REPLACE 移除換行符，外層 TRIM 去除首尾空格。

3. 批量修正 URL 域名遷移

UPDATE articles
SET content = REPLACE(content, 'https://old-domain.com', 'https://new-domain.com')
WHERE content LIKE '%https://old-domain.com%';

配合 WHERE 條件縮小掃描範圍，提升執行效率。

進階技巧與注意事項

1. 大小寫敏感性

REPLACE() 在預設校對規則下不區分大小寫（如 utf8mb4_general_ci）。若需區分大小寫替換，可使用二進制校對：

UPDATE t SET col = REPLACE(col COLLATE utf8mb4_bin, 'ABC', 'xyz');

2. 避免全表掃描的性能陷阱

• 永遠加上 WHERE 條件過濾需更新的行（如 col LIKE '%from_str%'）。
• 大表分批次更新，避免長事務鎖表、日誌暴漲：

-- 分批更新模板
SET @batch_size = 5000;
REPEAT
    UPDATE large_table
    SET col = REPLACE(col, 'old', 'new')
    WHERE id > @last_id AND col LIKE '%old%'
    ORDER BY id LIMIT @batch_size;
    SET @last_id = (SELECT MAX(id) FROM large_table WHERE col LIKE '%old%');
    SET @row_count = ROW_COUNT();
UNTIL @row_count = 0 END REPEAT;

3. 替換結果長度超限風險

若 to_str 比 from_str 長，可能導致欄位長度溢出（Data truncated）。執行前預估最大長度：

SELECT MAX(CHAR_LENGTH(REPLACE(long_col, 'short', 'very_long_replacement'))) FROM big_table;

4. 事務與備份

• 大規模更新前務必備份或建立臨時表備份：CREATE TABLE bak_table AS SELECT * FROM target_table;
• 使用顯式事務：START TRANSACTION; ... COMMIT;/ROLLBACK;

替代方案對比

結語

REPLACE() 是 MySQL 字串處理的基石函數，掌握其分批更新、校對規則、長度預估等實戰技巧，能安全高效地完成數據清洗與修正任務。記住鐵律：先備份、加 WHERE、分批次、驗證結果。