检索插入行的索引 [英] Retrieving the index of an inserted row

问题描述

我正在尝试为我正在处理的项目保留数据库表，并使其规范化，但是我遇到了一个问题.我试图弄清楚如何在表中插入一行，然后找出auto_incremented id列的值设置为什么，以便可以将其他数据插入到另一个表中.我知道有诸如mysql_insert_id之类的功能，它们获取从先前的INSERT操作生成的ID".但是，如果我没记错的话，mysql_insert_id只是返回最后一个操作的ID.因此，如果站点的流量足够大，则不一定会返回您想要的查询的ID，因为在插入行和查找ID之间可能已经运行了另一个查询.对mysql_insert_id的这种理解正确吗?任何有关如何执行此操作的建议，将不胜感激.谢谢.

I'm trying to keep the database tables for a project I'm working on nice and normalized, but I've run into a problem. I'm trying to figure out how I can insert a row into a table and then find out what the value of the auto_incremented id column was set to so that I can insert additional data into another table. I know there are functions such as mysql_insert_id which "get the ID generated from the previous INSERT operation". However, if I'm not mistaken mysql_insert_id just returns the ID of the very last operation. So, if the site has enough traffic this wouldn't necessarily return the ID of the query you want since another query could have been run between when you inserted the row and look for the ID. Is this understanding of mysql_insert_id correct? Any suggestions on how to do this are greatly appreciated. Thanks.

推荐答案

LAST_INSERT_ID()具有会话范围.

它将返回当前会话中插入的标识值.

It will return the identity value inserted in the current session.

如果您没有在INSERT和LAST_INSERT_ID之间插入任何行，那么它将正常工作.

If you don't insert any rows between INSERT and LAST_INSERT_ID, then it will work all right.

请注意，尽管对于多个值插入，它将返回插入的第一行的标识，而不是最后一行的标识:

Note though that for multiple value inserts, it will return the identity of the first row inserted, not the last one:

INSERT
INTO    mytable (identity_column)
VALUES  (NULL)

SELECT  LAST_INSERT_ID()

--
1

INSERT
INTO    mytable (identity_column)
VALUES  (NULL), (NULL)

/* This inserts rows 2 and 3 */

SELECT  LAST_INSERT_ID()

--
2

/* But this returns 2, not 3 */

这篇关于检索插入行的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！