php - 使用 MariaDB 的 PHP mysqli 接口调用存储过程的最佳实践是什么？

问题描述

我正在尝试使用 MariaDB 而不是 MySQL 将一个使用了 8 年的 PHP/MySQL Web 应用程序移植到更新的服务器堆栈。由于“数据包乱序”错误，我发现不可能在同一连接上运行多个存储过程。下面是应该工作但没有工作的代码。有人可以指出我可能在哪里误入歧途，或者什么是成功的替代方法？

<?php

$host = "localhost";
$user = "mysqli_test";
$password = "";
$database = "mysqli_test";

function get_connection()
{
   GLOBAL $host, $user, $password, $database;
   $connection = new mysqli($host, $user, $password, $database);
   if (! $connection || mysqli_connect_errno() )
     printf("Connection failure: %s.\n", mysqli_connect_error());

   return $connection;
}

// Minimum viable function: isolate necessary steps.
function get_person($connection, $first_name)
{
   $query = "CALL Get_By_First_Name(?)";
   if (($stmt = $connection->prepare($query)))  // error here after first pass
   {
      $stmt->bind_param('s', $first_name);
      if ($stmt->execute())
      {
         $stmt->store_result();
         $stmt->bind_result($id, $fname, $lname, $pets);

         while($stmt->fetch())
            printf("%3d %20s %20s %2d.\n", $id, $fname, $lname, $pets);

         $stmt->free_result();
         while ($stmt->next_result())  // my suspected culprit
         {
            $stmt->store_result();
            while ($stmt->fetch())
               ;
            $stmt->free_result();
         }
      }

      $stmt->close();
   }
}

if ($conn = get_connection())
{
   get_person($conn, "Samuel");  // it works the first time
   get_person($conn, "Zelda");   // this time it fails
}

?>

运行 aPHP/mysqli 代码，使用 C API 的几乎相同的 C++ 代码工作正常，所以我可以隔离我认为问题开始的地方：使用 next_result() 函数。在 C++ 代码中，next_result() 返回 TRUE 以指示在使用准备好的语句运行存储过程后有新的结果可用。在 PHP/mysqli 代码中，next_result() 返回 false，事实上，即使我忽略 false 返回值，也无法生成新结果。

我创建了一个github 存储库，其中包含更多解释和脚本，可以在您的计算机上运行以复制错误，如果有人感兴趣的话。

标签： phpmysqlstored-proceduresmysqlimariadb

解决方案

最好的做法是避免使用 PHP 中的存储过程……这并不总是可能的；有时存储过程是必要的，它们甚至可能在极少数情况下有用。但是，如果可以，请尝试将逻辑移至 PHP 应用程序，而不是将其存储在 MySQL 服务器上。这种方式不那么麻烦。

如果您想知道如何正确调用存储过程，最好的资源是 PHP 手册。我最近改进了手册中的大多数示例，因此我知道其中的示例反映了最佳实践并且确实有效。使用 mysqli和mysqli::multi_query()文档读取存储过程。

我建议避免mysqli::multi_query()使用，尽管存储过程可能是该功能甚至存在的主要原因。您已经做出了正确的选择来使用准备好的语句，这样您就可以绑定参数并避免 SQL 注入。

您必须记住的主要事情是该CALL()语句会产生一个空结果。如果存储过程也产生结果集/结果集，那么您需要遍历它们并获取每个结果集。存储过程的问题是您永远无法确定会产生多少结果集。你如何处理未知数？

看看这个例子（这是你的代码，但我做了一些更改并启用了错误报告）：

function get_connection($host, $user, $password, $database): mysqli
{
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    return new mysqli($host, $user, $password, $database);
    // you might want to set the correct charset (e.g. utf8mb4) here before returning
}

function get_person(mysqli $connection, $first_name): mysqli_result
{
    $query = "CALL Get_By_First_Name(?)";
    $stmt = $connection->prepare($query);
    $stmt->bind_param('s', $first_name);
    $stmt->execute();

    /* We expect this SP to return the main result set,
        which we want to return and then an empty result for CALL.
        Get the result here and return immediately.
        The prepared statement will be closed automatically once
        we leave the scope and this will clean up the remaining result set.
    */
    return $stmt->get_result();
}

$conn = get_connection($host, $user, $password, $database);
$res1 = get_person($conn, "Samuel");
$res2 = get_person($conn, "Zelda");

var_dump($res1->fetch_all(), $res2->fetch_all());

在上面的代码中，我假设我调用的 SP 将只返回两个结果集。我只想要第一个，我不关心CALL(). 当语句被清理时，第二个结果集被丢弃。如果不是这种情况，我将需要调用mysqli_stmt::next_result()直到获取所有结果集（不是mysqli::next_result()！）。这是处理繁琐的存储过程的最简单方法。

即使您将代码转换为 PDO，这仍然是最简单的方法。但是，使用 mysqli 时，事情可能会变得非常复杂，因此请注意不要过度设计解决方案。如果您的存储过程使用游标，请注意在 PHP 7.4 之前的 PHP 中有一个错误。

php - 使用 MariaDB 的 PHP mysqli 接口调用存储过程的最佳实践是什么？

问题描述

解决方案

推荐阅读