MySQL auto_increment_increment, auto_increment_offset usage, autoincrement

MySQL auto_increment_increment, auto_increment_offset usage, autoincrement

In MySQL, you can specify the auto_increment attribute of the column when creating the table for the ID auto-increment column. It is equivalent to the identity attribute in SQL server. Oracle is implemented by Sequence. In MySQL, the system variables auto_increment_increment and auto_increment_offset affect the values of auto-incrementing columns and their change rules. This document describes the usage of these two system variables.

 

1. auto_increment_increment and auto_increment_offset

Auto_increment_increment controls the increment value of values in the column, that is, the step size. Auto_increment_offset determines the starting point of the value of the AUTO_INCREMENT column, that is, the initial value. Variable range: You can set these two variables at the global and session level -- root @ localhost [(none)]> show variables like 'version' in the current system environment '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.5.39-log | + --------------- + ------------ + root @ localhost [mysql]> create database tempdb; root @ localhost [mysql]> use tempdb; -- view the variables auto_increment_increment and auto_increment_offsetroot @ localhost [tempdb]> show variables like '% auto_incre% '; + parameters + ------- + | Variable_name | Value | + -------------------------- + ------- + | auto_increment_increment | 1 | auto_increment_offset | 1 | + ------------------------ + ------- +

2. Demonstration of auto_increment_increment and auto_increment_offset

-- Create a demo table and use the auto_increment clause root @ localhost [tempdb]> create table t1 (id int not null auto_increment primary key, col varchar (20 )); -- insert record root @ localhost [tempdb]> insert into t1 (col) values ('Robin '), ('fred'), ('jack '), ('James '); -- the starting value of the id column is 1, and the increment is 1root @ localhost [tempdb]> select * from t1; + ---- + ------- + | id | col | + ---- + ------- + | 1 | robin | 2 | fred | 3 | jack | 4 | james | + ---- + ------- + -- set the step size to 5root @ localhost [tempdb]> set session auto_increment_increment = 5; root @ localhost [tempdb]> show variables like '% auto_incre% '; + sequence + ------- + | Variable_name | Value | + sequence + ------- + | auto_increment_increment | 5 | auto_increment_offset | 1 | + sequence + ------- + -- clear table t1root @ localhost [tempdb]> truncate table t1; -- insert record root @ localhost [tempdb]> insert into t1 (col) values ('Robin '), ('fred'), ('jack '), ('James '); -- The following query shows that the step size changes with a 5-digit base. root @ localhost [tempdb]> select * from t1; + ---- + ------- + | id | col | + ---- + ------- + | 1 | robin | 6 | fred | 11 | jack | 16 | james | + ---- + ------- + -- set the initial value to 5root @ localhost [tempdb]> set session auto_increment_offset = 5; root @ localhost [tempdb]> show variables like '% auto_incre% '; + keys + ------- + | Variable_name | Value | + keys + ------- + | auto_increment_increment | 5 | auto_increment_offset | 5 | + keys + ------- + root @ localhost [tempdb]> truncate table t1; root @ localhost [tempdb]> insert into t1 (col) values ('Robin '), ('fred'), ('jack'), ('James '); -- The following is the new result root @ localhost [tempdb]> select * from t1; + ---- + ------- + | id | col | + ---- + ------- + | 5 | robin | 10 | fred | 15 | jack | 20 | james | + ---- + ------- +

3. value range of auto_increment_increment and auto_increment_offset

-- Set the variable auto_increment_increment to 0root @ localhost [tempdb]> set session auto_increment_increment = 0; -- the actual value is changed to 1root @ localhost [tempdb]> show variables like '% auto_increment % '; + region + ------- + | Variable_name | Value | + region + ------- + | auto_increment_increment | 1 | auto_increment_offset | 5 | + region + ------- + -- set auto_increment_offset to 0root @ loca. Lhost [tempdb]> set session auto_increment_offset = 0; -- the actual value is also changed to 1root @ localhost [tempdb]> show variables like '% auto_increment % '; + variable + ------- + | Variable_name | Value | + ------------------------ + ------- + | auto_increment_increment | 1 | auto_increment_offset | 1 | + variable + ------- + -- try to set 2 variables greater 65535root @ localhost [tempdb]> set session auto_increment_increment = 65537; root @ localhost [tempdb]> set session auto_increment_offset = 65537; -- the actual values are changed to 65535root @ localhost [tempdb]> show variables like '% auto_increment % '; + variable + ------- + | Variable_name | Value | + -------------------------- + ------- + | auto_increment_increment | 65535 | auto_increment_offset | 65535 | + variable + ------- + -- try to set two variables as negative root @ localhost [tempdb]> set session Auto_increment_offset =-2; root @ localhost [tempdb]> set session auto_increment_increment =-5; -- The following query shows that all data is restored to the default value 1root @ localhost [tempdb]> show variables like '% auto_increment % '; + variable + ------- + | Variable_name | Value | + ------------------------ + ------- + | auto_increment_increment | 1 | auto_increment_offset | 1 | + variable + ------- + the preceding two variables can only be set an integer between 1 and 65535. All non-positive integers are set to the default value 1, and values greater than 65535 are automatically set to 65535.

