Last inserted id from specific table

Question

SELECT LAST_INSERT_ID() as id FROM table1

Why does this query sometimes return the last inserted id of another table other than table1? I call it in Node.js (db-mysql plugin) and I can only do queries.

Answer 1

LAST_INSERT_ID() can only tell you the ID of the most recently auto-generated ID for that entire database connection, not for each individual table, which is also why the query should only read SELECT LAST_INSERT_ID() - without specifying a table.
As soon as you fire off another INSERT query on that connection, it gets overwritten. If you want the generated ID when you insert to some table, you must run SELECT LAST_INSERT_ID() immediately after doing that (or use some API function which does this for you).

If you want the newest ID currently in an arbitrary table, you have to do a SELECT MAX(id) on that table, where id is the name of your ID column. However, this is not necessarily the most recently generated ID, in case that row has been deleted, nor is it necessarily one generated from your connection, in case another connection manages to perform an INSERT between your own INSERT and your selection of the ID.

(For the record, your query actually returns N rows containing the most recently generated ID on that database connection, where N is the number of rows in table1.)

Answer 2

SELECT id FROM tableName ORDER BY id DESC LIMIT 1

Answer 3

Try this. This is working

select (auto_increment-1) as lastId
from information_schema.tables
where table_name = 'tableName'
and table_schema = 'dbName'

Answer 4

I usually select the auto-incremented ID field, order by the field descending and limit results to 1. For example, in a wordpress database I can get the last ID of the wp_options table by doing:

SELECT option_id FROM wp_options ORDER BY option_id DESC LIMIT 1;

Hope that helps.

Edit - It may make sense to lock the table to avoid updates to the table which may result in an incorrect ID returned.

LOCK TABLES wp_options READ;
SELECT option_id FROM wp_options ORDER BY option_id DESC LIMIT 1;

Answer 5

Most easy way: select max(id) from table_name;

Answer 6

I only use auto_increment in MySQL or identity(1,1) in SQL Server if I know I'll never care about the generated id.

select last_insert_id() is the easy way out, but dangerous.

A way to handle correlative ids is to store them in a util table, something like:

create table correlatives(
    last_correlative_used int not null, 
    table_identifier varchar(5) not null unique
);

You can also create a stored procedure to generate and return the next id of X table

drop procedure if exists next_correlative;
DELIMITER //
create procedure next_correlative(
    in in_table_identifier varchar(5)
)
BEGIN
    declare next_correlative int default 1;

    select last_correlative_used+1 into next_correlative from correlatives where table_identifier = in_table_identifier;

    update correlatives set last_correlative_used = next_correlative where table_identifier = in_table_identifier;

    select next_correlative from dual;
END //
DELIMITER ;

To use it

call next_correlative('SALES');

This allows you to reserve ids before inserting a record. Sometimes you want to display the next id in a form before completing the insertion and helps to isolate it from other calls.

Here's a test script to mess around with:

create database testids;

use testids;

create table correlatives(
    last_correlative_used int not null, 
    table_identifier varchar(5) not null unique
);

insert into correlatives values(1, 'SALES');

drop procedure if exists next_correlative;
DELIMITER //
create procedure next_correlative(
    in in_table_identifier varchar(5)
)
BEGIN
    declare next_correlative int default 1;
    select last_correlative_used+1 into next_correlative  from correlatives where table_identifier = in_table_identifier;
    update correlatives set last_correlative_used = next_correlative where table_identifier = in_table_identifier;
    select next_correlative from dual;
END //
DELIMITER ;

call next_correlative('SALES');

Answer 7

If you want to use these workarounds:

• SELECT id FROM tableName ORDER BY id DESC LIMIT 1
• SELECT MAX(id) FROM tableName

It's recommended to use a where clause after inserting rows. Without this you are going to have inconsistency issues.

Answer 8

in my table inv_id is auto increment

for my purpose this is worked

select `inv_id` from `tbl_invoice`ORDER BY `inv_id` DESC LIMIT 1;