mysql - 当我向 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');

假设我有一个表，其中最后一个 id PRIMARY_KEY AUTO_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???
|____________________________________|

或任何其他组合 != 3) 和 4)？

我认为 1) 和 2) 是原子的。是否总是保证我会总是以1)或2)结束并且永远不会 以 3) 或 4) 或任何其他组合结束？如果是(我总是以 1) 或 2) 结尾)，对于 MyISAM 和 InnoDB ？

如果我执行 SELECT LAST_INSERT_ID(); 并且例如我得到 7，这是否自动意味着带有 id 8 和 9 的行也被 我的查询而不是其他人的查询？

Answer 1

答案是:嗯，这取决于。

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

然而，在 innodb 的情况下，自 mysql v5.1 以来行为是可配置的。在 v5.1 之前，InnoDB 的答案也是肯定的，之后它取决于 innodb_autoinc_lock_mode 设置。请参阅 InnoDB auto_increment configuration for details 上的 mysql 文档.

为了突出显示，有 3 个 innodb_autoinc_lock_mode 设置:

1. 传统 (0)
2. 后续 (1) - 默认
3. 交错 (2)

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

With innodb_autoinc_lock_mode set to 2 (“interleaved”), there may be gaps in the auto-increment values generated by “bulk inserts,” but only if there are concurrently executing “INSERT-like” statements.

For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.

如果事务已回滚，auto_increment 值可能会出现进一步的差距。批量插入只能作为一个整体回滚。

更新:如上所述，您将得到场景 1) 或 2)，如果您使用

• myisam 表引擎
• 或 innodb pre mysql v5.1
• 或带有 mysql v5.1 或更新版本的 innodb，innodb_autoinc_lock_mode 为 0 或 1

无法判断哪个先插入。

如果您使用

，您可能会得到 场景3)或4)

• innodb 与 innodb_autoinc_lock_mode 2

同样，没有办法说明 mysql 如何以及为什么会混淆记录的顺序。

因此，如果您的问题与您使用批量插入插入 3 条记录并且 last_insert_id() 仅返回第一条插入记录的 auto_increment 值这一事实有关，并且您想通过简单的加法获得其他 2 条记录的 ID就是你可能需要根据表引擎和使用的mysql版本检查mysql的配置。