Alter Table失败,因为行的数据不正确 - datetime值不正确:'0000-00-00 00:00:00' [英] Alter Table fails because a row has incorrect data - Incorrect datetime value: '0000-00-00 00:00:00'
问题描述
我正在使用以下语句更新当前用户表:
I am updating the current user table with the following statement:
`ALTER TABLE `users` ADD `title` VARCHAR( 5 ) NULL DEFAULT NULL AFTER `surname` ;`
其中返回以下错误:
#1292 - 日期时间值不正确:第1行列'created_at'的'0000-00-00 00:00:00'
Created_at列结构是:
Created_at column structure is:
created_at datetime NOT NULL
不知道为什么数据无效 - 它可能与MySQL版本相关(从5.5.43到5.1.73)?
Not sure why the data is invalid - could it be related to MySQL versions (from 5.5.43 into 5.1.73)?
目前我所修复的是在ALTER TABLE语句之前更新datetime值:
The fix I have at the moment is to update the datetime value before the ALTER TABLE statement:
UPDATE `users` SET `created_at`='2014-01-01 00:00:00' WHERE `created_at`='0000-00-00 00:00:00';
这是否合理解决?
推荐答案
Created_at列结构是:
created_at datetime NOT NULL
在新的MySQL版本中看起来0000-00-00 00:00:00被视为NULL,而不是您的旧版本, ve指定DB不应该接受NULL值(NOT NULL)。
It would seem that '0000-00-00 00:00:00' is treated as NULL in your new MySQL version but not your old one, and you've specified that the DB shouldn't accept NULL values ("NOT NULL").
这篇关于Alter Table失败,因为行的数据不正确 - datetime值不正确:'0000-00-00 00:00:00'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!