MySQL: Get next AUTO_INCREMENT value from/for table

Note to self: To get the next auto_increment value from a table run this query:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "databaseName"
AND TABLE_NAME = "tableName"

Don’t forget it (again).

Did this help you out? Like what you see?
Consider donating.

I don’t run ads on my blog nor do I do this for profit. A donation however would always put a smile on my face though. Thanks!

☕️ Buy me a Coffee ($3)

Published by Bramus!

Bramus is a frontend web developer from Belgium, working as a Chrome Developer Relations Engineer at Google. From the moment he discovered view-source at the age of 14 (way back in 1997), he fell in love with the web and has been tinkering with it ever since (more …)

Unless noted otherwise, the contents of this post are licensed under the Creative Commons Attribution 4.0 License and code samples are licensed under the MIT License

Join the Conversation

24 Comments

  1. Hahahahahahahaha!!! I really laughed hard when I read that! “Don’t forget it (again).” Hehehe.

    But seriously, thanks a million, i’ve been using count() + 1 for years!! shame on me 🙂

  2. Handy stuff, thanks for sharing :), and google for showing me your page. Some very cool projects you have here.

  3. Hello thks for the script.

    is it more effeciente than :
    SELECT MAX(id) FROM table
    where id is the auto-incremented primary key
    rgs

    1. This does work, but seems to be drastically slower than “select max(id) from table”. On my table (innodb) with 2M records, the method shown here takes 1.4 seconds. The plain old select max takes 0.0001 seconds.

  4. the problem with using count or max +1 is that if you occasionally delete, you get out of line.
    that is why the method proposed here is superior.
    worrying though if it is slow. i do not see why it should be!

    1. Seems like a smart hack. Not sure it’s usable in a multi-user environment.
      Better to stick with ex. mysql_insert_id()

  5. Not all database users may have access to the mysql database. This would effectively work as an alternative:

    SELECT `aa_field`+1 FROM `table` ORDER BY `aa_field` DESC;

    1. your solution only works if no record are deleted in your table

      if you delete the last record then, auto_increment != max(id)+1 but auto_increment==max(id)+2

  6. id +1 or count or max method are wrong, it looks like it works until you delete the last record, that ways only work if you never delete record !!!!

    if you delete the last record then, auto_increment != max(id)+1 but auto_increment==max(id)+2

  7. Running this in phpmyadmin needs just 0.0004 seconds if you include the databasename and the tablename correctly.
    In case you have various databases with equal tablenames and you omit the databasename it really needs a lot of time, like 2.4 seconds for searching in 5 databases (f.e. all wordpress installations and all containing the table wp_posts).

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.