mysql 主键自增的范围_MySQL自增主键知识点总结

自增主键

自增值的存储

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();

自增列不连续的情况发生事务回滚时。因为每次取自增值都会变化，事务回滚时并不会撤回已经被取走的自增值，因此当发生事务回滚时会发生自增列不连续的情况。

发生唯一键冲突时。发生唯一键冲突之前，首先会获取当前自增值填入自增列，然后由于发生唯一键冲突，导致插入失败，但此时自增值已经发生了变化，会导致不连续。

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。事务A插入两条数据，自增列值分别为1、2。

此时开启事务B，插入一条数据，然后回滚。

然后事务A再插入一条数据，自增列值为4。

提交事务A。

此时主库中数据为1、2、4。而同步到从库后执行插入语句，插入的数据为1、2、3，主从数据不一致。

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

解决办法：插入时指定ID。

将binlog_fomat设置为row。

自增列值用完了

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

last_insert_id()问题

查询上一次递增ID的值：

select last_insert_id();当在不同事务中各执行一条插入语句时，返回的是各自事务中生成的ID；

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

当在一个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)