六:PHP 操作 MySQL——增删改查与 PDO

一、回顾与本篇目标

上一篇你系统学习了 PHP 函数——定义、参数传递、类型声明、作用域、匿名函数和闭包。现在你的 PHP 代码可以封装成可复用的函数,结构更加清晰。

但到目前为止,我们的数据都是写死在代码里的——用户列表是一个硬编码的数组,程序退出后什么都没留下。一个真正的 Web 应用需要数据库来持久化存储数据。用户注册的信息、发布的文章、提交的订单——这些数据必须存到硬盘上,服务重启后还在。

PHP 和 MySQL 是 Web 开发中最经典的组合。从 WordPress 到无数的电商网站、论坛、CMS 系统,底层都是 PHP + MySQL。这一篇,我们学习如何在 PHP 中操作 MySQL 数据库——连接、查询、插入、更新、删除,以及最重要的安全机制:参数化查询防止 SQL 注入

如果你之前跟过《后端零基础入门》系列,在 Node.js 中用过 mysql2 模块,或者跟过《Python 零基础入门》用 pymysql 连接数据库,这一篇的概念你会觉得非常熟悉——连接数据库、执行 SQL 语句、处理查询结果,核心流程完全一样,只是 PHP 的语法和 API 不同。

本篇的目标:

  1. 理解 PHP 连接 MySQL 的两种方式:mysqli 和 PDO
  2. 学会用 PDO 连接数据库并处理连接错误
  3. 掌握增删改查的完整实现
  4. 彻底搞懂参数化查询——防止 SQL 注入的唯一正确方式
  5. 理解预处理语句的工作原理
  6. 学会事务的基本用法

二、PHP 操作 MySQL 的两种方式

PHP 提供了两种主要的 MySQL 操作方式:

特性 mysqli PDO
全称 MySQL Improved Extension PHP Data Objects
支持的数据库 仅 MySQL 12 种数据库(MySQL、SQLite、PostgreSQL 等)
编程风格 面向过程 + 面向对象 纯面向对象
参数化查询 支持(预处理语句) 支持(预处理语句)
命名参数 不支持(只能用 ? 占位符) 支持(:name 命名占位符)
推荐度 老项目维护 新项目首选

本系列统一使用 PDO。原因:

  • 数据库无关性:如果将来需要换成 SQLite 或 PostgreSQL,只需要修改连接字符串,代码不用改。
  • 命名参数WHERE id = :idWHERE id = ? 更可读,参数多的时候不会搞混顺序。
  • 更好的错误处理:PDO 可以抛出异常,比 mysqli 的返回值检查更优雅。
  • 现代 PHP 框架都在用:Laravel、Symfony 的底层数据库抽象都基于 PDO。

三、连接数据库

3.1 确保 MySQL 可用

在开始写 PHP 代码之前,确保你的 MySQL 服务正在运行。如果你之前跟过《后端零基础入门》系列,应该已经装好 MySQL 了。如果还没有,快速安装:

Windows:下载 MySQL Community Server 安装包。

Macbrew install mysql,然后 brew services start mysql

Linuxsudo apt install mysql-server,然后 sudo systemctl start mysql

进入 MySQL 命令行,创建一个测试数据库:

mysql -u root -p
CREATE DATABASE php_learning CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE php_learning;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email, age) VALUES
    ('张三', 'zhangsan@example.com', 28),
    ('李四', 'lisi@example.com', 22),
    ('王五', 'wangwu@example.com', 25);

现在你有一个 php_learning 数据库,里面有一张 users 表,包含三条测试数据。

3.2 用 PDO 连接数据库

<?php
// 数据库连接配置
$host = '127.0.0.1';      // 数据库服务器地址
$dbname = 'php_learning';  // 数据库名
$username = 'root';        // 用户名
$password = '';            // 密码(根据你自己的设置填写)
$charset = 'utf8mb4';      // 字符集

// DSN(数据源名称):告诉 PDO 连接什么数据库
$dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}";

try {
    // 创建 PDO 实例
    $pdo = new PDO($dsn, $username, $password);
    
    // 设置错误模式:抛出异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 设置默认的获取模式:关联数组
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    
    echo "数据库连接成功!<br>";
    
} catch (PDOException $e) {
    // 连接失败
    die("数据库连接失败:" . $e->getMessage());
}
?>

