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
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 | +----+-----------+
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.
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.
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.
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.
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.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/auto_increment/