当我向MySQL表中插入多行数据时，id是否会每次自动增加1？

Question

如果我有以下查询：

INSERT INTO table (col1,col2,col3) VALUES
('col1_value_1', 'col2_value_1', 'col3_value_1'),
('col1_value_2', 'col2_value_2', 'col3_value_2'),
('col1_value_3', 'col2_value_3', 'col3_value_3');

假设我有一张表，其中最后一个idPRIMARY_KEYAUTO_INCREMENT值为 56，那么这个插入查询是否总是会创建3条记录，它们的id为57, 58, 59？这个操作是原子的吗？

或者，如果另一个查询在同一张表上进行写入，ids是否总是增加1？

谢谢关注！

编辑：请看下面的说明，因为我可能没有表述清楚。

当然，AUTO_INCREMENT是安全递增1的，我知道。

重点是：

假设我有以下名为table的表：

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|____________________________________|

如果我知道运行查询：

INSERT INTO table (col1,col2) VALUES
('some val', 'some other val'),
('some val', 'some other val'),
('some val', 'some other val')

我最终将得到以下表格:

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |
|  8 | "some val" | "some other val" |
|  9 | "some val" | "some other val" |
|____________________________________|

没什么好说的。但如果我和另一个人同时运行相同的查询，这些查询是 原子的 吗？这意味着我们总是会得到：

1)

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- My 1st inserted record
|  8 | "some val" | "some other val" |<-- My 2nd inserted record
|  9 | "some val" | "some other val" |<-- My 3rd inserted record
| 10 | "some val" | "some other val" |<-- Another guy's 1st inserted record
| 11 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
| 12 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
|____________________________________|

使用以下方式： 2)

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- Another guy's 1st inserted record
|  8 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
|  9 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
| 10 | "some val" | "some other val" |<-- My 1st inserted record
| 11 | "some val" | "some other val" |<-- My 2nd inserted record
| 12 | "some val" | "some other val" |<-- My 3rd inserted record
|____________________________________|

根据先执行的两个MySQL查询中的哪一个，结果可能会有所不同。

或者以下异常情况也可能出现：

3）

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- My 1st inserted record
|  8 | "some val" | "some other val" |<-- My 2nd inserted record
|  9 | "some val" | "some other val" |<-- Another guy's 1st inserted record - WTF???
| 10 | "some val" | "some other val" |<-- My 3rd inserted record 
| 11 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
| 12 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
|____________________________________|

或者像这样：

4)

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- Another guy's 1st inserted record
|  8 | "some val" | "some other val" |<-- My 1st inserted record - WTF???
|  9 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
| 10 | "some val" | "some other val" |<-- My 2nd inserted record - WTF^2???
| 11 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
| 12 | "some val" | "some other val" |<-- My 3rd inserted record - WTF^3???
|____________________________________|

是否保证只会得到1)或2)，而不会得到3)、4)或其它组合？对于MyISAM和InnoDB，这种情况是否总是保证发生？我认为1)和2)是原子的。

如果我运行SELECT LAST_INSERT_ID();并得到7，那么这是否意味着id为8和9的行也是由我的查询而不是另一个人的查询插入的？

- tonix

---

LAST_INSERT_ID() 是基于连接操作的。其他人和你不会在同一个连接中进行操作。因此，你是独立的，可以获得自己操作的最后一个 id。 - Mjh

@Mjh 是的，我知道我和另一个用户将接收到自己操作的最后一个 id，并且它们将不同。这不是重点。重点是：给定我的批量 INSERT 查询，例如插入 10 条记录，我执行它，然后获得 LAST_INSERT_ID()。如果例如 LAST_INSERT_ID() = 71，这是否意味着我添加的这 10 条记录都具有 ID 71、72、73、74、75、76、77、78、79、80？ - tonix

不一定非得这样，但既然是批量插入，那就是的。 - Mjh

“不一定非要这样”，你是什么意思？ - tonix

如果它们是批量插入，那么是的。所以，我在编辑帖子时对批量插入的“原子性”做出的假设是正确的吗？如果它们是批量插入，那么是的。MyISAM也是这样吗？ - tonix

Answer 1

答案是：嗯，这取决于情况。

