Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » How to set AUTO_INCREMENT step

How to set AUTO_INCREMENT step

Author: rootsql Date: 29-10-2012, 22:44

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.

 

Additional information about used server variables:  Replication Master Options and Variables.

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
  • Group: Users
  • ICQ:
  • Registration: 26.01.2017
  • Comments: 1
  • Publications: 0
^
Привет! Нашёл отличную прогу где все фильмы и сериалы On-Line только в HD качестве и без рекламы и заморочек , и много фильмов которых ещё нет в прокате. и всё это счастье бесплатно, скачать можно здесь > planetfilms.gq < а так же On-Line TV. Пользуйтесь на здоровье!
Information
Members of Guests cannot leave comments.