My Monkey Do

A Log of Coding Solutions

MySQL – Get Next Auto Increment Value

by

Problem:

When adding an item to my mysql table, I needed to get the ID for the next inserted row.  The ID is just an auto increment column in the table.  At first I had:

select max(table_id) + 1 as next_table_id from tablename

I quickly realized how wrong that was.  When inserting ids 1, 2, 3, 4 and then deleting ID 4, my “next ID” query would return 4 instead of 5.  The MAX ID for a table varies depending on what is currently in the table and is not a reliable source for the next inserted ID.

 

Solution:

The query to use is:

select auto_increment
from information_schema.TABLES
where TABLE_NAME='tablename'
and TABLE_SCHEMA='basename'

 

An alternative solution is to use

SHOW TABLE STATUS WHERE name='tablename'

I have seen some comments that “SHOW TABLE STATUS” is not transaction safe.  Although I cannot find that in the MySQL documents.  Also if the table is partitioned, the first query should be used as documented here.