how to get from database the last inset id before insert

Question

How to select the last insert id from database table

i need to get the last insert id from database

i try the last_insert_id() function but this doesn't work for me because i want to get this id before i do an insert query and when i get this id i do an insert query

i try to use max for the id but this have a problem that if i have a deleted id it will not be taken

example if i have id 10 deleted than the max id will be 9 but the auto increment will be 11

than i want to have the new id that will be added by the auto increment

example:i have an id 10 deleted i want to take than next id it will be 11

Is this to fill in holes between ID sequences? If so, just don't bother. — Jonnix, Sep 16 '16 at 07:59
Make sure you use transactions. Otherwise even if you manage to get the now_insert_id somehow it'll screw things up. — mroman, Sep 16 '16 at 10:02

score 5 · Accepted Answer · answered Sep 16 '16 at 10:00

5

You Can :

    SELECT AUTO_INCREMENT
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'database_name'
    AND   TABLE_NAME   = 'table_name';

And get your last id.

answered Sep 16 '16 at 10:00

ahammar

282
1
14

what i information_schema.tables what is table_schema ? – mohamad mohamad Sep 16 '16 at 10:38
INFORMATION_SCHEMA is a database come with mysql by default [check mysql doc](http://dev.mysql.com/doc/refman/5.7/en/information-schema.html), you will find the info that you want, why not execute it instead of setting a -1 – ahammar Sep 16 '16 at 10:47

score 1 · Answer 2 · edited May 23 '17 at 10:27

The autoincrement value is generated by the database itself, when the insertion is done ; which means you cannot get it before doing the actual insert query. insert some half-empty data get the autoincrement value that's been generated do your calculations, using that autoincrement value update the row to put the new / full data in place -- using the autoincrement generated earlier in the where clause of the update query, to identify which row is being updated.

As pseudo-code :
begin transaction
insert into your table (half empty values);
$id = get last autoincrement id
do calculations
update set data = full data where id = $id
commit transaction

or you may follow Using Auto-Increment value in MYSQL Before Insert Trigger?

`which means you cannot get it before doing the actual insert query` isn't correct.. See ahammar's answer... — Fipsi, Aug 10 '19 at 10:00

score 0 · Answer 3 · answered Sep 16 '16 at 08:02

0

You can not and should not get the last inserted id before insert.

And there is not a single reason to do so. Just insert your data, get the actual id, and use it anywhere else. As simple as that.

answered Sep 16 '16 at 08:02

Your Common Sense

156,878
40
214
345

You *always* can do that. – Your Common Sense Sep 16 '16 at 08:23

how to get from database the last inset id before insert

3 Answers3