mysql 批量修改表主键（不连续变连续）

Heaven helps those who help themselves
资深码农+深耕理财=财富自由
欢迎关注

mysql 批量修改表主键（不连续变连续）

Created by Marydon on 2022-05-24 11:10

1.情景展示

如上图所示，主键值在12194之后，变成了1519240185321893907，如何把后面这些数据的主键值，统一修改成按照12194之后递增呢？

如何将不连续的主键，变成连续的呢？

2.具体分析

可通过局部变量来实现。

3.解决方案

语法：

SET @rownum = 当前主键最大值;
-- 先+1，再执行set
UPDATE TABLE_NAME SET id = @rownum := @rownum + 1 [WHERE 限制条件];

这里的@rownum就是局部变量。

我们可以看到：后面的数据主键已经接上了。

说明：

如果将表所有数据主键都变成不间断（连续）的话，我们可以这样

方式一：

SET @rownum = 0;
-- 先+1，再执行set
-- 表主键的起始值为1
UPDATE TABLE_NAME SET id = @rownum := @rownum + 1;

另外，如果需要后续新增数据的主键，和上面的主键接上的话，我们还需要修改AUTO_INCREMENT的值。

方式二：未测试

-- 1.删除主键字段
ALTER TABLE TABLE_NAME DROP CLOUMN_ID;
-- 2.重新添加主键列
ALTER TABLE TABLE_NAME ADD COLUMN_ID BIGINT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT;

注意：

如果要该表的主键，需要确保：别的表没有与该表的主键有关联关系，否则改完之后，关联关系就芭比Q啦。

4.mysql主键

当我们在插入数据时，指定表主键字段值为0或null时，id会自增，不受影响；

主键起始值为1；

每次增加的值为1；

在取自增值时，会先去查当前主键的最大值，然后和当前自增值进行比对：

如果，自增值>现有数据主键的最大值，则取自增值，作为新数据的主键；

反之，自增值<=现有数据主键的最大值，则取现有数据主键的最大值+1，作为新数据的主键。

mysql的自增列的值，只会越来越大，一般情况下，是无法人为手动变小的。

id在mysql中是怎么存储？

mysql有2中主流存储引擎，MyISAM和InnoDB，MyISAM自增id存储在数据文件上，而InnoDB在mysql8.0之前存储在内存中，8.0之后存储在redolog里。

存储在内存中，那mysql 服务重启了怎么记录自增id呢？

每次mysql重启都都会查找当前表的最大id值，然后加1存储到内存中作为当前id值。

造成ID不连续的情况：

情形一：尽管数据插入失败，主键值依然+1；

主键为什么不回滚？

原因：mysql在获取id时为了保证一致性，是加锁的，比如2个并发事务申请自增id，上面例子的情况，假如一个申请了4，一个申请了5，加入申请4的事务成功了，申请到5的事务唯一键冲突，这时候如果id回退到4，下一次插入必定是主键冲突。

情形二：insert A ... select ... from B

说明：

只有，在往A表中插入数据时，调用A表主键自增，且插入数据总条数>1，才会造成A表主键值不连续的情况发生。

做下测试：

第一步：复制表结构；

第二步：查询当前自增值；

show create table meta_theme_copy2

 

第三步：插入前4条数据（不带主键）；

 

第四步：查看当前自增值

我们可以看到，此时此刻，自增值，已经变成了8，本来该是5的。

第五步：插入第五条数据。

我们可以看到：新增数据的主键值为8。

原理：

出现自增id不连续的情况，因为mysql申请批量id的策略是对于同一条sql中的申请id，第一次分配一个，如果第一次分配后这个sql还会来申请，就会给2个，以次类推，下一次总是上一次的2倍。

上面的insert语句有4条记录，第一次申请id时分配了1个（id=1），不够用，第二次分配了1*2=2个（id=2,3），还是不够用，第三次申请2*2=4个（id=4,5,6,7），够用了，这时,B表的自增id已经变成8了，所以我们再次插入数据时，主键id就变成8了。

注意：批量插入的数据，主键值是连续的。

如何修改主键值，让其连续起来？

第一步：删除刚才新增的第五条记录（如果没有产生不连续的数据，请忽略此步骤）;

第二步：将主键自增值改成：当前主键最大值+1；

测试：

这种情况下，insert ... select ....，对于后续数据的插入，主键ID依然是连续的。

在往A表中插入数据时，B表自带主键值，即：不使用A表自增主键。

证实如下：

第一步：建表；

第二步：插入数据时，自带主键值；

第三步：查看建表语句，当前自增值为5；

第四步：插入数据。

我们可以看到：此时，新增数据，主键是连续的。

对这种自增id不连续的情况，对生产有什么影响吗？

  大家都知道，mysql的主备同步是通过binlog来进行的，binlog的格式有3中，statement格式及记录sql，row格式即记录数据，还有一种是上面2个混合使用。如果使用statement格式来记录binlog，那在备库那儿执行的只是成功的sql，备库的表自增id值会跟主库不一致，这种情况还是非常危险的，如果我们用id来做一些业务上的查询，会查到不一样的结果。

  为了应对这种情况，我建议把binlog记录为row格式，同时把系统参数innodb_autoinc_lock_mode设置为2，这个参数默认是1。

  这个参数是记录申请id获取锁后释放锁的策略，如果设置为0，则申请id的语句结束后才释放锁；

  如果是1，则对单条insert语句，申请到id后马上释放，对批量插入语句像上面的例子，则是语句执行结束后释放；

  如果设置成2，则所有语句都是申请到id后马上释放，效率最高。

 

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

• 个人主页
• mysql 如何修改主键起始值？
• sql insert select(使用select结果进行insert)

与君共勉：最实用的自律是攒钱，最养眼的自律是健身，最健康的自律是早睡，最改变气质的自律是看书，最好的自律是经济独立 。

您的一个点赞，一句留言，一次打赏，就是博主创作的动力源泉！

↓↓↓↓↓↓写的不错，对你有帮助？赏博主一口饭吧↓↓↓↓↓↓