ERROR 1292 (22007): Incorrect datetime value: ‘1970-00-00 08:00:00’ for column ‘GMT_CLEANUP’

ERROR 1292 (22007): Incorrect datetime value: ‘1970-00-00 08:00:00’ for column ‘GMT_CLEANUP’ at row 1;

查找error的信息:

$perror 1292

错误:1292 SQLSTATE: 22007 (ER_TRUNCATED_WRONG_VALUE)

接着想到mysql中alter table add column运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。那么报错的原因就是在ddl过程中copy原表,在copy表的过程中发现有表中GMT_CLEANUP的数据为’0000-00-00 00:00:00’,mysql认为该数据是不合法的数据:

第一个问题:那么为什么mysql认为0000-00-00 00:00:00是不正确的?

第二个问题:0000-00-00 00:00:00是怎么被插入到数据库中的,应用有这个需求吗?

对于第一个问题,还是需要回到mysql中对日期时间的定义上,在官方文档上说明MySQL允许将’0000-00-00’保存为“伪日期”(如果不使用NO_ZERO_DATE SQL模式)。这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小)。那么接下来,就是看看sql_mode中的参数了;

root@DB06:40:48>show variables like ‘sql_mode’;
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL |

1 row in set (0.00 sec)

参数中含有STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,

在严格模式,不要将 ‘0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告。

NO_ZERO_IN_DATE

在严格模式,不接受月或日部分为0的日期。如果使用IGNORE选项,我们日期插入’0000-00-00’。在非严格模式,可以接受该日期,但会生成警告。

问题可以解决了,改变sql_mode:将no_zero_date和no_zero_in_date去掉:
set global sql_mode
=’NO_AUTO_CREATE_USER’;

Query OK, 0 rows affected (0.00 sec)

root@DB 07:07:01>show variables like ‘%sql_mode%’;

| sql_mode | NO_AUTO_CREATE_USER |

1 row in set (0.00 sec)

即去掉sql_mode中的所有限制,就可以插入时间空值了,问题就解决了!!!

root@DB 07:07:10>exit

Bye

为什么会出现这种情况,是不是参数设置的不对,查看了其他库中sql_mode的参数,都没有设置,唯独这个库中sql_mode设置了,看来需要对sql_mode做详细的了解了:

简单说sql_mode是设置mysql应该支持哪些sql语法,以及哪种数据验证检查。这样可以更容易地在不同的环境中使用MySQL,并结合其它数据库服务器使用MySQL。可以通过用SET [SESSION|GLOBAL] sql_mode=’modes’语句设置sql_mode变量来更改SQL模式。设置 GLOBAL变量时需要拥有SUPER权限,并且会影响从那时起连接的所有客户端的操作。设置SESSION变量只影响当前的客户端。任何客户端可以随时更改自己的会话 sql_mode值。

当前数据库的sql_mode中有:

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

这些值,这是将sql_mode设为了:TRADITIONAL模式,所以只去除NO_ZERO_IN_DATE,NO_ZERO_DATE是不行的,还要去除TRADITIONAL;

一定要记得改完后从新进入数据库才生效!!!!

退出来后,重新登录:

show variables like ‘%sql_mode%’;

已经看到可以修改表的结构了,现在mysql允许ddl了。

对于第二个问题:为什么会插入0000-00-00 00:00:00?

。严格模式允许日期使用“零”部分,例如’2004-04-00’或“零”日期。要想禁止,应在严格模式基础上,启用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。

。每个时间类型有一个有效值范围和一个“零”值,当指定不合法的MySQL不能表示的值时使用“零”值。

。无效DATETIME、DATE或者TIMESTAMP值被转换为相应类型的“零”值(‘0000-00-00 00:00:00’、’0000-00-00’或者00000000000000)。

从上面的sql_mode中可以看到在严格模式(启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式)是可以插入:0000-00-00 00:00:00’,但是后面还启动了TRADITIONAL,TRADITIONAL中还有NO_ZERO_IN_DATE和NO_ZERO_DATE 模式,所以前期插入了0000-00-00 00:00:00数据,后面有改动了sql_mode,最后导致前面插入插入的数据变为了不合法,所以才会出现上面总总问题。

上一篇:错误 :Incorrect string value: '\xBE\xCD\xB2\xBB\xB8\xE6...' for column 'CONTENT


下一篇:MySql报错:1366-incorrect string value:'\xE8\x80\x8c\xE6\x88\x91' for column 'titl