UPDATE table_name
SET column_name = REPLACE(column_name, 'old_str', 'new_str')
[WHERE condition];
REPLACE(str, from_str, to_str):將 str 中所有 from_str 替換為 to_str,區分大小寫。WHERE 則全表替換;有條件則精確控制範圍。
UPDATE articles
SET content = REPLACE(content, 'https://old-domain.com/', 'https://new-domain.com/')
WHERE content LIKE '%https://old-domain.com/%';
UPDATE mysql.user
SET Host = REPLACE(Host, 'main', 'slave')
WHERE User = 'testuser';
-- 結果:localhost.localdomain → localhost.localdoslave
UPDATE posts
SET body = REPLACE(REPLACE(body, '<', '<'), '>', '>')
WHERE body LIKE '%<%';
-- 移除所有 HTML 標籤
UPDATE posts SET body = REGEXP_REPLACE(body, '<[^>]+>', '');
-- 統一多種空白字元為單一空格
UPDATE posts SET body = REGEXP_REPLACE(body, '[[:space:]]+', ' ');
8.0 前可用使用者定義函數 (UDF) 或應用層處理。
WHERE 會鎖全表,大表必須加條件或分批(LIMIT + 循環)。START TRANSACTION; ... COMMIT; 確保原子性。
SET @batch = 5000;
SET @last_id = 0;
REPEAT
UPDATE t
SET col = REPLACE(col, 'old', 'new')
WHERE id > @last_id AND col LIKE '%old%'
ORDER BY id LIMIT @batch;
SET @last_id = @last_id + @batch;
SET @affected = ROW_COUNT();
UNTIL @affected = 0 END REPEAT;