mysql插入数据返回主键值_从mysql插入查询中获取新记录的主键ID？

最新推荐文章于 2025-06-20 10:27:20 发布

洗心岛

最新推荐文章于 2025-06-20 10:27:20 发布

阅读量513

点赞数

CC 4.0 BY-SA版权

文章标签： mysql插入数据返回主键值

本文链接：https://blog.csdn.net/weixin_28221501/article/details/111921159

本文介绍了一种在MySQL中高效获取最新插入记录的自增主键的方法。通过使用`LAST_INSERT_ID()`函数，可以在插入新记录后立即获取到该记录的主键值，避免了再次查询的需要，确保了操作的一致性和准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.

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

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

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

解决方案

Eg:

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

SELECT LAST_INSERT_ID();

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

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.

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.