MySQL Auto Increment

How AUTO_INCREMENT Works

When a column marked AUTO_INCREMENT is omitted from an INSERT statement (or given NULL or DEFAULT explicitly), MySQL assigns it one more than the current highest value used in that column. The column almost always also serves as the table's PRIMARY KEY, since it needs to be unique for the sequence to make sense.

CREATE TABLE tasks (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL
);

INSERT INTO tasks (title) VALUES ('Write documentation');
INSERT INTO tasks (title) VALUES ('Review pull request');

After these two inserts, the tasks table contains rows with id 1 and id 2, generated entirely by the server. Reading back the id the server just assigned is done with the LAST_INSERT_ID() function, which returns the value for the current connection only, avoiding any race with other simultaneous inserts.

SELECT LAST_INSERT_ID();

Controlling the Starting Value

By default a new AUTO_INCREMENT column starts counting at 1. You can choose a different starting point either at creation time with the table option AUTO_INCREMENT = n, or afterward with ALTER TABLE.

CREATE TABLE tickets (
  id INT PRIMARY KEY AUTO_INCREMENT,
  subject VARCHAR(120) NOT NULL
) AUTO_INCREMENT = 1000;

ALTER TABLE tickets AUTO_INCREMENT = 5000;

• AUTO_INCREMENT values are never reused after a row is deleted, so gaps in the sequence are normal and expected.
• Only one AUTO_INCREMENT column is allowed per table, and it must be indexed (typically as the primary key).
• Resetting AUTO_INCREMENT to a value lower than an existing row's id has no visible effect until that lower value is reached again.