写在前面:此总结为2013年的某一天的讨论中延伸得到,以下为正文。
根据这几天的讨论和查阅相关资料,我将这个问题总结如下:
源问题为:mysql对含有auto_increment字段的表多次load数据时(中间不使用insert),表中auto_increment的字段中值会出现不连续的,叫做“间隙”或者“断裂”(gaps)。为什么会出现这种情况呢?
以这个问题扩展出来的问题为:
- load data和普通的insert有什么区别?
- Innodb的锁机制,load data和insert过程的锁机制?
- auto_increment的最大值?
**
从mysql的Insert说起
(以下出自MySQL官网):
First, some definitions:
“INSERT-like” statements
All statements that generate new rows in a table, including INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA.
“插入相关”的语句
以下所有的语句都能在表中产生新的行,有INSERT,INSERT… SELECT,REPLACE,REPLACE … SELECT,和LOAD DATA。“Simple inserts”
Statements for which the number of rows to be inserted can be determined in advance (when the statement is initially processed). This includes single-row and multiple-row INSERT and REPLACE statements that do not have a nested subquery, but not INSERT … ON DUPLICATE KEY UPDATE.
“一般插入”
当语句在初始化执行(initially processed)的时候,就知道了要插入的行的数量,这包括单行和多行INSERT以及REPLACE语句,并且这些语句没有子嵌套语句。但是不包括INSERT… ON DUPLICATE KEY UPDATE“Bulk inserts”
Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. This includes INSERT … SELECT, REPLACE … SELECT, and LOAD DATA statements, but not plain INSERT. InnoDB will assign new values for the AUTO_INCREMENT column one at a time as each row is processed.
“批量插入”
语句中要插入的行的数量和需要的auto_increment值的数量不能提前知道。这包括INSERT … SELECT,REPLACE …SELECT和load data语句,但不包括一般的插入语句(plain insert),innodb 会在每行执行的时候给auto_increment列分配新的值。“Mixed-mode inserts”
These are “simple insert” statements that specify the auto-increment value for some (but not all) of the new rows. An example follows, where c1 is an AUTO_INCREMENT column of table t1:
INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);
Another type of “mixed-mode insert” is INSERT … ON DUPLICATE KEY UPDATE, which in the worst case is in effect an INSERT followed by a UPDATE, where