如果我有以下查询:
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???
|____________________________________|
是否保证只会得到1)或2),而不会得到3)、4)或其它组合?对于MyISAM
和InnoDB
,这种情况是否总是保证发生?我认为1)和2)是原子的。
如果我运行SELECT LAST_INSERT_ID();
并得到7
,那么这是否意味着id
为8
和9
的行也是由我的查询而不是另一个人的查询插入的?
LAST_INSERT_ID()
是基于连接操作的。其他人和你不会在同一个连接中进行操作。因此,你是独立的,可以获得自己操作的最后一个 id。 - MjhINSERT
查询,例如插入 10 条记录,我执行它,然后获得LAST_INSERT_ID()
。如果例如LAST_INSERT_ID() = 71
,这是否意味着我添加的这 10 条记录都具有 ID71
、72
、73
、74
、75
、76
、77
、78
、79
、80
? - tonix