逐行解释:

  • $dsn(Data Source Name):告诉 PDO 连接什么类型的数据库、主机在哪、数据库名是什么、用什么字符集。mysql:host=...;dbname=...;charset=... 是固定的格式。
  • new PDO($dsn, $username, $password):创建 PDO 实例。如果连接失败(主机不可达、用户名密码错误、数据库不存在),会抛出 PDOException
  • $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)这一行非常重要!它设置 PDO 的错误模式为”抛出异常”。如果不设置,PDO 默认静默失败——SQL 语句写错了也不报错,你对着空结果排查半天不知道哪出了问题。设置了异常模式后,任何数据库错误都会抛出异常,开发阶段能立即看到问题。
  • $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC):设置查询结果默认以关联数组形式返回。这样查询出来的每一行都是 ["name" => "张三", "age" => 28] 这样的数组,而不是数字索引的数组。
  • die():终止程序并输出消息。仅在开发调试阶段使用,生产环境应该记录日志并显示友好的错误页面。

3.3 封装数据库连接

实际项目中不应该在每个文件里重复写连接代码。把连接逻辑封装到一个独立的函数或配置文件中:

<?php
// db.php —— 数据库连接配置文件

function getDBConnection(): PDO {
    static $pdo = null;  // 静态变量:只创建一次连接
    
    if ($pdo === null) {
        $host = '127.0.0.1';
        $dbname = 'php_learning';
        $username = 'root';
        $password = '';
        $charset = 'utf8mb4';
        
        $dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}";
        
        $pdo = new PDO($dsn, $username, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }
    
    return $pdo;
}
?>

使用 static 变量确保整个请求周期中只创建一次数据库连接,避免重复连接浪费资源。

四、查询数据(SELECT)

4.1 查询所有记录

<?php
require_once 'db.php';

$pdo = getDBConnection();

// 执行 SQL 查询
$stmt = $pdo->query("SELECT id, name, email, age, created_at FROM users ORDER BY id DESC");

// 获取所有行
$users = $stmt->fetchAll();

// 输出结果
foreach ($users as $user) {
    echo "ID: {$user['id']}, 姓名: {$user['name']}, 邮箱: {$user['email']}, 年龄: {$user['age']}<br>";
}
?>

逐行解释:

  • $pdo->query($sql):执行一条 SQL 查询语句,返回 PDOStatement 对象。这是查询结果集的”句柄”,通过它来获取实际数据。
  • $stmt->fetchAll():获取所有行,返回一个二维数组——每行是一个关联数组。如果查询结果为空,返回空数组 []
  • require_once:引入并执行指定的 PHP 文件,如果文件已经被引入过就不会重复引入。require 在文件不存在时会终止程序(include 只会警告)。

4.2 查询单条记录

<?php
require_once 'db.php';

$pdo = getDBConnection();

// 查询指定 ID 的用户
$id = 1;
$stmt = $pdo->query("SELECT * FROM users WHERE id = {$id}");

// 获取单行
$user = $stmt->fetch();

if ($user) {
    echo "姓名: {$user['name']}, 邮箱: {$user['email']}<br>";
} else {
    echo "用户不存在<br>";
}
?>

fetch()fetchAll() 的区别

  • fetch():返回一行数据(关联数组),没有更多行时返回 false
  • fetchAll():返回所有行(二维数组),没有结果时返回空数组。

⚠️ 上面这种直接把 $id 拼接到 SQL 中的写法有严重的安全问题! 如果你拼接的是用户输入的数据,攻击者可以注入恶意 SQL 代码。下一节讲正确的做法。

4.3 参数化查询(防止 SQL 注入)

永远不要直接把用户输入拼接到 SQL 语句中。 这是后端安全的第一条铁律。

假设你有一个登录接口,用户输入用户名和密码。如果直接拼接:

// 危险!不要这样写!
$name = $_POST['name'];
$password = $_POST['password'];
$sql = "SELECT * FROM users WHERE name = '{$name}' AND password = '{$password}'";

如果用户在用户名输入框中输入:' OR '1'='1,拼接出来的 SQL 变成:

SELECT * FROM users WHERE name = '' OR '1'='1' AND password = ''

'1'='1' 永远为真,这个查询会返回所有用户——攻击者绕过了登录验证。

正确的做法——参数化查询(预处理语句):

<?php
require_once 'db.php';

$pdo = getDBConnection();

// 用 ? 占位符
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);  // 数组中的值按顺序替换 ?
$user = $stmt->fetch();

// 用命名占位符(更推荐,参数多时不会搞混)
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => 'zhangsan@example.com']);
$user = $stmt->fetch();
?>

