PHP高效数据库查询：MySQLi与PDO实战教程与最佳实践159

在现代Web开发中，PHP作为最流行的后端语言之一，其与数据库的交互能力是构建动态网站和Web应用程序的核心。无论是用户认证、内容管理还是电子商务，数据存储与检索都离不开高效、安全的数据库查询。本文将深入探讨PHP如何进行数据库查询，主要聚焦于两种主流的数据库扩展：MySQLi和PDO，并通过丰富的代码示例，从基础连接到高级操作，再到安全最佳实践，为您提供一份全面的指南。

一、PHP与数据库连接的基石：MySQLi与PDO

PHP提供了多种与数据库交互的方式，其中最常用且推荐的是MySQLi（MySQL Improved Extension）和PDO（PHP Data Objects）。了解它们的特点和适用场景是高效开发的第一步。

1.1 MySQLi：专为MySQL优化

MySQLi是PHP官方为MySQL数据库提供的一个增强型扩展，支持MySQL的全部新特性，如预处理语句、多语句查询、事务等。它提供了面向过程和面向对象两种API风格，让开发者可以根据自己的偏好选择。

1.2 PDO：数据库抽象层

PDO是一个轻量级、一致性的接口，用于PHP访问多种数据库。它的最大优势在于提供了一个统一的API，无论底层数据库是MySQL、PostgreSQL、SQLite还是SQL Server，开发者都可以使用相同的函数和方法进行操作。这意味着如果未来需要更换数据库类型，代码改动会非常小，大大提高了代码的可移植性。

选择建议：
如果您确定项目将一直使用MySQL数据库，并且希望利用MySQL的特定功能，MySQLi是一个不错的选择。
如果您需要支持多种数据库，或者希望代码具有更好的可移植性和维护性，那么PDO是更优的选择。现代框架（如Laravel、Symfony）通常也默认使用PDO。

在本文中，我们将同时提供MySQLi和PDO的示例，以帮助您全面理解。

二、MySQLi 实践：面向过程与面向对象查询

本节将通过示例演示如何使用MySQLi进行基本的CRUD（创建、读取、更新、删除）操作。

2.1 连接数据库

首先，我们需要建立与MySQL数据库的连接。以下是面向过程和面向对象两种方式的连接示例：<?php
// 数据库配置
$servername = "localhost";
$username = "root"; // 您的数据库用户名
$password = "your_password"; // 您的数据库密码
$dbname = "my_database"; // 您的数据库名
// ====== 面向过程连接 ======
$conn_procedural = mysqli_connect($servername, $username, $password, $dbname);
// 检查连接
if (!$conn_procedural) {
die("面向过程连接失败: " . mysqli_connect_error());
}
echo "<p>面向过程连接成功!</p>";
mysqli_set_charset($conn_procedural, "utf8mb4"); // 设置字符集
// ====== 面向对象连接 ======
$conn_oop = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn_oop->connect_error) {
die("面向对象连接失败: " . $conn_oop->connect_error);
}
echo "<p>面向对象连接成功!</p>";
$conn_oop->set_charset("utf8mb4"); // 设置字符集
// ... 后续操作
// 操作完成后记得关闭连接
mysqli_close($conn_procedural); // 面向过程
$conn_oop->close(); // 面向对象
?>

为演示方便，后续示例将主要采用面向对象的方式。

2.2 SELECT 查询 (读取数据)

从数据库中检索数据是最常见的操作。这里我们将演示如何查询所有数据、带条件查询以及如何处理结果集。<?php
// 假设已建立 $conn_oop 连接
// ... (连接代码同上) ...
// 1. 查询所有用户
$sql_select_all = "SELECT id, firstname, lastname, email FROM users";
$result_all = $conn_oop->query($sql_select_all);
if ($result_all) {
if ($result_all->num_rows > 0) {
echo "<h3>所有用户:</h3>";
// 输出每行数据
while($row = $result_all->fetch_assoc()) {
echo "<p>ID: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "</p>";
}
} else {
echo "<p>0 结果</p>";
}
$result_all->free(); // 释放结果集
} else {
echo "<p>查询所有用户失败: " . $conn_oop->error . "</p>";
}
// 2. 带条件查询 (WHERE子句) - 查找姓氏为 'Doe' 的用户
$search_lastname = "Doe";
$sql_select_where = "SELECT id, firstname, lastname, email FROM users WHERE lastname = '$search_lastname'"; // 注意：这里直接拼接有SQL注入风险，后面会介绍预处理语句
$result_where = $conn_oop->query($sql_select_where);
if ($result_where) {
if ($result_where->num_rows > 0) {
echo "<h3>姓氏为 '$search_lastname' 的用户:</h3>";
while($row = $result_where->fetch_assoc()) {
echo "<p>ID: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "</p>";
}
} else {
echo "<p>0 结果</p>";
}
$result_where->free();
} else {
echo "<p>带条件查询失败: " . $conn_oop->error . "</p>";
}
// ... 关闭连接
$conn_oop->close();
?>

