Last inserted id from a specific table

Question

SELECT LAST_INSERT_ID() as id FROM table1 

Why does this query sometimes return the last inserted identifier of a different table than table1? I call it in Node.js (db-mysql plugin) and I can make queries.

Answer 1

LAST_INSERT_ID() can tell you only the ID most recent automatically generated ID for all this database connection, but not for each individual table, therefore, the query should also read only SELECT LAST_INSERT_ID() - without specifying a table.
As soon as you run another INSERT request for this connection, it is overwritten. If you want to get the generated ID when pasting into any table, you should immediately run SELECT LAST_INSERT_ID() (or use some API function that does this for you).

If you want the newest ID to currently be in an arbitrary table, you should do SELECT MAX(id) for that table, where id is the name of your ID column. However, this is not necessarily the last generated ID if this row has been deleted, and not necessarily generated from your connection if another connection manages to perform an INSERT between your own INSERT and your choice of ID .

(For the record, your query actually returns N rows containing the last generated identifier for this 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

Take a look at the document (maybe too late, but just for reference) https://github.com/felixge/node-mysql/#getting-the-id-of-an-inserted-row

Answer 4

Usually I select a field with an automatic incremental identifier, order the fields in descending order and limit the results to 1. For example, in the Wordpress database I can get the last identifier of the wp_options table by doing:

 SELECT option_id FROM wp_options ORDER BY option_id DESC LIMIT 1; 

Hope this helps.

Change It might make sense to lock the table in order to avoid updates to the table, which could lead to incorrect ID return.

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

Answer 5

Try it. It works

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

Answer 6

The easiest way: select max (id) from table_name;

Answer 7

I use auto_increment in MySQL or identity(1,1) in SQLServer only if I know that I will never care about the generated identifier.

select last_insert_id() is an easy way, but dangerous.

The method for processing the corresponding identifiers is to save them in the utility table, for example:

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

You can also create a storage procedure to generate and return the next table X identifier

 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 him

 call next_correlative('SALES'); 

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

Here is a test script to mess 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');