Auto_increment in Innodb

原文链接

To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(*ai_col*) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the first column of some table index.

Lock mode

• “INSERT-like” statements

  All statements that generate new rows in a table, including INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA. Includes “simple-inserts”,“bulk-inserts”, and “mixed-mode” inserts.

• “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.

• “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 assigns new values for the AUTO_INCREMENT column one at a time as each row is processed.

• “Mixed-mode inserts”

The settings are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode

Usage implications

replication的场景下，0,1会同步相应的lock mode，2则不会同步。但针对行锁以及mixed-format replication情况下，都无所谓

0,1,2情况下，回滚都会导致相应的auto_increment丢失

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.

如果指定为负数，行为无效？

当发现1被占了，auto_increment的起跳，变为100？

• consider the following statement, issued when the most-recently generated sequence number is 100:

  mysql> 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.

关于修改auto_increment的值的情况？

counter的实现

在5.7及之前，采用写内存的方式，server restart之后会做max的操作，来获取当前最大的

在8.0中，采用写redo log的方式，直接写文件的方式，这样可能导致的问题，是在写redo log的时候，server crash的情况，导致max值没有回写成功

所以在create table的时候，带上auto_increment的值时，5.7是不生效的，8.0中是生效的

5.7很容易引起reuse的情况，毕竟是在内存中操作