MySQL存储过程没有返回插入ID？

Question

我有一个非常简单的查询，不确定我在这里做错了什么。

我的数据库调用没有收到我期望的插入id。

表格：

存储过程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
    INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
    SELECT
        in_customerID,
        in_productID,
        p.retail,
        p.faceValue
    FROM
        products as p
    WHERE 
        p.productID = in_productID;
END

PHP：

   public function addProduct($data, $userID)
    {
        // Do we already have a pending order for this user?
        $orderID = $this->doesOrderExist($userID);

        // We had no order, lets create one
        if (!$orderID) {
            $orderID = $this->createOrder($userID);
        }

        /**
         * Insert the customer product.
         * This relates a denomination to a customer.
         */
        $customerProductID = $this->addCustomerProduct($data);

        // Add this customer product to the order
        $this->addProductToOrder(array("customerProductID" => $customerProductID, "orderID" => $orderID));

        // Return
        return $customerProductID;
    }

    /**
     * Description: Add a customer product / reward
     * Page: client/add_reward
     */
    public function addCustomerProduct($data){
        $procedure = "CALL addCustomerProduct(?,?)";
        $result = $this->db->query($procedure, $data);
        return $this->db->insert_id();
    }

这个有问题的行是：$customerProductID = $this->addCustomerProduct($data);。

正在向表中插入新记录，该表具有PK/AI。数据插入正常，但$customerProductID返回0。

也许从选择语句插入不会返回insert ID吗？ @Ravi 更新-

更新2：

我创建了一个单独的方法，并硬编码查询和发送的数据。

它成功添加记录，AI增加，0作为last id返回。

public function test(){
    $procedure = "CALL addCustomerProduct(?,?)";
    $result = $this->db->query($procedure, array("customerID" => 1, "productID" => 20));
    echo $this->db->insert_id();
}

我还重新启动了MySQL服务器，以确保没有任何奇怪的情况发生。

另外，更新了存储过程，只需将随机数据插入表中，而不使用select。

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT)
BEGIN
    INSERT INTO order_customer_product (customerID, productID, retailAmountAtPurchase, faceValue)
    VALUES(8,2,'4.55',25);
END

更新3：

在插入后，我会打印出最后一次运行的查询以及结果。您会注意到有1行受影响（插入正在发生），但 insert_id 仍为0。

CALL addCustomerProduct('8','33')

CI_DB_mysqli_result Object
(
    [conn_id] => mysqli Object
        (
            [affected_rows] => 1
            [client_info] => mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
            [client_version] => 50012
            [connect_errno] => 0
            [connect_error] => 
            [errno] => 0
            [error] => 
            [error_list] => Array
                (
                )

            [field_count] => 0
            [host_info] => Localhost via UNIX socket
            [info] => 
            [insert_id] => 0
            [server_info] => 5.6.35
            [server_version] => 50635
            [stat] => Uptime: 1637  Threads: 3  Questions: 508  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 106  Queries per second avg: 0.310
            [sqlstate] => 00000
            [protocol_version] => 10
            [thread_id] => 25
            [warning_count] => 0
        )

    [result_id] => 1
    [result_array] => Array
        (
        )

    [result_object] => Array
        (
        )

    [custom_result_object] => Array
        (
        )

    [current_row] => 0
    [num_rows] => 
    [row_data] => 
)

更新4：

根据我所做的一些研究，除非您使用mysqli方法，例如$this->db->insert()，否则它不会向您返回最后插入的id。

我将尝试弄清楚Ravi的建议，但似乎Code Igniter不允许显示的示例。至少我现在知道，除非您使用“insert”方法而不是存储过程，否则这不是正常行为。

- SBB

---

好的 - 我对任何框架都没有想法，我只知道标准的 mysqli 方法就像上面那样。值得一试！ - Professor Abronsius

打开“通用日志”以查看准确的命令发送情况。它不会包括 insert_id()，但可能会包含一些 Codeigniter 插入并干扰的语句。 - Rick James

为什么不直接在addCustomerProduct()函数中执行存储过程中的查询呢？ - Paul Spiegel