4. Global and session-level settings

-- Check the value of the two variables in the global range. root @ localhost [tempdb]> show global variables like '% auto_increment % '; + parameters + ------- + | Variable_name | Value | + -------------------------- + ------- + | auto_increment_increment | 1 | auto_increment_offset | 1 | + parameters + ------- + -- set the basic session Value root @ respectively @ localhost [tempdb]> set session auto_increment_increment = 5; root @ localhost [tempdb]> set session auto_increment_offset = 10; -- view the session-level value root @ localhost [tempdb]> show session variables like '% auto_increment % '; + region + ------- + | Variable_name | Value | + -------------------------- + ------- + | auto_increment_increment | 5 | auto_increment_offset | 10 | + region + ------- + -- view the global Value root @ localhost [tempdb]> show global variables like '% auto_increment % '; + region + ------- + | Variable_name | Value | + region + ------- + | auto_increment_increment | 1 | auto_increment_offset | 1 | + ---------------------- + ------- + -- set the global Value root @ localhost [tempdb]> set global auto_increment_increment = 2; root @ localhost [tempdb]> set global auto_increment_offset = 3; root @ localhost [tempdb]> show global variables like '% auto_increment % '; + parameters + ------- + | Variable_name | Value | + -------------------------- + ------- + | auto_increment_increment | 2 | auto_increment_offset | 3 | + ------------------------ + ------- +

5. Changes to any variable in the value of the auto_increment Column

Root @ localhost [tempdb]> truncate table t1; root @ localhost [tempdb]> show variables like '% auto_increment % '; + partition + ------- + | Variable_name | Value | + partition + ------- + | auto_increment_increment | 1 | auto_increment_offset | 1 | + partition + ------- + root @ localhost [tempdb]> insert into t1 (col) values ('Robin '), ('fred'), ('jack'); root @ localhost [tempdb]> Select * from t1; + ---- + ------- + | id | col | + ---- + ------- + | 1 | robin | 2 | fred | 3 | jack | + ---- + ------- + root @ localhost [tempdb ]> set session auto_increment_increment = 5; root @ localhost [tempdb]> show variables like '% auto_increment % '; + ------------------------ + ------- + | Variable_name | Value | + -------------------------- + ------- + | auto_increment_increment | 5 | auto_increment_offset | 1 | + ------ -------------------- + ------- + -- Author: Leshami -- Blog: Comment> insert into t1 (col) values ('David'), ('Tim'), ('Jerry '); root @ localhost [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_v Alue1 = 1 + 1*5 = 6New_value2 = 1 + 2*5 = 11 -- The following is the result after auto_increment_offset is modified. root @ localhost [tempdb]> set session auto_increment_offset = 2; root @ localhost [tempdb]> insert into t1 (col) values ('Lewis '), ('Ian'); root @ localhost [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, it should be calculated as follows: max (id) + (new_offset-old_offset) + increment that is to say, the first value after auto_increment_offset is max (id) + (new_offset-old_offset) + increment followed by step increments.

Auto_increment of mysql

Alter table <TABLE Name> AUTO_INCREMENT = <expected start id value>

Mysql issues with auto_increment

If the primary key has been set to auto-increment, you do not need to maintain the primary key columns when inserting data into the table. For example, your table has two columns: id and name, the insert statement is:
Insert into A (name)
Values ('xxxx ');

If you want the primary key to be the value you need, write it all. For example, if the primary key is 10:

Insert into A (id, name)
Values (10, 'xxxx ');

When loading a local file, if the primary key column in your file has a value, it will be inserted according to the value of the primary key in the file.
If not, the auto-increment flow will flow.
Hope to help you