Finding the next available id in MySQL

by

Update 2014-12-05

I am not recommending this approach due to reasons laid out in Simon’s (accepted) answer as well as Diego’s comment. Please use query below at your own risk.

Original answer

The shortest one I found on MySQL developer site:

SELECT Auto_increment
FROM information_schema.tables
WHERE table_name="the_table_you_want"

Mind you if you have few databases with same tables, you should specify database name as well, like so:

SELECT Auto_increment
FROM information_schema.tables
WHERE table_name="the_table_you_want"
      AND table_schema="the_database_you_want";

More Related Contents:

  1. You can’t specify target table for update in FROM clause
  2. Generating a range of numbers in MySQL
  3. How to get next/previous record in MySQL?
  4. How do I make a row generator in MySQL?
  5. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  6. How can I merge two MySQL tables?
  7. MySQL number of items within “in clause”
  8. Error 1046 No database Selected, how to resolve?
  9. What’s the difference between VARCHAR and CHAR?
  10. Alter a MySQL column to be AUTO_INCREMENT
  11. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  12. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  13. is there a group_concat function in ms-access?
  14. single fixed table with multiple columns vs flexible abstract tables
  15. MySQL remove duplicates from big database quick
  16. mysql, iterate through column names
  17. How to delete from select in MySQL?
  18. How to delete Duplicates in MySQL table
  19. How to ORDER BY a SUM() in MySQL?
  20. mySQL Returning the top 5 of each category
  21. Selecting Random Rows in MySQL
  22. Best database field type for a URL
  23. Use A Union Or A Join – What Is Faster [closed]
  24. MySQL – how to front pad zip code with “0”?
  25. Find the number of columns in a table
  26. Mysql, reshape data from long / tall to wide
  27. MySQL: Compare differences between two tables
  28. Mysql strip time component from datetime
  29. How do I start auto increment from a specific point?
  30. How do MySQL views work?

Leave a Comment