`ir_date_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
Error Code: 1067. Invalid default value for 'ir_date_time'
设置了SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 正确
NO_AUTO_VALUE_ON_ZERO影响not null default。一般情况,你可以向该列插入NULL或0生成下一个序列号。NO_AUTO_VALUE_ON_ZERO禁用0,因此只有NULL可以生成下一个序列号。
将时间设成 '2012-00-00 00:00:00' 后
0 row(s) affected, 1 warning(s): 1264 Out of range value for column 'ir_date_time' at row 1
改为2014-11-24 21:09:53 无报错 00 00作为月日显然是错误的
NO_AUTO_VALUE_ON_ZERO的文档
NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you
generate the next sequence number for the column by inserting either NULL or 0 into it.
NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next
sequence number.
Server SQL Modes
674
This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0
is not a recommended practice, by the way.) For example, if you dump the table with mysqldump
and then reload it, MySQL normally generates new sequence numbers when it encounters the
0 values, resulting in a table with contents different from the one that was dumped. Enabling
NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now
automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid
this problem.
主要是后面
For example, if you dump the table with mysqldump
and then reload it, MySQL normally generates new sequence numbers when it encounters the
0 values, resulting in a table with contents different from the one that was dumped. Enabling
NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now
automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid
this problem.
当你的语句是用mysqlump dump下来的
如果原语句的编码为0 mysql可能会生成一个新的编号给句子
导致句子前后不同 所以 xxx就是可以避免这个问题
我这里是因为 0000....其实也是0,然后把它设置成了default,可是原本的model就是0与空相同情况
所以会出现问题