MySQL将存储年份的int列修改为DATETIME类型且不丢失数据的方法

完全可以在不丢失原有数据的前提下完成类型变更，绝对不要直接执行MODIFY COLUMN修改原列类型：数据库会把int类型的年份值当做Unix时间戳解析，得到完全错误的时间值，比如int值2022直接转DATETIME会得到1970年附近的错误时间，和预期的2022-01-01完全不符。

具体操作步骤 #

按以下流程操作可以100%保留原有数据，转换结果完全符合要求：

• 新增临时DATETIME列
  先在原表新增一个DATETIME类型的临时列，用来存储转换后的正确时间值，避免直接修改原列导致数据损坏：

  -- 把your_table替换成你的实际表名，根据业务需求决定是否允许NULL值
  ALTER TABLE your_table ADD COLUMN year_col_temp DATETIME NULL;
  

• 批量转换数据到临时列
  用内置函数把原int列的年份值转为对应年份的1月1日，写入临时列，推荐用MAKEDATE函数，逻辑最严谨，不会出现格式拼接错误：

  -- MAKEDATE(年份, 当年第N天)，传1就直接返回对应年份的1月1日
  UPDATE your_table SET year_col_temp = MAKEDATE(原int年份列名, 1);
  
  -- 如果你的数据库版本不支持MAKEDATE，可以用字符串拼接方案替代
  -- UPDATE your_table SET year_col_temp = CONCAT(原int年份列名, '-01-01 00:00:00');
  

  注意：更新完成后一定要抽查10-20行数据，确认原int值和临时列的时间一一对应，比如原列值为2022的行，临时列值必须是2022-01-01 00:00:00；如果原列存在0、小于1000的非法年份值，提前清理修正后再往下走。

• 替换原列
  确认临时列所有数据无误后，删除原int列，再把临时列重命名为原列的名字即可：

  -- 删除原int类型的年份列
  ALTER TABLE your_table DROP COLUMN 原int年份列名;
  -- 把临时列改回原列名，非空、默认值等属性和原列保持一致即可
  ALTER TABLE your_table CHANGE COLUMN year_col_temp 原int年份列名 DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00';
  

大表优化提示 #

如果你的表数据量超过百万级，不要一次性执行全表UPDATE语句，会造成长时间锁表影响业务，可以按主键ID分批更新，每次更新1000-5000行，更新完等待1-2秒再执行下一批，把业务影响降到最低。

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