Detailed introduction to MySQL auto-increment field value (with code)

This article brings you a detailed introduction to the MySQL auto-increment field value (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1 Foreword

This article comes from answering a question from a Sifou netizen. This netizen created a new table, auto_increment_increment is set to 10, and the starting value of the AUTO_INCREMENT primary key is set to 9. When When he inserted the data, he found that the primary key value started from 11, so he was confused. This article discusses the issue of auto-increment fields.

2 Auto-increment field calculation method

The value of the auto-increment field is related to the auto_increment_increment parameter and the auto_increment_offset parameter. The auto_increment_offset parameter sets the auto-increment field offset value, also It is to calculate the starting value. The auto_increment_increment parameter sets the step size of the auto-increment field, that is, how much it increases each time. auto_increment_increment and auto_increment_offset are often used in master-master replication to prevent primary key duplication. auto_increment_increment is an Integer type, with a value of 1-65535. If it is set to 0, it will be changed to 1. If the setting exceeds the value range, it will be changed to 65535.

The value of the auto-increment field is calculated through the formula auto_increment_offset N × auto_increment_increment, and N is an increasing sequence similar to [1,2,3,...]. When a piece of data is inserted, the database will take the smallest element greater than or equal to the current AUTO_INCREMENT from the increment sequence calculated by auto_increment_offset N × auto_increment_increment as the next auto-increment value of the field.

auto_increment_increment can be changed dynamically, but when calculating the value of the auto-increment field, it will not be affected by the existing data, and the calculation method remains unchanged.

Let us do some tests

db83-3306>>SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

db83-3306>>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

db83-3306>>CREATE TABLE autoinc1(
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

db83-3306>>INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

db83-3306>>select * from autoinc1;
+----+
| id |
+----+
|  1 |
| 11 |
| 21 |
| 31 |
+----+
4 rows in set (0.00 sec)

We first created a table containing auto-increment fields, set the step size to 10, inserted the data and found that the growth is indeed the value calculated in the formula. Next, let's test whether setting AUTO_INCREMENT when creating a table will have any impact

db83-3306>>CREATE TABLE autoinc2(
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> ) engine=InnoDB AUTO_INCREMENT=8;
Query OK, 0 rows affected (0.01 sec)

db83-3306>>INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

db83-3306>>select * from autoinc2;
+----+
| id |
+----+
| 11 |
| 21 |
| 31 |
| 41 |
+----+
4 rows in set (0.00 sec)

You can see that even if the value of AUTO_INCREMENT is set, it will not affect the calculation of auto-increment fields

3 Others Situation

The above is the value under normal circumstances. Below we discuss a special situation

3.1 According to group auto-growth

There is a special situation that will cause The auto-incrementing field may not auto-increment, that is, when using the MyISAM engine and using a joint index, the auto-incrementing field is the first field of the non-joint index.

In this case, the value of the auto-increment field is MAX(auto_increment_column) auto_increment_offset WHERE prefix=given-prefix. How to understand it is that when calculating the growth value, group the fields in front of the joint index auto-increment field. , do an independent growth calculation within this group.

For example, if a joint index is made on a, b, and c, and c is an auto-increment field, then an independent growth calculation is performed on the data under the condition where a=xx and b=xx. The following is an example:

db83-3306>>CREATE TABLE user_pets (
    ->     name varchar(16) NOT NULL,
    ->     id_inc INT NOT NULL AUTO_INCREMENT,
    ->     pets varchar(16) NOT NULL,
    ->     PRIMARY KEY (name, id_inc)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

db83-3306>>INSERT INTO user_pets(name, pets) VALUES
    -> ('chengqm', 'dog'),
    -> ('chengqm', 'cat'),
    -> ('chengqm', 'fish'),
    -> ('yexm', 'dog'),
    -> ('yexm', 'cat'),
    -> ('yexm', 'fish');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

db83-3306>>SELECT * FROM user_pets;
+---------+--------+------+
| name    | id_inc | pets |
+---------+--------+------+
| chengqm |      1 | dog  |
| chengqm |     11 | cat  |
| chengqm |     21 | fish |
| yexm    |      1 | dog  |
| yexm    |     11 | cat  |
| yexm    |     21 | fish |
+---------+--------+------+
6 rows in set (0.00 sec)

It can be seen from the results that in the fields with the same name field, there will be independent self-growth calculations. This calculation method is doing group by It is very convenient when .

The above is the detailed content of Detailed introduction to MySQL auto-increment field value (with code). For more information, please follow other related articles on the PHP Chinese website!