Mysql: How to set AUTO_INCREMENT step

How to set AUTO_INCREMENT step

The AUTO_INCREMENT attribute is used to generate a unique value for an identity column.

Let us check how it works:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

<pre><code>-- Create table CREATE TABLE employees(   id INT(11) AUTO_INCREMENT,   name VARCHAR(30),   PRIMARY KEY (id) ); -- Add some records INSERT INTO employees (name)   VALUES ('John'), ('Angela'), ('George'), ('Kate'); -- Retrieve data SELECT id, name FROM employees; +----+--------+ | id | name   | +----+--------+ |  1 | John   | |  2 | Angela | |  3 | George | |  4 | Kate   | +----+--------+

As you can see id values were generated from 1 to 4 with step 1. To control AUTO_INCREMENT operation we can use server variables – auto_increment_increment  And auto_increment_offset.

• auto_increment_increment – is the incremental value, controls the interval between successive column values.
• auto_increment_offset – determines the starting value for the AUTO_INCREMENT field; this value is used for the first record inserted into the table.

These variables have session and global scope.

Let us try to use these variables –

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

<pre><code>-- Empty table TRUNCATE TABLE employees; -- Set variables (SESSION scope) SET @@session.auto_increment_increment = 10; SET @@session.auto_increment_offset = 5; -- Add some records INSERT INTO employees (name)   VALUES ('John'), ('Angela'), ('George'), ('Kate'); -- Retrieve data SELECT id, name FROM employees; +----+--------+ | id | name   | +----+--------+ |  5 | John   | | 15 | Angela | | 25 | George | | 35 | Kate   | +----+--------+

The first records has id = 5, next id is 15 and so on.