Auto Increment FAQ
Contents
- 1 How do I get the last inserted auto_increment value?
- 2 What if someone else inserts before I select my id?
- 3 How do I get the next value to be inserted?
- 4 How do I change what number auto_increment starts with?
- 5 How do I renumber rows once I've deleted some in the middle?
- 6 Can I do group-wise auto_increment?
- 7 How do I get the auto_increment value in a BEFORE INSERT trigger?
- 8 How do I assign two fields the same auto_increment value in one query?
- 9 Does the auto_increment field have to be primary key?
How do I get the last inserted auto_increment value?
SELECT LAST_INSERT_ID();
What if someone else inserts before I select my id?
LAST_INSERT_ID() is connection specific, so there is no problem from race conditions.
How do I get the next value to be inserted?
You don't. Insert, then find out what you did with LAST_INSERT_ID().
How do I change what number auto_increment starts with?
ALTER TABLE yourTable AUTO_INCREMENT = x; -- Next insert will contain x or MAX(autoField) + 1, whichever is higher
or
INSERT INTO yourTable (autoField) VALUES (x); -- Next insert will contain x+1 or MAX(autoField) + 1, whichever is higher
How do I renumber rows once I've deleted some in the middle?
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.
Can I do group-wise auto_increment?
Yes, if you use the MyISAM engine. See the manual for an example.
How do I get the auto_increment value in a BEFORE INSERT trigger?
You don't. It's only available after insert.
How do I assign two fields the same auto_increment value in one query?
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.
Does the auto_increment field have to be primary key?
No, it only has to be indexed. It doesn't even have to be unique.
