Brian Cryer

 
  

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:

select LAST_INSERT_ID()

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:

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

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:

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

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