MySQL 从mysql插入查询中获取新记录主键ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17112852/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the new record primary key ID from mysql insert query?
提问by Amy Neville
Ok, so lets say I am doing a mysql INSERTinto one of my tables and the table has the column item_idwhich is set to autoincrementand primary key.
好的，假设我正在INSERT我的一个表中执行 mysql ，并且该表的列item_id设置为autoincrementand primary key。
How do I get the query to output the value of the newly generated primary key item_idin 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 的最佳做法是什么？
回答by Duncan Lock
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.mysql.com/doc/refman/5.0/en/information-functions.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 KEYvalue of the last row that youinserted:
这将使您返回您插入PRIMARY KEY的最后一行的值：
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 the value returned by LAST_INSERT_ID()is per user and is unaffectedby other queries that might be running on the server from other users.
因此，通过返回的值LAST_INSERT_ID()是每用户是不会受到影响的，可能在服务器上运行的其他查询其他用户。
回答by Martin Sansone - MiOEE
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.
IMO 用于标识最后一个主键的单独 SELECT 比可选的 mysql_insert_id() 函数返回从前一个 INSERT 操作生成的 AUTO_INCREMENT ID 更安全。
回答by Explosion Pills
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()（除非您可能使用持久连接）。
回答by PVR
Here what you are looking for !!!
这里有你要找的！！！
select LAST_INSERT_ID()
This is the best alternative of SCOPE_IDENTITY()function being used in SQL Server.
这是SCOPE_IDENTITY()在SQL Server.
You also need to keep in mind that this will only work if Last_INSERT_ID()is fired following by your Insertquery.
That is the query returns the id inserted in the schema. You can not get specific table's last inserted id.
您还需要记住，这仅Last_INSERT_ID()在您的Insert查询之后触发时才有效。即查询返回插入到模式中的 id。您无法获取特定表的最后插入 ID。
For more details please go through the link The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
有关更多详细信息，请访问链接MySQL 中 SQLServer 函数 SCOPE_IDENTITY() 的等效项？
回答by Paulo A. Costa
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'
);
回答by MESepehr
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 insertIdis exactly what you need.
这insertId正是您所需要的。
(NodeJS-mySql)
(NodeJS-mySql)
回答by mojoken
If in python using pymysql, from the cursor you can use cursor.lastrowid
如果在 python 中使用 pymysql，从游标中你可以使用 cursor.lastrowid
回答by Arnav Singh
simply use "$last_id = mysqli_insert_id($conn);"
只需使用“$last_id = mysqli_insert_id($conn);”
回答by Olafnyr
If you are using PHP: On a PDOobject you can simple invoke the lastInsertIdmethod after your insert.
如果您使用的是 PHP：在PDO对象上，您可以在插入后简单地调用 lastInsertId方法。
Otherwise with a LAST_INSERT_IDyou can get the value like this: SELECT LAST_INSERT_ID();
否则，使用LAST_INSERT_ID可以获得如下值：SELECT LAST_INSERT_ID();
回答by Hamza Saeed
i used return $this->db->insert_id();for Codeigniter
我用于return $this->db->insert_id();Codeigniter
