|  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.

SET @@auto_increment_increment=3;

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 3     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

CREATE TABLE t (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO t VALUES (NULL), (NULL), (NULL);

SELECT * FROM t;
+---+
| c |
+---+
| 1 |
| 4 |
| 7 |
+---+

CREATE TABLE t2 (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

SET @@auto_increment_offset=2;

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 3     |
| auto_increment_offset    | 2     |
+--------------------------+-------+

INSERT INTO t2 VALUES (NULL), (NULL), (NULL);

SELECT * FROM t2;
+---+
| c |
+---+
| 2 |
| 5 |
| 8 |
+---+

If auto_increment_offset is larger than auto_increment_increment, the value of
auto_increment_offset is ignored, and the offset reverts to the default of 1
instead:

SET @@auto_increment_offset=5;

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 3     |
| auto_increment_offset    | 5     |
+--------------------------+-------+

CREATE TABLE t3 (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO t3 VALUES (NULL), (NULL), (NULL);

SELECT * FROM t3;
+---+
| c |
+---+
| 1 |
| 4 |
| 5 |
+---+

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 3     |
| auto_increment_offset    | 3     |
+--------------------------+-------+

INSERT INTO t4 VALUES (NULL), (NULL), (NULL);

SELECT * FROM t4;
+---+
| c |
+---+
| 3 |
| 6 |
| 9 |
+---+

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

auto_increment columns are not permitted in CHECK constraints, DEFAULT value
expressions and virtual columns. They were permitted until MariaDB 10.2.6, 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:

SET SQL_MODE='no_auto_value_on_zero';
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/https://mariadb.com/kb/en/auto_increment/