REPLACE(str, from_str, to_str)
-- 返回將 str 中所有 from_str 替換為 to_str 後的字串
UPDATE `table_name`
SET `field_name` = REPLACE(`field_name`, 'from_str', 'to_str')
WHERE `field_name` LIKE '%from_str%';
WHERE 子句可利用索引快速定位需更新行,避免全表掃描。
UPDATE `web_cn0797`
SET `subject` = REPLACE(`subject`, 'xxxxccc', '賴雪平')
WHERE INSTR(`subject`, 'xxxxccc') > 0;
UPDATE `web_cn0797`
SET `message` = REPLACE(`message`, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546')
WHERE INSTR(`message`, 'viewthread.php?tid=3989') > 0;
-- 步驟 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;
-- 移除方括號、點號
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`);
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%';
-- 僅替換符合正則的行
UPDATE `table`
SET `col` = REPLACE(`col`, '舊', '新')
WHERE `col` REGEXP '^pattern.*';
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; 確保原子性