This article will give you an in-depth understanding of the auto-incrementing primary key in MySQL. I hope it will be helpful to you!
Different engines have different strategies for saving auto-incremented values
1. The auto-incremented value of the MyISAM engine is stored in the data file
2.The auto-incremented value of the InnoDB engine , in MySQL5.7 and previous versions, the self-incremented value is stored in memory and is not persisted. After each restart, when you open the table for the first time, you will find the maximum value of the auto-increment max(id), and then use the max(id) step size as the current auto-increment value of the table
select max(ai_col) from table_name for update;
In MySQL8 .0 version records the changes in the self-increasing value in the redo log. When restarting, rely on the redo log to restore the value before restarting
If the field id is defined as AUTO_INCREMENT, when inserting a row of data, the behavior of auto-increment is as follows:
1. If the id field is specified as 0, null or unspecified value when inserting data, then this The current AUTO_INCREMENT value of the table is filled in the auto-increment field
2. If the id field specifies a specific value when inserting data, use the value specified in the statement directly
Assume that a certain value is to be inserted The value is X, and the current auto-increment value is Y
1. If The self-increment value is modified to a new self-increment value
The new self-increment value generation algorithm is: starting from auto_increment_offset (initial value), taking auto_increment_increment (step size) as the step size, and continuing to superpose until the first value is found. A value greater than Field, c is the only index. The table creation statement is as follows:CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c`) ) ENGINE=InnoDB;Assume that there is already a record (1,1,1) in table t, and then execute another insert data command:
insert into t values(null, 1, 1);The execution process is as follows:
After that, when inserting a new data row, the auto-incremented ID obtained is 3. There is a situation where the auto-incrementing primary key is not continuous
Unique key conflicts and transaction rollbacks will lead to the situation where the auto-incrementing primary key id is not continuous
4. Optimization of lock increase
But in MySQL5. In version 0, the scope of self-increasing locks is statement level. In other words, if a statement applies for a table auto-increment lock, the lock will not be released until the statement is executed.
MySQL version 5.1.22 introduces a new strategy, a new parameter innodb_autoinc_lock_mode, the default value is 1In ordinary insert statements, the self-increasing lock is released immediately after the application.
For statements such as insert...select that insert data in batches, the self-increasing lock still has to wait until the statement is completed before being released
3. This parameter is set to 2. All actions for applying for an auto-incremented primary key are to release the lock after application. For the sake of data consistency, the default setting is 1
, sessionB continued to execute and inserted two records ( 4,3,3), (5,4,4)
如果有批量插入数据(insert … select、replace … select和load data)的场景时,从并发插入数据性能的角度考虑,建议把innodb_autoinc_lock_mode设置为2,同时binlog_format设置为row,这样做既能并发性,又不会出现数据一致性的问题
对于批量插入数据的语句,MySQL有一个批量申请自增id的策略:
1.语句执行过程中,第一次申请自增id,会分配1个
2.1个用完以后,这个语句第二次申请自增id,会分配2个
3.2个用完以后,还是这个语句,第三次申请自增id,会分配4个
4.依次类推,同一个语句去申请自增id,每次申请到的自增id个数都是上一次的两倍
insert into t values(null, 1,1); insert into t values(null, 2,2); insert into t values(null, 3,3); insert into t values(null, 4,4); create table t2 like t; insert into t2(c,d) select c,d from t; insert into t2 values(null, 5,5);
insert … select,实际上往表t2中插入了4行数据。但是,这四行数据是分三次申请的自增id,第一次申请到了id=1,第二次被分配了id=2和id=3,第三次被分配到id=4到id=7
由于这条语句实际上只用上了4个id,所以id=5到id=7就被浪费掉了。之后,再执行insert into t2 values(null, 5,5)
,实际上插入了的数据就是(8,5,5)
这是主键id出现自增id不连续的第三种原因
自增主键字段在达到定义类型上限后,再插入一行记录,则会报主键冲突的错误
CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295; INSERT INTO t VALUES(NULL); INSERT INTO t VALUES(NULL);
第一个insert语句插入数据成功后,这个表的AUTO_INCREMENT没有改变(还是4294967295),就导致了第二个insert语句又拿到相同的自增id值,再试图执行插入语句,报主键冲突错误
【相关推荐:mysql视频教程】
The above is the detailed content of An article to talk about the auto-incrementing primary key in MySQL. For more information, please follow other related articles on the PHP Chinese website!