在PHP中的mySQL Server上执行存储过程时，如何获取lastInsertID

Question

方法1适用于标准准备的查询(我相信这就是它的名称(，但我无法让方法2或3返回LastInsertID-类似但不同，不确定两者的优点。

在这个阶段，我的主要工作是在使用存储过程时获取LastInsertID。

MySQL表

CREATE TABLE `forms` (
`FORM_ID` bigint(20) NOT NULL,
`OUID` bigint(20) NOT NULL,
`OID` bigint(20) NOT NULL,
`UPDATED_DATE` timestamp NOT NULL DEFAULT current_timestamp(),
`FORM_JSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`FORM_JSON`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL存储过程-用于方法2&3

DROP PROCEDURE `FORM_SAVEAS`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `FORM_SAVEAS`
(IN `V_OUID` INT, IN `V_OID` INT, IN `V_FORM_JSON` JSON) 
NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER INSERT INTO `forms` 
(OUID ,OID ,FORM_JSON ) 
VALUEs (V_OUID ,V_OID ,V_FORM_JSON)

方法1(Prepared语句(This Works-返回LastInsertID ok(-但我想使用存储过程

<?php  
$V_OUID      = 1;
$V_OID       = 2;
$V_FORM_JSON = '{"var1":"data 1","var2":"Data two","var3":"DATA III"}';
include('database_connection.php');
$query = "INSERT INTO forms (OUID       , OID    , FORM_JSON) 
VALUES ($V_OUID    , $V_OID , '" . $V_FORM_JSON . "')";
echo "The Query : $query";
try{
$DB_CON -> exec($query);
$form_id = $DB_CON->lastInsertId();
echo "New Record created Successfully ID is: " . $form_id;
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
echo $query;
}

方法2(这插入数据，但不返回lastInsertId

<?php  
$V_OUID      = 1;
$V_OID       = 2;
$V_FORM_JSON = '{"var1":"data 1","var2":"Data two","var3":"DATA III"}';
include('database_connection.php');
$query = "CALL FORM_SAVEAS($V_OUID    , $V_OID , '" . $V_FORM_JSON . "')";
echo "The Query : $query";
try{
$DB_CON -> exec($query);
$form_id = $DB_CON->lastInsertId();
echo "New Record created Successfully ID is: " . $form_id;
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
echo $query;
}

?>

方法3(这也插入数据，但不返回lastInsertId

<?php  
$V_OUID      = 1;
$V_OID       = 2;
$V_FORM_JSON = '{"var1":"data 1","var2":"Data two","var3":"DATA III"}';
include('database_connection.php');
$statement = $DB_CON->prepare('CALL FORM_SAVEAS(:V_OUID,:V_OID,:V_FORM_JSON)');
$statement->bindParam(':V_OUID', $V_OUID, PDO::PARAM_STR);
$statement->bindParam(':V_OID', $V_OID, PDO::PARAM_STR);
$statement->bindParam(':V_FORM_JSON', $V_FORM_JSON, PDO::PARAM_STR);
$statement->execute();
$form_id = $DB_CON->lastInsertID();
echo "New Record created Successfully ID is: " . $form_id;
?>

在方法2&3哪个是最好的，还有更好的方法吗？

Answer 1

这是一个解决方案，但不是一个完美的答案

我想用PDOlastInsertID()

相反，我给存储过程一个OUT参数，它从mySQL发回LAST_INSERT_ID()。。。存储过程现在是这样的。

CREATE DEFINER=`root`@`localhost` PROCEDURE `FORM_SAVEAS`
(IN `V_OUID` INT, IN `V_OID` INT, IN `V_FORM_JSON` JSON, OUT `V_FORM_ID` INT(11)) 
NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 
BEGIN 
INSERT INTO `forms` (OUID ,OID ,FORM_JSON ) 
VALUEs (V_OUID ,V_OID ,V_FORM_JSON);
set V_FORM_ID := last_insert_id(); 
END

PHP变成了。。。

$statement = $DB_CON->prepare('CALL FORM_SAVEAS(:V_OUID,:V_OID,:V_FORM_JSON,@V_FORM_ID);');
$statement->bindParam(':V_OUID', $V_OUID, PDO::PARAM_STR);
$statement->bindParam(':V_OID', $V_OID, PDO::PARAM_STR);
$statement->bindParam(':V_FORM_JSON', $V_FORM_JSON, PDO::PARAM_STR);
$statement->execute();
$statement->closeCursor();
// execute the second query to get result from the OUT parameter
$row = $DB_CON->query("SELECT @V_FORM_ID AS V_FORM_ID")->fetch(PDO::FETCH_ASSOC);
$V_FORM_ID = $row['V_FORM_ID'];
echo "New Record created Successfully ID is: " . $V_FORM_ID;

注意V_FORM_ID的所有添加如果PDOlastInsertID已经起作用，则不需要。我仍然很高兴知道这是PDO错误还是我做错了什么。

Answer 2

整个Web上有很多关于这个主题的令人困惑的信息。简单地说，在您的存储过程中，在您完成插入查询(插入到我们将称为"YourTable"的表中(后，用分号结束它，然后在该表上进行选择，如下所示：

选择LAST_INSERT_ID((作为您选择的变量来自YourTable；

然后返回该ID，就像任何选择一样。因此，在您的PHP中，您只需执行

$R=$stmt->fetch((；$LastInsert=$R[WhateverVariableYouChose]；