MySQL中的自增主键怎么修改

这篇文章主要介绍“MySQL中的自增主键怎么修改”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL中的自增主键怎么修改”文章能帮助大家解决问题。不同的引擎对于自增值的保存策略不同1.MyISAM引擎的自增值保存在数据文件中2.InnoDB引擎的自增值，在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+步长作为这个表当前的自增值在MySQL8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：1.如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段2.如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值假设，某次要插入的值是X，当前的自增值是Y1.如果X

2.如果X>=Y，就需要把当前自增值修改为新的自增值

新的自增值生成算法是：从auto_increment_offset（初始值）开始，以auto_increment_increment（步长）为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值

三、自增值的修改时机

创建一个表t，其中id是自增主键字段、c是唯一索引，建表语句如下：

CREATETABLE`t`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`c`int(11)DEFAULTNULL,
`d`int(11)DEFAULTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`c`(`c`)
)ENGINE=InnoDB;

假设，表t里面已经有了(1,1,1)这条记录，这时再执行一条插入数据命令：

insertintotvalues(null,1,1);

执行流程如下：

1.执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1)

2.InnoDB发现用于没有指定自增id的值，获取表t当前的自增值2

3.将传入的行的值改成(2,1,1)

4.将表的自增值改成3

5.继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error（唯一键冲突），语句返回

对应的执行流程图如下：

在这之后，再插入新的数据行时，拿到的自增id就是3。出现了自增主键不连续的情况

唯一键冲突和事务回滚都会导致自增主键id不连续的情况

四、自增锁的优化

自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请

但在MySQL5.0版本的时候，自增锁的范围是语句级别。也就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放

MySQL5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是1

1.这个参数设置为0，表示采用之前MySQL5.0版本的策略，即语句执行结束后才释放锁

2.这个参数设置为1

• 普通insert语句，自增锁在申请之后就马上释放

• 类似insert … select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放

3.这个参数设置为2，所有的申请自增主键的动作都是申请后就释放锁

为了数据的一致性，默认设置为1

如果sessionB申请了自增值以后马上就释放自增锁，那么就可能出现这样的情况：

• sessionB先插入了两行数据(1,1,1)、(2,2,2)

• sessionA来申请自增id得到id=3，插入了(3,5,5)

• 之后，sessionB继续执行，插入两条记录(4,3,3)、(5,4,4)

当binlog_format=statement的时候，两个session是同时执行插入数据命令的，所以binlog里面对表t2的更新日志只有两种情况：要么先记sessionA的，要么先记录sessionB的。无论是哪一种，这个binlog拿到从库执行，或者用来恢复临时实例，备库和临时实例里面，sessionB这个语句执行出来，生成的结果里面，id都是连续的。这时，这个库就发生了数据不一致

解决这个问题的思路：

1）让原库的批量插入数据语句，固定生成连续的id值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的

2）在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。也就是把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row

如果有批量插入数据（insert … select、replace … select和load data）的场景时，从并发插入数据性能的角度考虑，建议把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row，这样做既能并发性，又不会出现数据一致性的问题

对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

1.语句执行过程中，第一次申请自增id，会分配1个

2.1个用完以后，这个语句第二次申请自增id，会分配2个

3.2个用完以后，还是这个语句，第三次申请自增id，会分配4个

4.依次类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍

insertintotvalues(null,1,1);
insertintotvalues(null,2,2);
insertintotvalues(null,3,3);
insertintotvalues(null,4,4);
createtablet2liket;
insertintot2(c,d)selectc,dfromt;
insertintot2values(null,5,5);

insert … select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请的自增id，第一次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7

由于这条语句实际上只用上了4个id，所以id=5到id=7就被浪费掉了。之后，再执行insert into t2 values(null, 5,5)，实际上插入了的数据就是(8,5,5)

这是主键id出现自增id不连续的第三种原因

五、自增主键用完了

自增主键字段在达到定义类型上限后，再插入一行记录，则会报主键冲突的错误

CREATETABLEt(idINTUNSIGNEDauto_incrementPRIMARYKEY)auto_increment=4294967295;
INSERTINTOtVALUES(NULL);
INSERTINTOtVALUES(NULL);

第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变（还是4294967295），就导致了云主机域名免费试用第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误

关于“MySQL中的自增主键怎么修改”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注开发云行业资讯频道，小编每天都会为大家更新不同的知识点。

2.如果X>=Y，就需要把当前自增值修改为新的自增值新的自增值生成算法是：从auto_increment_offset（初始值）开始，以auto_increment_increment（步长）为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值创建一个表t，其中id是自增主键字段、c是唯一索引，建表语句如下：假设，表t里面已经有了(1,1,1)这条记录，这时再执行一条插入数据命令：执行流程如下：1.执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1)2.InnoDB发现用于没有指定自增id的值，获取表t当前的自增值23.将传入的行的值改成(2,1,1)4.将表的自增值改成35.继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error（唯一键冲突），语句返回对应的执行流程图如下：

在这之后，再插入新的数据行时，拿到的自增id就是3。出现了自增主键不连续的情况唯一键冲突和事务回滚都会导致自增主键id不连续的情况自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请但在MySQL5.0版本的时候，自增锁的范围是语句级别。也就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放MySQL5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是11.这个参数设置为0，表示采用之前MySQL5.0版本的策略，即语句执行结束后才释放锁2.这个参数设置为1普通insert语句，自增锁在申请之后就马上释放类似insert … select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放3.这个参数设置为2，所有的申请自增主键的动作都是申请后就释放锁为了数据的一致性，默认设置为1

如果sessionB申请了自增值以后马上就释放自增锁，那么就可能出现这样的情况：sessionB先插入了两行数据(1,1,1)、(2,2,2)sessionA来申请自增id得到id=3，插入了(3,5,5)之后，sessionB继续执行，插入两条记录(4,3,3)、(5,4,4)当binlog_format=statement的时候，两个session是同时执行插入数据命令的，所以binlog里面对表t2的更新日志只有两种情况：要么先记sessionA的，要么先记录sessionB的。无论是哪一种，这个binlog拿到从库执行，或者用来恢复临时实例，备库和临时实例里面，sessionB这个语句执行出来，生成的结果里面，id都是连续的。这时，这个库就发生了数据不一致解决这个问题的思路：1）让原库的批量插入数据语句，固定生成连续的id值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的2）在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。也就是把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row如果有批量插入数据（insert … select、replace … select和load data）的场景时，从并发插入数据性能的角度考虑，建议把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row，这样做既能并发性，又不会出现数据一致性的问题对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：1.语句执行过程中，第一次申请自增id，会分配1个2.1个用完以后，这个语句第二次申请自增id，会分配2个3.2个用完以后，还是这个语句，第三次申请自增id，会分配4个4.依次类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍insert … select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请的自增id，第一次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7由于这条语句实际上只用上了4个id，所以id=5到id=7就被浪费掉了。之后，再执行insert into t2 values(null, 5,5)，实际上插入了的数据就是(8,5,5)这是主键id出现自增id不连续的第三种原因自增主键字段在达到定义类型上限后，再插入一行记录，则会报主键冲突的错误第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变（还是4294967295），就导致了第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误关于“MySQL中的自增主键怎么修改”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注开发云行业资讯频道，小编每天都会为大家更新不同的知识点。

相关推荐: mysql查询区分大小写吗

本篇内容主要讲解“mysql查询区分大小写吗”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql查询区分大小写吗”吧! mysql的默认查询是不区分大小写的；如果需要区分大小写，可以利用Binary关键字在查询…