从mysql插入查询中获取新记录的主键ID? [英] Get the new record primary key ID from mysql insert query?

问题描述

好吧，所以可以说我在一个表中使用mysql INSERT，并且该表的列item_id设置为autoincrement和primary key.

Ok, so lets say I am doing a mysql INSERT into one of my tables and the table has the column item_id which is set to autoincrement and primary key.

如何获取查询以在同一查询中输出新生成的主键item_id的值?

How do I get the query to output the value of the newly generated primary key item_id in the same query?

当前，我正在运行第二个查询来检索ID，但是考虑到这可能会产生错误的结果，这似乎不是一种好习惯……

Currently I am running a second query to retrieve the id but this hardly seems like good practice considering this might produce the wrong result...

如果这不可能，那么确保我检索正确ID的最佳实践是什么?

If this is not possible then what is the best practice to ensure I retrieve the correct id?

推荐答案

您需要使用LAST_INSERT_ID()函数:例如:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

这将使您返回您插入的最后一行的PRIMARY KEY值:

This will get you back the PRIMARY KEY value of the last row that you inserted:

生成的ID会基于每个连接保留在服务器中.这意味着该函数返回给定客户端的值是针对影响该客户端 AUTO_INCREMENT列 的最新语句生成的第一个AUTO_INCREMENT值.

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

LAST_INSERT_ID()返回的值是针对每个用户的，不受其他用户在服务器 上运行的其他查询的影响.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

这篇关于从mysql插入查询中获取新记录的主键ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！