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, and the auto_increment field is NULL or
DEFAULT, 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/XtraDB
 
Until MariaDB 10.2.3, InnoDB and XtraDB 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 XtraDB/InnoDB.
 
Setting Explicit Values
 
It is possible to specify a value for an AUTO_INCREMENT
column. The value must not 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 behaviours:
 
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
 
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.
 


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