SQL中如何将字符串列的多种日期格式统一为指定格式并保留字符串类型

前置准备 #

• 不要直接修改原日期字段，先新增一个临时字段new_date_str存储转换后的结果，避免转换出错无法回滚
• 给表主键和原日期字段加索引，避免全表扫描，先统计两种格式的记录数：第一种是.分隔的DD.MM.YYYY HH24:MI:SS格式，第二种是已经符合要求的MM/DD/YYYY hh:mm:ss AM/PM格式，减少无效转换

核心转换逻辑 #

核心思路是先把异构日期字符串转成标准日期类型，再格式化为目标字符串格式，以下是主流MySQL环境的示例代码，其他数据库仅需调整对应日期函数即可：

-- 第一种带点格式的转换逻辑
DATE_FORMAT(STR_TO_DATE(old_date_str, '%d.%m.%Y %H:%i:%s'), '%m/%d/%Y %h:%i:%s %p')

已经符合目标格式的记录无需重复转换，仅做格式有效性校验后直接复用即可。

分批更新方案（核心优化，适配5000万条大表） #

千万级表禁止全表一次性更新，避免锁表、事务日志占满磁盘，按主键范围分批处理，每次处理1000-5000条，示例逻辑如下：

-- 假设表名为date_table，主键id为自增数值类型
SET @min_id = (SELECT MIN(id) FROM date_table), 
    @max_id = (SELECT MAX(id) FROM date_table), 
    @step = 2000;

WHILE @min_id <= @max_id DO
  UPDATE date_table 
  SET new_date_str = CASE 
    WHEN old_date_str LIKE '%.%' THEN DATE_FORMAT(STR_TO_DATE(old_date_str, '%d.%m.%Y %H:%i:%s'), '%m/%d/%Y %h:%i:%s %p')
    ELSE old_date_str 
  END
  WHERE id BETWEEN @min_id AND @min_id + @step - 1
  AND new_date_str IS NULL; -- 支持断点续跑，异常中断后无需从头开始
  SET @min_id = @min_id + @step;
  DO SLEEP(1); -- 每批暂停1秒，降低数据库IO压力，不影响正常业务
END WHILE;

收尾校验 #

• 转换完成后随机抽取1%的记录校验转换正确性，确认无转换异常后，可在业务低峰期将原字段与新字段交换，或直接用新字段替换业务逻辑中的原字段
• 确认业务无异常后，可按需删除原日期字段释放存储空间

额外优化建议 #

• 所有转换操作放在业务低峰期执行，进一步降低对线上业务的影响
• 支持并行计算的数据库可按主键范围拆分多任务并行执行，缩短整体处理时长
• 转换前临时关闭非必要的审计日志、慢查询日志，减少IO开销

内容的提问来源于stack exchange，提问作者anders1990