MySQL数据库设置主键自增、自增主键为什么不能保证连续递增

以你之姓@ 2024-04-06 15:01 80阅读 0赞

#### 文章目录 ####

*  一、设置主键自增
 *   *  1.1、建表时设置主键自增
     *  1.2、建表后设置主键自增
     *  1.3、删除自增约束
 *  二、自增列：AUTO\_INCREMENT
 *   *  2.1、自增起始值和自增步长
     *  2.2、自增主键存储策略
     *  2.3、自增值修改机制
     *  2.3、特点和要求
 *  三、自增字段值不连续
 *   *  3.1、自增不连续的示例
     *   *  3.1.1、示例一：唯一索引冲突导致自增不连续
         *  3.1.2、示例二：批量插入会造成主键不连续
         *  3.1.3、示例三：ON DUPLICATE KEY UPDATE每次更新时id自增1
     *  3.2、mysql自增主键不连续场景
     *  3.3、解决方法
     *  3.4、自增id为什么不回退复用
     *  3.5、总结

以下内容基于MySql8.0进行讲解。

## 一、设置主键自增 ##

### 1.1、建表时设置主键自增 ###

建表时设置主键自增并指定自增起始值

CREATE TABLE `user` (
      `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `name` varchar(30) DEFAULT NULL comment '姓名',
      `age` int DEFAULT NULL comment '年龄',
      UNIQUE KEY `uniq_name` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT = 100;

说明：

*  `AUTO_INCREMENT`：指定该列自增
 *  `AUTO_INCREMENT = 100`：指定自增起始值为100，默认起始值为1，每次自增1

![在这里插入图片描述][34ef6b9340054f588c98f0d77e48a337.png]

### 1.2、建表后设置主键自增 ###

如果数据表已经存在，可以使用 ALTER TABLE 语句修改表结构并设置自增主键。例如，将 user表中的 id 字段设置为自增主键，可以使用如下 SQL 语句：

ALTER TABLE user MODIFY id INT AUTO_INCREMENT PRIMARY KEY;

### 1.3、删除自增约束 ###

将AUTO\_INCREMENT 从字段类型中删除即可删除掉自增约束

alter table user MODIFY id INT PRIMARY KEY;

## 二、自增列：AUTO\_INCREMENT ##

### 2.1、自增起始值和自增步长 ###

MySQL提供了主键自增机制AUTO\_INCREMENT，对主键使用，保证主键的唯一性，默认值起始值为1，每次增长量为1，也就是自增起始值auto\_increment\_offset 和自增步长 auto\_increment\_increment。

# 查询mysql是否支持自增长，查询自增长步长，自增长起始值
    mysql> show variables like '%auto_increment%';
    +-------------------------|-------+
    |       Variable_name     | Value |
    +-------------------------|-------+
    |auto_increment_increment |   1   |
    +-------------------------|-------+
    |auto_increment_offset    |   1   |
    +-------------------------|-------+

说明：

*  auto\_increment\_increment：自增步长，默认每次自增1。
 *  auto\_increment\_offset：自增起始值，默认从1开始。

![在这里插入图片描述][5227347a8b594c52aaef67868058b036.png]

### 2.2、自增主键存储策略 ###

表的结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值。不同的引擎对于自增值的存储策略不同。

*  MyISAM引擎的自增值保存在数据文件中。
 *  InnoDB引擎的自增值是保存在了内存里，到了MySQL 8.0版本后，才有了“自增值持久化”的能力，即才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：
    
     *  在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。 比如一个表当前数据行里最大的id是10，AUTO\_INCREMENT=11。这时删除id=10的行，AUTO\_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO\_INCREMENT就会变成10。 也就是说，MySQL重启可能会修改一个表的AUTO\_INCREMENT的值。
     *  在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

### 2.3、自增值修改机制 ###

*  如果自增id插入值为0、null或未指定值，那么就把当前表的自增值AUTO\_INCREMENT填到自增字段。
 *  如果插入数据时，id指定了具体的值，如果这个具体值不小于自增值AUTO\_INCREMENT，则主键值不变，自增值AUTO\_INCREMENT=主键值+步长值；否则，自增值AUTO\_INCREMENT不变，不存在主键冲突则插入成功。

### 2.3、特点和要求 ###

*  默认情况下，AUTO\_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
 *  一个表中只能有一个字段使用 AUTO\_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
 *  AUTO\_INCREMENT 约束的字段必须具备 NOT NULL 属性。
 *  `AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。`
 *  AUTO\_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO\_INCREMENT 就会失效。例如 INT 类型的最大值为 2147483647。

错误演示

create table employee(
    	eid int auto_increment,
        ename varchar(20)
    );
    # ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

create table employee(
    	eid int primary key,
        ename varchar(20) unique key auto_increment
    );
    # ERROR 1063 (42000): Incorrect column specifier for column 'ename'  因为ename不是整数类型

## 三、自增字段值不连续 ##

在某些情况下自增字段的值的不连续的，如果你不知道有这个问题可能会踩坑。

下面我们通过一个实例分析自增字段的值为什么不连续。

### 3.1、自增不连续的示例 ###

以下列举几个自增不连续的示例。

#### 3.1.1、示例一：唯一索引冲突导致自增不连续 ####

向user表中插入一条数据，sql如下：

insert into user values(1,'张三',1);

此时，表 user 中已经有了（1,‘张三’,1）这条记录，这时再执行一条插入数据命令：

mysql> INSERT INTO user VALUES(null,'张三',1);
    ERROR 1062 (23000): Duplicate entry '张三' for key 'user.uniq_name'

由于表中已经存在 name=1 的记录，所以报 Duplicate key error（唯一键冲突）。在这之后，再插入新的数据时，自增 id 就是 3，这样就出现了自增字段值不连续的情况。  
![在这里插入图片描述][dd9b700cae954303955133b5cbf4d715.png]  
解释：`MySQL对于递增值的使用是一次性的，那么第二次执行插入时，不管语句成功还是失败，那么这个递增值就会浪费掉。`在执行insert into user values(null,‘张三’,1);时虽然唯一索引冲突了，但是自增id已经自增了一个，内存中自增id已经用到了2，所以下一次插入时id会再次自增变成了3。

#### 3.1.2、示例二：批量插入会造成主键不连续 ####

为了保证主键id的唯一性，在申请自增id时，MySQL会对申请操作加锁。一般情况下，这个申请动作会很快。

对于一般的批量插入，比如insert into … values(xxx)，由于插入的Value个数可以提前计算得出，MySQL会一次性的申请足够数量的id，以保证性能。

但是对于insert into … select 这种语句就有点麻烦了，由于无法确定到底需要申请多个主键id，如果插入一条申请一个的话，假设要插入100万条记录，那就得申请100万次，可想而知性能会有多么差劲。

批量插入数据，包含的语句类型是 insert…select、replace…select 和 load data 语句。

所以对于这种批量插入的语句，MySQL采用了一种翻倍申请的优化策略：

1.  语句执行过程中，第一次申请自增id，会分配1个；
2.  1个用完以后，这个语句第二次申请自增id，会分配2个；
3.  2个用完以后，还是这个语句，第三次申请自增id，会分配4个；
4.  依次类推，同一个语句去申请自增id，每次会申请到的自增id个数都是上一次的两倍。

CREATE TABLE `t` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `c` (`c`)
    ) ENGINE=InnoDB;
    insert into t values(null, 1,1);
    insert into t values(null, 2,2);
    insert into t values(null, 3,3);
    insert into t values(null, 4,4);
    create table t2 like t;
    insert into t2(c,d) select c,d from t;
    insert into t2 values(null, 5,5);

![在这里插入图片描述][1d9a0b6e66c04054bf2dde53697e47d9.png]  
由于这种规则，t2表的批量插入就只用到了 id=1 到 id=4，id=8，而id=5 到id=7就被浪费掉了，因此自增主键的批量申请也会导致自增主键出现不连续的情况。

#### 3.1.3、示例三：ON DUPLICATE KEY UPDATE每次更新时id自增1 ####

为了效率用到了on duplicate key update进行自动判断是更新还是新增,一段时间后发现该表的主键id(已设置为连续自增),不是连续的自增,总是跳跃的增加,这样就造成id自增过快,已经快超过最大值了,通过查找资料发现,on duplicate key update有一个特性就是,每次是更新的情况下id也是会自增加1的,比如说现在id最大值的5,然后进行了一次更新操作,再进行一次插入操作时,id的值就变成了7而不是6

drop table if exists tbl_test;
    create table tbl_test(
    	id int primary key auto_increment,
    	name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci unique not null,
    	age int comment '年龄',
    	address varchar(50) comment '住址',
    	update_time datetime default null
    ) comment '测试表';
    
    insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now());

![在这里插入图片描述][32adf828ad994b29a50ee604cf10395b.png]  
然后执行ON DUPLICATE KEY UPDATE进行更新，并且再插入一条数据

insert into tbl_test(name,age,address,update_time) values('zhangsan',22,'杭州2','2024-03-05 00:00:00')
    on duplicate key update
    age = values(age),
    address = values(address),
    update_time=values(update_time);
    
    insert into tbl_test(name,age,address,update_time) values('李四',21,'武汉',now());

![在这里插入图片描述][529aaf02555b445a87385f8e5a91f070.png]  
从以上结果可以看出，当on duplicate key update每次更新后也会占用一次id自增值。

### 3.2、mysql自增主键不连续场景 ###

**1. 删除记录导致的不连续**  
当我们删除表中的记录时，MySQL的自增主键并不会重置。下次插入记录时，自增主键会从上一次插入的最大主键值加1开始递增。如果删除了表中的某些记录，那么主键的值就会跳跃，导致不连续。

**2. 回滚操作导致的不连续**  
MySQL的自增主键是基于事务的，如果在事务中插入了记录，但是事务回滚了，那么自增主键的值也会回滚，导致不连续。

**3. 唯一键冲突导致的不连续**  
若有唯一索引冲突，则会导致自增主键的不连续。MySQL对于递增值的使用是一次性的，那么第二次执行插入时，不管语句成功还是失败，那么这个递增值就会浪费掉。

**4.MySQL5.7及以前的版本重启导致不连续**  
MySQL5.7及以前的版本InnoDB存储引擎不支持自增值持久化，重启导致自增值丢失。

**5.批量插入会造成主键不连续**  
为了保证主键id的唯一性，在申请自增id时，MySQL会对申请操作加锁。一般情况下，这个申请动作会很快。

对于一般的批量插入，比如insert into … values(xxx)，由于插入的Value个数可以提前计算得出，MySQL会一次性的申请足够数量的id，以保证性能。

所以对于这种批量插入的语句，MySQL采用了一种翻倍申请的优化策略：

语句执行时，第一次申请一个自增id，第二次申请2个自增id，第三次申请4个自增id…

即每次申请的数量都比上次多一倍，这样虽然会浪费一些自增id，但是可以保证插入的效率，从性能角度来看，是可以接受的。

**6.ON DUPLICATE KEY UPDATE每次更新时id自增1，下次插入时id不连续**  
通过查找资料发现,on duplicate key update有一个特性就是,每次是更新的情况下id也是会自增加1的,比如说现在id最大值的5,然后进行了一次更新操作,再进行一次插入操作时,id的值就变成了7而不是6

### 3.3、解决方法 ###

**1. 使用TRUNCATE TABLE重置自增主键**  
如果希望将自增主键重置为连续的递增序列，可以使用TRUNCATE TABLE语句删除表中的所有记录，并重置自增主键的值。例如：

TRUNCATE TABLE table_name;

需要注意的是，TRUNCATE TABLE语句会删除表中的所有记录，并且无法回滚。

**2. 使用ALTER TABLE重置自增主键**  
ALTER TABLE语句可以用来修改表的结构，包括重置自增主键。可以使用以下语句将自增主键重置为指定的值：

ALTER TABLE table_name AUTO_INCREMENT = value;

其中，table\_name是需要修改的表名，value是希望设置的自增主键值。

**3. 避免手动指定主键值**  
为了避免因手动指定主键值而导致的主键冲突，可以遵循以下原则：

*  不要在插入记录时手动指定主键值，而是让MySQL自动生成；
 *  如果需要获取自动生成的主键值，可以使用LAST\_INSERT\_ID()函数。  
    例如，插入一条记录并获取自动生成的主键值的示例代码如下：

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
    SELECT LAST_INSERT_ID();

### 3.4、自增id为什么不回退复用 ###

大家可能会有点疑问，为什么自增id是一次性使用的？

其实原因也很简单，大家稍微一想就明白了。

假设有两个事务在同时执行，为了保证自增id的唯一性，MySQL会对申请动作加锁，然后两个事务各获得一个自增id。比如事务1申请到了自增id100，事务2申请到了自增id101。

当事务2成功提交，事务1因为某些原因回滚了。

如果我们要回退复用事务1的id，将AUTO\_INCREMENT又设置成了100+1，那么下一个事务来申请自增id时，就会拿到101，而这时101已经被事务2用掉了，就会造成主键冲突。

当然我们也可以每次都让MySQL检查一下主键是否冲突，如果冲突就跳过这个id，但是这样一来，本来申请自增id这个很轻的动作就会变得很重，对性能的影响就会很大。

**`所以，从性能角度考虑，InnoDB只保证了主键id是大致递增的，而不保证是顺序递增的。`**

### 3.5、总结 ###

MySQL的自增主键在特定情况下可能会出现不连续的情况，包括删除记录、回滚操作和主键冲突。为了解决这个问题，我们可以使用TRUNCATE TABLE或ALTER TABLE语句来重置自增主键的值，也可以避免手动指定主键值来避免主键冲突。在实际应用中，根据具体需求选择合适的解决办法。

[34ef6b9340054f588c98f0d77e48a337.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9c53de94baec492687bad52e52a3df6f.png
[5227347a8b594c52aaef67868058b036.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8c32795aad474db4a9d5e3505ea9e0dd.png
[dd9b700cae954303955133b5cbf4d715.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e3004bb3178e4ed4bd3bb7df1da38a21.png
[1d9a0b6e66c04054bf2dde53697e47d9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b3771021f19046f79784a613de4e8f99.png
[32adf828ad994b29a50ee604cf10395b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4373bd88574e483baa145c7914efb306.png
[529aaf02555b445a87385f8e5a91f070.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f833d6248e604af1abfef63ba1a46fde.png