参数化查询的工作原理:

  1. prepare($sql):把 SQL 模板发送给数据库。数据库解析 SQL 结构(知道哪里是关键字、哪里是数据),但不执行。占位符 ?:name 标记了”这里会有一个值”。
  2. execute($params):把实际的值发送给数据库。数据库已经把 SQL 结构解析完了,所以这些值永远不会被当作 SQL 代码执行——它们只是数据。

这和 Node.js 的 db.query('SELECT ... WHERE id = ?', [id])、Python 的 cursor.execute('SELECT ... WHERE id = %s', (id,)) 是完全相同的机制。

使用命名占位符的优势:

<?php
// 命名占位符:参数多的时候一目了然
$sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'name' => '赵六',
    'email' => 'zhaoliu@example.com',
    'age' => 30
]);

// 等价于使用 ? 占位符的写法(参数顺序必须和 SQL 中一致,容易搞混)
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['赵六', 'zhaoliu@example.com', 30]);
?>

参数少时两者都可以。参数多时(比如 5 个以上),命名占位符明显更清晰。

4.4 查询结果的其他获取方式

<?php
require_once 'db.php';

$pdo = getDBConnection();
$stmt = $pdo->query("SELECT * FROM users");

// fetch() —— 逐行获取(游标模式,处理大数据集时省内存)
while ($row = $stmt->fetch()) {
    echo $row['name'] . "<br>";
}

// fetchAll() —— 一次性获取所有行(数据量小时最方便)
$allUsers = $stmt->fetchAll();

// fetchColumn() —— 获取某一行某一列的值
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$count = $stmt->fetchColumn();  // 返回第一行第一列的值
echo "总用户数:{$count}<br>";

// 用 fetchAll 加参数获取单列
$stmt = $pdo->query("SELECT name FROM users");
$names = $stmt->fetchAll(PDO::FETCH_COLUMN);  // 返回所有 name 组成的数组
print_r($names);  // ['张三', '李四', '王五']
?>

五、插入数据(INSERT)

<?php
require_once 'db.php';

$pdo = getDBConnection();

// 准备 SQL
$sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
$stmt = $pdo->prepare($sql);

// 执行插入
$stmt->execute([
    'name' => '赵六',
    'email' => 'zhaoliu@example.com',
    'age' => 30
]);

// 获取插入后的自增 ID
$newId = $pdo->lastInsertId();
echo "新用户的 ID 是:{$newId}<br>";

// 获取影响的行数
$affectedRows = $stmt->rowCount();
echo "影响了 {$affectedRows} 行<br>";
?>

关键点:

  • $pdo->lastInsertId():返回最后一次 INSERT 操作生成的自增 ID。注意:这是 PDO 对象的方法,不是 PDOStatement 的方法。
  • $stmt->rowCount():返回受影响的记录行数。对于 INSERT,通常是 1。

六、更新数据(UPDATE)

<?php
require_once 'db.php';

$pdo = getDBConnection();

$sql = "UPDATE users SET age = :age, email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'age' => 29,
    'email' => 'zhangsan_new@example.com',
    'id' => 1
]);

$affectedRows = $stmt->rowCount();

if ($affectedRows > 0) {
    echo "更新成功,影响了 {$affectedRows} 行<br>";
} else {
    echo "没有记录被更新(可能 ID 不存在,或者新值和旧值相同)<br>";
}
?>

注意 rowCount() 的行为:如果 UPDATE 语句执行了但新值和旧值完全相同,MySQL 不会实际修改这条记录,rowCount() 返回 0。所以不能用 rowCount() === 0 来判断”ID 不存在”。

七、删除数据(DELETE)

<?php
require_once 'db.php';

$pdo = getDBConnection();

$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 3]);

$affectedRows = $stmt->rowCount();

if ($affectedRows > 0) {
    echo "删除成功,影响了 {$affectedRows} 行<br>";
} else {
    echo "没有记录被删除(ID 不存在)<br>";
}
?>

八、事务

当多个操作必须要么全部成功,要么全部不执行时,使用事务。最经典的例子是转账:A 扣钱和 B 加钱必须同时成功,不能出现 A 扣了钱但 B 没收到的情况。

<?php
require_once 'db.php';

$pdo = getDBConnection();

