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

Elsewhere , ,

23 Responses to MySQL: Get next AUTO_INCREMENT value from/for table

  1. wiki says:

    MAN! THANKS FOR THIS!!!
    i am searching this from years!!!!!!!

  2. And for the record, if you want to change the auto_increment, issue the statement:

    alter table tablename auto_increment = newvalue

  3. David says:

    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 🙂

  4. Malinda says:

    thanks lot , i searched hundred of sites

  5. rakesh says:

    excellent 🙂
    i was doing +1 method 🙂
    but this really rocks
    super cool

  6. septianw says:

    nice!!!!
    I should know this method.
    let me bookmark this, and repost to my blog with backlink of course.

  7. marion says:

    Thanks!

  8. fred says:

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

  9. Pedro Vidal says:

    SHOW TABLE STATUS LIKE ‘tablename’

  10. Phillip says:

    Thanks, I WILL NOT FORGET IT (AGAIN)!!!!!!!!

  11. isi duk says:

    Hello thks for the script.

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

  12. Alberto Cherubini says:

    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!

    • TrasherDK says:

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

  13. Sergio Avila says:

    excellent, thank you …

  14. adi says:

    thanks it’s work for me

  15. Ivan Dompè says:

    Hi,

    That’s great! I got such headache to find the solution.. Thanks a lot.

  16. Jay Sudo says:

    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;

    • jo Melnik says:

      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

  17. jo Melnik says:

    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

  18. Wolfgang says:

    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 Reply

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.