Mysql: 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:

-- 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 и 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 -

-- 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.