MySQL: update datetime值格式错误

Question

我试图用不正确的日期时间格式修复值。问题是下面的SELECT查询正在工作，但是UPDATE查询将失败。

选择工作

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

更新失败

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

显示以下错误

ERROR 1292 (22007): Incorrect datetime value: '12.05.2013 4:10'

表描述

+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| username   | varchar(30)     | NO   |     | NULL    |                |
| created_at | varchar(20)     | YES  |     | NULL    |                |
| updated_at | varchar(20)     | YES  |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

Mysql version 8.0.26

公立小学我可以在where子句中使用regex来修复它，但是它看起来不太好

感谢

Answer 1

不能将datetime保存为每种格式。你应该保持支持的格式，在选择中，你可以根据需要改变格式。

DATETIME类型用于同时包含日期和时间的值部分。MySQL检索并显示YYYY-MM-DD格式的DATETIME值Hh:mm:ss格式。取值范围为:10001-01 00:00:00 ~99999-12-31 23:59:59TIMESTAMP数据类型用于值包含日期和时间部分。

尝试此更新:

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

Answer 2

如果created_at和updated_at的数据类型为varchar(如问题所述)，则查询不应给出12.05.2013 4:10的任何错误。请检查下面的示例:

Schema和insert语句:

create table users ( id bigint unsigned , username   varchar(30), created_at  varchar(20) , updated_at  varchar(20));

insert into users values(1,'A',STR_TO_DATE('12.05.2013 4:10', '%d.%m.%Y %H:%i'),STR_TO_DATE('12.05.2013 4:10', '%d.%m.%Y %H:%i'))

Select查询:

select * from users

输出:

updated_at2013-05-12 04:10:002013-05-12 04:10:00