当我在MySQL表中插入多行时，id每次都会增加1吗? [英] When I INSERT multiple rows into a MySQL table, will the ids be increment by 1 everytime?

问题描述

如果我有如下查询:

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的记录.此操作是原子操作吗?

Suppose that I have a table where the last id PRIMARY_KEY AUTO_INCREMENT value is 56, then will this insert query always create 3 records with ids 57, 58, 59. Is this operation atomic?

或者，如果另一个查询写在同一张表上，ids不能总是增加1吗?

Or, if another query writes on the same table, could the ids not increment always by 1?

感谢您的关注！

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

EDIT: Please read the following because maybe I wasn't so clear.

我当然知道，AUTO_INCREMENT会安全地递增一.

Of course AUTO_INCREMENT increments by one safely, I know that.

重点是:

比方说，我有一个名为table的下表:

Let's say I have the following table called 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" |
|____________________________________|

这里无话可说.但是，如果我和另一个人同时运行相同的查询，这些查询是否是 atomic ?，这意味着我们总是最终会得到:

Nothing to say here. But if me and another guy run the same query at the same time, are these queries atomic?, meaning that we will always end up with:

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调度中的哪个.

Depending on which query of the two MySQL schedules first.

还是会出现以下异常?

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)?

Or any other combination != 3) and 4)?

我认为 1)和 2)是原子的.是总是保证，我会总是以 1)或 2)结束，并且永远不会最终以 3)或 4)还是其他组合?如果是的话(我将始终以1)或2)结尾)，对于MyISAM和InnoDB都是如此?

I consider 1) and 2) as atomic. Is it always guaranteed that I will always end up with 1) or 2) and never ever end up with 3) or 4) or any other combination? And if yes (I will always end up with 1) or 2)), both for MyISAM and InnoDB?

如果我这样做SELECT LAST_INSERT_ID();，例如我得到7，这是否自动表示id 8和9的行也是由我的查询而不是由另一个人的查询插入的?

If I do SELECT LAST_INSERT_ID(); and e.g. I get 7, does it automatically mean that the rows with id 8 and 9 were also inserted by my query and not by the query of the other guy?

推荐答案

答案是:嗯，这要视情况而定.

The answer is: well, it depends.

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

In case of myisam, the answer is a definite yes, since myisam sequences insert requests.

但是，在innodb的情况下，自mysql v5.1起，该行为是可配置的.在v5.1之前，对于InnoDB的回答也是是"，此后它取决于innodb_autoinc_lock_mode设置.有关详细信息，请参见 InnoDB auto_increment配置上的mysql文档.

In case of innodb, however, the behaviour is configurable since mysql v5.1. before v5.1, then answer for InnoDB is also yes, after that it depends the on the innodb_autoinc_lock_mode setting. See mysql documentation on InnoDB auto_increment configuration for details.

要给您带来亮点，共有3个innodb_autoinc_lock_mode设置:

To give you the highlights, there are 3 innodb_autoinc_lock_mode settings:

1. 传统(0)
2. 相应(1)-默认
3. 交错的(2)

在innodb_autoinc_lock_mode设置为0(传统")或1(连续")的情况下，任何给定值生成的自动增量值 语句将是连续的，没有间隙，因为表级 AUTO-INC锁将保持到语句结束，并且只有一个 这样的语句可以一次执行.

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.

在innodb_autoinc_lock_mode设置为2("interleaved")的情况下，可能存在 批量插入"生成的自动增量值中的差距，但是 仅当同时执行类似INSERT"的语句时.

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.

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

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值中可能还会有更多的空白.批量插入只能整体回滚.

Further gaps can be experience in the auto_increment value, if a transactions has been rolled back. A bulk insert can only be rolled back as a whole.

更新: 如上所述，如果使用

UPDATE: As described above, you will get scenario 1) or 2), if you use

• myisam表引擎
• 或innodb mysql v5.1之前版本
• 或具有mysql v5.1或更高版本且innodb_autoinc_lock_mode为0或1的innodb

• myisam table engine
• or innodb pre mysql v5.1
• or innodb with mysql v5.1 or newer and the innodb_autoinc_lock_mode is 0 or 1

无法判断哪个首先插入.

There is no way of telling which gets inserted first.

如果使用

• 带有innodb_autoinc_lock_mode 2
的innodb

• innodb with innodb_autoinc_lock_mode 2

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

Again, there is no way of telling how and why mysql mixes up the order of the records.

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

So, if your question is related to the fact that you insert 3 records with bulk insert and last_insert_id() returns the auto_increment value of the first inserted record only, and you want get the ids of the other 2 records by simple addition is that you may need to check mysql's configuration based on the table engine and mysql version used.

这篇关于当我在MySQL表中插入多行时，id每次都会增加1吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！