@PaulSpiegel - 我需要使存储过程可重复使用，因此我尽量让它们只做一件事。如果我在应用程序的其他地方调用它而不需要进行插入操作，我就不想让它在这个SP中执行额外的插入操作。我还喜欢将PK/AI返回到我的应用程序中，以便在代码后面记录任何发生的错误并引用涉及的ID/记录。 - SBB

在您的 test() 函数中尝试以下代码：$this->db->conn_id->query("CALL addCustomerProduct(1,20)"); echo $this->db->conn_id->insert_id; 它仍然是 0 吗？ - Paul Spiegel

Answer 1

这个答案 可能会解释为什么您现有的代码无法工作。引用一下：

CodeIgniter的insert_id()只会返回insert()的ID。 除非您在调用函数之前执行类似于$this->db->insert('table', $data);这样的操作，否则它将无法返回ID。

MySQL的LAST_INSERT_ID();应该对您有所帮助（假设您有权限更改存储过程定义）。将其更改为：

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(
    IN in_customerID INT, in_productID INT, OUT out_customerProductID INT)
BEGIN
    INSERT INTO order_customer_product (
        customerID, productID, retailAmountAtPurchase, faceValue)
    VALUES(8,2,'4.55',25);

    SELECT LAST_INSERT_ID() INTO out_customerProductID;
END

然后使用以下代码获取输出参数的值：

public function addCustomerProduct($data) {
    $procedure = "CALL addCustomerProduct("
                   . $this->db->escape($data["customerID"]).", "
                   . $this->db->escape($data["productID"]).", "
                   . "@customerProductID);"
    $this->db->query($procedure);
    $query = $this->db->query("SELECT @customerProductID AS customerProductID");
    if($query->num_rows() > 0)
      return $query->result()->customerProductID;
    else
      return NULL;
}

如果上述方法不起作用，请尝试在存储过程调用之前和之后添加$this->db->trans_start();和$this->db->trans_complete();，以确保事务被提交。

- Steve Chambers

---

我终于开始尝试了，当我像上面那样设置我的SP和代码时，我会得到两种错误之一。首先，如果我只提供2个?,?而它需要3个（一个用于out），它会抛出有关参数数量不正确的错误。如果我在调用中添加第三个?，我会收到You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?)' at line 1的错误提示。我尝试过使用事务和不使用事务。 - SBB

好的，我对Codeigniter不是很熟悉（这就是为什么我写了“类似于…”），但现在我已经根据这个答案修改了上面的代码。 - Steve Chambers

Answer 2

理想情况下，以下代码行应该起作用

$this->db->insert_id;

但是，我不确定为什么它没有起作用，因此我建议采用以下解决方法：使用附加参数out_lastId重新编译您的程序，该参数将返回最后插入的id。

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomerProduct`(IN in_customerID INT, in_productID INT, OUT out_lastId INT)

在插入后，使用最后插入的id设置值。

 SET out_lastId = LAST_INSERT_ID();

==更新==

$this->db->multi_query( "CALL addCustomerProduct($data, @id);SELECT @id as id" );
$db->next_result();            // flush the null RS from the call
$rs=$this->db->store_result();       // get the RS containing the id
echo $rs->fetch_object()->id, "\n";
$rs->free();

- Ravi

---

我添加了$procedure = "CALL addCustomerProduct(?,?, ?)";，但是对于从那里获取值，我不确定该怎么做。 - SBB

@SBB，很久没有写 PHP 了，但我会尝试。 - Ravi

@SBB请检查，你可能需要在这里和那里纠正代码。 - Ravi

我很快就会检查这个 - 我只是感到困惑，为什么我无法在硬编码数据时让AI返回。 - SBB

看起来这个框架不支持multi_query。 - SBB

Answer 3

为什么

insert_id() 仅适用于 查询构建器 和 查询。但是存储过程使用 $this->db->query() 调用，但它不会返回数据 insert_id()。

---

如何操作

在SP结束之前添加SELECT MAX(id) FROM order_customer_product;。这样将返回最后一个ID给您的代码。

---

建议

我看到有一个插入查询到数据库的情况。如果我使用类似的情况，我会使用普通的查询构建器或/和将其包装在Codeigniter事务（我在另一个问题上的答案）中。

- Abdulla Nilam