2.3 INSERT 查询 (插入数据)

向数据库中添加新记录。<?php
// 假设已建立 $conn_oop 连接
// ...
$firstname = "John";
$lastname = "Doe";
$email = "@";
$sql_insert = "INSERT INTO users (firstname, lastname, email) VALUES ('$firstname', '$lastname', '$email')"; // 同样有SQL注入风险
if ($conn_oop->query($sql_insert) === TRUE) {
echo "<p>新记录插入成功</p>";
echo "<p>最新插入的ID: " . $conn_oop->insert_id . "</p>";
} else {
echo "<p>Error: " . $sql_insert . "<br>" . $conn_oop->error . "</p>";
}
// ... 关闭连接
$conn_oop->close();
?>

2.4 UPDATE 查询 (更新数据)

修改数据库中现有记录的数据。<?php
// 假设已建立 $conn_oop 连接
// ...
$user_id = 1;
$new_email = "@";
$sql_update = "UPDATE users SET email = '$new_email' WHERE id = $user_id"; // 同样有SQL注入风险
if ($conn_oop->query($sql_update) === TRUE) {
if ($conn_oop->affected_rows > 0) {
echo "<p>记录更新成功</p>";
} else {
echo "<p>没有记录被更新 (可能ID不存在或数据相同)</p>";
}
} else {
echo "<p>Error updating record: " . $conn_oop->error . "</p>";
}
// ... 关闭连接
$conn_oop->close();
?>

2.5 DELETE 查询 (删除数据)

从数据库中删除记录。<?php
// 假设已建立 $conn_oop 连接
// ...
$user_id_to_delete = 2;
$sql_delete = "DELETE FROM users WHERE id = $user_id_to_delete"; // 同样有SQL注入风险
if ($conn_oop->query($sql_delete) === TRUE) {
if ($conn_oop->affected_rows > 0) {
echo "<p>记录删除成功</p>";
} else {
echo "<p>没有记录被删除 (可能ID不存在)</p>";
}
} else {
echo "<p>Error deleting record: " . $conn_oop->error . "</p>";
}
// ... 关闭连接
$conn_oop->close();
?>

三、PDO 实践：更强大的抽象层与预处理

PDO提供了一致的API和强大的预处理语句功能，极大地提升了安全性和可维护性。

3.1 连接数据库

PDO连接需要一个数据源名称（DSN），它指定了数据库类型、主机、数据库名等信息。<?php
// 数据库配置
$dsn = "mysql:host=localhost;dbname=my_database;charset=utf8mb4";
$username = "root";
$password = "your_password";
try {
$pdo = new PDO($dsn, $username, $password);
// 设置PDO错误模式为异常，这样可以在出错时抛出异常
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 禁用模拟预处理语句 (如果数据库本身支持，可以提高性能和安全性)
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
echo "<p>PDO 连接成功!</p>";
} catch (PDOException $e) {
die("PDO 连接失败: " . $e->getMessage());
}
// ... 后续操作
// PDO连接在脚本结束时会自动关闭，或可以通过 $pdo = null; 显式关闭
?>

3.2 SELECT 查询 (读取数据) - 使用预处理语句

