获取最后插入的id的标准方法是什么？

Question

如何获取最后插入的id的SQL标准？如果有的话。

mysql: LAST_INSERT_ID()
postgresql: ... RETURNING f_id
mssql: SCOPE_IDENTITY()
... 更多例子请见这里 ...

我的意思是，所有数据库都对此有不同的实现，难道没有针对这样一个常见任务的标准吗？

- arthurprs

---

如果ID列是整数且自动递增，则最后一行将为max(ID)。这是你的意思吗？ - william

@william 天真，但有道理 :P - arthurprs

如果在你获取 MAX(id) 之前发生了另一个 INSERT，会发生什么？ - mu is too short

@mu：我认为插入的行将被检索出来。插入到表中的值（.）然后选择*从表中选择id=max(id)。您插入的行将被检索出来。这是我的假设。 - william

"MAX(id)"是错误的。选择最后插入值的ID的标准方法是使用具有所有已插入值的条件查询。因此，如果您执行INSERT INTO table (a,b,c) VALUES (A,B,C)，则可以通过SELECT id FROM table WHERE a = A AND b = B AND c = C获得ID。当然，如果存在重复值（例如记录A、B、C不是唯一的），则不能依赖此方法。 - jmz

Answer 1

请参见这个答案：在SQL中检索插入的行ID

简而言之，除了使用MAX(ID)（但这不是一种可保证的结果，并且存在许多很多的缺陷，例如：

• 其它插入操作可能出现在最后一个插入和max查询之间；
• 不能用于高事务表（max会发出读锁定，特定于rdbms的方法不会从任何表读取）。

与identity/autonumber/auto_increment/sequences相关的ANSI标准首次出现在SQL：2003中，正在等待所有主要RDBMS的实现。它最可能类似于Oracle/PostgreSQL序列。

SQL：2003标准对SQL：1999（也称为SQL3）的所有部分进行了轻微修改，并正式引入了一些新功能，例如：

- 序列生成器，允许标准序列。

SQL：2003中的另一个变化是OUTPUT USING CLAUSE，但是关于它的信息很少。Sybase和SQL Server对其采取了不同的处理方式，因此目前还不清楚它将如何发展。 SQL Server将其实现为

INSERT INTO TBL(..)
OUTPUT inserted.identity_col
INTO @sometablevar
VALUES(..)

- RichardTheKiwi

Answer 2

Oracle和PostgreSQL支持RETURNING子句，并使用称为序列的对象提供自动顺序编号。SQL Server的下一个版本Denali将支持序列，但我还没有看到是否支持RETURNING子句的消息。获取当前序列值的另一种方法是：

Oracle: sequence_name.CURRVAL 
PostgreSQL: CURRVAL('sequence_name')

DB2支持序列和RETURNING INTO子句。

不建议使用SELECT MAX(auto_increment_column) ...，因为它不可靠。在Oracle中，读者（SELECT）不会被写入者（INSERT/UPDATE）阻塞，因此无法保证该值的正确性。

结论

我不知道ANSI SQL:2003标准包括使用序列进行自动编号，但目前还没有一致的方式实现检索该值。

- OMG Ponies

Answer 3

这更多是对一些评论的澄清，而不是一个真正的新回答，但它在这里更合适。只要客户端处于可串行化事务中，select max(id)就可以正常工作。在pgsql中，你可以自己证明这一点。打开两个psql会话，并依次在默认的读取提交和可串行化模式下运行以下代码：

p1: create table test (id serial);
p1 and p2: begin;
p1 and p2: set transaction isolation level serializable;
p1: insert into test values (DEFAULT);
p2: insert into test values (DEFAULT);
p1: select max(id) from test;
 1
p2: select max(id) from test;
 2
p2: commit;
p1: select max(id) from test;
 2

然而，使用读取提交（read committed）：

p1: create table test (id serial);
p1 and p2: begin;
p1 and p2: set transaction isolation level read committed;
p1: insert into test values (DEFAULT);
p2: insert into test values (DEFAULT);
p1: select max(id) from test;
 1
p2: select max(id) from test;
 2
p2: commit;
p1: select max(id) from test;
 1

在性能方面，可序列化事务可能会产生负面影响或导致事务失败，必须回滚并重试等。

返回或currval()是更好的选择。然而，如果执行它的事务是可序列化的，则说max(id)不能信任是错误的。

- Scott Marlowe

---

你的意思是“只要每个客户端都在可序列化事务中”吗？ - Mike Sherrill 'Cat Recall'

唯一需要处于可序列化事务中的客户端是执行max(id)操作的客户端。其他执行其他操作的客户端可以处于任何模式。 - Scott Marlowe

Answer 4

一种可能适用于所有数据库的技术，在序列不是简单增加数字的情况下（例如存在具有较高ID的现有行，因此无法使用MAX），是：

1. 确定下一个ID将是什么，例如使用 nextval 函数或类似函数。
2. 使用该ID插入行。
3. 将该ID用于您余下的需求。

- Edmund

---

我不确定SQL Server是否支持nextval或类似的东西，但我们可以假装它只是一种小型的服务器产品，所以不算数。我想知道的是你的方法实际上应该如何工作。这里有一个场景：插入了一行数据，不久之后又被删除了。此后没有插入更多的行，但即将添加一行。此时MAX(ID)为某个N。新行将使用ID = N + 2插入，因为N + 1是最近插入和删除的行的ID。使用nextval能正确解决这种情况吗？ - Andriy M