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