auto_increment 自增鍵的一些說明
- 2019 年 10 月 5 日
- 筆記
auto_increment 問題：
原文： https://yq.aliyun.com/articles/59263?spm=5176.8091938.0.0.bjzoFN
官方文檔：https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html
導致auto_increment變小的幾種情況：
1、 alter table xx auto_increment = yy;
2、 truncate table
3、 restart mysql
第三種的復現方法：
一張剛創建的innoDB表，目前自增是1.
插入3條記錄後，auto_increment=4.
然後再刪除掉這三條記錄,auto_increment=4 沒變
重啟MySQL，會發現auto_increment值被清空了。我們插入的話，自動從1開始編號了
官方對於自增序號的初始化的幾種情況的說明：
InnoDB AUTO_INCREMENT Counter Initialization
This section describes how InnoDB initializes AUTO_INCREMENT counters.
If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.
To initialize an auto-increment counter after a server restart, InnoDB executes 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;
InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.
If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.
If a SHOW TABLE STATUS statement examines the table before the auto-increment counter is initialized, InnoDB initializes but does not increment the value. The value is stored for use by later inserts. This initialization uses a normal exclusive-locking read on the table and the lock lasts to the end of the transaction. InnoDB follows the same procedure for initializing the auto-increment counter for a newly created table.
After the auto-increment counter has been initialized, if you do not explicitly specify a value for an AUTO_INCREMENT column, InnoDB increments the counter and assigns the new value to the column. If you insert a row that explicitly specifies the column value, and the value is greater than the current counter value, the counter is set to the specified column value.
InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.
A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables to set the initial counter value or alter the current counter value.
