我正在执行一个事务(使用PDO),但是我需要获取事务中第一个元素的插入id,例如:
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 ?也许可以使用如下内容:
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'];
我和^完全out to lunch吗?有更好的方法吗?
EDIT 1
根据建议,我已经更新了查询使用变量…但是,我不知道如何使用PDO检索变量值。使用$stmt->fetchAll()只返回一个空数组;
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;
你可以这样做,将值放入变量中然后选中它
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;