PDO的预处理语句是防止SQL注入的关键。<?php
// 假设已建立 $pdo 连接
// ... (连接代码同上) ...
// 1. 查询所有用户
$stmt_all = $pdo->query("SELECT id, firstname, lastname, email FROM users");
echo "<h3>所有用户 (PDO):</h3>";
while ($row = $stmt_all->fetch(PDO::FETCH_ASSOC)) {
echo "<p>ID: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "</p>";
}
// 2. 带条件查询 (WHERE子句) - 使用预处理语句
$search_lastname = "Doe";
$stmt_where = $pdo->prepare("SELECT id, firstname, lastname, email FROM users WHERE lastname = :lastname"); // 使用命名占位符
$stmt_where->bindParam(':lastname', $search_lastname); // 绑定参数
$stmt_where->execute(); // 执行查询
echo "<h3>姓氏为 '$search_lastname' 的用户 (PDO):</h3>";
if ($stmt_where->rowCount() > 0) {
while ($row = $stmt_where->fetch(PDO::FETCH_ASSOC)) {
echo "<p>ID: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "</p>";
}
} else {
echo "<p>0 结果</p>";
}
// 3. 另一种参数绑定方式：execute() 时传入数组
$search_id = 1;
$stmt_by_id = $pdo->prepare("SELECT id, firstname, lastname, email FROM users WHERE id = ?"); // 使用问号占位符
$stmt_by_id->execute([$search_id]); // 执行并传入参数数组
echo "<h3>ID 为 '$search_id' 的用户 (PDO):</h3>";
$user = $stmt_by_id->fetch(PDO::FETCH_ASSOC); // 获取单条结果
if ($user) {
echo "<p>ID: " . $user["id"]. " - Name: " . $user["firstname"]. " " . $user["lastname"]. " - Email: " . $user["email"]. "</p>";
} else {
echo "<p>未找到该用户</p>";
}
// ... PDO连接会自动关闭
?>

3.3 INSERT, UPDATE, DELETE 查询 - 使用预处理语句

所有DML（数据操作语言）语句都应使用预处理语句。<?php
// 假设已建立 $pdo 连接
// ...
// 1. INSERT 插入数据
$firstname_new = "Jane";
$lastname_new = "Smith";
$email_new = "@";
$stmt_insert = $pdo->prepare("INSERT INTO users (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");
$stmt_insert->bindParam(':firstname', $firstname_new);
$stmt_insert->bindParam(':lastname', $lastname_new);
$stmt_insert->bindParam(':email', $email_new);
if ($stmt_insert->execute()) {
echo "<p>新记录插入成功 (PDO), 最新ID: " . $pdo->lastInsertId() . "</p>";
} else {
echo "<p>Error inserting record (PDO): " . $stmt_insert->errorCode() . " - " . print_r($stmt_insert->errorInfo(), true) . "</p>";
}
// 2. UPDATE 更新数据
$user_id_update = 1;
$new_email_update = "@";
$stmt_update = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
$stmt_update->bindParam(':email', $new_email_update);
$stmt_update->bindParam(':id', $user_id_update, PDO::PARAM_INT); // 明确指定参数类型
if ($stmt_update->execute()) {
echo "<p>记录更新成功 (PDO), 影响行数: " . $stmt_update->rowCount() . "</p>";
} else {
echo "<p>Error updating record (PDO): " . $stmt_update->errorCode() . " - " . print_r($stmt_update->errorInfo(), true) . "</p>";
}
// 3. DELETE 删除数据
$user_id_delete = 3;
$stmt_delete = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt_delete->bindParam(':id', $user_id_delete, PDO::PARAM_INT);
if ($stmt_delete->execute()) {
echo "<p>记录删除成功 (PDO), 影响行数: " . $stmt_delete->rowCount() . "</p>";
} else {
echo "<p>Error deleting record (PDO): " . $stmt_delete->errorCode() . " - " . print_r($stmt_delete->errorInfo(), true) . "</p>";
}
// ...
?>

四、数据库查询中的安全与最佳实践

数据库安全是Web应用开发中最重要的方面之一。不安全的查询可能导致数据泄露、篡改甚至整个系统的崩溃。

4.1 SQL 注入漏洞与预防（重中之重）

SQL注入是一种常见的攻击手段，攻击者通过在输入字段中插入恶意的SQL代码来操纵数据库。上述直接拼接用户输入的SQL语句，如`"SELECT ... WHERE id = $id"`，都是存在严重SQL注入风险的。

预防方法：使用预处理语句（Prepared Statements）

预处理语句的工作原理是：首先将SQL查询模板发送到数据库，数据库会预编译这个模板。然后，再将用户输入的数据作为参数绑定到这个模板上，而不是直接拼接到SQL字符串中。这样，即使参数中包含恶意SQL代码，数据库也会将其视为普通数据值，从而避免了注入攻击。
MySQLi 的预处理语句示例：

<?php
// 假设已建立 $conn_oop 连接
$search_email = "@";
$stmt = $conn_oop->prepare("SELECT id, firstname, lastname FROM users WHERE email = ?"); // 使用问号占位符
$stmt->bind_param("s", $search_email); // 绑定参数，"s" 表示字符串类型
$stmt->execute(); // 执行查询
$result = $stmt->get_result(); // 获取结果集
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "<p>Found: " . $row['firstname'] . " " . $row['lastname'] . "</p>";
}
} else {
echo "<p>No user found with that email.</p>";
}
$stmt->close(); // 关闭预处理语句
?>