try {
    // 开启事务
    $pdo->beginTransaction();
    
    // 操作一:从张三的账户扣 100 元
    $stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - 100 WHERE user_id = :id");
    $stmt1->execute(['id' => 1]);
    
    // 模拟错误(实际项目中可能是业务逻辑判断)
    // throw new Exception("模拟的错误");
    
    // 操作二:给李四的账户加 100 元
    $stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + 100 WHERE user_id = :id");
    $stmt2->execute(['id' => 2]);
    
    // 提交事务——两个操作都成功
    $pdo->commit();
    echo "转账成功!<br>";
    
} catch (Exception $e) {
    // 回滚事务——撤销所有操作
    $pdo->rollBack();
    echo "转账失败,已回滚:" . $e->getMessage() . "<br>";
}
?>

事务的四个特性(ACID):

  • 原子性:事务中的所有操作要么全部完成,要么全部不完成。
  • 一致性:事务前后,数据库从一个一致状态转换到另一个一致状态。
  • 隔离性:并发执行的事务之间互不干扰。
  • 持久性:事务提交后,数据永久保存,即使系统崩溃也不会丢失。

什么时候需要事务? 任何涉及多表、多行、多步骤的数据修改,且这些修改必须保持一致性时。转账、下单扣库存、删除用户同时删除其关联数据——这些都是典型的场景。

九、综合演示:一个简单的用户管理系统

下面这个完整的 PHP 页面实现了用户的增删改查,展示了 PDO 的实际应用:

<?php
require_once 'db.php';

$pdo = getDBConnection();
$message = '';

// 处理表单提交
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = $_POST['action'] ?? '';
    
    try {
        if ($action === 'create') {
            $stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
            $stmt->execute([
                'name' => $_POST['name'],
                'email' => $_POST['email'],
                'age' => (int)$_POST['age']
            ]);
            $message = "用户创建成功!";
            
        } elseif ($action === 'update') {
            $stmt = $pdo->prepare("UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id");
            $stmt->execute([
                'name' => $_POST['name'],
                'email' => $_POST['email'],
                'age' => (int)$_POST['age'],
                'id' => (int)$_POST['id']
            ]);
            $message = "用户更新成功!";
            
        } elseif ($action === 'delete') {
            $stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
            $stmt->execute(['id' => (int)$_POST['id']]);
            $message = "用户删除成功!";
        }
    } catch (PDOException $e) {
        $message = "操作失败:" . $e->getMessage();
    }
}

