MySQL, AUTO_INCREMENT, SHOW TABLE STATUS

Some useful notes about MySQL’s AUTO_INCREMENT value:
  • Available only for those tables where one column uses AUTO_INCREMENT. See a very clear example for a table that uses AUTO_INCREMENT in MySQL documentation here.
  • To find what the current AUTO_INCREMENT value of some table is, you should NOT rely on the rows count, as some threads in StackOverflow suggest. Instead, SHOW TABLE STATUS LIKE `yourtablename`; will reveal some info on your table. One of the columns will be “AUTO_INCREMENT”, with the value you’re looking for: The next time a row is inserted, the field with the AUTO_INCREMENT property will get this value. For example, this value is initially 1 when creating the table, and also after a TRUNCATE.
  • To get only the AUTO_INCREMENT value: SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
  • To compare with the number of rows (say, to verify that the AUTO_INCREMENT value equals to the number of rows plus 1), consider: SELECT TABLE_ROWS, AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
  • To modify the AUTO_INCREMENT value yourself (say, to the number 123), you need: ALTER TABLE `yourtablename` AUTO_INCREMENT = 123;
  • A hack: To “reset” the AUTO_INCREMENT value yourself to the lowest possible number, try: ALTER TABLE `yourtablename` AUTO_INCREMENT = 1;
  • Last notes about ‘SHOW TABLE STATUS’: Yes, it has a weird syntax. Also, don’t get startled if the TABLE_ROWS column shows a different number of rows than you actually have in that table (i.e. when calling SELECT COUNT(1) FROM `yourtablename`, for example).