Answers to common questions about AUTO_INCREMENT. Learn about gaps in sequences, resetting values, and behavior in different storage engines.
Use the LAST_INSERT_ID() function:
is connection specific, so there is no problem from race conditions.
You don't. Insert, then find out what you did with .
Run a query, ALTER TABLE yourTable AUTO_INCREMENT = x; . The next insert will contain x or MAX(autoField) + 1, whichever is higher.
As an alternative, run INSERT INTO yourTable (autoField) VALUES (x); . The next insert will contain x+1 or MAX(autoField) + 1, whichever is higher.
Issuing will delete all the rows in the table, and will reset the auto_increment value to 0 in most cases.
Typically, you don't want to. Gaps are hardly ever a problem; if your application can't handle gaps in the sequence, you probably should rethink your application.
Yes, if you use the .
This isn't possible. It's only available after insert.
You can't, not even with an AFTER INSERT trigger. Insert, then go back and update using LAST_INSERT_ID(). Those two statements could be wrapped into one stored procedure if you wish.
However, you can mimic this behavior with a BEFORE INSERT trigger and a second table to store the sequence position:
The same sequence table can maintain separate sequences for multiple tables (or separate sequences for different fields in the same table) by adding extra rows.
No, it only has to be indexed. It doesn't even have to be unique.
See .
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers wrap over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.
The initial version of this article was copied, with permission, from on 2012-10-05.
This page is licensed: CC BY-SA / Gnu FDL
SELECT LAST_INSERT_ID();AUTO_INCREMENTCREATE TABLE sequence (table_name VARCHAR(255), position INT UNSIGNED);
INSERT INTO sequence VALUES ('testTable', 0);
CREATE TABLE testTable (firstAuto INT UNSIGNED, secondAuto INT UNSIGNED);
DELIMITER //
CREATE TRIGGER testTable_BI BEFORE INSERT ON testTable FOR EACH ROW BEGIN
UPDATE sequence SET position = LAST_INSERT_ID(position + 1) WHERE table_name = 'testTable';
SET NEW.firstAuto = LAST_INSERT_ID();
SET NEW.secondAuto = LAST_INSERT_ID();
END//
DELIMITER ;
INSERT INTO testTable VALUES (NULL, NULL), (NULL, NULL);
SELECT * FROM testTable;
+-----------+------------+
| firstAuto | secondAuto |
+-----------+------------+
| 1 | 1 |
| 2 | 2 |
+-----------+------------+CREATE TABLE autoinc_test (
h INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
m INT UNSIGNED
) AUTO_INCREMENT = 100;
INSERT INTO autoinc_test ( m ) VALUES ( 1 );
SELECT * FROM autoinc_test;
+-----+------+
| h | m |
+-----+------+
| 100 | 1 |
+-----+------+