mysql - 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.)