MySQL-使用自增ID无法插入ID为0值的记录问题分析

落日映苍穹つ 2022-02-16 00:25 1338阅读 0赞

# MySQL-使用自增ID无法插入ID为0值的记录的问题分析 #

> 记录一次数据库备份还原过程中的问题，以便今后遇到该问题能迅速定位。

> 问题产生的背景  
> 1、数据库中表使用的是自增ID  
> 2、在项目开发过程中数据库分为开发库和生产库，由于业务逻辑的需要，在生产库中把其中一个表的一条记录的ID手动改成了0。  
> 3、备份生产库导入开发库过程中

> 问题  
> 1、项目运行后，发现数据出现了严重的偏差，一些关联查询的数据不对，一开始以为是备份数据库或者导入数据库过程中出现了问题，来来回回折腾了几次，问题依旧。  
> 2、追寻出问题的数据，查询数据库，发现ID手动改为0的那条记录被插入到最后一条记录，当然ID也不是0了。查看备份的源文件，生成的insert语句中这条记录的ID确实是0。难道产生了幻觉。

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

> 看一下官方解释以及解决方案  
> 1、最权威的还是官网解释  
> [https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html\#sqlmode\_no\_auto\_value\_on\_zero][https_dev.mysql.com_doc_refman_8.0_en_sql-mode.html_sqlmode_no_auto_value_on_zero]  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMTQ3Nw_size_16_color_FFFFFF_t_70]2、大体意思就是说默认情况下，自增列插入null或者0的时候数据库会产生一个新的自增数值。如果想让自增列在只插入null的时候产生自增序列。就要提前设置mysql的sql\_mode包括NO\_AUTO\_VALUE\_ON\_ZERO。  
> 3、如果要还原的数据库中有很多自增列为0值的数据，那么就设置一下sql\_mode把，在my.ini或者my.cnf中设置sql\_mode  
> 4、在设置sql\_mode之前需要查询数据库原来拥有那些设置了的mode  
> SELECT @@sql\_mode  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMTQ3Nw_size_16_color_FFFFFF_t_70 1]5、在配置文件中加入  
> sql\_mode = “STRICT\_TRANS\_TABLES,NO\_ENGINE\_SUBSTITUTION,NO\_AUTO\_VALUE\_ON\_ZERO”  
> ![在这里插入图片描述][20190421100303536.png]  
> 6、重启数据库，这时候插入0值自增列数据就不会有问题了。

> 如果导入的数据中只有一条或或者几条数据，其实不必大费周章，导入后手动改一改也是可以的。  
> 但是不管怎么样，我们要知其然，更要知其所以然。

> 小尾巴~~  
> 只要有积累，就会有进步

[https_dev.mysql.com_doc_refman_8.0_en_sql-mode.html_sqlmode_no_auto_value_on_zero]: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_auto_value_on_zero
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMTQ3Nw_size_16_color_FFFFFF_t_70]: /images/20220216/c934bf78bbfe4a128cb14fc57984c573.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMTQ3Nw_size_16_color_FFFFFF_t_70 1]: /images/20220216/209fb64f477e456bbfb9c0e4ddddc436.png
[20190421100303536.png]: /images/20220216/e5ddb45f551d45198c4e654dbb7beb76.png