MySQL Auto_increment_increment and Auto_increment_offset

Refer to this article: (But I disagree with this article)

http://blog.csdn.net/leshami/article/details/39779509

1: Build a test environment

CREATE TABLE T
(t Int (ten) primary key auto_increment);

Mysql> Show variables like '%increment% '; +-----------------------------+-------+| Variable_name               | Value |+-----------------------------+-------+| Auto_increment_increment    | 1     | | auto_increment_offset       | 1     | | div_precision_increment     | 4     | | innodb_autoextend_increment | 8     |+-----------------------------+-------+4 rows in Set (0.00 sec)

Perform 3 insert operations as test data

INSERT into t values ();

INSERT into t values ();

INSERT into t values ();

#插入3个测试数据

Mysql> select * FROM t;+---+| T |+---+| 1 | | 2 | | 3 |+---+3 rows in Set (0.00 sec)

2: Modify the step size to 5

Mysql> set auto_increment_increment=5;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like '%increment% '; +-----------------------------+-------+| Variable_name               | Value |+-----------------------------+-------+| Auto_increment_increment    | 5     | | auto_increment_offset       | 1     | | div_precision_increment     | 4     | | innodb_autoextend_increment | 8     |+-----------------------------+-------+4 rows in Set (0.00 sec)

Then perform the three insert operation:

Mysql> INSERT into t values ();
Query OK, 1 row Affected (0.00 sec)

Mysql> INSERT into t values ();
Query OK, 1 row Affected (0.00 sec)

Mysql> INSERT into t values ();
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * FROM t;+----+| T  |+----+|  1 | |  2 | |  3 | |  6 | | 11 | | |+----+6 rows in Set (0.00 sec)

Conclusion: The initial value is 1: Step: 1, change to initial value 1: Step: 5. Then the value changes from 3 to 6.
###################################################################
#插入4个测试数据, then change the step size to 5. Then insert the data again.

Mysql> select * FROM t;+---+| T |+---+| 1 | | 2 | | 3 | | 4 | | 6 |+---+5 rows in Set (0.00 sec)

You can see that when the previous amount of data is less than step 5, the newly inserted data is still 6.

###################################################################

###################################################################
#插入5个测试数据, then change the step size to 5, and then insert the data again.

Mysql> SELECT * FROM t;+----+| T  |+----+|  1 | |  2 | |  3 | |  4 | |  5 | |  6 | | |+----+7 rows in Set (0.00 sec)

You can see that when the previous amount of data is less than step 5, the new data inserted is still 6.

###################################################################

###################################################################
Insert 6 test data, then change the step size to 5, and then insert the data again. It becomes 11 straight.

Mysql> select * FROM t;+---+| T |+---+| 1 | | 2 | | 3 | | 4 | | 5 | | 6 |+---+6 rows in Set (0.00 sec) mysql> set auto_increment_increment=5; Query OK, 0 rows Affected (0.00 sec) mysql> Show variables like '%increment% '; +-----------------------------+-------+| Variable_name               | Value |+-----------------------------+-------+| Auto_increment_increment    | 5     | | auto_increment_offset       | 1     | | div_precision_increment     | 4     | | innodb_autoextend_increment | 8     |+-----------------------------+-------+4 rows in Set (0.00 sec) mysql> insert into t values (); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * FROM t;+----+| T  |+----+|  1 | |  2 | |  3 | |  4 | |  5 | |  6 | | |+----+7 rows in Set (0.00 sec)

###################################################################
The first time is to insert 3 test data, then change the step to 5, then insert the data is 6, the second Insert 4 test data, and then change the step to 5, then insert the data or 6, insert 5 test data, and then change the step to 5, then insert the data or 6, insert 6 test data, and then change the step to 5, It becomes 11.
If the previous data is less than or equal to the step size, then the newly inserted data is the Step + initial value after changing the step size. If it is larger than the step size, it grows directly on this basis.

################################################################################
Delete from is deleted and still grows on the original base.
################################################################################

MySQL Auto_increment_increment and Auto_increment_offset