MySQL Training prep (2): MySQL Self-growth trap

In MySQL, the syntax for creating tables in a database is as follows:

1 CREATE TABLE TableName (2column_name_1 column_type_1 constraints,3column_name_2 column_ type_2 constraints,4 ... 5 column_name_n column_type_n constraints, 6 PRIMARY KEY (column_name) 7 );

Where TableName is the table name, COLUMN_NAME is the column name, Column_type is the data type of the column, constraints is the related constraint. In all constraints, there is a auto_increment condition that represents the self-growth column, which automatically grows according to the set step size.

Today we talk about the self-growth column in MySQL.
In MS SQL Server, we know that the self-growth column is automatically growing in strict stride size. But there is a growing trap in MySQL. For a single record, an insert into is really self-growing. But if we bulk INSERT, the self-growth column is not contiguous. Take a look at the example below.

First, we create a departmental table with the following statements:

CREATE TABLE ' IVR '. ' T_dept ' (' f_id ' int not null auto_increment, ' f_areaid ' int null, ' f_dept ' VARCHAR) NULL, PRIM ARY KEY (' f_id '));

The self-growing column is contiguous when we insert the data one at a time. As follows:

The query results are as follows:

Mysql> SELECT * FROM t_dept;+------+----------+--------+| f_id | F_areaid |    F_dept |+------+----------+--------+|        1 | 2 |    Network Excellent | |        2 | 2 |    Transmission | |        3 | 2 |    Data | |        4 | 2 | Home width |+------+----------+--------+4 rows in Set (0.00 sec)

When we bulk INSERT, the problem arises, see below:

The results are as follows:

Mysql> SELECT * FROM t_dept;+------+----------+--------+| f_id | F_areaid |    F_dept |+------+----------+--------+|        1 | 2 |    Network Excellent | |        2 | 2 |    Transmission | |        3 | 2 |    Data | |        4 | 2 |    Home Width | |        5 | 3 |    Network Excellent | |        6 | 3 | Transfer |+------+----------+--------+6 rows in Set (0.00 sec)

At this time the f_id is still continuous. When we insert the record again, the problem arises.

Insert into T_dept (f_areaid,f_dept) Select 3, ' Data ' unionselect 3, ' home width ';

We see the result again, 7 missing:

mysql> select * from t_dept;+------+----------+--------+| f_id | f_areaid | f_ Dept |+------+----------+--------+|    1 |        2 | Network Excellent    | |     2 |        2 | Transfer    | |     3 |        2 | Data    | |     4 |        2 | Home Width    | |     5 |        3 | Network Excellent    | |     6 |        3 | Transfer    | |     8 |        3 | Data    | |     9 |        3 | Home width    |+------+----------+--------+8 rows in Set (0.00 sec) 

Why does this happen, I look at the manual, MySQL, the self-growing column only guarantees the uniqueness of the field. When you bulk Insert n records, MySQL needs to n-1 the record cache, which will default to inserting 2n-1 records, resulting in a record discontinuity.

MySQL Training prep (2): MySQL Self-growth trap