• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

My Monkey Do

A Log of Coding Solutions

  • Home
  • Web Hosts
  • Tools
  • About

MySQL – Get Next Auto Increment Value

May 24, 2011 by Webhead

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.

 

 

 

Filed Under: Random Thoughts

Primary Sidebar

Topics

apache apple block editor chrome cms css debug eCommerce embed firebug firefox git gmail goDaddy google hosting htaccess html html 5 IE crap image iPad iPhone javascript jquery linux localization mac os x ms sql mysql open source optimize php php 5.3 responsive rest api seo svg tinymce woocommerce wordpress wpengine xss yii youtube




Categories

  • Coding
  • Off the Shelf
  • Plugins
  • Random Thoughts
  • Server Stuff
  • Tools