CodeIgniter中存储过程结果集使用及多查询同步错误解决

嘿，针对你提出的两个CodeIgniter存储过程使用诉求，还有碰到的「Commands out of sync; you can't run this command now」错误，我来一步步帮你解决：

一、先搞定你遇到的同步错误 #

这个错误的核心原因很明确：存储过程执行后会生成多个结果集（哪怕你的过程最后只输出两个会话变量，内部的INSERT/UPDATE操作也会产生空结果集），CodeIgniter的数据库驱动要求你手动遍历并释放所有结果集后，才能执行新的查询。

你现有代码里已经用到了next_result()和free_result()，但顺序不对——得先处理存储过程的结果集，再去查会话变量。修改后的模型代码如下：

public function addCustomerSales($data, $data2, $data4)
{
    // 注意：当前拼接参数的方式有严重SQL注入风险，后面会给优化方案
    $pr = "'" . implode("', '", $data) . "'";
    $pr2 = "'" . implode("', '", $data2) . "'";
    $pr5 = "'" . implode("', '", $data4) . "'";

    // 调用存储过程
    $spQuery = $this->db->query('CALL addCustomerSalesData("'.$pr.'", "'.$pr2.'", "'.$pr5.'", @CustSalesID, @CustSalesProID)');

    // 关键步骤：遍历并释放存储过程产生的所有结果集
    while ($this->db->conn_id->next_result()) { 
        $this->db->conn_id->free_result(); 
    }

    // 现在可以安全查询会话变量了
    $query = $this->db->query('SELECT @CustSalesID AS cust_sales_id, @CustSalesProID AS cust_sales_pro_id');
    $row = $query->row_array();

    $query->free_result();
    return $row;
}

重要优化：替换掉不安全的字符串拼接，用CodeIgniter的参数绑定功能，既安全又省心：

// 按存储过程参数顺序合并数组，直接绑定
$sql = 'CALL addCustomerSalesData(?, ?, ?, @CustSalesID, @CustSalesProID)';
$this->db->query($sql, array_merge($data, $data2, $data4));

二、诉求1：在CodeIgniter中使用存储过程返回的结果集 #

根据存储过程返回结果集的数量，分两种情况处理：

情况1：存储过程返回单个结果集 #

如果你的存储过程是直接返回查询数据（比如SELECT * FROM customers WHERE id = ?），可以直接获取：

public function getCustomerDetails($customerId)
{
    $sql = 'CALL getSingleCustomer(?)';
    $query = $this->db->query($sql, [$customerId]);

    // 获取结果集
    $result = $query->result_array();

    // 清理结果集，避免后续查询出错
    while ($this->db->conn_id->next_result()) {
        $this->db->conn_id->free_result();
    }

    return $result;
}

情况2：存储过程返回多个结果集 #

如果存储过程里有多个SELECT语句，需要依次获取每个结果集：

public function getMultiSetData()
{
    $query = $this->db->query('CALL getCustomerAndSalesData()');
    
    // 第一个结果集：客户列表
    $customerList = $query->result_array();
    $query->free_result();

    // 切换到下一个结果集
    $this->db->conn_id->next_result();
    $salesQuery = $this->db->query(''); // 空查询获取当前结果集
    $salesList = $salesQuery->result_array();
    $salesQuery->free_result();

    // 有更多结果集的话，重复上面的切换+获取步骤

    return [
        'customers' => $customerList,
        'sales' => $salesList
    ];
}

三、诉求2：单个存储过程执行多个查询，获取所有结果 #

当存储过程内部执行了多个INSERT/UPDATE/SELECT操作，需要同时获取操作结果（比如受影响行数、返回的数据集、输出变量），可以这么做：

示例参考（假设你的存储过程结构） #

DELIMITER //
CREATE PROCEDURE addCustomerSalesData(
    IN custData VARCHAR(255),
    IN salesData VARCHAR(255),
    IN proData VARCHAR(255),
    OUT CustSalesID INT,
    OUT CustSalesProID INT
)
BEGIN
    -- 插入客户数据
    INSERT INTO customers (...) VALUES (...);
    SET CustSalesID = 438040;

    -- 插入销售数据
    INSERT INTO sales (...) VALUES (...);
    SET CustSalesProID = 438040;

    -- 返回刚插入的客户详情
    SELECT * FROM customers WHERE id = CustSalesID;
END //
DELIMITER ;

模型中获取所有结果的代码 #

public function executeMultiQuerySP($data, $data2, $data4)
{
    // 参数绑定调用存储过程
    $sql = 'CALL addCustomerSalesData(?, ?, ?, @CustSalesID, @CustSalesProID)';
    $spQuery = $this->db->query($sql, array_merge($data, $data2, $data4));

    // 获取存储过程最后一个SELECT的结果：客户详情
    $customerDetails = $spQuery->result_array();
    $spQuery->free_result();

    // 清理存储过程中INSERT操作产生的空结果集
    while ($this->db->conn_id->next_result()) {
        $this->db->conn_id->free_result();
    }

    // 获取输出的ID变量
    $outputQuery = $this->db->query('SELECT @CustSalesID AS cust_sales_id, @CustSalesProID AS cust_sales_pro_id');
    $insertedIds = $outputQuery->row_array();
    $outputQuery->free_result();

    // 返回所有结果
    return [
        'customer_info' => $customerDetails,
        'inserted_ids' => $insertedIds
    ];
}

最后再划几个重点：

• 调用存储过程后必须遍历释放所有结果集，否则一定会触发同步错误
• 永远用参数绑定传递参数，别自己拼字符串，防SQL注入
• 多结果集要逐个切换、获取、释放，不能跳步

内容的提问来源于stack exchange，提问作者Vishwa Dave