CODEWITHSUNDEEP
mysqlauto-increment
simonTifo
simonTifo

Reputation: 307

knowing next value from auto increment field mysql java

I want to know the next value of auto increment field I wanted to test this :

select max(contactid) from contact

and I add 1 but I realized that it can give me an error for exemple if I insert one record and I delete it so if I insert after the field will increase by two

How can I achieve that?

Upvotes: 3

Views: 4769

Answers (3)

Archer
Archer

Reputation: 5147

You should use LAST_INSERT_ID like this:

SELECT LAST_INSERT_ID()

It will return the last value of AUTO_INCREMENT ID field.

More details here: http://goo.gl/RkmR5

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

This will give you the next id value that will be inserted:

SELECT LAST_INSERT_ID() + 1;

Upvotes: -1

jeremycole
jeremycole

Reputation: 2761

There are multiple solutions to this problem:

1. (Preferable) Stop trying to predict auto-increment values

This is the more typical case, and basically is using auto-increment as designed. This assumes that you don't actually need the auto-increment value before you insert. What you can do is:

DROP TABLE IF EXISTS t;
CREATE TABLE t (id INT UNSIGNED NOT NULL auto_increment, x INT NOT NULL, PRIMARY KEY(id));
INSERT INTO t (x) VALUES (100);
SELECT LAST_INSERT_ID();

The call to SELECT LAST_INSERT_ID() will return the ID that was just generated for your INSERT.

2. Set up an ID generation table specifically to generate IDs

You can create a table with just an auto-increment column, like so:

DROP TABLE IF EXISTS id_generator;
CREATE TABLE id_generator (id INT UNSIGNED NOT NULL auto_increment, PRIMARY KEY(id));

You can then generate a new, unique ID with:

INSERT INTO id_generator (id) VALUES (NULL);
SELECT LAST_INSERT_ID();

And use that ID to insert into the table you're actually working with. As long as all generated IDs come from this ID generation table, there will be no conflicts. However there is a cost to generating these IDs, and auto-increment is not very efficient at it.

3. Use an external ID generation scheme

This is more or less similar to solution 2, but doesn't use MySQL at all for the ID generation. You can use something like a UUID/GUID scheme which generates a string, or you could use something like Snowflake to generate integer IDs.

Upvotes: 4

Related Questions