MySQL 字符串替換 REPLACE 函數實戰：單表批量更新、條件過濾、正則配合

核心語法

UPDATE `table_name` 
SET `field_name` = REPLACE(`field_name`, 'from_str', 'to_str')
WHERE `field_name` LIKE '%from_str%';

實戰範例

1. 簡單替換：userid 中 10005 → 10010

UPDATE `ubk_vhost_list` 
SET `userid` = REPLACE(`userid`, '10005', '10010');

2. 條件過濾：僅替換包含舊值的行（效能更好）

UPDATE `ubk_vhost_list` 
SET `userid` = REPLACE(`userid`, '10005', '10010')
WHERE `userid` LIKE '%10005%';

3. 搭配 REGEXP 精確匹配：僅替換純數字欄位

UPDATE `comments` 
SET `url` = REPLACE(`url`, 'test.yahoo.com.cn', 'www.sina.com')
WHERE `url` REGEXP 'test\.yahoo\.com\.cn';

4. 多條件分支替換（IF/CASE 模擬）

UPDATE `comments` 
SET `author_url` = CASE
  WHEN `author_url` REGEXP 'www\.sohu\.com' THEN REPLACE(`author_url`, 'sohu', 'sina')
  WHEN `author_url` REGEXP 'www\.163\.com' THEN REPLACE(`author_url`, '163', 'sina')
  ELSE `author_url`
END;

REPLACE 函數特性

• 🔄 **全域替換**：欄位值中**所有**出現的 from_str 均被替換。
• 🔍 **大小寫敏感**：取決於欄位校對規則（_ci 不敏感，_bin 敏感）。
• 📏 **長度變化**：替換後長度可能超過欄位定義，會**截斷並產生 Warning**（嚴格模式報錯）。
• ⚡ **不使用索引**：WHERE col LIKE '%xxx%' 導致全表掃描，大表建議分批或加覆蓋索引。

大表分批更新模板

DELIMITER //
CREATE PROCEDURE batch_replace()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id BIGINT;
  DECLARE cur CURSOR FOR SELECT id FROM large_table WHERE col LIKE '%old%' LIMIT 1000;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  REPEAT
    FETCH cur INTO v_id;
    IF NOT done THEN
      UPDATE large_table SET col = REPLACE(col, 'old', 'new') WHERE id = v_id;
      COMMIT;
      DO SLEEP(0.01);  -- 降低鎖競爭
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur;
END//
DELIMITER ;

CALL batch_replace();

避坑提醒