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

本文详细介绍了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与空相同情况 

所以会出现问题

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值