Find next value of AUTO_INCREMENT column in MySQL

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.

neither. I want to receive a value. But these answers print a description. Not a retrievable value — Xperiaz X, Aug 12 '13 at 11:32
http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/ — Mihai, Aug 12 '13 at 13:15
USE THIS CODE: `SELECT (IFNULL(max(id),0) + 1 ) as id from table`. IFNULL will be helpful if there is no record in the table — Eugine Joseph, Sep 28 '13 at 08:05

Flash Thunder · Accepted Answer · 2013-08-12T13:21:56.407

9

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

edited Aug 12 '13 at 13:21

answered Aug 12 '13 at 11:18

Flash Thunder

11,672
8
47
91

Yes it is giving an output. How do I retrieve that value from the status displayed? select auto_increment from(SHOW TABLE STATUS WHERE `Name` = 'table_name') does not work – Xperiaz X Aug 12 '13 at 11:26
But in what language? – Flash Thunder Aug 12 '13 at 11:27
java using JDBC. I want to receive only the value as the result in a ResultSet. – Xperiaz X Aug 12 '13 at 11:29
Use it as edited above. – Flash Thunder Aug 12 '13 at 11:34
I received an error message, java.sql.SQLException: Column 'Auto_increment' not found. – Xperiaz X Aug 12 '13 at 11:40
oh sorry wrong query, code fixed... don't forget to change `table_name` to your table name. – Flash Thunder Aug 12 '13 at 13:22
Yes. This is simpler than schema search. Thanks a lot – Xperiaz X Aug 12 '13 at 16:03

score 1 · Answer 2 · answered Aug 12 '13 at 12:05

1

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();

answered Aug 12 '13 at 12:05

Mihai

26,325
7
66
81

score 0 · Answer 3 · answered Aug 12 '13 at 11:25

0

There is no way to guarantee what value you are going to get before inserting the row. This is mostly because you will have to lock the entire table to guarantee no other thread will do an insert with "your" next value.

You can reserve a value by starting a transaction, inserting a row, getting the value and then doing a rollback. Then you can safely use that value.

It will be much simpler to just insert the row, so maybe I'm not understanding the purpose of what you are doing.

answered Aug 12 '13 at 11:25

Vatev

7,493
1
32
39

I want to perform insert query into another table, check if it is valid, and only then insert into this table. – Xperiaz X Aug 12 '13 at 11:28
The best way to do that would be a transaction. If you are using MyISAM the best I can think of is to insert the row, get the autoincrement value, , if it fails delete the row. – Vatev Aug 12 '13 at 12:02

Find next value of AUTO_INCREMENT column in MySQL

3 Answers3