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

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.

假设我在其中一个表中插入mysql，表中有item_id列，它被设置为自动递增和主键。

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

如何让查询在同一个查询中输出新生成的主键item_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?

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

7 个解决方案

#1

197

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

您需要使用LAST_INSERT_ID()函数:http://dev.myl.com/doc/refman/5.0/en/informfunctions.html #function_last-insert-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.

生成的ID在服务器中按每个连接维护。这意味着函数返回给给定客户端的值是为最近影响该客户端的AUTO_INCREMENT列的语句生成的第一个AUTO_INCREMENT值。

So this is unaffected by other queries that might be running on the server from other users.

所以这不会受到其他用户在服务器上运行的其他查询的影响。

#2

BEWARE !! of "LAST_INSERT_ID()" if trying to return this primary key value within PHP.

小心! !如果试图在PHP中返回主键值，则返回“LAST_INSERT_ID()”。

I know this thread is not tagged php, but for anybody who came across this answer looking to return a MySQL insert id from a PHP scripted insert using standard mysql_query calls - it wont work and is not obvious without capturing SQL errors.

我知道这个线程不是带标签的php，但是对于任何遇到这个问题的人来说，他们希望通过使用标准的mysql_query调用从php脚本中返回一个MySQL插入id——它不会起作用，而且在没有捕获SQL错误的情况下是不明显的。

The newer mysqli supports multiple queries - which LAST_INSERT_ID() actually is a second query from the original.

更新的mysqli支持多个查询——LAST_INSERT_ID()实际上是来自原始查询的第二个查询。

IMO a separate SELECT to identify the last primary key is safer than the optional mysql_insert_id() function returning the AUTO_INCREMENT ID generated from the previous INSERT operation.

在我看来，单独的选择来标识最后一个主键比可选的mysql_insert_id()函数更安全，该函数返回从先前的插入操作生成的AUTO_INCREMENT ID。

#3

Here what you are looking for !!!

给你要找的东西!!

select LAST_INSERT_ID()

This is the best alternative of SCOPE_IDENTITY() function being used in SQL Server.

这是SQL Server中使用的SCOPE_IDENTITY()函数的最佳选择。

You also need to keep in mind that this will only work if Last_INSERT_ID() is fired following by your Insert query. That is the query returns the id inserted in the schema. You can not get specific table's last inserted id.

您还需要记住，只有在Insert查询之后触发Last_INSERT_ID()时，这才会有效。该查询返回插入到模式中的id。无法获得特定表的最后插入id。

For more details please go through the link The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?

要了解更多细节，请在mySQL中查看与SQLServer函数SCOPE_IDENTITY()等效的链接。

#4

From the LAST_INSERT_ID() documentation:

从LAST_INSERT_ID()文档:

The ID that was generated is maintained in the server on a per-connection basis

生成的ID在服务器中按每个连接维护

That is if you have two separate requests to the script simultaneously they won't affect each others' LAST_INSERT_ID() (unless you're using a persistent connection perhaps).

也就是说，如果您同时对脚本有两个单独的请求，那么它们不会影响彼此的LAST_INSERT_ID()(除非您可能正在使用一个持久连接)。

#5

If you need the value before insert a row:

如果在插入一行之前需要该值:

CREATE FUNCTION `getAutoincrementalNextVal`(`TableName` VARCHAR(50))
    RETURNS BIGINT
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    DECLARE Value BIGINT;

    SELECT
        AUTO_INCREMENT INTO Value
    FROM
        information_schema.tables
    WHERE
        table_name = TableName AND
        table_schema = DATABASE();

    RETURN Value;

END

You can use this in a insert:

你可以在插入语中使用:

INSERT INTO
    document (Code, Title, Body)
VALUES (                
    sha1( concat (convert ( now() , char), ' ',   getAutoincrementalNextval ('document') ) ),
    'Title',
    'Body'
);

#6

You will receive these parameters on your query result:

您将在查询结果中收到这些参数:

    "fieldCount": 0,
    "affectedRows": 1,
    "insertId": 66,
    "serverStatus": 2,
    "warningCount": 1,
    "message": "",
    "protocol41": true,
    "changedRows": 0

The insertId is exactly what you need.

insertId正是你所需要的。

(NodeJS-mySql)

(NodeJS-mySql)

#7

-7

If LAST_INSERT_ID() is acting a little strange or not working like it is for me then use this simple statement...

--- INSERT STATEMENT GOES HERE --- SELECT id FROM table ORDER BY id DESC LIMIT 1;

This should return the greatest ID in the table, thus the last ID inserted

如果LAST_INSERT_ID()的行为有点奇怪，或者不像我这样工作，那么使用这个简单的语句……---插入语句到这里---根据id DESC LIMIT 1从表顺序中选择id;这将返回表中最大的ID，从而插入最后一个ID。

#1

197