当我将多行插入MySQL表时,每次都会将ID递增1吗？

如果我有如下查询:

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,那么这个插入查询总是会创建带有id的3条记录57, 58, 59.这个操作是原子的吗？

或者,如果另一个查询写在同一个表上,那么ids不能总是增加1吗？

感谢您的关注!

编辑:请阅读以下内容,因为我可能不太清楚.

当然AUTO_INCREMENT,安全地增加一个,我知道.

重点是:

假设我有下表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也被我的查询插入而不是通过另一个人的查询？

1> Shadow..：

---

答案是:嗯,这取决于.

在myisam的情况下,答案是肯定的,因为myisam序列插入请求.

但是,对于innodb,从mysql v5.1开始,行为是可配置的.在v5.1之前,然后对InnoDB的回答也是肯定的,之后它取决于innodb_autoinc_lock_mode设置.有关详细信息,请参阅InnoDB auto_increment配置的 mysql文档.

为了突出显示,有3种innodb_autoinc_lock_mode设置:

传统(0)

结果(1) - 默认

交错(2)

将innodb_autoinc_lock_mode设置为0("传统")或1("连续")时,任何给定语句生成的自动递增值将是连续的,没有间隙,因为表级AUTO-INC锁定一直保持到结束声明,一次只能执行一个这样的声明.

将innodb_autoinc_lock_mode设置为2("interleaved")时,"批量插入"生成的自动增量值可能存在间隙,但前提是同时执行"INSERT-like"语句.

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

如果已回滚事务,则可以在auto_increment值中体验进一步的差距.批量插入件只能作为整体回滚.

更新: 如上所述,如果使用,您将获得方案1)或2)

myisam表引擎

或者innodb pre mysql v5.1

或innodb与mysql v5.1或更新版本,innodb_autoinc_lock_mode为0或1

没有办法告诉哪个先插入.

如果使用,您可以获得方案3)或4)

innodb innodb_autoinc_lock_mode2

同样,没有办法告诉mysql如何以及为什么混合记录的顺序.

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

---

为了您的方便：您可以通过运行查询`SHOW VARIABLES LIKE'％innodb_autoinc_lock_mode％'来检查设置。