MySQL中如何将月/日/年格式的text类型列转换为datetime类型？

错误原因 #

你之前使用的STR_TO_DATE格式参数'%Y-%m-%d %T'对应「四位年-月-日 时:分:秒」的格式，和你实际存储的「月/日/两位年」文本格式完全不匹配，会返回NULL值，导致后续修改字段类型操作失败。

正确操作步骤 #

• 操作前建议先备份全表数据，避免误操作丢失数据：

  CREATE TABLE Packages_bak AS SELECT * FROM Packages;
  

• 先验证转换逻辑是否正确，确认转换结果符合预期再执行批量更新：

  SELECT `date` AS 原始文本日期, STR_TO_DATE(`date`, '%c/%e/%y') AS 转换后日期 FROM Packages LIMIT 20;
  

  用到的格式符说明：
  • %c：兼容带/不带前导零的月份（可识别2、02两种写法）
  • %e：兼容带/不带前导零的日期（可识别5、05两种写法）
  • %y：两位年份，默认补全为20xx年，如果你的数据包含19xx年份需要单独处理逻辑
• 确认转换结果无误后，执行批量更新：

  UPDATE Packages SET `date` = STR_TO_DATE(`date`, '%c/%e/%y');
  

  如果你的原始日期文本包含时间部分，自行在格式参数后补充对应的时间格式符即可，无时间部分转换后会默认补00:00:00。
• 最后执行字段类型修改操作：

  ALTER TABLE Packages MODIFY COLUMN `date` DATETIME;
  

如果执行更新时出现报错，优先检查表内是否存在不符合月/日/年格式的脏数据，清理脏数据后再重新执行上述操作即可。

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