Documentation Home
MySQL Tutorial
Related Documentation Download this Excerpt
PDF (US Ltr) - 196.8Kb
PDF (A4) - 197.5Kb


MySQL Tutorial  / Examples of Common Queries  /  Using AUTO_INCREMENT

7.9 Using AUTO_INCREMENT

TheAUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

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'),    ('lax'),('whale'),('ostrich');SELECT * FROM animals;

Which returns:

+----+---------+| id | name    |+----+---------+|  1 | dog     ||  2 | cat     ||  3 | penguin ||  4 | lax     ||  5 | whale   ||  6 | ostrich |+----+---------+

No value was specified for theAUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless theNO_AUTO_VALUE_ON_ZERO SQL mode is enabled. For example:

INSERT INTO animals (id,name) VALUES(0,'groundhog');

If the column is declaredNOT NULL, it is also possible to assignNULL to the column to generate sequence numbers. For example:

INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

When you insert any other value into anAUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:

INSERT INTO animals (id,name) VALUES(100,'rabbit');INSERT INTO animals (id,name) VALUES(NULL,'mouse');SELECT * FROM animals;+-----+-----------+| id  | name      |+-----+-----------+|   1 | dog       ||   2 | cat       ||   3 | penguin   ||   4 | lax       ||   5 | whale     ||   6 | ostrich   ||   7 | groundhog ||   8 | squirrel  || 100 | rabbit    || 101 | mouse     |+-----+-----------+

Updating an existingAUTO_INCREMENT column value also resets theAUTO_INCREMENT sequence.

You can retrieve the most recent automatically generatedAUTO_INCREMENT value with theLAST_INSERT_ID() SQL function or themysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Use the smallest integer data type for theAUTO_INCREMENT column that is large enough to hold the maximum sequence value you require. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use theUNSIGNED attribute if possible to allow a greater range. For example, if you useTINYINT, the maximum permissible sequence number is 127. ForTINYINT UNSIGNED, the maximum is 255. SeeInteger Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT for the ranges of all the integer types.

Note

For a multiple-row insert,LAST_INSERT_ID() andmysql_insert_id() actually return theAUTO_INCREMENT key from thefirst of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

To start with anAUTO_INCREMENT value other than 1, set that value withCREATE TABLE orALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB Notes

For information aboutAUTO_INCREMENT usage specific toInnoDB, seeAUTO_INCREMENT Handling in InnoDB.

MyISAM Notes

  • ForMyISAM tables, you can specifyAUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for theAUTO_INCREMENT column is calculated asMAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

    CREATE TABLE animals (    grp ENUM('fish','mammal','bird') NOT NULL,    id MEDIUMINT NOT NULL AUTO_INCREMENT,    name CHAR(30) NOT NULL,    PRIMARY KEY (grp,id)) ENGINE=MyISAM;INSERT INTO animals (grp,name) VALUES    ('mammal','dog'),('mammal','cat'),    ('bird','penguin'),('fish','lax'),('mammal','whale'),    ('bird','ostrich');SELECT * FROM animals ORDER BY grp,id;

    Which returns:

    +--------+----+---------+| grp    | id | name    |+--------+----+---------+| fish   |  1 | lax     || mammal |  1 | dog     || mammal |  2 | cat     || mammal |  3 | whale   || bird   |  1 | penguin || bird   |  2 | ostrich |+--------+----+---------+

    In this case (when theAUTO_INCREMENT column is part of a multiple-column index),AUTO_INCREMENT values are reused if you delete the row with the biggestAUTO_INCREMENT value in any group. This happens even forMyISAM tables, for whichAUTO_INCREMENT values normally are not reused.

  • If theAUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with theAUTO_INCREMENT column, if there is one. For example, if theanimals table contained indexesPRIMARY KEY (grp, id) andINDEX (id), MySQL would ignore thePRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence pergrp value.

Further Reading

More information aboutAUTO_INCREMENT is available here: