Description
-----------

The AUTO_INCREMENT attribute can be used to generate a unique identity for new
rows. When you insert a new record to the table (or upon adding an
AUTO_INCREMENT attribute with the ALTER TABLE statement), and the
auto_increment field is NULL or DEFAULT (in the case of an INSERT), the value
will automatically be incremented. This also applies to 0, unless the
NO_AUTO_VALUE_ON_ZERO SQL_MODE is enabled.

AUTO_INCREMENT columns start from 1 by default. The automatically generated
value can never be lower than 0.

Each table can have only one AUTO_INCREMENT column. It must defined as a key
(not necessarily the PRIMARY KEY or UNIQUE key). In some storage engines
(including the default InnoDB), if the key consists of multiple columns, the
AUTO_INCREMENT column must be the first column. Storage engines that permit
the column to be placed elsewhere are Aria, MyISAM, MERGE, Spider, TokuDB,
BLACKHOLE, FederatedX and Federated.

CREATE TABLE animals (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
 );

INSERT INTO animals (name) VALUES
  ('dog'),('cat'),('penguin'),
  ('fox'),('whale'),('ostrich');

SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | fox     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

CREATE TABLE t (id SERIAL, c CHAR(1)) ENGINE=InnoDB;

SHOW CREATE TABLE t \G
*************************** 1. row ***************************
   Table: t
Create Table: CREATE TABLE `t` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `c` char(1) DEFAULT NULL,
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Setting or Changing the Auto_Increment Value
--------------------------------------------

You can use an ALTER TABLE statement to assign a new value to the
auto_increment table option, or set the insert_id server system variable to
change the next AUTO_INCREMENT value inserted by the current session.

LAST_INSERT_ID() can be used to see the last AUTO_INCREMENT value inserted by
the current session.

ALTER TABLE animals AUTO_INCREMENT=8;

INSERT INTO animals (name) VALUES ('aardvark');

SELECT * FROM animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | fox       |
|  5 | whale     |
|  6 | ostrich   |
|  8 | aardvark  |
+----+-----------+

SET insert_id=12;

INSERT INTO animals (name) VALUES ('gorilla');

SELECT * FROM animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | fox       |
|  5 | whale     |
|  6 | ostrich   |
|  8 | aardvark  |
| 12 | gorilla   |
+----+-----------+

InnoDB
------

Until MariaDB 10.2.3, InnoDB used an auto-increment counter that is stored in
memory. When the server restarts, the counter is re-initialized to the highest
value used in the table, which cancels the effects of any AUTO_INCREMENT = N
option in the table statements.

From MariaDB 10.2.4, this restriction has been lifted and AUTO_INCREMENT is
persistent.

See also AUTO_INCREMENT Handling in InnoDB.

Setting Explicit Values
-----------------------

It is possible to specify a value for an AUTO_INCREMENT column. If the key is
primary or unique, the value must not already exist in the key.

If the new value is higher than the current maximum value, the AUTO_INCREMENT
value is updated, so the next value will be higher. If the new value is lower
than the current maximum value, the AUTO_INCREMENT value remains unchanged.

The following example demonstrates these behaviors:

CREATE TABLE t (id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE =
InnoDB;

INSERT INTO t VALUES (NULL);
SELECT id FROM t;
+----+
| id |
+----+
|  1 |
+----+

INSERT INTO t VALUES (10); -- higher value
SELECT id FROM t;
+----+
| id |
+----+
|  1 |
| 10 |
+----+

INSERT INTO t VALUES (2); -- lower value
INSERT INTO t VALUES (NULL); -- auto value
SELECT id FROM t;
+----+
| id |
+----+
|  1 |
|  2 |
| 10 |
| 11 |
+----+

The ARCHIVE storage engine does not allow to insert a value that is lower than
the current maximum.

Missing Values
--------------

An AUTO_INCREMENT column normally has missing values. This happens because if
a row is deleted, or an AUTO_INCREMENT value is explicitly updated, old values
are never re-used. The REPLACE statement also deletes a row, and its value is
wasted. With InnoDB, values can be reserved by a transaction; but if the
transaction fails (for example, because of a ROLLBACK) the reserved value will
be lost.

Thus AUTO_INCREMENT values can be used to sort results in a chronological
order, but not to create a numeric sequence.

Replication
-----------

To make master-master or Galera safe to use AUTO_INCREMENT one should use the
system variables auto_increment_increment and auto_increment_offset to
generate unique values for each server.

CHECK Constraints, DEFAULT Values and Virtual Columns
-----------------------------------------------------

MariaDB starting with 10.2.6
----------------------------
From MariaDB 10.2.6 auto_increment columns are no longer permitted in CHECK
constraints, DEFAULT value expressions and virtual columns. They were
permitted in earlier versions, but did not work correctly. See MDEV-11117.

Generating Auto_Increment Values When Adding the Attribute
----------------------------------------------------------

CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (0),(0),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+

CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (5),(0),(8),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 5 |
| 6 |
| 8 |
| 9 |
+---+

If the NO_AUTO_VALUE_ON_ZERO SQL_MODE is set, zero values will not be
automatically incremented:

CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (3), (0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 0 |
| 3 |
+---+

URL: https://mariadb.com/kb/en/auto_increment/