3.5.9 Using AUTO_INCREMENT

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)
             );
INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),
                                  ("lax"),("whale");
SELECT * FROM animals;

Which returns:

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

You can retrieve the used AUTO_INCREMENT key with the LAST_INSERT_ID() SQL function or the mysql_insert_id() API function. Note: for a multi-row insert, LAST_INSERT_ID()/mysql_insert_id() will actually return the AUTO_INCREMENT key from the first inserted row. This allows multi-row inserts to be reproduced on other servers.

For MyISAM and BDB tables you can specify AUTO_INCREMENT on secondary column in a multi-column key. In this case the generated value for the autoincrement column is calculated as MAX(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)
             );
INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
                  ("bird","penguin"),("fish","lax"),("mammal","whale");
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 |
+--------+----+---------+

Note that in this case, the AUTO_INCREMENT value will be reused if you delete the row with the biggest AUTO_INCREMENT value in any group.

User Comments

[following on from the last example]
Of course, Bowser shuffled off this mortal coil
in 1995, which kind of makes him ineligible as a
potential mate. Refining this query to eliminate
dead pets resolves this:
SELECT p1.name, p1.sex, p2.name, p2.sex,
p1.species FROM pet AS p1, pet AS p2 WHERE
p1.species = p2.species AND p1.sex ="f" AND
p2.sex="m" AND p1.death IS NULL AND p2.death IS
NULL;

ALTER TABLE tbl_name AUTO_INCREMENT = 100
will start your records at 100

ALTER TABLE tbl_name AUTO_INCREMENT = 1000
will start your records at 1000

etc...

Just in case there's any question, the
AUTO_INCREMENT field /DOES NOT WRAP/. Once you
hit the limit for the field size, INSERTs generate
an error. (As per Jeremy Cole)

This is my comment.
It will go here?

Any way to control how auto_increment sequences? I often need to use negative integers for keys so that can combine data sets (one positive and one negative). Can you start a key at -1 and have the next assigned value get -2?

Thanks

How does the combination of auto_increment and last_insert_id() work in a concurrent situation:
User1 inserts with auto_increment, and for some reason is delayed with the last_insert_id() call.
User2 inserts with auto_increment.
User2 calls last_insert_id()
User1 finally calls last_insert_id()

Which id does User1 get?
Which id does User2 get?

From comment by James Wilson below:
LAST_INSERT_ID() operates on a per connection basis. So you will never retrieve the id of someone else inserting into the DB when using the query "SELECT LAST_INSERT_ID()".

How to reset the auto_increment field value.

I have create a table name 'master'. There is fild ID with auto_increment option.

I have inserted 10 records in table. Then I have deleted 3 of them, which have 2,5,7 ID. Still the last record ID is 10. How can reset these values?

Thanks in Advance.
Krishna Sunuwar
http://www.multinetnepal.com

LAST_INSERT_ID() operates on a per connection basis. So you will never retrieve the id of someone else inserting into the DB when using the query "SELECT LAST_INSERT_ID()".

I've a table created for PHP-Nuke 6.5, table name : nuke_users
Here's the structure of the table's field 'user_id' :
Field Type Attributes Null Extra
user_id int(11) No auto_increment

The 'user_id' value of the 1st record is -1
The last 'user_id' value of the records is 71
Those 72 records are manually imported(inserted) from sequences of SQL commands .

The abnormal alwasy happened as : the newest inserted records always got the user_id value '2147483647' .
So strange and no solution.... :(

Something this fails to mention is that an auto_incremented value CAN NOT be zero. EVER. any value ever set to zero in an auto_incremented value whenever the auto_increment number is SET. Also note that attempting to INSERT a value of zero will be ignored and changed to the auto_incremented value.
eg:
ALTER TABLE `table` AUTO_INCREMENT = 0; #ignored, auto_increment is set to 1
INSERT INTO `table` (`id`, `name`) VALUES ('0', 'mom'); #the '0' is ignored, `id` is set to 1
UPDATE `table` SET `id`=0 WHERE `name`='mom' LIMIT 1; #works as expected
INSERT INTO `table` (`name`) VALUES ('dad'); #`id` is set to 2, this makes sense, but isnt really what is wanted.
UPDATE `table` SET `id`=1 WHERE `name`='dad' LIMIT 1; #works as expected
ALTER TABLE `table` AUTO_INCREMENT=2; #`id` for 'mom' is changed to 2, AUTO_INCREMENT is set to 3

If you want to use the auto_increment feature you'll have to do without zeros. (yes, there _are_ cases where you really really want a zero for the first one)

The inability to insert zero into an auto-increment field causes a problem: Suppose you have a table where you have, in fact, put in a zero value in that field (using update or whatever), and your application needs the zero in that field in order to work as expected. Suppose further that you need to copy the database to another server, and you use mysqldump | mysql to do the copy. Your new table doesn't have the zero where you needed it.

For those that are looking to "reset" the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following.

DROP the field you are auto_incrementing.
ALTER the table to ADD the field again with the same attributes.

You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1.

(Keep in mind that DROPping that column will remove all existing data, so if you have exterior resources that rely on that data, or the numbers that are already there, you may break the link. Also, as with any major structure change, it's a good idea to backup your table BEFORE you make the change.)

Another interesting feature I found while playing with auto_increments is that you can assign any number when you add a row, and the increment will continue from there.

Lets say you had 1,2,3,4 as the values of your 4 records in the autoinc column. When you insert another row, you can put 17 (for example) as the value of the autoinc field. The next row you insert will have a value of 18.

Now, lets say you add another row and put the value at 16. The next value will be 19, since 17 and 18 already exist.

If you try to add a row with a autoinc value the same as an existing autoinc value, an error will occur (Duplicate Value), so be careful with this.

In order to reset the auto_increment, in a situation where some of the most recently added rows were deleted, use:

ALTER TABLE theTableInQuestion AUTO_INCREMENT=1234

and future insertions will be numbered from 1234 again (unless you still had rows numbered greater than 1234, and then the future insertions will start from the greatest number + 1 ).

I wish we could use ORDER BY in update statements

I like using this method to reset my auto_increment ids (a la 2, 5, 7, 8... to 1 2 3 4) using

SET @i=0;
UPDATE table SET column=@i:=@i+1

Downside to this I see is where it's stored out-of-order (which is where ORDER BY would come in handy), and in a table in which the primary key is more than one column, in which case I use something like the following pseudocode:

for value in (SELECT keyone FROM table GROUP BY keyone)
SET @i=0;
UPDATE table SET id_column=@i:=@i+1 WHERE keyone = '$value';
done

About the error on getting a high number after importing data from MySQL Dump, verify if any number of your ID is a negative number. If you have IDUSER=-1, for example, you will have the next auto inc IDUSER be a number about 2000000000, because auto increment is unsigned.

Add your own comment.