MySQL 特定表中最后插入的 id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12125385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Last inserted id from specific table
提问by ElSajko
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.
为什么这个查询有时会返回 table1 以外的另一个表的最后插入的 id？我在 Node.js（db-mysql 插件）中调用它，我只能做查询。
回答by Michael Madsen
LAST_INSERT_ID()can only tell you the IDof the most recently auto-generated IDfor 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 INSERTquery on that connection, it gets overwritten. If you want the generated IDwhen you insert to some table, you mustrun SELECT LAST_INSERT_ID()immediately after doing that (or use some API function which does this for you).
LAST_INSERT_ID()只能告诉您ID最近ID为整个数据库连接自动生成的，而不是为每个单独的表，这也是为什么查询应该只读取SELECT LAST_INSERT_ID()- 不指定表。
一旦您INSERT对该连接发出另一个查询，它就会被覆盖。如果您希望在ID插入某个表时生成，则必须SELECT LAST_INSERT_ID()在执行该操作后立即运行（或使用一些为您执行此操作的 API 函数）。
If you want the newest IDcurrently in an arbitrary table, you have to do a SELECT MAX(id)on that table, where idis the name of your IDcolumn. 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 INSERTbetween your own INSERTand your selection of the ID.
如果您想要ID任意表中当前的最新数据，则必须SELECT MAX(id)对该表执行 a ，其中id是您的ID列名。但是，这不一定是最近生成的ID，以防该行已被删除，也不一定是从您的连接生成的，以防另一个连接设法INSERT在您自己INSERT的ID.
(For the record, your query actuallyreturns N rows containing the most recently generated ID on that database connection, where N is the number of rows in table1.)
（对于记录，您的查询实际上返回 N 行，其中包含该数据库连接上最近生成的 ID，其中 N 是 中的行数table1。）
回答by Hitesh
SELECT id FROM tableName ORDER BY id DESC LIMIT 1
SELECT id FROM tableName ORDER BY id DESC LIMIT 1
回答by Whisher
Take a look at doc (may be is too late but just for reference) https://github.com/felixge/node-mysql/#getting-the-id-of-an-inserted-row
看一下文档（可能为时已晚但仅供参考） https://github.com/felixge/node-mysql/#getting-the-id-of-an-inserted-row
回答by Saranga Jayaruwan
Try this. This is working
尝试这个。这是工作
select (auto_increment-1) as lastId
from information_schema.tables
where table_name = 'tableName'
and table_schema = 'dbName'
回答by recurse
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:
我通常选择自动递增的 ID 字段，按字段降序排序并将结果限制为 1。例如，在 wordpress 数据库中，我可以通过执行以下操作获取 wp_options 表的最后一个 ID：
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.
编辑- 锁定表以避免更新可能导致返回错误 ID 的表可能是有意义的。
LOCK TABLES wp_options READ;
SELECT option_id FROM wp_options ORDER BY option_id DESC LIMIT 1;
回答by Bart Mensfort
Most easy way: select max(id) from table_name;
最简单的方法：从 table_name 中选择 max(id)；
回答by Wesos de Queso
I only use auto_incrementin MySQL or identity(1,1)in SQL Server if I know I'll never care about the generated id.
如果我知道我永远不会关心生成的 ID，我只会auto_increment在 MySQL 或identity(1,1)SQL Server 中使用。
select last_insert_id()is the easy way out, but dangerous.
select last_insert_id()是容易的出路，但危险。
A way to handle correlative ids is to store them in a util table, something like:
处理相关 ID 的一种方法是将它们存储在一个 util 表中，例如：
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
你也可以创建一个存储过程来生成并返回X表的下一个id
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.
这允许您在插入记录之前保留 ID。有时您希望在完成插入之前在表单中显示下一个 id，并有助于将其与其他调用隔离。
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');
