Error Code: 1067. Invalid default value for 'ir_date_time'

最新推荐文章于 2024-10-04 10:00:00 发布

原创最新推荐文章于 2024-10-04 10:00:00 发布 · 1.9k 阅读

CC 4.0 BY-SA版权

本文详细介绍了MySQL中NO_AUTO_VALUE_ON_ZERO模式的使用及其对自动增量列的影响。通过实例展示了如何设置SQL模式、处理无效默认值错误、以及避免在自动增量列中存储0值的问题。同时提供了mysqldump导出文件重新加载时的解决方案，以保持数据一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

`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与空相同情况

所以会出现问题