04-四、MySQL 面试题 InnoDB AUTO_INCREMENT ( 下 )

前面两章节，我们介绍了 MySQL Innodb AUTO_INCREMENT 锁的三种模式，分别为 「 传统模式 」 、 「 连续模式 」 、「 交错模式 」 ，这三种模式我们可以用同学聚会定餐馆来形象的描述下

1、 「 传统模式 」 – 老板，你家的店我包了，等到我同学聚会完了你再招待其它客人啊….其它客人：坑啊！ 你们快点吃啊，慢吞吞的…
2、 「 连续模式 」 – 老爸，我同学总共 50 个人，先给我来 50 个位置，有些同学可能没来，那就空着。剩下的位置，你可以招待其它的同学。有时候，如果不知道有多少同学 ( 健忘症犯了… )，就只能再回到传统模式了。
3、 「 交错模式 」 – 老板，我也不知道会来几个同学，反正，来了一个就坐一个位置，其它客人来了也可以坐 ( 假设老板家座位足够 )

这三种锁模式的优缺点是啥 ？

1、 「 传统模式 」 能保证所有的自增值是连续的，且不会浪费 ( 也就是会一直 123456789…自增下去，不会断掉 )，但其它的事务或语句要等到当前语句执行完才会继续执行
2、 「 交错模式 」解决了要 「 传统模式 」中要等待的问题，但也会引入新问题，就是可能造成自增值出现断层，比如 12345 缺了 4 这样。同时，在指定插入数据条数不确定的情况下，会回到 「 传统模式 」
3、 「 交错模式 」 解决了自增值断层的问题，但引入了自增值顺序混乱的问题，可能会导致自增值如下 13245687

交错模式在日常的 SELECT 语句中是不会出啥问题的，因为会按照自增值排序，出问题就处恢复数据或主从过程中的二进制日志回放，可能导致从库或者恢复的数据的自增值和源数据不一致。

好了，MySQL Innodb AUTO_INCREMENT 锁的三种模式我们就介绍到此了，接下来的部分我们讨论讨论下这三种模式在 MySQL Innodb 中的使用说明

MySQL Innodb AUTO_INCREMENT 锁的使用说明

主从复制中的 AUTO_INCREMENT

主从复制时，如果你使用的是基于 SQL 语句的复制，需要将 innodb_autoinc_lock_mode 配置项的值设置为 0 或 1 ，而且主服务器和从服务器上的值必须相同。

如果设置 innodb_autoinc_lock_mode = 2，也就是使用 「 交错锁 」 模式，或者主从服务器上的值不相同，则不能确保从服务器上相同行的自增值和主服务器相同

如果你使用的是基于 行 ( row ) 或混合模式的主从复制，那么使用任何一个 AUTO_INCREMENT 锁模式都是安全的，因为基于行的复制对 SQL 语句的执行顺序不敏感。

在 交错锁 模式下，主从复制来说，任何基于语句的复制都是不安全的

注意： 混合模式使用的是基于行的复制方式。

「 丢失 」 ( lost ) 自增值和序列间隙

无论你使用的是哪种锁模式 ( 0 , 1 或 2 )，如果生成自增值的事务回滚，则这些自增量值将 「 丢失 」。

一旦为 AUTO_INCREMENT 列生成一个自增值后，无论 「 INSERT-like 」 语句是否完成，以及包含事务是否回滚，都无法回滚该自增值。

这些丢失的自增值不会被重复使用。因此，表的 AUTO_INCREMENT 列中的值可能存在间隙

为 AUTO_INCREMENT 列指定 NULL 或 0 时候

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 )，如果用户在INSERT 语句中为 AUTO_INCREMENT 列指定的值为 NULL 或 0，那么 Innodb 将会忽略这些值并为该列生成一个新的自增值。

也就是说，不管是否为 AUTO_INCREMENT 列指定了值为 NULL ，还是指定了值为 0 ，或是未指定，Innodb 都会自动生成一个自增值作为该列的值。

为 AUTO_INCREMENT 列指定一个负值 ( < 0 )

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 )，如果用户在INSERT 语句中为 AUTO_INCREMENT 列指定的值为负数，那么 Innodb 为 AUTO_INCREMENT 生成的值就是不确定的 ( is not defined )

也就是说，如果指定了一个负数，那么当前插入行的 AUTO_INCREMENT 列的值就是不确定的 ( is not defined )

如果 AUTO_INCREMENT 列的值大于指定列数据类型 ( 一般是整数类型 ) 的最大整数

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 )，如果 AUTO_INCREMENT 列的值变得大于可以存储在指定整数类型中的最大整数，则最终的结果也是不确定的 ( is not defined )

