MySQL 笔记（十），包括常见的INSER
一.insert类别语句划分
1.1 Simple inserts
这类语句是指在执行初始阶段（when the statement is initially processed）就知道了插入（或影响）的行数。包括常见的 INSERT 和 REPLACE 语句。需要注意的是：此时没有要求能插入一条；语句中不能包括子查询；不包括 INSERT ... ON DUPLICATE KEY UPDATE 语句。
1.2 Bulk inserts
这类语句不能提前知道要插入的数据行数。
常见类型：This includes INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements, but not plain INSERT.
怎么给AUTO_INCREMENT赋值的呢？逐行赋值。 InnoDB assigns new values for the AUTO_INCREMENT column one at a time as each row is processed.
1.3 Mixed-mode inserts
一种情形在sql语句中部分（非全部）指定了auto-increment列对应的值，而部分行对应的auto-increment值未指定。
These are “simple insert” statements that specify the auto-increment value for some (but not all) of the new rows.
举例：c1是表t1的auto-increment列
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另外一种情形就是INSERT ... ON DUPLICATE KEY UPDATE。这种情形怎么给AUTO_INCREMENT赋值的呢？t取决于update 阶段。he allocated value for the AUTO_INCREMENT column may or may not be used during the update phase.
二. innodb_autoinc_lock_mode
此值可以设置为:
0 ---- traditional
1 ---- consecutive：MySQL 8.0之前版本的默认值
2 ---- interleaved：MySQL 8.0的默认值。
2.1 innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
这个值的设计，是为了向后兼容。
The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.
在这种模式下，上面提到的三种insert语句，针对 AUTO_INCREMENT columns，都需要获取table-level AUTO-INC lock。这种锁的生命期如下：This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of INSERT statements, and to ensure that auto-increment values assigned by any given statement are consecutive.这种设计对兼顾了主从复制。否则，If auto-increment values generated by multiple INSERT statements were interleaved, the result of two concurrent INSERT statements would be nondeterministic, and could not reliably be propagated to a replica server using statement-based replication.即，如果不加这种锁，在statement-based模式下，主从数据就会不一致了。
以上内容，这说明了 table-level AUTO-INC lock 设计的初衷和作用。
为了更好的理解锁的生命周期，举例如下:
CREATE TABLE t1 ( c1 INT(11) NOT NULL AUTO_INCREMENT, c2 VARCHAR(10) DEFAULT NULL, PRIMARY KEY (c1) ) ENGINE=InnoDB;
开启两个独立的事务:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ... Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
Tx1 属于 Bulk inserts 类型，开始执行，并不能判断要分配多少个auto-increment values，所有会有一个table-level lock，保证了循环，逐一插入，逐一生成auto-increment values。都插入完毕了，锁才可以释放，锁持有的时候Tx2 只能干等，不可以interleaved。Tx2产生的auto-increment values 要不全部大于Tx1的auto-increment values值；要不全部小于Tx1的auto-increment values值。
缺点是:
those table-level locks limit concurrency and scalability when multiple transactions are executing insert statements at the same time.
2.2 innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements.Only one statement holding the AUTO-INC lock can execute at a time. If the source table of the bulk insert operation is different from the target table, the AUTO-INC lock on the target table is taken after a shared lock is taken on the first row selected from the source table. If the source and target of the bulk insert operation are the same table, the AUTO-INC lock is taken after shared locks are taken on all selected rows.
“Simple inserts” (for which the number of rows to be inserted is known in advance) avoid table-level AUTO-INC locks by obtaining the required number of auto-increment values under the control of a mutex (a light-weight lock) that is only held for the duration of the allocation process, not until the statement completes. No table-level AUTO-INC lock is used unless an AUTO-INC lock is held by another transaction. If another transaction holds an AUTO-INC lock, a “simple insert” waits for the AUTO-INC lock, as if it were a “bulk insert”.
This lock mode ensures that, in the presence of INSERT statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “INSERT-like” statement are consecutive, and operations are safe for statement-based replication.
Simply put, this lock mode significantly improves scalability while being safe for use with statementbased replication. Further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. There is no change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception.
The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all,(部分AUTO_INCREMENT值明确了，部分没有明确) rows in a multiple-row “simple insert”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.这时候就会全员分配，就是不管你实际上需要不需要，先分配，导致分配的部分值没有用到，这些值会被舍弃。即连续分配，不一定都会写入。
2.3 innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.
In this lock mode, auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing “INSERT-like” statements. However, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is interleaved across statements), the values generated for the rows inserted by any given statement may not be consecutive.
If the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there are no gaps in the numbers generated for a single statement, except for “mixed-mode inserts”. However, when “bulk inserts” are executed, there may be gaps in the auto-increment values assigned by any given statement.
三.Usage Implications
3.1 auto-increment 的值不连贯（丢失）
In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.
3.2 Gaps in auto-increment values for “bulk inserts”
With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement are consecutive, without gaps, because the table-level AUTOINC 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.
3.3 AUTO_INCREMENT列指定NULL or 0 无效
In all lock modes (0, 1, and 2), if a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value was not specified and generates a new value for it.
3.4 Auto-increment values assigned by “mixed-mode inserts”
不同模式下，针对 mixed-mode inserts 生成的Auto-increment values值。
测试表
CREATE TABLE t1 ( c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(1) ) ENGINE = INNODB;
注意，默认自动生成的子增值应该是100.（the most recent automatically generated sequence number is 100.）
插入的语句
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
当 With innodb_autoinc_lock_mode = 0 (“traditional”):
+-----+------+ | c1 | c2 | +-----+------+ | 1 | a | | 101 | b | | 5 | c | | 102 | d | +-----+------+
继续插入的话，下一个值是103
The next available auto-increment value is 103 because the auto-increment values are allocated one at a time, not all at once at the beginning of statement execution. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).
当 innodb_autoinc_lock_mode set to 1 (“consecutive”)
+-----+------+ | c1 | c2 | +-----+------+ | 1 | a | | 101 | b | | 5 | c | | 102 | d | +-----+------+
继续插入的话，下一个值是105
However, in this case, the next available auto-increment value is 105, not 103 because four autoincrement values are allocated at the time the statement is processed, but only two are used. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).
当innodb_autoinc_lock_mode set to 2 (“interleaved”)
+-----+------+ | c1 | c2 | +-----+------+ | 1 | a | | x | b | | 5 | c | | y | d | +-----+------+
The values of x and y are unique and larger than any previously generated rows. However, the specific values of x and y depend on the number of auto-increment values generated by concurrently executing statements.
注意的话，无论模式怎么设置，下面的语句都会报错。
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
With any innodb_autoinc_lock_mode setting, this statement generates a duplicate-key error 23000 (Can't write; duplicate key in table) because 101 is allocated for the row (NULL, 'b') and insertion of the row (101, 'c') fails.
3.5 更新和插入同时进行，是否会引起“Duplicate entry” errors
5.7会，8.0 不会了。
In MySQL 5.7 and earlier, modifying an AUTO_INCREMENT column value in the middle of a sequence of INSERT statements could lead to “Duplicate entry” errors. For example, if you performed an UPDATE operation that changed an AUTO_INCREMENT column value to a value larger than the current maximum auto-increment value, subsequent INSERT operations that did not specify an unused autoincrement value could encounter “Duplicate entry” errors. In MySQL 8.0 and later, if you modify an AUTO_INCREMENT column value to a value larger than the current maximum auto-increment value, the new value is persisted, and subsequent INSERT operations allocate auto-increment values starting from the new, larger value.
四 auto-increment counter（自增值的分配器）
If you specify an AUTO_INCREMENT column for an InnoDB table, the in-memory table object contains a special counter called the auto-increment counter that is used when assigning new values for the column.
In MySQL 5.7 and earlier, the auto-increment counter is stored in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB would execute the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and saved to the data dictionary on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.
On a server restart following a normal shutdown, InnoDB initializes the in-memory auto-increment counter using the current maximum auto-increment value stored in the data dictionary.
On a server restart during crash recovery, InnoDB initializes the in-memory auto-increment counter using the current maximum auto-increment value stored in the data dictionary and scans the redo log for auto-increment counter values written since the last checkpoint. If a redo-logged value is greater than the in-memory counter value, the redo-logged value is applied. However, in the case of an unexpected server exit, reuse of a previously allocated auto-increment value cannot be guaranteed. Each time the current maximum auto-increment value is changed due to an INSERT or UPDATE operation, the new value is written to the redo log, but if the unexpected exit occurs before the redo log is flushed to disk, the previously allocated value could be reused when the auto-increment counter is initialized after the server is restarted.
In MySQL 5.7 and earlier, a server restart cancels the effect of the AUTO_INCREMENT = N table option, which may be used in a CREATE TABLE or ALTER TABLE statement to set an initial counter value or alter the existing counter value, respectively. In MySQL 8.0, a server restart does not cancel the effect of the AUTO_INCREMENT = N table option. If you initialize the auto-increment counter to a specific value, or if you alter the auto-increment counter value to a larger value, the new value is persisted across server restarts.
In MySQL 5.7 and earlier, a server restart immediately following a ROLLBACK operation could result in the reuse of auto-increment values that were previously allocated to the rolled-back transaction, effectively rolling back the current maximum auto-increment value. In MySQL 8.0, the current maximum autoincrement value is persisted, preventing the reuse of previously allocated values.
---学习文档 、mysql官方文档