// 获取所有用户
$users = $pdo->query("SELECT * FROM users ORDER BY id DESC")->fetchAll();
?>

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>用户管理</title>
    <style>
        body { font-family: sans-serif; padding: 40px; background: #f0f4f8; }
        .container { max-width: 800px; margin: 0 auto; }
        .card { background: white; padding: 30px; border-radius: 12px; margin-bottom: 20px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
        th, td { padding: 10px 12px; text-align: left; border-bottom: 1px solid #e0e0e0; }
        th { background: #4a90d9; color: white; }
        input { padding: 8px 12px; border: 1px solid #ddd; border-radius: 6px; font-size: 14px; width: 100%; box-sizing: border-box; }
        button { padding: 8px 16px; border: none; border-radius: 6px; cursor: pointer; font-size: 14px; }
        .btn-primary { background: #4a90d9; color: white; }
        .btn-danger { background: #e74c3c; color: white; }
        .btn-sm { padding: 4px 10px; font-size: 12px; }
        .message { padding: 12px; border-radius: 6px; margin-bottom: 20px; }
        .success { background: #e8f5e9; color: #2e7d32; }
        .error { background: #fbe9e7; color: #c62828; }
        .form-row { display: flex; gap: 10px; margin-bottom: 10px; }
        .form-row > * { flex: 1; }
    </style>
</head>
<body>

<div class="container">
    <h1>👥 用户管理系统</h1>

    <?php if ($message): ?>
        <div class="message success"><?= htmlspecialchars($message) ?></div>
    <?php endif; ?>

    <!-- 添加用户表单 -->
    <div class="card">
        <h2>添加用户</h2>
        <form method="POST">
            <input type="hidden" name="action" value="create">
            <div class="form-row">
                <input type="text" name="name" placeholder="姓名" required>
                <input type="email" name="email" placeholder="邮箱" required>
                <input type="number" name="age" placeholder="年龄" min="0" max="150" required>
                <button type="submit" class="btn-primary">添加</button>
            </div>
        </form>
    </div>

    <!-- 用户列表 -->
    <div class="card">
        <h2>用户列表(共 <?= count($users) ?> 人)</h2>
        <table>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>姓名</th>
                    <th>邮箱</th>
                    <th>年龄</th>
                    <th>注册时间</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($users as $user): ?>
                    <tr>
                        <td><?= $user['id'] ?></td>
                        <td><?= htmlspecialchars($user['name']) ?></td>
                        <td><?= htmlspecialchars($user['email']) ?></td>
                        <td><?= $user['age'] ?></td>
                        <td><?= $user['created_at'] ?></td>
                        <td>
                            <form method="POST" style="display:inline" onsubmit="return confirm('确定删除?')">
                                <input type="hidden" name="action" value="delete">
                                <input type="hidden" name="id" value="<?= $user['id'] ?>">
                                <button type="submit" class="btn-danger btn-sm">删除</button>
                            </form>
                        </td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>

</body>
</html>

代码解析:

  • $_SERVER['REQUEST_METHOD']:获取当前请求的 HTTP 方法(GET、POST 等)。这里用来判断是否是表单提交。
  • $action = $_POST['action'] ?? ''?? 是 NULL 合并运算符,如果 $_POST['action'] 不存在就使用默认值 ''
  • (int)$_POST['age']:从 $_POST 中获取的值都是字符串。对于数字字段,用 (int) 强制转换,确保传给数据库的是正确的类型。
  • htmlspecialchars($message):转义 HTML 特殊字符(<>& 等),防止 XSS 攻击。输出任何用户可能操控的数据时都应该使用这个函数。
  • 参数化查询:所有 SQL 语句都使用 prepare() + execute(),绝对没有直接拼接用户输入。

十、本篇动手练习

练习 1:创建文章表并实现 CRUD

新建 practice6-1.php,在数据库中创建 articles 表(字段:idtitlecontentauthorcreated_at),实现文章的增删改查功能。所有 SQL 操作必须使用参数化查询。

练习 2:搜索功能

新建 practice6-2.php,在用户列表页面增加搜索功能。用户可以输入关键词,查询姓名或邮箱中包含关键词的用户。使用参数化查询实现模糊搜索:WHERE name LIKE :keyword OR email LIKE :keyword。注意:LIKE 的参数需要自己加 % 通配符(如 '%关键词%')。

练习 3:事务模拟转账

新建 practice6-3.php,创建 accounts 表(字段:iduser_namebalance)。插入两条记录,模拟转账操作:从用户 A 转 50 元给用户 B。用事务保证要么全部成功,要么全部回滚。

练习 4:错误处理改进

修改第九节的综合演示代码:

  • 添加 try/catch 捕获 PDO 异常。
  • 在插入用户时检查邮箱是否已存在(先查询,存在则返回友好提示而不是让数据库报唯一约束错误)。
  • 删除用户时,先检查用户是否存在。

十一、本篇小结

这一篇你学会了 PHP 操作 MySQL 数据库的完整技能:

  • mysqli vs PDO:PDO 更现代、支持多种数据库、命名参数更清晰,是新项目的首选。
  • 连接数据库:创建 PDO 实例时需要 DSN、用户名、密码。必须设置异常模式(ERRMODE_EXCEPTION)。用静态变量确保一次请求只创建一个连接。
  • 查询数据query() 执行查询,fetch() 获取单行,fetchAll() 获取所有行,fetchColumn() 获取单个值。
  • 参数化查询永远不要拼接用户输入到 SQL 中。使用 prepare() + execute(),占位符 ? 或命名参数 :name。这是防止 SQL 注入的唯一正确方式。
  • 增删改操作execute() 执行 INSERT/UPDATE/DELETE,lastInsertId() 获取自增 ID,rowCount() 获取影响行数。
  • 事务beginTransaction() 开启,commit() 提交,rollBack() 回滚。用于保证多步操作的一致性。

数据库操作是后端开发中最核心的技能之一。把这一篇的增删改查和参数化查询练熟,你就掌握了 PHP 后端开发的基础能力。下一篇,我们学习如何接收用户输入——GET/POST 参数、表单处理、输入验证和安全防护。

下一篇预告

下一篇——《PHP 表单处理——接收用户输入与安全防护》:GET 和 POST 的区别、超全局变量 $_GET$_POST、文件上传、表单验证、XSS 防御、CSRF 基础。用户输入是 Web 应用中最危险的环节,处理好它是后端开发的基本功。

PHP 零基础入门,每周更新。

© 版权声明
THE END
喜欢就支持一下吧
点赞9 分享
评论 抢沙发

请登录后发表评论

    暂无评论内容