Movatterモバイル変換

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 in anInnoDB table does not reset theAUTO_INCREMENT sequence as it does forMyISAM andNDB tables.

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 need. 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. SeeSection 11.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” for the ranges of all the integer types.

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;