在PHP中高效执行多个MySQL查询：技巧与实践

在PHP中，直接使用mysqli::query()执行包含多个分号分隔的SQL查询字符串通常只会处理第一个查询。本文将详细介绍两种在PHP中有效执行多个MySQL查询的方法：一是利用SQL的UNION操作符将多个SELECT语句合并为一个单一结果集，适用于结果结构相同的查询；二是使用mysqli::multi_query()函数，它能处理任意数量和类型的SQL语句，但需要更复杂的代码来遍历每个查询的结果集。文章还将强调在多查询场景下的安全性和性能考量。

问题解析：mysqli::query()的局限性

当尝试在php中使用mysqli::query()方法执行一个包含多个sql语句（例如select * from table1; select * from table2;）的字符串时，mysqli::query()默认只会执行第一个语句并返回其结果。对于后续的语句，它会忽略，这导致开发者在尝试获取第二个查询结果时，会遇到“trying to get property 'num_rows' of non-object”的错误，因为$result变量并非一个有效的查询结果对象。这是mysqli::query()设计上的特性，它旨在处理单个sql语句。

解决方案一：利用SQL UNION 操作符合并查询结果

对于需要从多个SELECT语句中获取结果，并且这些SELECT语句的列结构（列数、列名和数据类型）兼容时，最简洁有效的方法是使用SQL的UNION或UNION ALL操作符。UNION操作符用于合并两个或多个SELECT语句的结果集。

UNION与UNION ALL的区别：

• UNION: 会自动去除结果集中的重复行。
• UNION ALL: 不会去除重复行，直接合并所有结果。在不需要去重时，UNION ALL通常性能更优。

示例代码：

<?php
// 假设已包含数据库连接配置
require('configuration.php');

// 创建数据库连接
$conn = new mysqli($db_host, $db_username, $db_password, $db_name);

// 检查连接
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// 使用 UNION ALL 合并两个 SELECT 查询
// 注意：每个 SELECT 语句必须用括号括起来，以确保 UNION 操作符的正确解析
$query = "(SELECT * FROM `table` WHERE `ip` LIKE '1.1.1.1') UNION ALL (SELECT * FROM `table` WHERE `ip` LIKE '2.2.2.2')";

$sql = $query;
$result = $conn->query($sql);

if ($result) { // 检查查询是否成功
  if ($result->num_rows > 0) {
    // 遍历并输出结果
    while($row = $result->fetch_assoc()) {
      echo "ID: " . $row["id"]. "<br>IP:" . $row["dedicatedip"]. "<br><br>";
    }
  } else {
    echo "0 results";
  }
  $result->free(); // 释放结果集
} else {
  echo "Error: " . $conn->error; // 输出查询错误信息
}

$conn->close(); // 关闭数据库连接
?>

注意事项：

• 使用UNION或UNION ALL时，所有参与合并的SELECT语句必须具有相同数量的列，并且对应列的数据类型应兼容。
• 列的顺序也很重要，结果集中的列名通常会取第一个SELECT语句的列名。
• 这种方法将多个逻辑上独立的查询合并为一个大的查询，PHP端只需处理一个结果集，代码逻辑相对简单。

解决方案二：使用 mysqli::multi_query() 处理多语句查询

当需要执行的SQL语句类型不同（例如混合INSERT、UPDATE和SELECT），或者SELECT语句的列结构不兼容，无法使用UNION时，mysqli::multi_query()是更强大的选择。这个方法允许执行一个包含多个由分号分隔的SQL语句的字符串。

mysqli::multi_query()的工作原理：multi_query()执行后，不会立即返回所有查询的结果。你需要使用mysqli::store_result()来获取当前查询的结果集，然后使用mysqli::next_result()来前进到下一个查询的结果。这个过程需要循环进行，直到没有更多的结果集为止。

示例代码：

<?php
// 假设已包含数据库连接配置
require('configuration.php');

// 创建数据库连接
$conn = new mysqli($db_host, $db_username, $db_password, $db_name);

// 检查连接
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// 包含多个 SELECT 语句的查询字符串
$query = "SELECT * FROM `table` WHERE `ip` LIKE '1.1.1.1';";
$query .= "SELECT * FROM `table` WHERE `ip` LIKE '2.2.2.2';";
// 也可以混合其他类型的语句，例如：
// $query .= "UPDATE `table` SET `status` = 'active' WHERE `id` = 1;";

