blocks|key|2920077|text|对于MS+SQL+Server：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2920078|SCOPE_IDENTITY()将返回您当前作用域中最后生成的标识值：|offset|length|style|CODE|2920079|SELECT+SCOPE_IDENTITY()+AS+NewID|code-block|syntax|javascript|2920080|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms190315.aspx^0|0|0|G|0|G|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|H|3|I|5|J|7|11|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

For MS SQL Server:

<a href="http://msdn.microsoft.com/en-us/library/ms190315.aspx"><code>SCOPE_IDENTITY()</code></a> will return you the last generated identity value within your current scope:

<pre><code>SELECT SCOPE_IDENTITY() AS NewID
</code></pre>

blocks|key|2920093|text|对于SQL+Server2005及更高版本，无论您的主键是什么类型，您都可以使用OUTPUT+clause返回插入的值：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2920094|INSERT+INTO+dbo.YourTable(col1,+col2,+....,+colN)
OUTPUT+Inserted.PrimaryKey
VALUES(val1,+val2,+.....,+valN)|code-block|syntax|javascript|2920095|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms177564.aspx^0|14|6|14|D|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

For SQL Server 2005 and up, and regardless of what type your primary key is, you could always use the <a href="http://msdn.microsoft.com/en-us/library/ms177564.aspx"><code>OUTPUT</code> clause</a> to return the values inserted:

<pre><code>INSERT INTO dbo.YourTable(col1, col2, ...., colN)
OUTPUT Inserted.PrimaryKey
VALUES(val1, val2, ....., valN)
</code></pre>

blocks|key|245350|text|SQL+Server：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|245351|您可以使用@@IDENTITY。在insert语句之后，您可以运行：|offset|length|style|CODE|245352|select+@@identity|code-block|syntax|javascript|245353|这将为您提供刚刚插入的记录的主键。如果您打算以后使用它，我建议您保存它：|245354|set+@MyIdentity+=+@@identity|245355|如果要在存储过程中使用它，并希望在应用程序中重新访问它，请确保关闭nocount。|245356|entityMap^0|0|5|A|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

SQL Server:

You can use <code>@@IDENTITY</code>. After an insert statement, you can run:

<pre><code>select @@identity
</code></pre>

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:

<pre><code>set @MyIdentity = @@identity
</code></pre>

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

blocks|key|3134238|text|对于MySQL，使用LAST_INSERT_ID()|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3134239|http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html|offset|length|3134240|您还应该能够启动事务，插入行，并使用具有您刚才插入的唯一值的某个字段来选择行。这应该适用于几乎所有支持事务的RDBMS，只要您有一个很好的惟一字段来选择行。|3134241|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1P|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

For MySQL, use LAST_INSERT_ID()

<a href="http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html</a>

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.

blocks|key|248239|text|如果当表上有触发器时，您需要在MS+SQL中检索新索引，那么您必须使用一些解决方法。简单的输出将不起作用。您必须这样做(在VB.NET中)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|248240|DECLARE+@newKeyTbl+TABLE+(newKey+INT);
INSERT+INTO+myDbName(myFieldName)+OUTPUT+INSERTED.myKeyName+INTO+@newKeyTbl+VALUES('myValue');+"+&+_
SELECT+newKey+FROM+@newKeyTbl;"|code-block|syntax|javascript|248241|如果使用.NET，则此查询的返回值可以直接转换为整数(您必须在.NET+SqlCommand上调用"ExecuteScalar“才能获得返回)。|248242|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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):

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

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

blocks|key|3134308|text|对于Postgresql：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3134309|SELECT+CURRVAL(pg_get_serial_sequence('schema.table','id'))|code-block|syntax|javascript|3134310|来源：https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id|offset|length|3134311|entityMap|0|LINK|mutability|MUTABLE|url|http://https//stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id^0|0|0|3|2C|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

For Postgresql:

<pre><code>SELECT CURRVAL(pg_get_serial_sequence('schema.table','id'))
</code></pre>

Source: <a href="http://https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id" rel="nofollow noreferrer">https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id</a>

