content_image/858025.jpg
| 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 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO  SQL mode is enabled. For example: 

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

If the column is declared NOT NULL , it is also possible to assign NULL  to the column to generate sequence numbers. For example: 

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

When you insert any other value into an AUTO_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 existing AUTO_INCREMENT  column value also resets the AUTO_INCREMENT  sequence. 

You can retrieve the most recent automatically generated 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 the smallest integer data type for the AUTO_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 the UNSIGNED  attribute if possible to allow a greater range. For example, if you use TINYINT , the maximum permissible sequence number is 127. For TINYINT UNSIGNED , the maximum is 255. See Section 11.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT”  for the ranges of all the integer types. 

<img src='content_image/858025.jpg'>

## Note 

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. 

To start with an AUTO_INCREMENT  value other than 1, set that value with CREATE TABLE  or ALTER TABLE , like this: 

