Find next value of AUTO_INCREMENT column in MySQL [duplicate]

Question

I am using MySQL. I want to retrieve the next value that the AUTO_INCREMENT column will take without entering a new record.

create table ABC(id int(10) NOT NULL AUTO_INCREMENT,name char(10));

In oracle I would have used sequencename.nextval(); But what to I use in MySQL?

Here is why I did not use

select max(id) from ABC;

Suppose I have an entry with id=2. Now column id will take the next value as 3. Before I create a record with id=3, If I delete the record with id=2. The answer for query I mentioned will be 2. But I want the actual value 3, which the auto_increment column will anyway take.

Answer 1

Query table status like this:

SHOW TABLE STATUS WHERE `Name` = 'table_name'

Now in result you will get a column named Auto_increment. This is the value You were asking for.

In JAVA:

conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
stmt = conn.createStatement();
rs = stmt.executeQuery("SHOW TABLE STATUS WHERE `Name` = 'table_name'");
rs.next();
String nextid = rs.getString("Auto_increment");

Full example here: http://www.avajava.com/tutorials/lessons/how-do-i-use-jdbc-to-query-a-mysql-database.html

Answer 2

If I understand correctly,you could use the number of rows as indicator:

SELECT TABLE_ROWS+1
FROM information_schema.tables 
WHERE table_name='tableName'
AND table_schema = DATABASE();