blocks|key|248264|text|对于SQLite：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|248265|SELECT+[Column_1],++[Column_2],...++[Column_n]
FROM+[YourTable]
WHERE+rowid+=+(SELECT+last_insert_rowid())|code-block|syntax|javascript|248266|，而：|offset|length|style|BOLD|248267|248268|Column_1，Column_2，...Column_n:是YourTable的主键。|unordered-list-item|248269|248270|如果您创建的YourTable用主键替换了rowid+(即定义为整数主键的一列pk+)，则只需使用：|248271|SELECT+last_insert_rowid()|248272|这是一种常见的情况。|248273|最后，这不适用于WITHOUT_ROWID表。|248274|请检查：|248275|https://www.sqlite.org/lang_corefunc.html#last_insert_rowid|248276|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|2|0|0|0|0|W|4|0|0|0|0|0|0|1N|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1C|8|@$I|1D|J|1E|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1I|8|@$I|1J|J|1K|K|L]]|9|@]|A|$]]|$1|T|3|U|5|D|7|1L|8|@]|9|@]|A|$E|F]]|$1|V|3|W|5|6|7|1M|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1N|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1O|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|1P|8|@]|9|@$I|1Q|J|1R|1|1S]]|A|$]]|$1|13|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]]|14|$15|$5|16|17|18|A|$19|12]]]]

For SQLite: 

<pre><code>SELECT [Column_1], [Column_2],... [Column_n]
FROM [YourTable]
WHERE rowid = (SELECT last_insert_rowid())
</code></pre>

whereas: 

<ul>
<li>Column_1, Column_2,... Column_n: are the primary key of YourTable.</li>
</ul>

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

<pre><code>SELECT last_insert_rowid()
</code></pre>

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

Please Check: 

<a href="https://www.sqlite.org/lang_corefunc.html#last_insert_rowid" rel="nofollow noreferrer">https://www.sqlite.org/lang_corefunc.html#last_insert_rowid</a>

blocks|key|2920359|text|对于PostgreSQL，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2920360|INSERT+INTO+tablename+(col1,+col2,+...)
VALUES+(val1,+val2,+...)
RETURNING+idcol;|code-block|syntax|javascript|2920361|2920362|可选的RETURNING子句使INSERT根据实际插入(或更新，如果使用了on+CONFLICT+DO+UPDATE子句，则为updated+)的每一行计算并返回值。这主要用于获取由默认值提供的值，例如序列号。但是，允许使用该表的列的任何表达式。|blockquote|2920363|2920364|2920365|https://www.postgresql.org/docs/current/sql-insert.html|offset|length|2920366|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|0|1J|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@$O|14|P|15|1|16]]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|N]]]]

For PostgreSQL,
<pre><code>INSERT INTO tablename (col1, col2, ...)
VALUES (val1, val2, ...)
RETURNING idcol;
</code></pre>
<blockquote>
The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed.
</blockquote>
<a href="https://www.postgresql.org/docs/current/sql-insert.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/current/sql-insert.html</a>

blocks|key|3134390|text|select+MAX(id_column)+from+table|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3134391|从理论上讲，它应该返回最后插入的id。如果它是一个繁忙的数据库，有许多插入正在进行，那么它可能不会得到您刚才做的那个，而是另一个。|unstyled|3134392|无论如何，这是其他方法的一个替代方法。|3134393|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>select MAX(id_column) from table
</code></pre>

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. 

Anyhow, an alternative to other methods.

I have inserted a row with some data in a table where a primary key is present. How would one &quot;SELECT&quot; the primary key of the row one just inserted?
<blockquote>
I should have been more specific and mentioned that I'm currently
using SQLite.
</blockquote>

SQL - Inserting a row and returning primary key

SQL Server

PostgreSQL

数据库

SQLite

MySQL

我在一个有主键的表中插入了一个包含一些数据的行。如何“选择”刚刚插入的行的主键？我应该说得更具体一些，并提到我目前使用的是SQLite。

问SQL -插入行并返回主键
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL -插入行并返回主键EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL -插入行并返回主键
EN