CODEWITHSUNDEEP
mysql
Ashik Basheer
Ashik Basheer

Reputation: 1601

get next auto increment value from an empty table

I've got a table named 'messages'

I flooded it with approx 150 tables I'm not sure about the count. The 'id' field is 'primary' and 'auto_increment'.

by mistake i deleted all the records in the table (NOTE: I did not truncate the table so the count would not be reset) and I lost the count of the number of records.

Is there a possibility that I can get the next count of 'id' field? This is causing problems for me while trying to insert a new message using ajax message.

Upvotes: 0

Views: 1434

Answers (2)

juergen d
juergen d

Reputation: 204746

If you did not truncate the table then you can use

select auto_increment 
from information_schema.TABLES 
where TABLE_NAME ='messages' and TABLE_SCHEMA='database_name'

to get the next auto-increment value.

SQLFiddle demo

Upvotes: 1

randak
randak

Reputation: 2001

You could try a query like this to get the next ID:

SELECT id+1 AS next_id FROM your_table_name_here ORDER BY id DESC LIMIT 1;

You should probably not be inserting the ID into the table if it has auto-increment, however. You can give "NULL" as the ID value and it will fill in automatically.

Upvotes: 1

Related Questions