自增主键
自增值的存储
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)
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/228349.html原文链接：https://javaforall.net
