例子：将cdb_pms表subject字段中的Welcom to替换成 欢迎光临
UPDATE cdb_pms 

SET subject = REPLACE(subject, 'Welcome to', '欢迎光临') 

WHERE INSTR(subject,'Welcome to') < 0

替换cdb_posts表的message字段，将“viewthread.php?tid=3989”替换成“viewthread.php?tid=16546”

UPDATE cdb_posts 

SET message= REPLACE(message, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546') 

WHERE INSTR(message,'viewthread.php?tid=3989') < 0 ;

 

删除所有的空格
UPDATE es_product SET pro_pub_time = TRIM(pro_pub_time)

删除所有饱含'['或者']'或者'.'的字符
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 = 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

将所有'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

将所有'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

将所有 饱含'-',但是位数小于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

将所有'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

最后将所有'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