在MySQL中将datetime列数据迁移到date列的方法及兼容性问题

兼容性说明 #

MySQL原生支持datetime类型到date类型的合法转换，绝大多数场景不会出现兼容性问题，仅需注意几个特殊情况：

• 转换逻辑符合预期：datetime赋值给date列时，会自动丢弃时分秒部分，仅保留YYYY-MM-DD格式的日期，默认不会触发截断告警或报错，属于MySQL内置的合法精度收缩逻辑
• 时区风险：如果你的数据库开启了时区自动转换，或使用了带时区属性的datetime字段，转换前需确认会话时区和字段存储时区一致，避免因时区偏移导致日期差一天的问题。比如东八区的2024-06-01 00:30:00在UTC时区下对应是2024-05-31 16:30:00，时区不一致时转换出的date会出现偏差
• 零值兼容：如果datetime列存在0000-00-00 00:00:00这类非法零值，若sql_mode开启了NO_ZERO_DATE参数，转换会报错，建议迁移前先清理这类非法值，或临时调整sql_mode参数避免报错

高效迁移方案 #

根据表的数据量选择对应方案即可：

小表（100万行以下） #

直接全量更新即可，速度快操作简单：

-- 替换为实际的表名、新date列名、旧datetime列名
UPDATE 表名 SET 新date列 = DATE(旧datetime列);

显式调用DATE()函数比依赖隐式转换更稳妥，可避免不同sql_mode配置下的异常行为。

大表（100万行以上） #

直接全量更新会锁表过久影响线上业务，建议分批次更新，每次更新1000~5000行：
如果表有自增主键id，可按id范围分批：

SET @last_id = 0;
SELECT MAX(id) INTO @max_id FROM 表名;
WHILE @last_id < @max_id DO
  UPDATE 表名
  SET 新date列 = DATE(旧datetime列)
  WHERE id > @last_id AND id <= @last_id + 2000;
  SET @last_id = @last_id + 2000;
  -- 业务低峰期可省略，高并发场景下可加SLEEP(0.1)降低IO压力
END WHILE;

如果没有自增主键，可按旧datetime列的时间范围分批，每次更新1~7天的数据即可。

迁移校验 #

迁移完成后可执行以下语句校验数据一致性，返回0说明所有数据迁移正确：

SELECT COUNT(*) FROM 表名 WHERE 新date列 != DATE(旧datetime列);

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