PDO 的预处理语句示例（同前文）：

<?php
// 假设已建立 $pdo 连接
$search_email = "@";
$stmt = $pdo->prepare("SELECT id, firstname, lastname FROM users WHERE email = :email");
$stmt->bindParam(':email', $search_email);
$stmt->execute();
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
echo "<p>Found: " . $user['firstname'] . " " . $user['lastname'] . "</p>";
} else {
echo "<p>No user found with that email.</p>";
}
?>

总结：始终使用预处理语句来处理所有用户输入或外部来源的数据，包括GET、POST、COOKIE、SESSION等。

4.2 错误处理与日志记录

良好的错误处理机制对于调试和维护至关重要。将错误信息记录到日志文件中，而不是直接显示给用户，可以避免泄露敏感信息，同时方便开发者追踪问题。
MySQLi 错误处理： 通过 `mysqli_error()` 或 `$conn->error` 获取错误信息。
PDO 错误处理： 设置 `PDO::ATTR_ERRMODE` 为 `PDO::ERRMODE_EXCEPTION`，然后使用 `try-catch` 块捕获 `PDOException` 异常。这是推荐的做法。

<?php
try {
// ... PDO 连接代码 ...
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare("INSERT INTO non_existent_table (col1) VALUES (?)");
$stmt->execute(['test']);
} catch (PDOException $e) {
// 记录错误到文件，而不是直接显示给用户
error_log("数据库错误: " . $e->getMessage() . " - " . $e->getCode() . " - SQLSTATE: " . $e->getPrevious()?->getCode(), 0);
echo "<p>操作失败，请稍后再试。</p>"; // 给用户友好的提示
// 开发环境下可以显示详细错误，生产环境则不要
// echo "<p>Detailed Error (Dev Only): " . $e->getMessage() . "</p>";
}
?>

4.3 资源管理与连接关闭

虽然PHP脚本执行结束后会自动关闭数据库连接和释放资源，但在长时间运行的脚本或特定场景下，显式地关闭连接和释放结果集是一种良好的习惯。
MySQLi： 使用 `mysqli_close($conn)` 关闭连接，`$result->free()` 释放结果集。
PDO： 通常不需要显式关闭，将 `$pdo` 变量设置为 `null` 即可关闭连接（如果它引用了唯一的连接对象）。

4.4 密码存储

切勿明文存储用户密码。在将密码存入数据库之前，务必使用安全的哈希函数（如 `password_hash()`）对其进行处理。<?php
$password = "mySecretPassword123";
$hashed_password = password_hash($password, PASSWORD_DEFAULT);
// 然后将 $hashed_password 存储到数据库
?>

4.5 事务处理 (Transactions)

当一系列数据库操作需要作为一个原子操作（要么全部成功，要么全部失败）时，应使用事务。例如，转账操作需要从一个账户扣款，同时给另一个账户加款。<?php
// 假设已建立 $pdo 连接
try {
$pdo->beginTransaction(); // 开启事务
// 1. 从账户A扣款
$stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
$stmt1->execute([100, 1]); // 从ID为1的账户扣100
// 2. 给账户B加款
$stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
$stmt2->execute([100, 2]); // 给ID为2的账户加100
$pdo->commit(); // 提交事务
echo "<p>转账成功！</p>";
} catch (PDOException $e) {
$pdo->rollBack(); // 发生错误时回滚事务
error_log("转账失败: " . $e->getMessage());
echo "<p>转账失败，系统错误，请联系管理员。</p>";
}
?>

4.6 数据过滤与验证

除了SQL注入，还应该对所有用户输入进行严格的过滤和验证，以确保数据符合预期格式和业务规则。例如，使用 `filter_var()` 过滤邮箱、URL，或者使用正则表达式验证复杂数据。

PHP的数据库查询功能强大而灵活，无论是MySQLi还是PDO，都能帮助开发者高效地与数据库进行交互。然而，掌握其基本操作只是第一步，更重要的是理解并实践安全最佳实践，尤其是SQL注入的防范和正确的错误处理。

通过本文的学习，您应该已经掌握了PHP数据库查询的核心概念、MySQLi和PDO的实际应用，以及构建安全、健壮Web应用的关键策略。在实际开发中，请始终坚持使用预处理语句，妥善处理错误，并合理管理数据库资源，以确保您的应用程序既高效又安全。```

2025-11-07

上一篇：PHP数组重复元素深度解析：查找、统计、去重与性能优化

下一篇：PHP驱动双银行系统集成：字符串连接的精妙与安全防护