在使用SQL选择，更新或插入后，获取主键是否受影响？ [英] Get primarys keys affected after select, update or insert only using SQL?

问题描述

如何获得主键（假设知道他的名字通过查看显示键）从一个插入？
如何获取受更新影响的行的主键？ （如前面的情况，独立于密钥名称）。
如何获取从选择查询返回的主键（在查询中，即使键不是被调查字段之一）。

How to get the primary key (assuming know his name by looking show keys) resulting from an insert into? How to get the primary keys of rows affected by an update? (as in the previous case, independent of the key name). How to get the primary keys returned from a select query (in the query even if the key is not one of the fields surveyed).

我需要SQLs命令我运行后插入，更新和选择在我的应用程序中获取这样的信息，可以吗？
我的数据库是MySQL。

I need to SQLs commands I run after the inserts, updates and selects in my application to obtain such information, it is possible? My database is MySQL.

我只需要sqls，因为我在许多应用程序（java和php）中创建缓存查询aplicate的逻辑，该逻辑独立于语言。

I need only sqls because i am making a logic of cache queries to aplicate in many applications (java and php) and i wish that the logic be independent of language.

例如：

从人中选择姓名

我需要在这个人返回pk之后执行查询。

i need that a query executed after this return the pk of these people

推荐答案

SELECT LAST_INSERT_ID();

并且认真地，把主键从插入mysql到谷歌得到一个Stack Overflow答案作为第一个结果。

And seriously, putting "primary key from insert mysql" into Google gets you a Stack Overflow answer as the first result.

编辑：根据评论进行更多讨论。

more discussion based on comments.

如果您想查看哪些行通过更新，只需做一个SELECT和UPDATE语句具有相同的WHERE子句和JOIN标准，例如：

If you want to see what rows are affected by an update, just do a SELECT with the same WHERE clause and JOIN criteria as the UPDATE statement, e.g.:

UPDATE foo SET a = 5 WHERE b > 10;

SELECT id FROM foo WHERE b > 10;

如果要插入到没有自动递增主键的表中， t需要做什么特别的。您已经知道新的主键是什么，因为您自己在INSERT语句中设置它。如果你想要能够处理来自将跟踪PK更改的代码之外的INSERT语句的代码，那么你需要解析INSERT语句，或者让调用代码提供关于主键的信息。

If you are INSERTing into a table that does not have an auto-increment primary key, you don't need to do anything special. You already know what the new primary key is, because you set it yourself in the INSERT statement. If you want code that can handle INSERT statements coming from outside of the code that will be tracking PK changes, then you'll either need to parse the INSERT statement, or have the calling code provide information about the primary key.

这篇关于在使用SQL选择，更新或插入后，获取主键是否受影响？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！