// 执行多语句查询
if ($conn->multi_query($query)) {
    $i = 1;
    do {
        // 存储当前查询的结果集（如果存在）
        if ($result = $conn->store_result()) {
            echo "<h2>Result Set " . $i . ":</h2>";
            if ($result->num_rows > 0) {
                // 遍历并输出结果
                while ($row = $result->fetch_assoc()) {
                    echo "ID: " . $row["id"] . "<br>IP:" . $row["dedicatedip"] . "<br><br>";
                }
            } else {
                echo "0 results for this query.<br>";
            }
            $result->free(); // 释放当前结果集
        }
        // 检查是否有更多结果集
        if ($conn->more_results()) {
            $i++;
        } else {
            break; // 没有更多结果集，退出循环
        }
    } while ($conn->next_result()); // 移动到下一个结果集
} else {
    echo "Error: " . $conn->error; // 输出查询错误信息
}

$conn->close(); // 关闭数据库连接
?>

注意事项：

• 复杂性： multi_query()的实现比UNION更复杂，因为它需要手动遍历每个结果集。
• 安全性： 当使用multi_query()时，SQL注入的风险会增加，因为攻击者可能会通过注入额外的语句来执行恶意操作。强烈建议对所有用户输入进行参数化查询（使用预处理语句mysqli_prepare），而不是直接拼接SQL字符串。 multi_query本身无法直接与预处理语句结合使用，因此在使用它时必须格外小心地验证和清理所有输入。
• 错误处理： 务必检查multi_query()的返回值，并在循环中检查$conn->error以捕获任何查询错误。

安全性与性能考量

SQL注入防护： 无论是使用UNION还是multi_query，当SQL查询中包含来自用户输入的数据时，都必须使用预处理语句（Prepared Statements）来防止SQL注入。mysqli_prepare()和mysqli_stmt_bind_param()是实现这一目标的关键。虽然multi_query不直接支持预处理语句的批量执行，但对于每个独立的查询，如果其参数来自用户输入，都应单独使用预处理语句。

示例（使用预处理语句）： 对于单个查询，例如使用UNION的情况，如果ip值来自用户输入：

// 假设 $ip1 和 $ip2 是用户输入
$ip1 = '1.1.1.1'; // 示例用户输入
$ip2 = '2.2.2.2'; // 示例用户输入

// 构建包含占位符的查询
$query = "(SELECT * FROM `table` WHERE `ip` LIKE ?) UNION ALL (SELECT * FROM `table` WHERE `ip` LIKE ?)";

if ($stmt = $conn->prepare($query)) {
    // 绑定参数
    $stmt->bind_param("ss", $ip1, $ip2); // "ss" 表示两个字符串参数

    // 执行查询
    $stmt->execute();

    // 获取结果
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "ID: " . $row["id"]. "<br>IP:" . $row["dedicatedip"]. "<br><br>";
        }
    } else {
        echo "0 results";
    }
    $stmt->close(); // 关闭预处理语句
} else {
    echo "Prepare failed: " . $conn->error;
}

性能优化：

• UNION ALL vs UNION： 如果不需要去重，始终优先使用UNION ALL，因为它避免了去重操作，性能更优。
• 索引： 确保查询中WHERE子句使用的列（如ip）上建立了适当的索引，这将显著提高查询性能。
• 批量操作： 对于大量的INSERT或UPDATE操作，考虑使用multi_query批量执行，或者使用单个INSERT ... VALUES (), (), ...语句来减少数据库往返次数。

总结

在PHP中执行多个MySQL查询时，选择正确的方法至关重要：

• 对于结构相同、需要合并结果的SELECT查询，UNION或UNION ALL是最佳选择，它将多个逻辑查询合并为一个物理查询，简化了PHP端的处理。
• 对于需要执行不同类型或不同结构的多个SQL语句，mysqli::multi_query()提供了更大的灵活性，但要求开发者手动遍历和处理每个结果集，且对安全性的要求更高。

无论选择哪种方法，始终将SQL注入防护放在首位，通过使用预处理语句来确保应用程序的安全性。同时，通过合理的索引和查询优化，可以进一步提升多查询操作的性能。

php免费学习视频：立即学习
踏上前端学习之旅，开启通往精通之路！从前端基础到项目实战，循序渐进，一步一个脚印，迈向巅峰！