MySQL REPLACE 函數進階用法：批量替換、REGEXP 配合、IF 分支、大表分批事務

基礎語法

REPLACE(str, from_str, to_str)
-- 返回替換後字符串，所有 from_str 被 to_str 替換

實戰範例庫

1. 簡單欄位替換

UPDATE dede_addonsoft SET dxylink = REPLACE(dxylink, '.zip', '.rar') WHERE aid > 45553;

2. 條件過濾（效能優化）

UPDATE `table_name` SET `field` = REPLACE(`field`, '.rar', '.7z')
WHERE `field` LIKE '%.rar%';

3. REGEXP 精確匹配替換

-- 僅替換包含特定域名的行
UPDATE comment SET url = REPLACE(url, 'www1.sohu.com', 'www.sina.com')
WHERE url REGEXP 'test\.yahoo\.com\.cn';

-- 多域名分支替換
UPDATE comment SET url = CASE
  WHEN url REGEXP 'test\.yahoo\.com\.cn' THEN REPLACE(url, 'www1.sohu.com', 'www.sina.com')
  WHEN url REGEXP 'www2\.yahoo\.com' THEN REPLACE(url, 'www2.yahoo.com', 'www.sina.com')
  ELSE url
END;

4. author_url 欄位替換

UPDATE comment SET author_url = REPLACE(author_url, 'sohu', 'sina')
WHERE author_url REGEXP 'www\.sohu\.com';

REPLACE vs REPLACE INTO

大表分批更新存儲過程（避免長鎖表、Replication 延遲）

DELIMITER //
CREATE PROCEDURE batch_replace_field(
  IN p_table VARCHAR(64),
  IN p_field VARCHAR(64),
  IN p_from VARCHAR(255),
  IN p_to VARCHAR(255),
  IN p_batch INT DEFAULT 1000
)
BEGIN
  DECLARE v_done INT DEFAULT 0;
  DECLARE v_id BIGINT;
  DECLARE v_sql TEXT;
  
  -- 動態游標需 PREPARE
  SET @cur_sql = CONCAT('SELECT id FROM `', p_table, '` WHERE `', p_field, '` LIKE ', QUOTE(CONCAT('%', p_from, '%')), ' LIMIT ', p_batch);
  PREPARE stmt FROM @cur_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  -- 簡化版：直接分批 UPDATE（需主鍵連續或有覆蓋索引）
  SET @upd_sql = CONCAT(
    'UPDATE `', p_table, '` SET `', p_field, '` = REPLACE(`', p_field, '`, ', QUOTE(p_from), ', ', QUOTE(p_to), ') ',
    'WHERE `', p_field, '` LIKE ', QUOTE(CONCAT('%', p_from, '%')), ' LIMIT ', p_batch
  );
  
  REPEAT
    PREPARE stmt FROM @upd_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT ROW_COUNT() INTO v_done;
    DO SLEEP(0.01);  -- 降低鎖競爭
  UNTIL v_done = 0 END REPEAT;
END//
DELIMITER ;

CALL batch_replace_field('large_table', 'content', 'old_str', 'new_str', 500);

避坑清單