How to know the next value of an automatically incremented primary key if all rows were deleted from a table?

There is a table which had records but these records were deleted. There is a primary key which is i开发者_高级运维ncremented automatically on the database table.

Is there a way to know , before inserting a new row in the table , the next value of this primary key ? If it is not possible is there a way to get the immediately the value of the generated primary key after inserting a row ?

If you're just sitting at a MySQL prompt, type:

show create table my_table;

At the bottom you'll see something like:

) ENGINE=MyISAM AUTO_INCREMENT=876159 DEFAULT CHARSET=latin1 | 

The AUTO_INCREMENT is what the next id would be.

One more variant -

SELECT `AUTO_INCREMENT` FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '<table_schema>' AND TABLE_NAME = '<table_name>';

You could insert a row and then look at last_insert_id:

insert into ....;
select last_insert_id();

That would give you the auto_increment value that was used for the INSERT.

You can also try looking at the show table status like 'your_table_name' output, there will be an Auto_increment value that should tell you the next one that will be used.