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

问题描述

假设我在我的一张表中执行 MySQL INSERT 并且该表的列 item_id 设置为 autoincrement 和主键.

Let's 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()函数:http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_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屋！