How to know what would be the next auto increment id in mysql?

Question

Suppose I have a table like this:

----Id-------Data---
|   1   |   Data1   |
--------------------
|   2   |   Data2   |
--------------------
|   3   |   Data3   |
--------------------

If I run this query

SELECT MAX(id) FROM table

it will show the result 3 and if i increment it with 1 generally this will be the next auto incremented id.

If i delete the record where id is 3 and after that when i run this query again mean

SELECT MAX(id) FROM table

it will show 2, and if i increment it with 1 then it will be 3. Now if i insert a new record the auto increment id will be 4.

Now my question is how know what would be the next exact auto increment id?

score 2 · Accepted Answer · answered Dec 26 '15 at 11:27

2

To know the next auto incremented value you have to use information_schema.TABLES table.

Try this:

SELECT T.AUTO_INCREMENT 
FROM information_schema.TABLES T 
WHERE T.TABLE_SCHEMA = 'YourDBName' 
  AND T.TABLE_NAME = 'YourTableName';

answered Dec 26 '15 at 11:27

Saharsh Shah

1

@AshisBiswas You're most welcome... – Saharsh Shah Dec 26 '15 at 11:29
1

@AshisBiswas: Yes, but unless you can guarantee that there are no other connections to the database, this value may change before you can use it. This is a bad way of doing things, and if you explain why you think you need to do this then I am sure we can help you with a better way – Borodin Dec 26 '15 at 11:33
Why would you ever need to do this? Answer: you wouldn't. – Strawberry Dec 26 '15 at 11:59
@Borodin I want to use this for [this purpose](http://stackoverflow.com/questions/34470082/how-to-generate-randome-string-from-row-idpk-by-using-base-convert) – Ashis Biswas Dec 26 '15 at 13:20
@AshisBiswas: Okay, well that won't generate a number that is remotely random. In fact, most of the time it will just add one to the row ID. And it is using the *last* row ID and not the *next* one, as you have asked here – Borodin Dec 26 '15 at 13:32
1

Yes last row id. But if i delete some row the auto increment id would not be changed and it will cause some problem. – Ashis Biswas Dec 26 '15 at 15:46
Run this before your query: ANALYZE TABLE `TABLENAME`; – Jul 30 '22 at 16:18

score 0 · Answer 2 · answered Dec 26 '15 at 11:30

0

use LAST_INSERTED_ID as SELECT LAST_INSERT_ID();

answered Dec 26 '15 at 11:30

xrcwrn

Thanks for your ans. But i think this is not perfect ans. – Ashis Biswas Dec 26 '15 at 11:31
It will work only after insert of record. after deletion of record it won't work – Saharsh Shah Dec 26 '15 at 11:31
While it's not a correct answer to the question, it's probably a correct way to solve the problem behind. – Paul Spiegel Dec 26 '15 at 22:25

2 Answers2