The AUTO_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) ) ENGINE=MyISAM; 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 the AUTO_INCREMENT
column, so MySQL assigned sequence numbers automatically. You
can also explicitly assign NULL
or 0 to the
column to generate sequence numbers.
You can retrieve the most recent
AUTO_INCREMENT
value with the
LAST_INSERT_ID()
SQL function or
the mysql_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 a large enough integer data type for the
AUTO_INCREMENT
column to hold the maximum
sequence value you will need. When the column reaches the upper
limit of the data type, the next attempt to generate a sequence
number fails. For example, if you use
TINYINT
, the maximum permissible
sequence number is 127. For
TINYINT
UNSIGNED
, the maximum is 255.
For a multiple-row insert,
LAST_INSERT_ID()
and
mysql_insert_id()
actually
return the AUTO_INCREMENT
key from the
first of the inserted rows. This enables
multiple-row inserts to be reproduced correctly on other
servers in a replication setup.
For MyISAM
tables you can specify
AUTO_INCREMENT
on a secondary column in a
multiple-column index. In this case, the generated value for the
AUTO_INCREMENT
column is calculated as
MAX(
. This
is useful when you want to put data into ordered groups.
auto_increment_column
)
+ 1 WHERE
prefix=given-prefix
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 the AUTO_INCREMENT
column
is part of a multiple-column index),
AUTO_INCREMENT
values are reused if you
delete the row with the biggest
AUTO_INCREMENT
value in any group. This
happens even for MyISAM
tables, for which
AUTO_INCREMENT
values normally are not
reused.
If the AUTO_INCREMENT
column is part of
multiple indexes, MySQL will generate sequence values using the
index that begins with the AUTO_INCREMENT
column, if there is one. For example, if the
animals
table contained indexes
PRIMARY KEY (grp, id)
and INDEX
(id)
, MySQL would ignore the PRIMARY
KEY
for generating sequence values. As a result, the
table would contain a single sequence, not a sequence per
grp
value.
To start with an AUTO_INCREMENT
value other
than 1, you can set that value with CREATE
TABLE
or ALTER TABLE
,
like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
More information about AUTO_INCREMENT
is
available here:
How to assign the
AUTO_INCREMENT
attribute to a column: Section 12.1.14, “CREATE TABLE
Syntax”, and Section 12.1.6, “ALTER TABLE
Syntax”.How
AUTO_INCREMENT
behaves depending on theNO_AUTO_VALUE_ON_ZERO
SQL mode: Section 5.1.7, “Server SQL Modes”.How to use the
LAST_INSERT_ID()
function to find the row that contains the most recentAUTO_INCREMENT
value: Section 11.14, “Information Functions”.Setting the
AUTO_INCREMENT
value to be used: Section 5.1.4, “Server System Variables”.AUTO_INCREMENT
and replication: Section 17.4.1.1, “Replication andAUTO_INCREMENT
”.Server-system variables related to
AUTO_INCREMENT
(auto_increment_increment
andauto_increment_offset
) that can be used for replication: Section 5.1.4, “Server System Variables”.