如何将MySQL中存储日期的varchar列转为date类型且不丢失数据

前置注意事项 #

date类型仅支持存储年月日信息，原有数据中的时分秒部分会被舍弃，这是date类型的固有特性，不属于数据丢失。如果需要保留完整时间信息，请改用DATETIME或TIMESTAMP类型。

操作步骤 #

1. 先备份全表数据 #

任何表结构修改操作前都要先备份，避免操作失误导致数据丢失：

CREATE TABLE 表名_bak AS SELECT * FROM 表名;

2. 排查非法日期数据 #

先查询所有无法正常转换为日期的异常数据，提前修正：

-- 替换语句中的「原日期列名」「表名」为你实际的列名和表名
SELECT 原日期列名 FROM 表名 
WHERE STR_TO_DATE(原日期列名, '%Y-%m-%d') IS NULL;

如果查询返回结果，说明这些行的日期格式不符合要求，手动修正后再进行后续操作。

3. 新增临时日期列过渡（推荐，避免直接修改原列出错） #

-- 新增date类型的临时列
ALTER TABLE 表名 ADD COLUMN 临时日期列 DATE;

-- 将原有varchar日期转换后写入临时列
UPDATE 表名 SET 临时日期列 = STR_TO_DATE(原日期列名, '%Y-%m-%d');

4. 校验转换结果 #

• 抽查带时分秒格式的转换结果：

SELECT 原日期列名, 临时日期列 FROM 表名 
WHERE 原日期列名 LIKE '____-__-__ __:__:__%' LIMIT 20;

• 抽查纯日期格式的转换结果：

SELECT 原日期列名, 临时日期列 FROM 表名 
WHERE 原日期列名 LIKE '____-__-__' LIMIT 20;

• 统计转换前后的非空值数量，确认无数据丢失：

SELECT 
  COUNT(原日期列名) AS 原有数据量,
  COUNT(临时日期列) AS 转换后数据量
FROM 表名;

5. 替换原列 #

确认转换完全正确后，执行替换操作：

-- 删除原有varchar类型的日期列
ALTER TABLE 表名 DROP COLUMN 原日期列名;

-- 将临时列重命名为原日期列名
ALTER TABLE 表名 CHANGE COLUMN 临时日期列 原日期列名 DATE;

其他说明 #

如果原有列上存在索引、约束，需要先删除索引/约束，修改完列类型后再重新创建。

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