MySQL更新格式错误的datetime值时报错1292该如何解决

问题根因 #

你遇到的SELECT正常、UPDATE报错的核心原因有两个：

1. 目前created_at、updated_at是varchar类型，你在WHERE子句中调用DATE(created_at)时，MySQL会隐式尝试将字符串按YYYY-MM-DD HH:MM:SS的标准datetime格式转换，遇到12.05.2013 4:10这类点分隔的非标准格式会转换失败。在MySQL 8.0默认开启的严格SQL模式下，UPDATE操作会将隐式转换的警告升级为错误抛出，而SELECT操作仅返回NULL不会中断执行，因此出现两种语句执行结果不一致的情况。
2. 你的筛选逻辑不符合需求：你需要筛选的是可以通过%d.%m.%Y %H:%i格式解析为合法时间的字符串，但DATE(created_at) IS NULL会把所有不符合标准datetime格式的字符串全部筛选出来，其中可能包含完全无法用你指定格式解析的脏数据，也会触发转换报错。

最优解决方案 #

1. 先排查脏数据 #

执行以下查询找出所有无法按指定格式解析的脏数据，优先单独处理：

SELECT id, created_at, updated_at 
FROM users 
WHERE STR_TO_DATE(created_at, '%d.%m.%Y %H:%i') IS NULL 
   OR STR_TO_DATE(updated_at, '%d.%m.%Y %H:%i') IS NULL;

2. 执行安全的批量更新 #

使用UPDATE IGNORE跳过转换失败的脏数据，同时替换掉WHERE子句的隐式转换逻辑，避免触发严格模式报错：

UPDATE IGNORE users
   SET created_at = DATE_FORMAT(STR_TO_DATE(created_at, '%d.%m.%Y %H:%i'), '%Y-%m-%d %H:%i:%s'),
       updated_at = DATE_FORMAT(STR_TO_DATE(updated_at, '%d.%m.%Y %H:%i'), '%Y-%m-%d %H:%i:%s')
WHERE STR_TO_DATE(created_at, '%d.%m.%Y %H:%i') IS NOT NULL
  AND STR_TO_DATE(updated_at, '%d.%m.%Y %H:%i') IS NOT NULL;

如果仍有报错，可以会话级临时关闭严格模式，执行完更新后会自动恢复，不会影响全局配置：

SET SESSION sql_mode = REPLACE(REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''), 'NO_ZERO_DATE', '');
SET SESSION allow_invalid_dates = 1;

3. 彻底修正字段类型（强烈推荐） #

等所有可转换的字符串都转为标准datetime格式后，直接修改字段类型为DATETIME，从根源避免后续的格式兼容问题：

ALTER TABLE users 
MODIFY COLUMN created_at DATETIME NULL,
MODIFY COLUMN updated_at DATETIME NULL;

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