mysql交易-混合插入&选择获得last_insert_id? [英] mysql transaction - mixing insert & select to attain last_insert_id?

问题描述

我正在执行事务(使用PDO)，但是我需要获取事务中第一个元素的插入ID，例如:

I'm performing a transaction (using PDO), however I need to grab the insert id of the first element in the transaction, for example:

BEGIN
INSERT INTO user (field1,field2) values (value1,value2)
INSERT INTO user_option (user_id,field2) values (LAST_INSERT_ID(),value2);
COMMIT;

然后做pdo的东西:

[...]
$pdo->execute();
$foo = $pdo->lastInsertId(); // This needs to be the id from the FIRST insert

是否有一种方法可以从事务中的第一个元素获取最后的插入ID?也许使用如下所示的内容:

Is there a way to get the last insert id from the first element in a transaction? Perhaps using something like the following:

BEGIN
INSERT INTO user (field1,field2) values (value1,value2)
SELECT id AS user_id FROM user WHERE id=LAST_INSERT_ID()
INSERT INTO user_option (user_id,field2) values (LAST_INSERT_ID(),value2);
COMMIT;

$pdo->execute();
$fooArray = $pdo->fetchAll();
$lastId = $fooArray[0]['user_id'];

我可以和^一起吃午餐吗?有更好的方法吗?

Am I completely out to lunch with ^ ? Is there a better way to do this?

编辑1

基于建议，我已经更新了查询以使用变量...但是，我不知道如何使用PDO检索变量值.使用$ stmt-> fetchAll()只会返回一个空数组；

Based on suggestion, i've updated the query to use variables... however, i don't know how to retrieve the variable values using PDO. Using $stmt->fetchAll() just returns an empty array;

BEGIN
DECLARE User_ID int
DECLARE Option_ID int
INSERT INTO user (field1,field2) values (value1,value2);
set User_ID = select LAST_INSERT_ID();
INSERT INTO user_option (user_id,field2) values (LAST_INSERT_ID(),value2);
set Option_ID = select LAST_INSERT_ID();
select User_ID, Option_ID
COMMIT;

推荐答案

您可以这样做，将值放入变量中，然后选择它即可.

You can do it this way, put the value into variable then just select it

BEGIN
DECLARE User_ID int
DECLARE Option_ID int
INSERT INTO user (field1,field2) values (value1,value2);
set User_ID = select LAST_INSERT_ID();
INSERT INTO user_option (user_id,field2) values (LAST_INSERT_ID(),value2);
set Option_ID = select LAST_INSERT_ID();
select User_ID, Option_ID
COMMIT;

这篇关于mysql交易-混合插入&选择获得last_insert_id?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！