Cry MySQL How to...
Get or Reset Auto-Increment
(last insert id)
An field which has been defined as AUTO_INCREMENT, allows new ever-increasing integer values to be assigned automatically each time a new record is inserted. This is often useful for generating primary keys.
The normal behaviour is for the AUTO_INCREMENT value to start at 1 (for the first record inserted into the table), 2 for the next and so on.
Getting the AUTO_INCREMENT value
If you need to get the value recently used after having inserted a record use the SQL:
be sure to use the same database connection, and to do this immediately after the insert. It returns the first automatically generated value that was used by the most recent insert. This means that if your insert inserted say 3 rows then the value returned will be the value used for the first of those.
Alternatively if you want to get the next value that will be used then use the following query:
just be sure to change 'MyTable' with the name of the table that contains the AUTO_INCREMENT field.
Setting the AUTO_INCREMENT value
When a table is first created the next AUTO_INCREMENT value defaults to 1. However you can reset that at any time using:
Alter Table MyTable AUTO_INCREMENT=someValue
Just be sure to replace 'MyTable' with the name of your MySQL database table and 'someValue' with the next AUTO_INCREMENT value to set.
Some points to be aware of if you use this:
- If you set a value which is equal to or lower than the highest value
in the table then the highest current value + 1 is used instead.
This means that if the table MyTable had the values 1, 2 and 3 and I did:
Alter Table MyTable AUTO_INCREMENT=1
then the AUTO_INCREMENT value will actually be set to 4, because 4 is one more than the highest value in my table.
- InnoDB will reset the AUTO_INCREMENT value after each restart of the database. Mostly this is not a problem (see point above), but if you are relying on specific values then it may be significant.
These notes have been tested against MySQL 5.1.30 and may apply to other versions as well.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.