对于myisam，答案肯定是“是”，因为myisam序列化插入请求。

然而，对于innodb，自MySQL V5.1以来，行为是可配置的。在V5.1之前，InnoDB的答案也是肯定的，之后就取决于innodb_autoinc_lock_mode设置。有关详细信息，请参阅mysql文档中的InnoDB自动增量配置。

简要概述一下，有3个innodb_autoinc_lock_mode设置：

1. 传统（0）
2. 连续（1）- 默认
3. 交替（2）

当innodb_autoinc_lock_mode设置为0（“传统”）或1（“连续”）时，由任何给定语句生成的自动递增值将是连续的，没有间隙，因为表级别AUTO-INC锁定将保持到语句结束，并且只能同时执行一个这样的语句。

当innodb_autoinc_lock_mode设置为2（“交替”）时，“批量插入”生成的自动递增值可能会有间隙，但仅当存在并发执行的“INSERT-like”语句时才会出现。

对于锁定模式1或2，连续语句之间可能会出现间隙，因为对于批量插入，可能不知道每个语句所需的自动递增值的确切数量，并且可能会出现高估。

如果已回滚事务，则在auto_increment值中还可能遇到间隙。批量插入只能作为一个整体回滚。 更新： 如上所述，如果您使用以下内容，则会得到场景1）或2）

• myisam表引擎
• 或innodb pre mysql v5.1
• 或者使用MySQL v5.1或更新版本的InnoDB，并且innodb_autoinc_lock_mode为0或1

无法确定哪个会先插入。

如果使用以下配置，可能会发生情况3）或4）

• 具有innodb_autoinc_lock_mode为2的InnoDB

同样，无法说明mysql如何混合记录的顺序和原因。

因此，如果您的问题与批量插入3条记录并且last_insert_id()仅返回第一条插入记录的自增值相关，并且您想通过简单的加法获取另外两条记录的ID，则可能需要根据所使用的表引擎和MySQL版本检查MySQL的配置。

- Shadow

---

为了方便您，您可以运行查询 SHOW VARIABLES LIKE '%innodb_autoinc_lock_mode%' 来检查您的设置。 - Dustin

很好的解释！ - Steven

Answer 2

MySQL将多行插入查询视为一个事务或一个查询，所有行都将被插入，如果插入失败，则不会插入任何行，因此如果您执行此查询：

INSERT INTO table (col1,col2,col3) VALUES
('col1_value_1', 'col2_value_1', 'col3_value_1'),
('col1_value_2', 'col2_value_2', 'col3_value_2'),
('col1_value_3', 'col2_value_3', 'col3_value_3');

如果您的ID是自动递增的，MySQL将把这个作为一个查询运行，它将获取您的ID 57、58、59。如果其他用户在同一时间内插入查询，那么有两种可能性：如果其他用户的查询需要更长的时间，那么您的查询将获取ID 57、58、59；如果您的查询需要更长的时间，那么您的ID将从其他用户查询的结尾开始。因此，无论哪种情况，在ID自动递增的情况下，多插入查询都将按查询排序。

- Gouda Elalfy

---

你所说的话对于MyISAM和InnoDB引擎永远都是这样吗？ - tonix

我认为这是针对InnoDB的，但MyIsam会在同一时间锁定插入，因此如果您开始运行查询，则其他用户的查询将无法完成。 - Gouda Elalfy

所以，你的意思肯定总是适用于MyISAM，并且像我发布的那些异常情况不可能出现。你确认吗？ - tonix

我对InnoDB进行测试并确信，在InnoDB中，但我知道但未确认MyISAM在同一时间锁定查询，就像我在http://dba.stackexchange.com/questions/21075/way-to-prevent-queries-from-waiting-for-table-level-lock中所读到的那样。 - Gouda Elalfy

Answer 3

如果您将任何列定义为主键自动增量，则它将自动从1开始增加值，您不需要在插入查询中定义此列，它将自动在主键列中插入递增的值。

- Pramod Kumar

Answer 4

auto_increment 在并发环境中是安全的。它的作用是提供唯一值，无论有多少人连接和在表上工作。您可以控制增量的偏移量，默认值为1。

现在这到底意味着什么-这意味着在表中写入的内容不必按照1进行递增。这就是著名的“间隙”问题。

