MySQL Auto_increment_increment,auto_increment_offset Usage

MySQL can specify the Auto_increment property on the column when the table is created, and the IDENTITY property in SQL Server, and Oracle is implemented in sequence way. In MySQL, the system variable Auto_increment_increment,auto_increment_offset affects the value of the self-increment column and its change rule. This article mainly describes the related usage of these two system variables.

1. Auto_increment_increment and Auto_increment_offset effect

auto_ Increment_increment the increment value of the value in the control column, which is the step size. Auto_increment_offset determines the starting point of the Auto_increment column value, which is the initial value. Variable range: These 2 variables can be set at the global and session level-the current system environment [email protected][(none)]> show variables like ' version '; +------------ ---+------------+| variable_name | Value |+---------------+------------+| Version | 5.5.39-log |+---------------+------------+[email protected][mysql]> CREATE database tempdb; [Email protected] [mysql]> use tempdb;--View variables auto_increment_increment and auto_increment_offset[email protected][tempdb]> Show variables like '%auto_incre% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 | | Auto_increment_offset | 1 |+--------------------------+-------+ 

2, demo Auto_increment_ Increment and Auto_increment_offset

--Create a demo table using the AUTO_INCREMENT clause [email protected][tempdb]> CREATE TABLE t1 (ID int not NULL auto_increment primary key , col varchar (20));--insert record [email protected][tempdb]> insert into T1 (col) values (' Robin '), (' Fred '), (' Jack '), (' James ');--Below you can see that the ID column has a starting value of 1, an increment of 1[email protected][tempdb]> select * from t1;+----+-------+| ID |  Col |+----+-------+| 1 |  Robin | | 2 |  Fred | | 3 |  Jack | | 4 | James |+----+-------+--Set the step length to 5[email protected][tempdb]> set session auto_increment_increment=5; [Email protected] [tempdb]> Show variables like '%auto_incre% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 5 | | Auto_increment_offset | 1 |+--------------------------+-------+--empty table t1[email protected][tempdb]> truncate TABLE t1;--Insert record again [email& nbsp;protected][tempdb]> INSERT INTO T1 (' Robin '), (' Fred '), (' Jack '), (' James ');--The following query can see that the stride size changes by 5-bit cardinality [ Email protEcted][tempdb]> SELECT * from t1;+----+-------+| ID |  Col |+----+-------+| 1 |  Robin | | 6 | Fred | | 11 | Jack | | 16 | James |+----+-------+--Set the initial value to 5[email protected][tempdb]> set session auto_increment_offset=5; [Email protected] [tempdb]> Show variables like '%auto_incre% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 5 | | Auto_increment_offset | 5 |+--------------------------+-------+[email protected][tempdb]> truncate table t1; [Email protected] [tempdb]> INSERT INTO T1 (' Robin '), (' Fred '), (' Jack '), (' James ');--Here's the new result [email protected][ Tempdb]> SELECT * from t1;+----+-------+| ID |  Col |+----+-------+| 5 | Robin | | 10 | Fred | | 15 | Jack | | 20 | James |+----+-------+

3, Auto_increment_ Increment and Auto_increment_offset values range

--Set the variable auto_increment_increment to 0[email protected][tempdb]> set session auto_increment_increment=0;-- The actual value becomes 1[email protected][tempdb]> show variables like '%auto_increment% '; +--------------------------+----- --+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 | | Auto_increment_offset | 5 |+--------------------------+-------+--also set Auto_increment_offset as 0[email protected][tempdb]> set Session auto_increment_offset=0;--actual value also became 1[email protected][tempdb]> show variables like '%auto_increment% ' ;+--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 | | Auto_increment_offset | 1 |+--------------------------+-------+--below try to set 2 variables greater than 65535[email protected][tempdb]> set session Auto_ increment_increment=65537; [Email protected] [tempdb]> Set session auto_increment_offset=65537;--its actual value becomes 65535[EMAil protected][tempdb]> Show variables like '%auto_increment% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 65535 | | Auto_increment_offset | 65535 |+--------------------------+-------+--try to set a negative value for 2 variables [email protected][tempdb]> set session Auto_ Increment_offset=-2; [Email protected] [tempdb]> Set session auto_increment_increment=-5;--The following query can see that all revert to the default value 1[email protected][tempdb]> show Variables like '%auto_increment% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 | | Auto_increment_offset | 1 |+--------------------------+-------+ from above you can see that 2 variables can only be set to an integer value between 1 and 65535. All non-positive integers are set to the default value of 1, and values greater than 65535 are automatically set to 65535.

4, global and session-level settings

--View global scope The values of these 2 variables [email protected][tempdb]> show global variables like '%auto_increment% '; +------------------ --------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 | | Auto_increment_offset | 1 |+--------------------------+-------+--set session basic values [email protected][tempdb]> set session Auto_ increment_increment=5; [Email protected] [tempdb]> Set Session auto_increment_offset=10;--View Session level value [email protected][tempdb]> show session Variables like '%auto_increment% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 5 | | Auto_increment_offset | |+--------------------------+-------+--View global-level values [email protected][tempdb]> show global variables like '% auto_increment% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| Auto_increment_increment | 1 | | Auto_increment_offset | 1 |+--------------------------+-------+--set global-level values [email protected][tempdb]> set Globals Auto_increment_ increment=2; [Email protected] [tempdb]> set global auto_increment_offset=3; [Email protected] [tempdb]> show global variables like '%auto_increment% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 2 | | Auto_increment_offset | 3 |+--------------------------+-------+

5, a condition in which any variable of the Auto_increment column value has been changed

[Email protected] [tempdb]> truncate TABLE t1; [Email protected] [tempdb]> Show variables like '%auto_increment% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 | | Auto_increment_offset | 1 |+--------------------------+-------+[email protected][tempdb]> insert into T1 (col) values (' Robin '), ('          Fred '), (' Jack '); [Email protected] [tempdb]> SELECT * from t1;+----+-------+| ID |  Col |+----+-------+| 1 |  Robin | | 2 |  Fred | | 3 | Jack |+----+-------+[email protected][tempdb]> set session auto_increment_increment=5; [Email protected] [tempdb]> Show variables like '%auto_increment% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment | 5 | | Auto_increment_offset | 1 |+--------------------------+-------+--author:leshami--blog:http://blog.csdn.net/leshami[email protected][tempdb]> insert into T1 (col) VALUES (' David '), (' Tim '), (' Jerry '); [Email protected] [tempdb]> SELECT * from t1;+----+-------+| ID |  Col |+----+-------+| 1 |  Robin | | 2 |  Fred | | 3 |  Jack | | 6 | David | | 11 | Tim | | 16 |  Jerry |+----+-------+new_value = auto_increment_offset+ N * auto_increment_incrementnew_value1 = 1 + 1 * 5 = 6new_value2 = 1 + 2 * 5 = 11--The following is the result of modifying Auto_increment_offset [email protected][tempdb]> set session auto_increment_offset=2; [Email protected] [tempdb]> insert into T1 (col) VALUES (' Lewis '), (' Ian '); [Email protected] [tempdb]> SELECT * from t1;+----+-------+| ID |  Col |+----+-------+| 1 |  Robin | | 2 |  Fred | | 3 |  Jack | | 6 | David | | 11 | Tim | | 16 | Jerry | | 22 | Lewis | | 27 | Ian |+----+-------+ This ID is 22, which should be inferred from this: Max (ID) + (new_offset-old_offset) +increment that is, change Auto_increment_ The first value after offset is max (ID) + (new_offset-old_offset) +increment and then increments by step.

MySQL Auto_increment_increment,auto_increment_offset Usage