MySQL自增主键知识点总结

  • 自增主键
  • 自增值的存储
  • 自增值的变化
  • 自增列不连续的情况
  • 自增列的锁
  • 自增列导致的主从数据不一致问题
  • 自增列值用完了
  • last_insert_id()问题


自增主键

自增值的存储

MyISAM引擎将当前自增值存储在表数据文件中。
InnoDB引擎在5.7及之前将当前自增值存储在内存中，MySQL重启时从表中查询自增列最大值+步长作为当前自增值。
InnoDB引擎在8.0及之后版本中将自增值变动记录存储在redo log中，重启MySQL后根据redo log恢复之前的自增值。

自增值的变化

执行插入语句时，如果自增列的值不指定，或指定为0或null，则插入时将表当前自增值填入该列，并增长自增值(当前自增值+步长)。
执行插入语句时，如果自增列指定了某个值，则使用指定的值。然后从当前自增值开始循环+步长，选择第一个大于指定值的数值作为新的当前自增值。
即：如果设置自增从1开始，步长10，则自增值依次是1、11、21。如果插入了一条自增列为37的记录，则下次获取到的自增值是41，因为41是21之后第一个大于37的自增值。

-- 查看自增值与自增步长
SHOW VARIABLES LIKE 'auto_inc%';  
-- 设置自增步长，重启MySQL后失效
SET @@auto_increment_increment = 10;
-- 查询上次获取到的自增ID
SELECT last_insert_id();

自增列不连续的情况

  1. 发生事务回滚时。因为每次取自增值都会变化，事务回滚时并不会撤回已经被取走的自增值，因此当发生事务回滚时会发生自增列不连续的情况。
  2. 发生唯一键冲突时。发生唯一键冲突之前，首先会获取当前自增值填入自增列，然后由于发生唯一键冲突，导致插入失败，但此时自增值已经发生了变化，会导致不连续。
    是所有插入失败都会导致不连续吗？不是如果是列的值超出范围，这个是在插入之前检查的，此时还没有申请自增值，所以不会改变当前自增值，所以不会造成不连续
  3. insert…select语句执行时。
    insert…select语句执行时如果插入多行，会批量申请自增值，第一次申请1个，第二次申请2个，第三次申请4个，第N次申请2^(N-1)个。所以如果第N次申请了多个值，但是没有用掉的话，也会造成自增列不连续。
    比如insert…select插入了4行，自增列自增值从1开始。首先申请1个，然后申请2个，再申请3个。此时共申请了7个自增值，但是只插入了5行数据，表中自增列最大值为5，但当前自增值是8，下次插入的行自增列会是8。

自增列的锁

自增列的锁并非事务锁，申请完拿到自增值后立即释放，而不是等到事务提交。

MySQL5.0之前，自增锁是与语句相关的，如果在插入语句中用到自增锁，会等到语句执行完成后才释放。

MySQL5.1及之后，添加了一个参数innodb_autoinc_lock_mode配置，用于控制自增锁行为：

  • 值为0，表示采用MySQL5.0之前的策略，语句执行完成后释放；
  • 值为1，普通insert语句在申请用完之后立即释放；批量插入语句如insert多个values或insert…select语句，还是要等到语句执行完成后释放；
  • 值为2，所有申请自增主键的动作都是申请用完后释放。

默认值是1。

自增列导致的主从数据不一致问题

当binlog_format设置为STATMENT时，binlog中记录的是每次执行的修改数据SQL。此时如果插入数据未指定自增列值而是使用自动获取的话，可能发生主从数据不一致问题。
如：自增值从1开始，步长1。

  1. 事务A插入两条数据，自增列值分别为1、2。
  2. 此时开启事务B，插入一条数据，然后回滚。
  3. 然后事务A再插入一条数据，自增列值为4。
  4. 提交事务A。
    此时主库中数据为1、2、4。而同步到从库后执行插入语句，插入的数据为1、2、3，主从数据不一致。

解决思路：避免从库自动得到自增列ID。

解决办法：

  • 插入时指定ID。
  • 将binlog_fomat设置为row。

自增列值用完了

会继续使用上一次生成的自增值。也就是自增值不再增长。

last_insert_id()问题

查询上一次递增ID的值：

select last_insert_id();
  1. 当在不同事务中各执行一条插入语句时，返回的是各自事务中生成的ID；
  2. 当在同一个事务中多次执行插入语句时，会返回最后一次插入语句的ID；
  3. 当在一个SQL语句中批量插入多条数据时，返回的是批量插入的第一条数据的ID；

当在不同事务中各执行一条插入语句时，返回的是各自事务中生成的ID。

mysql> begin;
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t(c, d) values(5, 6);
Query OK, 1 row affected (0.11 sec)
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                8 |
+------------------+
1 row in set (0.06 sec)
mysql> begin;
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t(c, d) values(6, 4);
Query OK, 1 row affected (0.05 sec)
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                9 |
+------------------+
1 row in set (0.06 sec)

当在同一个事务中多次执行插入语句时，会返回最后一次插入语句的ID。

mysql> begin;
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t(c, d) values(6, 4);
Query OK, 1 row affected (0.05 sec)
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                9 |
+------------------+
1 row in set (0.06 sec)
 
mysql> insert into t(c, d) values(76, 1);
Query OK, 1 row affected (0.06 sec)
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               14 |
+------------------+
1 row in set (0.05 sec)

当在一个SQL语句中批量插入多条数据时，返回的是批量插入的第一条数据的ID。

mysql> insert into t(c, d) values(76, 1);
Query OK, 1 row affected (0.06 sec)
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               14 |
+------------------+
1 row in set (0.05 sec)
 
mysql> insert into t(c, d) values(22, 1), (23, 1), (24, 1);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               15 |
+------------------+
1 row in set (0.05 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.06 sec)