MySQL auto_increment - start value and increment value

Q

How to set column to be AUTO_INCREMENT in MySQL? And how to set its start value and increment value?

✍: FYIcenter.com

A

1. If you want to create a table with a primary key column in MySQL and want to have it automatically increase it's value, you follow the following example CREATE TABLE statement:

CREATE TABLE MyTable (
   ID   INT PRIMARY KEY AUTO_INCREMENT,
   DATA TEXT
); 

By default, the ID column will automatically increase by 1 and starting from 1. So next auto-increment values are 1, 2, 3, ...

2. If you want change the starting value of the auto_increment column, you can use the following ALTER TABLE statement:

ALTER TABLE MyTable AUTO_INCREMENT = 1000000;

From now on, next auto-increment values are 1000000, 1000001, 1000002, ...

3. If you want to change the increment interval, you can use the following

SET @auto_increment_increment = 5;

From now on, next auto-increment values are 1000000, 1000005, 1000010, ...

2016-08-30, 1426👍, 1💬