identify next increment value

jansky

to all guru

how can i get the value of next increment value for the primary ID (auto increment)
EG:
id
1
3
4
6
9
16
my next auto increment is 24 (considered that from id 17-23 was deleted)

how would i get the value 24??

Roger_Ramjet

You can't, not with ANY certainty.

mysql insert id will give you the value of the last autoinc that the last insert ON YOUR CONNECTION generated. But web is multi-user, so you cannot be certain that another user or process has not used the next one right after you.

Autoincs also burn up ids even when an insert fails. So querying for highest id is no guarantee that the next one in the series will by +1. If an insert was attempted and failed then it will be +2.

Weedpacket

This is one reason why other DBMSs that better support standard SQL employ an independent 'sequence' rather than a special autoincrement field as MySQL has it. In PostgreSQL, for example, you'd

create sequence foo

(You can actually have a lot more than that - the starting point, the size of the increment, its minimum and maximum values, and whether or not it wraps around at the ends.)

The sequence has a number of functions that can be used to query its state. currval('foo') is the current value of the "foo" sequence, nextval('foo') kicks the sequence one notch and returns the new value, and setval('foo', newvalue) also does the obvious thing.

Using it in a table definition is straightforward:

CREATE TABLE 'bar' (
  id integer DEFAULT nextval('foo'),
...
)

(PostgreSQL even has a bit of syntactic sugar to not only simplify this but make the field a primary key for indexing as well.)

Sequences run independently of the table - it doesn't matter what's in the table or not in the table, and it doesn't matter how many concurrent inserts are happening - they all see the same sequence so nextval() won't return the same value twice (unless the sequence is set to wrap around and it manages to wrap all the way around!)

Roger_Ramjet

I know that mysql is very simple in some respects, Weedpacket, but then it's always been a tool for a specific job to me.

When it comes to autoincs though, I don't see what the fuss is. I'm sure you agree that they are not DATA, have no meaning outside of the db, are only for internal db use and should not be used by, or even revealed to users.

For me, they exist for 2 reasons:
to facilitate table joins as integer indexes are much faster than other data types
to provide a globally unique key even when records are deleted and new ones inserted - which would overcome any other unique key. No chance of the wrong records from related tables being joined by accident after a delete and new insert.

Given that definition of their use and value, why does it matter what the next value will be, or whether they are in an unbrocken sequence, or whether they increment by 1 or 10 or any specific interval?

It is the mis-use of them that causes all the kerfuffle, and that should not be encouraged. 🆒