从 MySQL 插入查询中获取新记录主键 ID?

问题描述

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

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-插入-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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！