Description\n-----------\n\nThe AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. When you insert a new record into 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), automatically be incremented. This also applies to 0, unless the NO_AUTO_VALUE_ON_ZERO SQL_MODE is enabled.\n\nAUTO_INCREMENT columns start from 1 by default. The automatically generated value can never be lower than 0.\n\nEach 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.\n\n``sql\nCREATE TABLE animals (\n     id MEDIUMINT NOT NULL AUTO_INCREMENT,\n     name CHAR(30) NOT NULL,\n     PRIMARY KEY (id)\n );\n\nINSERT INTO animals (name) VALUES\n    ('dog'),('cat'),('penguin'),\n    ('fox'),('whale'),('ostrich');\n`\n\n`sql\nSELECT  FROM animals;\n+----+---------+\n| id | name    |\n+----+---------+\n|  1 | dog     |\n|  2 | cat     |\n|  3 | penguin |\n|  4 | fox     |\n|  5 | whale   |\n|  6 | ostrich |\n+----+---------+\n`\n\nSERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.\n\n`sql\nCREATE TABLE t (id SERIAL, c CHAR(1)) ENGINE=InnoDB;\n\nSHOW CREATE TABLE t \\G\n*********************** 1. row ***********************\n       Table: t\nCreate Table: CREATE TABLE t (\n  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n  c char(1) DEFAULT NULL,\n  UNIQUE KEY id (id)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\n`\n\nSetting or Changing the Auto_Increment Value\n\nYou 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.\n\nLAST_INSERT_ID() can be used to see the last AUTO_INCREMENT value inserted by the current session.\n\n`sql\nALTER TABLE animals AUTO_INCREMENT=8;\n\nINSERT INTO animals (name) VALUES ('aardvark');\n\nSELECT  FROM animals;\n+----+-----------+\n| id | name      |\n+----+-----------+\n|  1 | dog       |\n|  2 | cat       |\n|  3 | penguin   |\n|  4 | fox       |\n|  5 | whale     |\n|  6 | ostrich   |\n|  8 | aardvark  |\n+----+-----------+\n\nSET insert_id=12;\n\nINSERT INTO animals (name) VALUES ('gorilla');\n\nSELECT  FROM animals;\n+----+-----------+\n| id | name      |\n+----+-----------+\n|  1 | dog       |\n|  2 | cat       |\n|  3 | penguin   |\n|  4 | fox       |\n|  5 | whale     |\n|  6 | ostrich   |\n|  8 | aardvark  |\n| 12 | gorilla   |\n+----+-----------+\n`\n\nInnoDB\n\nAUTO_INCREMENT is persistent in InnoDB.\n\nSee also AUTO_INCREMENT Handling in InnoDB.\n\nSetting Explicit Values\n\nIt 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.\n\nIf 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.\n\nThe following example demonstrates these behaviors:\n\n`sql\nCREATE TABLE t (id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;\n\nINSERT INTO t VALUES (NULL);\nSELECT id FROM t;\n+----+\n| id |\n+----+\n|  1 |\n+----+\n\nINSERT INTO t VALUES (10); -- higher value\nSELECT id FROM t;\n+----+\n| id |\n+----+\n|  1 |\n| 10 |\n+----+\n\nINSERT INTO t VALUES (2); -- lower value\nINSERT INTO t VALUES (NULL); -- auto value\nSELECT id FROM t;\n+----+\n| id |\n+----+\n|  1 |\n|  2 |\n| 10 |\n| 11 |\n+----+\n`\n\nThe ARCHIVE storage engine does not allow to insert a value that is lower than the current maximum.\n\nMissing Values\n\nAn 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.\n\nThus AUTO_INCREMENT values can be used to sort results in a chronological order, but not to create a numeric sequence.\n\nReplication\n\nTo make master-master or Galera safe to use AUTO_INCREMENT , you should use the system variables auto_increment_increment and auto_increment_offset to generate unique values for each server.\n\n`sql\nSET @@auto_increment_increment=3;\n\nSHOW VARIABLES LIKE 'auto_inc%';\n+--------------------------+-------+\n| Variable_name            | Value |\n+--------------------------+-------+\n| auto_increment_increment | 3     |\n| auto_increment_offset    | 1     |\n+--------------------------+-------+\n\nCREATE TABLE t (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);\n\nINSERT INTO t VALUES (NULL), (NULL), (NULL);\n\nSELECT  FROM t;\n+---+\n| c |\n+---+\n| 1 |\n| 4 |\n| 7 |\n+---+\n\nCREATE TABLE t2 (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);\n\nSET @@auto_increment_offset=2;\n\nSHOW VARIABLES LIKE 'auto_inc%';\n+--------------------------+-------+\n| Variable_name            | Value |\n+--------------------------+-------+\n| auto_increment_increment | 3     |\n| auto_increment_offset    | 2     |\n+--------------------------+-------+\n\nINSERT INTO t2 VALUES (NULL), (NULL), (NULL);\n\nSELECT  FROM t2;\n+---+\n| c |\n+---+\n| 2 |\n| 5 |\n| 8 |\n+---+\n`\n\nIf 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:\n\n`sql\nSET @@auto_increment_offset=5;\n\nSHOW VARIABLES LIKE 'auto_inc%';\n+--------------------------+-------+\n| Variable_name            | Value |\n+--------------------------+-------+\n| auto_increment_increment | 3     |\n| auto_increment_offset    | 5     |\n+--------------------------+-------+\n\nCREATE TABLE t3 (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);\n\nINSERT INTO t3 VALUES (NULL), (NULL), (NULL);\n\nSELECT  FROM t3;\n+---+\n| c |\n+---+\n| 1 |\n| 4 |\n| 5 |\n+---+\n\n+--------------------------+-------+\n| Variable_name            | Value |\n+--------------------------+-------+\n| auto_increment_increment | 3     |\n| auto_increment_offset    | 3     |\n+--------------------------+-------+\n\nINSERT INTO t4 VALUES (NULL), (NULL), (NULL);\n\nSELECT  FROM t4;\n+---+\n| c |\n+---+\n| 3 |\n| 6 |\n| 9 |\n+---+\n`\n\nChanging auto_increment_increment and auto_incremenet_offset when adding a new master to a multi-master setup\n\nThe purpose of auto_increment_increment and auto_increment_offset is to ensure that in a multi-master or multi-source setup, all masters generate unique values for auto_increment fields or for sequences with INCREMENT=0.\n\nIf auto_increment_increment is larger than the current number of masters, then one can configure the new master with the not used auto_increment_offset. The easiest way to add a new master is to stop all MariaDB servers, update auto_increment_increment and auto_increment_offset in the configuration files, and restart.\\\\\n\nThis has to be done if auto_increment_increment is 1. If one has more than one master (auto_increment_increment > 1), there is a way to add more masters with only having to restart one of the masters. The 'trick' is to configure one of the masters to not use all the values in its current sequence.\n\nThe following example illustrates how to do it. Assume you have two masters, A and B.\n\nIn this case you will have auto_increment_increment=2 for both masters and A would have auto_increment_offset=1 and B would have auto_increment_offset=2. For A, all auto_increment and generated sequence values will be odd.\n\n`sql\n1\n3\n5\n7\n`\n\nFor B, all values will be even:\n\n`sql\n2\n4\n6\n8\n`\n\nSee the Replication section above.\n\nIf we change auto_increment_increment from 2 to 4 in A, it will now generate values from this sequence:\n\n`sql\n1\n5\n9\n13\n`\n\nAs you can see, values 3, 7, 11 are not going to be used.We can get C to use values from this sequence by configuring auto_increment_increment=4 and auto_increment_offset=3.\n\n`sql\n3\n7\n11\n`\n\nIf we would like to add a 4'th master (D), we can do that by changing 'B' to use auto_increment_increment=4 and then configure D to have auto_increment_increment=4 and auto_increment_offset=4.\n\nNote that when changing the auto_increment_increment or auto_increment_offset on a server, you have to either restart the server or ensure that all current connections are killed. This is needed to force all connections to use the new values.\n\nAlso ensure that, if you want to use the above trick, you always double the value of auto_increment_increment. This is needed to ensure that the sequence used will not conflict with numbers generated by any other master.\n\nCHECK Constraints, DEFAULT Values and Virtual Columns\n\nAUTO_INCREMENT columns are not permitted in CHECK constraints, DEFAULT value expressions and virtual columns.\n\nGenerating Auto_Increment Values When Adding the Attribute\n\n`sql\nCREATE OR REPLACE TABLE t1 (a INT);\nINSERT t1 VALUES (0),(0),(0);\nALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;\nSELECT  FROM t1;\n+---+\n| a |\n+---+\n| 1 |\n| 2 |\n| 3 |\n+---+\n`\n\n`sql\nCREATE OR REPLACE TABLE t1 (a INT);\nINSERT t1 VALUES (5),(0),(8),(0);\nALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;\nSELECT  FROM t1;\n+---+\n| a |\n+---+\n| 5 |\n| 6 |\n| 8 |\n| 9 |\n+---+\n`\n\nIf the NO_AUTO_VALUE_ON_ZERO SQL_MODE is set, zero values will not be automatically incremented:\n\n`sql\nSET SQL_MODE='no_auto_value_on_zero';\nCREATE OR REPLACE TABLE t1 (a INT);\nINSERT t1 VALUES (3), (0);\nALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;\nSELECT  FROM t1;\n+---+\n| a |\n+---+\n| 0 |\n| 3 |\n+---+\n``\n\nURL: https://mariadb.com/docs/server/reference/data-types/auto_increment