插入ID字段(由触发器生成，但未传递) [英] Insert ID field generated from trigger, but not passed along

问题描述

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

In MySQL, I have a trigger:

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

此触发器从对象表获取新的ID时，会将ID插入到设备表的ID列中.

When this trigger gets a new id (from the objects table) it inserts the id into the id column of the devices table.

当我引用它时(例如在PHP中使用mysql_insert_id();)，它为空.

When I refer to it (for example with mysql_insert_id(); in PHP), its empty.

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

How can I return the insert id from the trigger (LAST_INSERT_ID();) to the function in PHP as the mysql_insert_id(); ?

推荐答案

我个人使用存储过程.
这是使用PDO的基本示例:

创建存储过程的代码:

Personally I use stored procedures.
Here is a basic example with PDO:

Code to create the Stored Procedures:

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);

我希望能有所帮助. :)

I hope I have helped. :)

这篇关于插入ID字段(由触发器生成，但未传递)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！