「 批量插入 」 中的自增值的间隙

在 innodb_autoinc_lock_mode 配置项的值设置为 0 ( 传统锁模式 ) 或 1 ( 连续锁模式 ) 时，任何给定语句生成的自增值都是连续的，没有间隙。

因为在这两个模式下，「 批量插入 」 会使用表级别的 AUTO-INC 锁，且会一直持有直到所有语句运行结束。在表级别的 AUTO-INC 锁控制下，一次只能执行一个这样的语句

在 innodb_autoinc_lock_mode 配置项的值设置为 2 ( 交错锁模式 ） 时，「 批量插入 」 生成的自增值可能存在间隙，仅有的出现前提是同时执行 「 INSERT-like 」 语句

对于锁模式 1 和 2 ，连续的语句间也可能出现间隙，因为批量插入，可能并不知道每个语句所需的确切数量的自增值，可能会存在高估，如果一旦高估了，那么高估的自增值将会被抛弃且永远也不会使用到。

「 混合模式插入 」 中的自增值

「 混合模式插入 」 中，那些 「 简单插入 」中的某些语句 ( 但不是全部 ) 可能显式的为 AUTO_INCREMENT 列指定了值。

如果真的发生了这种情况，三种锁模式下的结果是各不相同的。

例如，假设 c1 列是表 t1 的 AUTO_INCREMENT 列，且假设最新自动生成的序列号为 100

CREATE TABLE t1 (
    c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 CHAR(1)
) ENGINE = INNODB;

现在，我们来看看下面这条混合模式插入语句

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

在 innodb_autoinc_lock_mode 设置为 0 ( 传统锁模式下 )，刚刚插入的 4 条数据如下

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

下一个可用的自增值为 103，因为自增值一次分配一个，而不是在语句执行开始时一次性分配。这时候无论是否同时并发的执行其它 「 INSERT-like 」 语句 ( 任何类型 )，此结果都是正确的

但如果设置 innodb_autoinc_lock_mode 的值为 1 ，也就是 「 连续锁模式 」，那么刚刚插入的 4 条数据如下

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

虽然最终数据看起来一样。但是，行为结果还是不同的。

在这种情况下，下一个可用的自增值是 105，而不是 103，因为在处理语句时分配了四个自增值，但只使用了两个，也就是 103 和 104 永远的被丢失了。这时候无论是否同时并发的执行其它 「 INSERT-like 」 语句 ( 任何类型 )，此结果都是正确的

但如果设置 innodb_autoinc_lock_mode 的值为 2 ，也就是 「 交错锁模式 」，那么刚刚插入的 4 条数据如下

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

x 和 y 的值是唯一的，并且比任何先前生成的行的都大。但是，x 和 y 的具体值取决于同时执行语句生成的自增值的数量。如果没有其它 「 INSERT-like 」语句并发执行，结果和 「 传统锁 」 模式是一样的。

最后，我们来看看下面这个 SQL 语句，假设最新自动生成的序列号为 100

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');

无论配置项 innodb_autoinc_lock_mode 的值设置为何种锁模式，该语句会抛出生成重复键错误 23000 ( Can’t write; duplicate key in table ) 。因为自增值 101 已经分配给了 (NULL,'b') ，这时候 (101,'c') 再使用 101 就会失败，当然了，前提是 AUTO_INCREMENT 列设置为主键或唯一索引

在一序列 INSERT 语句的中间修改 AUTO_INCREMENT 列值

在 MySQL 5.7 及更早版本中，在一序列 INSERT 语句的中间修改 AUTO_INCREMENT 列值可能会导致 「 重复条目 」 ( Duplicate entry ) 错误。例如，如果执行了将 AUTO_INCREMENT 列值更改为大于当前最大自增值的 UPDATE 操作，那些后续的未指定也未使用自增值的 INSERT 操作可能会遇到 「 重复条目 」 错误

在MySQL 8.0 及更高版本中，如果将 AUTO_INCREMENT 列值修改为大于当前最大自增值的值，那么新值将保持不变，后续 INSERT 操作将从新的较大值开始分配自增值

下面的示例演示了这种行为

mysql> CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (c1)
    ->  ) ENGINE = InnoDB;
mysql> INSERT INTO t1 VALUES(0), (0), (3);
mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;
mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+
mysql> INSERT INTO t1 VALUES(0);
mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
+----+

结束语

知识量好多有没有 ？ 这次的文字简单明了，我就不做过多解释了…看完了你有啥感觉？

希望读者能够给小编留言，也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")