SQL - 插入一行并返回主键

SQL - Inserting a row and returning primary key

提问by Samuel Moriarty

I have a little witty problem. Say, I inserted a row with some data in a table where a primary key is present. How would one "SELECT" the primary key of the row one just inserted?

我有一个小问题。比如说，我在一个有主键的表中插入了一行数据。如何“选择”刚刚插入的第一行的主键？

I should have been more specific and mentioned that I'm currently using SQLite.

我应该更具体地提到我目前正在使用 SQLite。

回答by Michael Fredrickson

For MS SQL Server:

对于 MS SQL 服务器：

SCOPE_IDENTITY()will return you the last generated identity value within your current scope:

SCOPE_IDENTITY()将返回您当前范围内最后生成的标识值：

SELECT SCOPE_IDENTITY() AS NewID

回答by marc_s

For SQL Server 2005 and up, and regardless of what type your primary key is, you could always use the OUTPUTclauseto return the values inserted:

对于 SQL Server 2005 及更高版本，无论您的主键是什么类型，您始终可以使用该OUTPUT子句返回插入的值：

INSERT INTO dbo.YourTable(col1, col2, ...., colN)
OUTPUT Inserted.PrimaryKey
VALUES(val1, val2, ....., valN)

回答by Dagg Nabbit

For MySQL, use LAST_INSERT_ID()

对于 MySQL，使用 LAST_INSERT_ID()

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

You should also be able to start a transaction, insert the row, and select the row using some field that has a unique value that you just inserted, like a timestamp or guid. This should work in pretty much any RDBMS that supports transactions, as long as you have a good unique field to select the row with.

您还应该能够启动事务、插入行，并使用一些具有您刚刚插入的唯一值的字段（例如时间戳或 guid）选择行。这应该适用于几乎所有支持事务的 RDBMS，只要您有一个很好的唯一字段来选择行。

回答by Farhan

SQL Server:

SQL 服务器：

You can use @@IDENTITY. After an insert statement, you can run:

您可以使用@@IDENTITY. 在插入语句之后，您可以运行：

select @@identity

This will give you the primary key of the record you just inserted. If you are planning to use it later, I suggest saving it:

这将为您提供刚刚插入的记录的主键。如果你打算以后使用它，我建议保存它：

set @MyIdentity = @@identity

If you are using this in a stored procedure and want to access it back in your application, make sure to have nocount off.

如果您在存储过程中使用它并希望在您的应用程序中访问它，请确保没有计数。

回答by Ian

If you need to retrieve the new index in MS SQL when there are triggers on the table then you have to use a little workaround. A simple OUTPUT will not work. You have to do something like this (in VB.NET):

如果您需要在表上有触发器时在 MS SQL 中检索新索引，那么您必须使用一些解决方法。一个简单的 OUTPUT 是行不通的。你必须做这样的事情（在 VB.NET 中）：

DECLARE @newKeyTbl TABLE (newKey INT);
INSERT INTO myDbName(myFieldName) OUTPUT INSERTED.myKeyName INTO @newKeyTbl VALUES('myValue'); " & _
SELECT newKey FROM @newKeyTbl;"

If using .NET, then the return value from this query can be directly cast to an integer (you have to call "ExecuteScalar" on the .NET SqlCommand to get the return).

如果使用 .NET，则此查询的返回值可以直接转换为整数（您必须在 .NET SqlCommand 上调用“ExecuteScalar”才能获得返回值）。

回答by Esau

For SQLite:

对于 SQLite：

SELECT [Column_1],  [Column_2],...  [Column_n]
FROM [YourTable]
WHERE rowid = (SELECT last_insert_rowid())

whereas:

而：

• Column_1, Column_2,... Column_n: are the primary key of YourTable.

• Column_1, Column_2,... Column_n: 是YourTable的主键。

If you'd created YourTablewith primary key replaced rowid(i.e. one column pk defined as INTEGER PRIMARY KEY) you just use:

如果您使用主键替换rowid（即定义为INTEGER PRIMARY KEY 的一列 pk ）创建了YourTable，则只需使用：

SELECT last_insert_rowid()

Which is a common case.
Finally, this wont work for WITHOUT_ROWID tables.

这是一个常见的情况。
最后，这不适用于 WITHOUT_ROWID 表。

Please Check:

请检查：

https://www.sqlite.org/lang_corefunc.html#last_insert_rowid

https://www.sqlite.org/lang_corefunc.html#last_insert_rowid

回答by z-index

For Postgresql:

对于 PostgreSQL：

SELECT CURRVAL(pg_get_serial_sequence('schema.table','id'))

Source: https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

来源：https: //stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

回答by dynabaul

select MAX(id_column) from table

That, in theory, should return you that last inserted id. If it's a busy database with many inserts going on it may not get the one you just did but another.

从理论上讲，这应该返回最后插入的 id。如果它是一个繁忙的数据库，有很多插入正在进行，它可能不会得到你刚刚做的那个，而是另一个。

Anyhow, an alternative to other methods.

无论如何，这是其他方法的替代方法。