Skip navigation links
3.7 Queries from the Twin Project »
Section Navigation      [Toggle]

3.6.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'),('ostrich');

SELECT * FROM animals;

Which returns: 

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

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.

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 allows 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(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 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:


User Comments

Posted by Jim Martin on October 1 2002 7:57am[Delete] [Edit]

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)

Posted by Guy Martin on June 19 2003 3:50pm[Delete] [Edit]

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.)

Posted by [name withheld] on June 22 2003 4:19pm[Delete] [Edit]

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 ).

Posted by [name withheld] on October 23 2003 8:41pm[Delete] [Edit]

The manual should probably make *better* mention of the fact that the order in which primary keys are specified determines the semantics by which a new value is selected. (Saying "... is calculated as MAX(auto_increment_column)+1) WHERE prefix=given-prefix." is unclear given that this is the first mention of the word "prefix" in the document.

For example,

create table location
(
id bigint not null auto_increment, -- "serial" per 4.1
longitude int,
latitude int,
place int,
primary key(id, longitude, latitude, place)
);

insert into location (longitude, latitude, place)
values (0,0,0), (1,1,1), (2,2,2);

select * from foo;

+----+-----------+----------+-------+
| id | longitude | latitude | place |
+----+-----------+----------+-------+
|  1 |         0 |        0 |     0 |
|  2 |         1 |        1 |     1 |
|  3 |         2 |        2 |     2 |
+----+-----------+----------+-------+

drop table location;

create table location
(
id bigint not null auto_increment, -- "serial" per 4.1
longitude int,
latitude int,
place int,
primary key(longitude, latitude, place, id)
);

insert into location (longitude, latitude, place)
values (0,0,0), (1,1,1), (2,2,2), (0,0,0);

select * from location order by id;
+----+-----------+----------+-------+
| id | longitude | latitude | place |
+----+-----------+----------+-------+
|  1 |         0 |        0 |     0 |
|  1 |         1 |        1 |     1 |
|  1 |         2 |        2 |     2 |
|  2 |         0 |        0 |     0 |
+----+-----------+----------+-------+

Unless I've misunderstood (please correct me if I'm wrong), it's a nice feature but should be better documented than it is.

Thanks,

jrl.

Posted by [name withheld] on November 4 2004 2:36pm[Delete] [Edit]

It seems as though auto_increment doesnt work with load date infile commands. I'm using "REPLACE" syntax with this, and it just makes auto-inc treat each session of upload as one insert - so you get a load of records as 1, and then a load as 2 etc... very frustrating...

Posted by [name withheld] on November 8 2004 8:35pm[Delete] [Edit]

Drop table command will also reset autoincrement

Posted by Willem on January 24 2005 10:58am[Delete] [Edit]

A better way to delete all rows and reset the auto_increment, is truncate:
TRUNCATE TABLE tbl_name

Posted by Martine Petrod on March 25 2005 8:47pm[Delete] [Edit]

3.6.9 - AUTO_INCREMENT on a secondary column in a multiple-column index:

Specify 'type=myisam' for the table if you want to avoid getting:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Ref. script example from manual:
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)
);
Does not work if your default is not MyISAM, while the script below works fine:

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)
) type=myisam;

Posted by Gary Affonso on March 29 2005 5:46pm[Delete] [Edit]

Note MySQL's AUTO_INCREMENT behavior for ID=0 cases. (for MyISAM tables, at least).

* Autoincrement starts at 1 by default
* Autoincrement, if applied to a table with existing data (using the ALTER statement) will attempt to overwrite the record with ID=0 and assign that columns value the next AUTO_INCREMENT value.

Posted by gigi frost on May 26 2005 11:50am[Delete] [Edit]

Be careful using autoincrement keys; they're not actually real primary keys, but unique row identifiers, so you could insert n times the same row.

You can make real mess with that!

Posted by Norman Santos on October 7 2005 5:13am[Delete] [Edit]

Not sure if this is mentioned anywhere in the doc's. It seems if you ALTER a table that has a Auto Increment column it will remove the Auto Increment flag from that column. In the case of a primary key column MySql will start throwing a 1062 error on the second insert after the ALTER. The first one goes in fine if the default value has not been used in that column already.

Posted by Joonas Kekoni on January 9 2006 10:43am[Delete] [Edit]

drop table location;

create table location
(
id serial,
longitude int,
latitude int,
place int,
primary key(longitude, latitude, place, id)
);

insert into location (longitude, latitude, place)
values (0,0,0), (1,1,1), (2,2,2), (0,0,0);

select * from location order by id;

+----+-----------+----------+-------+
| id | longitude | latitude | place |
+----+-----------+----------+-------+
|  1 |         0 |        0 |     0 |
|  2 |         1 |        1 |     1 |
|  3 |         2 |        2 |     2 |
|  4 |         0 |        0 |     0 |
+----+-----------+----------+-------+
4 rows in set (0.00 sec)

This seems to be working now the column is serial.

Posted by nav on February 7 2006 7:56am[Delete] [Edit]

reset auto_increment using....

alter table "table_name" auto_increment=1
resets auto_increment to 1 + max(auto_increment)

Posted by chaoss on September 9 2006 4:43pm[Delete] [Edit]

Cody Brunson's solution isn't very good, becouse it leads to 'id' will be absolutely unique, but example from manual says about using the AUTO_INCREMENT column as a part of a multiple-column index!

Example result:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+
('id' is unique inside each 'grp')

Cody Brunson's solution result:
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  2 | dog     |
| mammal |  3 | cat     |
| mammal |  4 | whale   |
| bird   |  5 | penguin |
| bird   |  6 | ostrich |
+--------+----+---------+
(id is absolutely unique)

So, if you want to get a multiple-column index with AUTO_INCREMENT field, you must specify table type as myisam or bdb (see Martine Petrod's post).

Posted by kernel panic on March 24 2008 5:25am[Delete] [Edit]

Another way to get the next Auto_increment value is using the information_schema:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';

Posted by Gabe Holmes on July 12 2009 9:41pm[Delete] [Edit]

If you're using a phpmyadmin, go to the table in question and then Operations->Table Options->Auto-Increment
Set the auto-increment to whatever you please.

Remember to check any foreign keys before doing anything serious, brush your teeth every day and wear sunscreen at the beach.

Posted by Lewis Graham on August 26 2009 5:54am[Delete] [Edit]

InnoDB resets the next auto_increment value to the highest value in the table + 1 after a server restart.
This means that if you delete the highest value(s) in the table, then restart you can get the same values for auto_increment again.
See: http://bugs.mysql.com/bug.php?id=727

Add your own comment.