假设您和我同时写入您的表。我写了记录10, 11, 12，您写了13, 14, 15。然而，可能发生了一些不好的事情（死锁或事务失败），我的结果未被持久化-查询失败，auto_increment已经花费。在这种情况下，您的记录（13, 14, 15）被写入磁盘，我的记录未被写入。

这是正常行为。您的表不必包含按1递增的数字。它将包含唯一的数字，这是auto_increment的工作。

- Mjh

---

@tonix - 你最终得到的是什么并不重要吧？我认为顺序或实际值都不相关。你最终得到的取决于是否在事务中执行了所有插入查询。从技术上讲，你将得到你示例中的情况1。然而，准确地说，你最终会得到具有唯一标识符的记录。出于好奇，你为什么要问关于插入顺序的实际问题呢？ - Mjh

PHP的PDO类有一个lastInsertId()方法。该方法返回MySQL的LAST_INSERT_ID()结果。当我使用一个批量INSERT查询插入多行后，我调用PDO的lastInsertId()。它将返回批量INSERT中第一行插入的id。现在，我想知道在该批量查询中第一行之后添加的下n行的id，但是PDO没有提供这样的lastInsertIds()方法。假设我知道我已经添加了3行并且lastInsertId() = 7，我能否安全地取(7..9)之间的范围，即7、8、9？ - tonix

好的，既然您正在使用PDO，我可以问一下，为什么不使用预处理语句而使用批量插入？这比使用批量插入要明智得多，而且您可以通过这种方式检索所有生成的id。使用批量插入，你不能得到你需要的 - 有些东西可以满足你的需求。 - Mjh

嗯，我可以使用批量插入的准备语句。你是说我不能用准备语句进行批量插入吗？如果是的话，我可以向您保证，您是错误的。 - tonix

我没有说您不能在批量插入中使用预处理语句，这只是无意义的，而您想要的是每个查询的insert_id。批量插入不允许这样做。此外，批量插入还有各种问题-例如，您很容易超出“max_allowed_packet”的值。我不认为在您的使用场景中批量插入有任何意义，似乎执行循环中的准备好的语句就能够实现您想要的功能。将其包装在事务中也会给您带来高插入速率。 - Mjh

我现在明白你的意思了。 - tonix

Answer 5

如果您在一个语句场景中插入值，则即使使用了innodb_autoinc_lock_mode = 2，也不会出现场景3和4。根据文档https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

innodb_autoinc_lock_mode = 2 在此锁定模式下，自增值保证在所有并发执行的“INSERT-like”语句中是唯一且单调递增的。

已测试。

- SilverRAT

---

测试了我的脚本，针对myisam和场景脚本3和4没有出现。这是因为插入语句，即使它们有多个值，也是一个事务，并且不会拆分成单个插入语句。关于Innodb_autoinc_lock_mode = 2来自文档的解释是：“在此锁定模式下，没有“类似INSERT”的语句使用表级AUTO-INC锁定，并且多个语句可以同时执行。这是最快和最可扩展的锁定模式，但在使用基于语句的复制或恢复方案时，当从二进制日志中重放SQL语句时，它不安全。” - SilverRAT

很抱歉，我没有从文档中的解释中理解这个锁定模式，请您能否进一步解释并举个例子？谢谢！ - tonix

如果您使用innodb_autoinc_lock_mode = 2，则数据库不会等待上一个INSERT完成，而是同时执行。开始插入1， 开始插入2， 开始插入3， 结束插入2， 结束插入3， 结束插入1 这样做速度更快，但只有在二进制日志使用语句的情况下才能实现，因为插入可以以不同的速度进行。从库上的主键可以接受不同的值。 - SilverRAT

这个答案是不正确的。在交错模式下（innodb_autoinc_lock_mode = 2），链接的MySQL文档在引用段落之后明确写道：“但是，由于多个语句可以同时生成数字（即，分配的数字在语句之间交错），因此由任何给定语句插入的行所生成的值可能不是连续的。”几个月前我已经在我的答案中描述了所有这些内容。 - Shadow

那么，我应该将你的标记为正确的，但无论如何还是感谢@SilverRAT！ - tonix