3.5.9 Использование атрибута AUTO_INCREMENT

Атрибут AUTO_INCREMENT может использоваться для генерации уникального идентификатора для новых строк:

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;

Что вернет:

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

В таблицах MyISAM и BDB можно определить AUTO_INCREMENT для вторичного столбца составного ключа. В этом случае значение, генерируемое для автоинкрементного столбца, вычисляется как MAX(auto_increment_column)+1) WHERE prefix=given-prefix. Столбец с атрибутом AUTO_INCREMENT удобно использовать, когда данные нужно помещать в упорядоченные группы.

CREATE TABLE animals (grp ENUM('fish','mammal','bird') NOT NULL,
             id MEDIUMINT NOT NULL AUTO_INCREMENT
             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;

Что вернет:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
+--------+----+---------+

Обратите внимание, что в этом случае значение AUTO_INCREMENT будет использоваться повторно, если в какой-либо группе удаляется строка, содержащая наибольшее значение AUTO_INCREMENT.

Последнее значение поля AUTO_INCREMENT, которое было создано автоматически, можно получить при помощи функции SQL LAST_INSERT_ID() или функции API mysql_insert_id().

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;

Hmmm... issuing "lock tables shop read" seems to
lock the tmp table too, so the subsequent "insert
into tmp select..." fails with "ERROR 1030: Got
error 13 from table handler". Trying:
"insert into tmp values (8,7.77);" results
in "ERROR 1099: Table 'tmp' was locked with a
READ lock and can't be updated" which seems to
confirm this. Unlocking the tables before
attempting the insert cures the problem: but
presumably there's a reason for locking the
tables in the first place... (spot the newbie :)

Spent some time banging around for this.

If your syntax demands a value be INSERTed, use
NULL or 0 to get an auto_increment to happen.

This was in the manual, but it was under CREATE
TABLE.

Steve

I finally figured out how to reset the
AUTO_INCREMENT counter:

ALTER TABLE tbl_name AUTO_INCREMENT
= 1;


This will reset the counter to the current
maximum value.

How do you reset the auto_increment after you
empty your table.. ??? If I delete all the
records in a table I want auto_increment to
reset.. how do I do that???

If mySQL doesnt optimize on the multiple-key
OR query:

SELECT * FROM pet WHERE sex = 'f' OR
species = 'cat';

but it says right here that "The AND handling
is, in comparison, now completely general
and works very well.", then why go through all
the complexity of the TEMPORARY TABLE
business? It seems obvious that if the OR is
not optimized, yet the AND is, why not, as a
general rule, just rewrite the OR condition
using DeMorgan's law to make it an AND
condition?:

SELECT * FROM pet WHERE NOT (sex <> 'f'
AND species <> 'cat');

This would also prevent the duplicate rows
you receive as consequences of the
TEMPORARY TABLE and UNION methods.

For the less bit-wise-inclined, just do:

SELECT year, month, COUNT(DISTINCT day)
AS days FROM t1 GROUP BY year, month;

When using auto_increment in a secondary column
of a multi-part key, it only seems to work if
higher-order column(s) are declared not null
(this will be enforced anyway for a primary
key). The auto_imcrement column itself need not
be not null. Using MySQL 4.0.1.

AUTO_INCREMENT query fails::

I found that you have to specify the column as
either 'primary key' or 'unique' for it to work.

I kept trying ALTER TABLE i MODIFY COLUMN j INT
NOT NULL AUTO_INCREMENT and it would give me
errors - so I finally found that ALTER TABLE i
MODIFY COLUMN j INT NOT NULL AUTO_INCREMENT
PRIMARY KEY works as well as ALTER TABLE i MODIFY
COLUMN j INT NOT NULL AUTO_INCREMENT UNIQUE

How do you reset the Auto_Increment of a table?
After a long search I found the answer to my
question and thought I would share. Here is the
SQL statement (ALTER TABLE tbl_name
AUTO_INCREMENT = 1)

Is it possible to seed the value of an
AUTO_INCREMENT field? I want one of my tables to
start at 100000, not 1.

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

Under some circumstances the auto_increment of a table will be reset when the table
becomes empty.
"delete from tbl_name where id=XXX" - auto_increment value will not be reset when
the table becomes empty.

"delete from tbl_name" - table will be empty and auto_increment value will be reset.

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)

The solution presented here, ALTER TABLE tbl_name
AUTO_INCREMENT = 1, only seems to work for me when
the table is empty. I have found other suggestions
in other forums, but have not found one that will
reset the auto_increment number on a populated
table. This would be very useful for making
columns such as session_ids wrap without having to
drop and recreate them, or without having to use
BIGINT to try and avoid running out of ids. Having
an upper limit to your auto_increment column could
create the possibility of DOS attacks in some
applications.

Can't you alter every record in the table? If the table is full, the database would have to do this anyway.

{min} = select MIN(P_KEY) from table;
update table set P_KEY = P_KEY - {min};

A way through resetting the auto_increment to 100 (for example) is to delete or recreate the table, and then insert the first record with an ID that has the value of 100. After that, when you allow MYSQL to assign the ID, it will continue the series at 101, 102, etc. The suggestion above to simply set the AUTO_INCREMENT to 100 doesn't work (as far as I can tell).

For what it's worth - this function doesn't work in phpMyAdmin. It removes the whitespace from between the table name and the AUTO_INCREMENT causing errors. I successfully reset my auto_increment from the command line.

to change the auto_increment index for a table to zero:

alter table <table name> auto_increment = 0;

Add your own comment.