MySQL中varchar日期字符串转合法格式及字段类型修改求助

你的问题出在两个关键点：一是日期里的月份是荷兰语（juni/juli），MySQL默认语言环境无法识别；二是格式符使用错误——你用了%h %m，但%h是12小时制格式，而你的时间是24小时制，%m代表月份而非分钟，正确格式应该用%H:%i。

第一步：验证日期转换正确性 #

先设置MySQL的时间语言为荷兰语，确保月份名称能被识别，再测试转换效果：

SET lc_time_names = 'nl_NL';

-- 查看原字符串与转换后日期的对应关系
SELECT strdate, STR_TO_DATE(strdate, '%e %M %Y, %H:%i') AS converted_datetime
FROM data 
WHERE topic = 26801;

格式符说明：

• %e：1位或2位数字的日期（如7、19）
• %M：完整的月份名称（匹配荷兰语的juni、juli）
• %Y：4位数字的年份
• %H：24小时制的小时（00-23）
• %i：分钟（00-59）

第二步：将原列修改为正规日期类型 #

为避免数据丢失，建议按以下安全步骤操作：

1. 添加临时列存储转换后的日期

SET lc_time_names = 'nl_NL';

ALTER TABLE data ADD COLUMN temp_datetime DATETIME;

-- 先针对指定topic的数据测试转换，确认无误后可去掉WHERE执行全量更新
UPDATE data 
SET temp_datetime = STR_TO_DATE(strdate, '%e %M %Y, %H:%i')
WHERE topic = 26801;

2. 检查转换结果
   确认是否存在转换失败的记录（表现为temp_datetime为NULL），如果有，说明对应的原字符串格式不符合要求，需要先手动修复：

SELECT strdate 
FROM data 
WHERE temp_datetime IS NULL;

3. 替换原列并清理临时列
   所有数据转换正确后，替换原列：

-- 删除原有的varchar类型列
ALTER TABLE data DROP COLUMN strdate;

-- 将临时列重命名为原列名
ALTER TABLE data CHANGE COLUMN temp_datetime strdate DATETIME;

注意事项 #

• 每次MySQL会话都需要执行SET lc_time_names = 'nl_NL';，或者在MySQL配置文件中设置默认语言，避免重启后失效。
• 转换操作前务必备份全表数据，防止意外导致数据丢失。
• 若存在格式不统一的日期字符串，需先修正这些数据再执行转换。

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