How to get next auto increased id value?

Question

I have a table with a field 'Text'. The 'id' field is automatically increased when data is inserted. I run this query:

INSERT into answer (Text) VALUES ('Test');
INSERT into answer (Text) VALUES ('Test');
INSERT into answer (Text) VALUES ('Test');
INSERT into answer (Text) VALUES ('Test');
Delete From answer;

I need get future id (when I will insert next text). Can you help me with query, that take me number = 5 (but not 1, because 'id' automatically increased)?

Why do you need that? (You can't really do anything with that information safely.) — Mat, Aug 11 '13 at 11:38
see here http://stackoverflow.com/questions/1685860/php-how-to-get-last-inserted-id-of-a-table — Garry, Aug 11 '13 at 11:38

score 4 · Accepted Answer · answered Aug 11 '13 at 11:39

You cannot know the next auto_increment value. Well, probably not the way you think of it. You can only reliably obtain the last one used (by using LAST_INSERT_ID()).

Some would probably tell you the other way: "this is easy, just take LAST_INSERT_ID()+1".

But this is a bad idea. Why? Because there will be concurrent access to your database. So you probably could guess the next ID in the table. But not the next ID that will be attributed in your transaction.

_{(in addition, depending your DB setup, auto_increment step might be greater than 1 -- see http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment)}

How to get next auto increased id value?

1 Answers1