MySQL 部分替換 SQL 實用大全：REPLACE、TRIM、日期格式化一網打盡

字符串替換核心函數

• REPLACE(str, from_str, to_str)：將所有 from_str 替換為 to_str
• TRIM(str)：去除首尾空格
• INSTR(str, substr)：檢查子串存在（>0 表示存在），配合 WHERE 減少無效更新
• CONCAT(str1, str2, ...)：拼接字符串
• SUBSTRING_INDEX(str, delim, count)：按分隔符分割取段
• DATE_FORMAT(date, format)：日期格式化輸出

實戰案例集

1. 簡單字段替換

UPDATE `cdb_pms`
SET `subject` = REPLACE(`subject`, 'Welcome to', '歡迎光臨')
WHERE INSTR(`subject`, 'Welcome to') > 0;

2. 含特殊字符的 URL 替換

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

3. 去除所有空格

UPDATE `es_product` SET `pro_pub_time` = TRIM(`pro_pub_time`);

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;

5. 全角/半角統一：中文破折號 → 英文連字符

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '－', '-') WHERE INSTR(`pro_pub_time`, '－') > 0;

6. 中文年月日 → 統一連字符

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;

7. 補全日期：'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`) > 0 AND LENGTH(`pro_pub_time`) > 5;

8. '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;

9. 含連字符但長度<8 → 補 '-01'

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

10. 純年份 '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;

11. 統一格式化輸出：'2005-01-01' → '2005年01月'

UPDATE `es_product`
SET `pro_pub_time` = DATE_FORMAT(`pro_pub_time`, '%Y年%m月')
WHERE INSTR(`pro_pub_time`, '-') > 0;

避坑指南

1. **永遠加 WHERE 過濾**（INSTR > 0 或 LIKE），避免全表無意義更新、觸發大量 Binlog、鎖表
2. 大表分批更新（LIMIT 1000 循環），控制事務大小
3. 先 SELECT 驗證影響行數，再執行 UPDATE
4. 備份原表或開啟 Binlog 確保可回滾