萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL REPLACE() 函數實戰:欄位內容批次替換指定/全部字串

基本語法


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 則全表替換;有條件則精確控制範圍。

實戰範例

1. 更換網域名稱


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

2. 修正用戶表主機欄位


UPDATE mysql.user
SET Host = REPLACE(Host, 'main', 'slave')
WHERE User = 'testuser';
-- 結果:localhost.localdomain → localhost.localdoslave

3. 清洗 HTML 標籤/特殊字元


UPDATE posts
SET body = REPLACE(REPLACE(body, '<', '<'), '>', '>')
WHERE body LIKE '%<%';

進階:正則替換(MySQL 8.0+)


-- 移除所有 HTML 標籤
UPDATE posts SET body = REGEXP_REPLACE(body, '<[^>]+>', '');

-- 統一多種空白字元為單一空格
UPDATE posts SET body = REGEXP_REPLACE(body, '[[:space:]]+', ' ');

8.0 前可用使用者定義函數 (UDF) 或應用層處理。

效能與安全注意事項

  1. 全表掃描:無 WHERE 會鎖全表,大表必須加條件或分批(LIMIT + 循環)。
  2. 索引失效:更新索引欄位會導致索引重建,評估影響範圍。
  3. 字元集一致:舊/新字串編碼需與欄位字元集一致,否則產生亂碼。
  4. 事務包裹:重要業務用 START TRANSACTION; ... COMMIT; 確保原子性。
  5. 備份先行:大範圍替換前務必備份或建立影子表驗證。

分批替換模板(大表安全)


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;
copyright © 萬盛學電腦網 all rights reserved