从触发器生成但未传递的插入ID字段

Question

在MySQL中，我有一个触发器：

BEGIN
   IF (EXISTS(SELECT * FROM devices WHERE device_id = NEW.device_id)) THEN
    SET NEW.id = NULL;
   ELSE
INSERT INTO objects (object_type) VALUES ('3');
SET NEW.id = LAST_INSERT_ID();
   END IF;
END

当该触发器(从objects表)获得新的id时，它会将该id插入到设备表的id列中。

当我提到它(例如PHP中的mysql_insert_id(); )时，它是空的。

如何将触发器(LAST_INSERT_ID();)中的插入id作为mysql_insert_id();返回给PHP中的函数？

Answer 1

就我个人而言，我使用存储过程。

以下是PDO的基本示例：

用于创建存储过程的代码

CREATE DEFINER=`user`@`localhost` PROCEDURE `InsertUser`(IN `Input_username` INT, OUT `Out_ID` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

   INSERT INTO users(
        username)
    VALUES (
    Input_username);

    SET Out_ID = LAST_INSERT_ID();
    SELECT Out_ID;
END

和PHP代码

  $insert = "CALL InsertUser(:Input_username,
                             @Out_ID)";
  $bdd = new PDO('mysql:host=localhost;dbname=db-name', 'user', 'password');

  $stmt = $bdd->prepare($insert);     
  $stmt->bindParam(':Input_username', rand(), PDO::PARAM_STR); // to create random name

  $stmt->execute();
  $tabResultat = $stmt->fetch();
  $id_user = $tabResultat['Out_ID'];
  var_dump($id_user);

我希望我能帮上忙。:)

Answer 2

此行为为by design

如果存储过程执行的语句更改了LAST_INSERT_ID()的值，则该过程调用后的语句会看到更改后的值。

对于更改该值的存储函数和触发器，该值将在函数或触发器结束时恢复，因此后面的语句将看不到更改的值。

• 解决方法1：Stored Procedures

不幸的是，这在您的表和objects之间引入了不一致的风险，因为插入仍然可能发生在这个过程之外(这个问题可以通过对表的复杂的访问限制来解决)

• 解决方法2:

将值保存在用户变量中：

CREATE TRIGGER
....
BEGIN
    INSERT INTO objects (object_type) VALUES ('3');
    SET NEW.id = LAST_INSERT_ID();
    SET @myLastInsertID = LAST_INSERT_ID();
END //

INSERT INTO your_table... -- trigger the above
SELECT @myLastInsertID; -- here is your value

• 解决方法3:

只需从object获取值;)

INSERT INTO your_table... -- trigger the above
SELECT MAX(autoinc_column) FROM objects; -- here is your value!

变通方法2和3应该封装在事务中，以确保在此过程中没有人会干扰@myLastInsertID或object。