MySQL REPLACE 函數全解析：字符串替換、REPLACE INTO 與 RPAD 補位實戰

MySQL 中 REPLACE 關鍵字有三種截然不同的用法：字符串替換函數 REPLACE(str, from, to)、REPLACE INTO 覆蓋插入、RPAD 右側填充。本文逐一詳解語法、場景、性能與避坑指南。

一、字符串替換函數 REPLACE(str, from_str, to_str)

基本語法

REPLACE(str, from_str, to_str)
-- 返回將 str 中所有 from_str 替換為 to_str 後的新字符串
-- 區分大小寫、不支持正則

常見場景

• 數據清洗：UPDATE user SET phone = REPLACE(phone, ' ', '') 去除空格
• 脫敏/掩碼：SELECT REPLACE(id_card, SUBSTR(id_card, 7, 8), '********')
• 格式統一：UPDATE url SET path = REPLACE(path, '\\', '/')
• 批量修正：UPDATE config SET value = REPLACE(value, 'old_domain', 'new_domain')

多字段同時替換

UPDATE userinfo
SET remark = REPLACE(remark, '1', '9'),
    reserved1 = REPLACE(reserved1, '4', '9');

性能注意

• 全表掃描 + 全列重寫，大表需分批、加 WHERE 過濾
• REPLACE 不使用索引，WHERE col LIKE '%old%' 同樣全掃
• 極大文本列（TEXT/BLOB）替換會產生大量 Undo/Redo，建議應用層處理

二、REPLACE INTO：主鍵/唯一鍵衝突時覆蓋插入

語法

REPLACE INTO tbl (col1, col2) VALUES (val1, val2), (val3, val4);
-- 等效於：
-- INSERT ... ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2);
-- 但實際是 DELETE + INSERT，觸發器行為不同

關鍵差異對比

建議：優先用 INSERT ... ON DUPLICATE KEY UPDATE，除非業務明確要求「完全替換行」（如重新生成自增 ID）。

三、RPAD(str, len, padstr) 右側填充補位

語法

RPAD(str, len, padstr)
-- 將 str 右側用 padstr 重複填充至長度 len
-- 若 str 長度 ≥ len，直接截斷

實戰示例

-- 手機號掩碼顯示：前 3 後 4，中間 *
SELECT CONCAT(LEFT(phone,3), RPAD('', 4, '*'), RIGHT(phone,4)) FROM users;

-- 固定長度報文組裝（如銀行 8583 報文）
SELECT RPAD(merchant_id, 15, ' '),
       RPAD(terminal_id, 8, '0'),
       RPAD(amount, 12, '0');

-- 補齊位數排序
SELECT RPAD(CAST(score AS CHAR), 5, '0') FROM exam ORDER BY 1 DESC;

四、LPAD 左側填充（姊妹函數）

LPAD(str, len, padstr)
-- 常用：數字補零對齊
SELECT LPAD(id, 8, '0') FROM users;  -- 00000123

五、正則替換替代方案（MySQL 8.0+）

-- REGEXP_REPLACE(str, pattern, repl)
SELECT REGEXP_REPLACE('abc123def456', '[0-9]+', '#');  -- 'abc#def#'

-- 8.0 之前：應用層處理或 UDF

總結

• REPLACE(str, a, b)：字符串全量替換，清洗/脫敏首選
• REPLACE INTO：DELETE+INSERT 覆蓋插入，慎用，優先 ON DUPLICATE KEY UPDATE
• RPAD/LPAD：固定長度補位，報文、掩碼、對齊常用
• 大表操作必分批、加 WHERE、監控主從延遲