MySQL中使用UPDATE语句转换日期格式报错求助

错误原因分析 #

你的UPDATE语句存在两个关键问题：

• SET子句格式符不匹配：原数据是February 06 2023 08:26:44（英文月份+日+年+时间）格式，但你用了MySQL标准日期的格式符%Y-%m-%d %H:%i:%s来解析，导致STR_TO_DATE无法正确转换，触发#1292截断错误。
• WHERE子句逻辑无效：STR_TO_DATE解析失败会返回NULL，无法和原字符串相等，这个条件不会匹配任何行，起不到过滤作用。

正确解决方案 #

方案1：直接转换现有字段（字段为字符串类型时用） #

如果time字段当前是VARCHAR类型（因为导入了字符串格式的日期），先执行转换：

UPDATE tblname SET `time` = STR_TO_DATE(`time`, '%M %d %Y %H:%i:%s');

转换完成后，可将字段类型改为DATETIME以适配MySQL标准：

ALTER TABLE tblname MODIFY COLUMN `time` DATETIME;

方案2：新增字段保留原数据（更安全） #

如果需要保留原始日期字符串，建议新增DATETIME字段存储转换后的值：

-- 新增datetime类型字段
ALTER TABLE tblname ADD COLUMN `log_time` DATETIME;
-- 转换并写入新字段
UPDATE tblname SET `log_time` = STR_TO_DATE(`time`, '%M %d %Y %H:%i:%s');

方案3：过滤无效数据（避免转换失败） #

若CSV中存在部分格式错误的日期，可添加WHERE条件只转换能正确解析的行：

UPDATE tblname 
SET `time` = STR_TO_DATE(`time`, '%M %d %Y %H:%i:%s')
WHERE STR_TO_DATE(`time`, '%M %d %Y %H:%i:%s') IS NOT NULL;

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