MySQL的sql_mode参数之NO_AUTO_VALUE_ON_ZERO对主键ID为0的记录影响

　　最近遇到一个不合理使用数据库进行项目开发最终导致项目进度受阻的一个问题，某天几位开发人员找到我并告知数据库中某张表数据无法写入，又告知某行记录被删除了，因为被删除的记录对开发框架影响很大，他们已尝试重新写入但无法生效并以为是表坏了（有时候你以为的就真的只是你以为）。

　　遇到这种紧急需求肯定是要先明确需求和问题，需要清楚开发需要DB支持什么。最终才明白某几张表中的起始数据被插入ID为0的记录，这个与我们经常说的自增ID起始为1不符合，明显是不符合数据库开发规范的。数据删除容易，恢复起来真的不容易，还是恢复一条不规范的数据库需求，由于现象比较特殊且是历史问题，优化不易暂且先恢复（这里强烈不建议插入ID为0的数据记录）。

#表结构如下
CREATE TABLE `xx_xxxx_xxxx` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `deposit_name` varchar(100) NOT NULL COMMENT '存款类型名称',
    ->   `deposit_threshold` decimal(11,2) NOT NULL COMMENT '预存门槛金',
    ->   `deposit_interest` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '预计赠送总额',
    ->   `is_interest` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否赠送（1赠送 0不赠送）',
    ->   `rate_type` tinyint(4) NOT NULL DEFAULT '2' COMMENT '利率类型（1月利率，2年利率）',
    ->   `rate` int(3) NOT NULL COMMENT '利率（%）',
    ->   `interest_id` int(11) DEFAULT NULL COMMENT '赠送id',
    ->   `shop_id` int(11) NOT NULL DEFAULT '0' COMMENT '商铺id',
    ->   `merchant_id` int(11) NOT NULL DEFAULT '0' COMMENT '商户id',
    ->   `account_type` tinyint(4) DEFAULT '0' COMMENT '赠送类型（0-卡项金，1-本金 , 2-商品）',
    ->   `pay_support_type` tinyint(4) DEFAULT '0' COMMENT '0 支持线下支付 1支持线下支付和线上支付',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;

#查询数据（排查表损坏的可能）
　　select max（id） from xx_xxxx_xxxx;

#插入删除的记录

mysql> INSERT INTO `xx_xxxx_xxxx` (`id`, `deposit_name`, `deposit_threshold`, `deposit_interest`, `is_interest`, `rate_type`, `rate`, `interest_id`, `shop_id`, `merchant_id`, `account_type`, `pay_support_type`) VALUES ('0', '普通会员（无权益）', '0.00', '0.00', '1', '2', '0', '1', '0', '0', '0', '0');
Query OK, 1 row affected (0.14 sec)

#查询刚才的记录(没有结果集返回)
select id,deposit_name from xx_xxxx_xxxx where id = 0;

 　　查询和写入操作都进行了，并且都返回了正常的结果集，排查了开发以为的表损坏无法写入的问题（如果表有损坏一般业务群或者开发群早炸锅了）。但是有一个奇怪的现象，为什么数据正常写入了但就是查不到ID为0的记录？既然ID查找不到，对于已经写入的数据也可以用其他字段值查找，最终找到了这条记录，但它的ID不是0而是当前表中的max(id)。为什么会出现这种情况呢？查看官方文档才发现这个与sql_mode有关，MySQL对于插入ID为NULL或者0的记录会使用自增的策略分配ID。

　　但是对于ID为0的记录不能直接写入，但是我们可以updateID的值，保证这行记录能顺利存在。

mysql> INSERT INTO `xx_xxxx_xxxx` (`id`, `deposit_name`, `deposit_threshold`, `deposit_interest`, `is_interest`, `rate_type`, `rate`, `interest_id`, `shop_id`, `merchant_id`, `account_type`, `pay_support_type`) VALUES ('0', '普通会员（无权益）', '0.00', '0.00', '1', '2', '0', '1', '0', '0', '0', '0');
Query OK, 1 row affected (0.14 sec)

mysql> select * from xx_xxxx_xxxx;
+----+-----------------------------+-------------------+------------------+-------------+-----------+------+-------------+---------+-------------+--------------+------------------+
| id | deposit_name                | deposit_threshold | deposit_interest | is_interest | rate_type | rate | interest_id | shop_id | merchant_id | account_type | pay_support_type |
+----+-----------------------------+-------------------+------------------+-------------+-----------+------+-------------+---------+-------------+--------------+------------------+
|  1 | 普通会员（无权益）          |              0.00 |             0.00 |           1 |         2 |    0 |           1 |       0 |           0 |            0 |                0 |
+----+-----------------------------+-------------------+------------------+-------------+-----------+------+-------------+---------+-------------+--------------+------------------+
1 row in set (0.00 sec)

mysql> update xx_xxxx_xxxx set id = 0 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from xx_xxxx_xxxx;                
+----+-----------------------------+-------------------+------------------+-------------+-----------+------+-------------+---------+-------------+--------------+------------------+
| id | deposit_name                | deposit_threshold | deposit_interest | is_interest | rate_type | rate | interest_id | shop_id | merchant_id | account_type | pay_support_type |
+----+-----------------------------+-------------------+------------------+-------------+-----------+------+-------------+---------+-------------+--------------+------------------+
|  0 | 普通会员（无权益）          |              0.00 |             0.00 |           1 |         2 |    0 |           1 |       0 |           0 |            0 |                0 |
+----+-----------------------------+-------------------+------------------+-------------+-----------+------+-------------+---------+-------------+--------------+------------------+
1 row in set (0.00 sec)

总结：

#MySQL 5.7的sql_mode值
sql_mode=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION

　　因为在数据库表中ID采用了自增ID策略。默认情况下当ID是0或者null的时候，数据库会自动产生一个新的自增序列作为这条记录的ID。这就是我们插入0值ID记录时与期望不符原因。

　　如果想让自增列在只插入null的时候产生自增序列，就要提前设置mysql的sql_mode包括NO_AUTO_VALUE_ON_ZERO。

　　如果您使用mysqldump转储表然后重新加载它，MySQL通常会在遇到0值时生成新的序列号，从而生成一个内容与表中的内容不同的表那被倾倒了。

转载于:https://www.cnblogs.com/liyingxiao/p/11143663.html