Shulou(Shulou.com)06/01 Report--

This article mainly introduces the value of MySQL self-increment field, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the value of MySQL self-increment field.

1 preface

This article comes from answering a question from a netizen who has created a new table, and auto_increment_increment sets the starting value of the primary key to 10 and sets the starting value of the primary key to 9. When he inserts the data, he finds that the primary key value starts from 11, so he is confused. This article will discuss the problem of self-increasing fields together.

2 calculation method of self-increasing field

The value of the self-increasing field is related to the auto_increment_increment parameter and the auto_increment_offset parameter. The auto_increment_offset parameter sets the offset value of the self-increasing field, that is, the starting value of the calculation, and the auto_increment_increment parameter sets the step size of the self-increasing field, that is, how much to increase each time. Auto_increment_increment and auto_increment_offset are often used in primary primary replication to prevent duplicate primary keys. Auto_increment_increment is Integer, 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 self-increment field is calculated by the formula auto_increment_offset + N × auto_increment_increment, while N is similar to [1mem2jing3p.] Such an incremental sequence. When inserting a piece of data, the database takes the smallest element greater than or equal to the current AUTO_INCREMENT from the incrementing sequence calculated by auto_increment_offset + N × auto_increment_increment as the next self-increasing value in that field.

The auto_increment_increment can be changed dynamically, but when calculating the value of the self-increasing field, it will not be affected by the existing data, and the calculation method remains the same.

Let's 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); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0db83-3306 > > select * from autoinc1;+----+ | id | +-+ | 1 | | 11 | 21 | 31 | +-+ 4 rows in set (0.00 sec)

We first create a table with self-increment fields, set the step size to 10, and insert the data to find that the growth is indeed the value calculated in the formula. Let's test again whether setting AUTO_INCREMENT when building the table will have an effect.

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); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0db83-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 does not affect the calculation of the self-increment field.

3 other circumstances

That is the value under normal circumstances. Let's discuss a special case below.

3.1 self-growth according to grouping

There is a special case where the self-growing field may not grow itself, that is, when the MyISAM engine is used and the federated index is used, the self-increment field is the first field of the non-federated index.

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

For example, if we do a joint index on a where a=xx and b=xx BBJ c, and c is a self-increasing field, then we do an independent growth calculation for the data under this condition. Here 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: 0db83-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)

As you can see from the results, there will be independent self-growing calculation in the same field of name field, which is very convenient when doing group by.

After reading the above about what is the value of MySQL self-increasing field, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.