萬盛學電腦網

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

MySQL REPLACE() 字符串批量替換實戰:從基礎到複雜清洗

REPLACE() 基礎語法

REPLACE(str, from_str, to_str)
-- 返回將 str 中所有 from_str 替換為 to_str 後的字串

UPDATE 批量替換模板

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

WHERE 子句可利用索引快速定位需更新行,避免全表掃描。

實戰案例集

1. 簡單字串替換

UPDATE `web_cn0797`
SET `subject` = REPLACE(`subject`, 'xxxxccc', '賴雪平')
WHERE INSTR(`subject`, 'xxxxccc') > 0;

2. URL 參數批量修正

UPDATE `web_cn0797`
SET `message` = REPLACE(`message`, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546')
WHERE INSTR(`message`, 'viewthread.php?tid=3989') > 0;

3. 日期格式標準化(多步驟)

-- 步驟 1: 移除中文年月日
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '年', '-') WHERE INSTR(`pro_pub_time`, '年') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '月', '-') WHERE INSTR(`pro_pub_time`, '月') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '日', '') WHERE INSTR(`pro_pub_time`, '日') > 0;

-- 步驟 2: 全角轉半角
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '-', '-') WHERE INSTR(`pro_pub_time`, '-') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '.', '.') WHERE INSTR(`pro_pub_time`, '.') > 0;

-- 步驟 3: 補全日期
-- 2005-04- → 2005-04-01
UPDATE `es_product` SET `pro_pub_time` = CONCAT(`pro_pub_time`, '01')
WHERE SUBSTRING_INDEX(`pro_pub_time`, '-', -1) = '' AND LENGTH(`pro_pub_time`) > 5;

-- 2005- → 2005-01-01
UPDATE `es_product` SET `pro_pub_time` = CONCAT(`pro_pub_time`, '01-01')
WHERE INSTR(`pro_pub_time`, '-') > 0 AND LENGTH(`pro_pub_time`) = 5;

-- 2005 → 2005-01-01
UPDATE `es_product` SET `pro_pub_time` = CONCAT(`pro_pub_time`, '-01-01')
WHERE INSTR(`pro_pub_time`, '-') = 0 AND LENGTH(`pro_pub_time`) = 4;

-- 步驟 4: 統一格式化顯示
UPDATE `es_product`
SET `pro_pub_time` = DATE_FORMAT(`pro_pub_time`, '%Y年%m月')
WHERE INSTR(`pro_pub_time`, '-') > 0;

4. 清理特殊字符

-- 移除方括號、點號
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '[', '') WHERE INSTR(`pro_pub_time`, '[') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, ']', '') WHERE INSTR(`pro_pub_time`, ']') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '.', '-') WHERE INSTR(`pro_pub_time`, '.') > 0;

-- 去除首尾空格
UPDATE `es_product` SET `pro_pub_time` = TRIM(`pro_pub_time`);

進階技巧

巢狀 REPLACE 一條 SQL 完成多重替換

UPDATE `table`
SET `col` = REPLACE(REPLACE(REPLACE(`col`, '舊1', '新1'), '舊2', '新2'), '舊3', '新3')
WHERE `col` LIKE '%舊1%' OR `col` LIKE '%舊2%' OR `col` LIKE '%舊3%';

REGEXP 配合複雜模式(MySQL 8.0+)

-- 僅替換符合正則的行
UPDATE `table`
SET `col` = REPLACE(`col`, '舊', '新')
WHERE `col` REGEXP '^pattern.*';

效能與安全建議

  • 先 SELECT 驗證:SELECT * FROM table WHERE col LIKE '%from%' 確認影響範圍
  • 小批次執行:大表分批 LIMIT 10000 迴圈更新,避免長事務鎖表
  • 備份先行:CREATE TABLE table_bak AS SELECT * FROM table;
  • 索引利用:WHERE col LIKE 'prefix%' 可用索引,%suffix 不能
  • 事務包裹:START TRANSACTION; ... COMMIT; 確保原子性
copyright © 萬盛學電腦網 all rights reserved