Skip to content

12. 使用 MySQL 数据库管理系统

现在我们将编写使用 MySQL 数据库的 PHP 脚本:

Image

在上面的架构中,PHP 脚本 [1] 并不直接与 DBMS(Database Management System,数据库管理系统)[3] 通信,而是与一个称为 DBMS 驱动程序的中介 [2] 通信。PHP 为这些驱动程序提供了标准接口,即 [PDO] 接口(PHP Data Objects)。该接口由针对各个 DBMS 的不同驱动程序实现:一个用于 MySQL DBMS 的驱动程序,另一个用于 PostgreSQL DBMS 的驱动程序……要切换到不同的 DBMS,只需切换驱动程序:

Image

PDO 驱动程序把 PHP 脚本 [1] 与 DBMS [3, 6] 隔离开来。由于这些驱动程序实现的是标准接口,我们可以预期,从 MySQL DBMS [3] 切换到 PostgreSQL DBMS [6] 时,PHP 脚本 [1] 将保持不变。实际上,这种理想状态并不存在。事实上,为了与 DBMS 通信,PHP 脚本发送 SQL 命令(Structured Query Language,结构化查询语言)。所有 DBMS 都实现了这种语言,但它并不完整。因此,各个 DBMS 都为它添加了专有命令。这是 DBMS 之间不兼容的首要原因。此外,数据库中可使用的数据类型在不同 DBMS 之间可能有所不同。例如,PostgreSQL 支持的数据类型范围比 MySQL 广泛得多。这是第二个不兼容的原因。另一个原因是自动主键(由 DBMS 生成)的处理:几乎每个 DBMS 都有自己的策略。等等……不兼容的原因有很多。

如果你想在从 MySQL [3] 过渡到 PostgreSQL [6] 时避免重写 PHP 脚本 [1],通常需要在 PHP 脚本 [1] 与 PDO 驱动程序 [2, 5] 之间插入一个新层,其目的是消除两个 DBMS 之间的不兼容。不过,在我们将遇到的简单情形中,这个额外的层并无必要。

我们现在将使用 MySQL DBMS。它的 8.4 版本包含在我们于本课程开头安装的 [Laragon] 软件包中。

如果读者对数据库和 SQL 语言的概念还不熟悉,可以参阅文档 [http://sergetahe.com/cours-tutoriels-de-programmation/cours-tutoriel-sql-avec-le-sgbd-firebird/]。该文档使用的是 Firebird DBMS 而不是 MySQL,但它涵盖了数据库和 SQL 语言的基础知识。MySQL 8.4 的文档见 [https://dev.mysql.com/doc/refman/8.4/en/]。

本章的脚本位于 [scripts-console/mysql] 文件夹中:

mysql
├── creation.txt
├── dbpersons.sql
├── mysql-01.php
├── mysql-02.php
├── mysql-03.php
├── mysql-04.php
├── mysql-05.php
├── mysql-06.php
├── mysql-07.php
├── pdo-drivers.php
├── sql.txt
└── sql2.txt

12.1. 创建数据库

现在我们将演示如何使用 [Laragon] 工具创建数据库和 MySQL 用户。

启动之后,[Laragon] 通过其主窗口和菜单(在窗口中或在任务栏图标上右击)进行管理:

  • [Start All] 按钮启动 Apache Web 服务器和 MySQL DBMS。随后 [Laragon] 窗口会显示它们各自处于活动状态,以及各自的监听端口:Apache 为 80,MySQL 为 3306;
  • [Database] 按钮启动 [HeidiSQL] 工具,它包含在 [Laragon] 中,可让你以图形方式管理 MySQL 数据库。你还可以安装 Web 工具 [phpMyAdmin] (Tools / Quick Add 菜单),然后可通过 URL [http://localhost/phpmyadmin] 访问它;
  • [Terminal] 按钮打开一个命令窗口,可在其中直接使用 [Laragon] 的工具(PHP、Composer、MySQL 等)。

PHP 7 课程是用鼠标通过 [phpMyAdmin] 创建数据库及其用户的。这里,我们更愿意把相应的 SQL 命令写在一个文本文件中,称为 SQL 脚本。这样的脚本可以被审阅、修改、与应用程序代码一起保存,并可随意重新运行,以便完全按原样重建数据库。[dbpersons.sql] 脚本如下:

-- creation of the [dbpersons] database and of its user [admpersons]
-- to be executed by the administrator [root] of the MySQL DBMS:
--   mysql -u root < dbpersons.sql

-- the database, with the [utf8mb4] character set (all Unicode characters)
CREATE DATABASE IF NOT EXISTS dbpersons CHARACTER SET utf8mb4;

-- the user [admpersons] with password [nobody], who connects from the local machine
CREATE USER IF NOT EXISTS 'admpersons'@'localhost' IDENTIFIED BY 'nobody';

-- he has all rights on the [dbpersons] database and only on it
GRANT ALL PRIVILEGES ON dbpersons.* TO 'admpersons'@'localhost';

说明

  • 第 1 行:在 SQL 中,以两个短横线开头的行是注释;
  • 第 6 行:如果 [dbpersons] 数据库尚不存在,就创建它。我们将在其中创建一个人员表。字符集 [utf8mb4] 允许你存储所有 Unicode 字符(带重音的字母,以及亚洲字符、表情符号等)。这是 MySQL 8 的默认字符集。PHP 7 课程中使用的名称 [utf8] 指的是 MySQL 中较旧、不完整的字符集(每个字符最多三个字节),现已被弃用;
  • 第 9 行:创建用户 [admpersons] ,密码为 [nobody]。MySQL 用户由其名称和登录所用的机器来标识——这里是 [localhost],即本机。密码 [nobody] 非常弱(很容易被猜到)。在生产环境中,应选择强密码;
  • 第 12 行:用户 [admpersons] 被授予对所有表([dbpersons.*])的全部权限,这些表位于数据库 [dbpersons] 中,而对其他数据库没有任何权限。

该脚本必须由 DBMS 的管理员运行。在 [Laragon] 安装中,这个管理员是用户 [root],他没有密码。在 [Laragon] 终端中打开 [scripts-console/mysql] 文件夹,并使用 [mysql] 命令行客户端运行该脚本:

> mysql -u root < dbpersons.sql
  • [-u root] 选项指定登录 DBMS 所用的用户名。由于 [root] 没有密码,因此不必指定密码(选项 [-p]);
  • 符号 [<] 把文件 [dbpersons.sql] 的内容重定向到客户端 [mysql] 的输入,客户端会逐条执行这些 SQL 命令。一切顺利时,不会显示任何内容。

我们同样可以打开 [dbpersons.sql] 文件,用 [HeidiSQL](File 菜单 / Load a SQL file)或 [SQL] 选项卡中的 [phpMyAdmin] 从那里运行它。通过以 [admpersons] 用户身份登录,来验证该用户的权限。[-e] 选项允许你把 SQL 命令直接传给 [mysql] 客户端;[show grants] 命令显示已登录用户的权限:

1
2
3
4
5
6
7
8
> mysql -u admpersons -pnobody -e "show grants"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------------------------------------------+
| Grants for admpersons@localhost                                   |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `admpersons`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `dbpersons`.* TO `admpersons`@`localhost` |
+-------------------------------------------------------------------+
  • 第 2 行:[mysql] 客户端提示,在命令行上输入的密码([-pnobody],[-p] 后面没有空格)可能被机器上的其他用户看到。只使用 [-p] 选项时,密码通过键盘输入,并且不会显示;
  • 第 7 行:用户 [admpersons] 对数据库 [dbpersons] 拥有全部权限。

现在我们有了:

  • 一个 MySQL 数据库 [dbpersons];
  • 一个名为 [admpersons/nobody] 的用户,他对该数据库拥有完全访问权限。

我们将编写 PHP 脚本来操作该数据库。PHP 提供了各种用于管理数据库的库。我们将使用 [PDO] 库(PHP Data Objects),它位于 PHP 代码与 DBMS 之间:

Image

[PDO] 库使 PHP 脚本独立于所用 DBMS 的具体性质。因此,如上图所示,可以用 PostgreSQL DBMS 取代 MySQL DBMS,而对 PHP 脚本代码的影响极小。每个 PDO 驱动程序都是 PHP 的一个必须启用的扩展。下面的 [pdo-drivers.php] 脚本显示可用的驱动程序:

<?php

// 严格遵守所声明的函数参数和结果的类型
declare(strict_types=1);

// 此 PHP 安装中可用的 PDO 驱动
print "Available PDO drivers: " . implode(", ", PDO::getAvailableDrivers()) . "\n";
// 对应的 PHP 扩展
foreach (["pdo_mysql", "pdo_pgsql", "pdo_sqlite"] as $extension) {
    print "extension [$extension]: " . (extension_loaded($extension) ? "loaded" : "not loaded") . "\n";
}
  • 第 7 行:静态方法 [getAvailableDrivers] (属于类 [PDO] )返回可用的 PDO 驱动程序列表;
  • 第 10 行:函数 [extension_loaded] 指示某个 PHP 扩展是否已加载。MySQL、PostgreSQL 和 SQLite 的 PDO 驱动程序分别是扩展 [pdo_mysql]、[pdo_pgsql], 和 [pdo_sqlite]。

结果:

1
2
3
4
5
> php pdo-drivers.php
Available PDO drivers: mysql, pgsql, sqlite
extension [pdo_mysql]: loaded
extension [pdo_pgsql]: loaded
extension [pdo_sqlite]: loaded

在这个安装中,三个驱动程序都处于启用状态。本章只需要 [mysql] 驱动程序。如果缺少某个驱动程序,请在 [Laragon] 菜单中启用它:PHP / Extensions,然后点击所需的扩展,例如 [pdo_mysql]。启用扩展的另一种方法是直接编辑配置 PHP 的 [php.ini] 文件([Laragon] 的 PHP / php.ini 菜单)。扩展在其中通过如下格式的一行来启用:

extension=pdo_mysql

当该行被分号注释掉([;extension=pdo_pgsql])时,扩展即被禁用。修改 [php.ini] 文件后,必须重新启动 [Laragon] 服务([Reload] 按钮),Apache 才会应用这些更改。而控制台脚本每次运行时都会重新读取 [php.ini] 文件。

12.2. 连接 MySQL 数据库

要连接 DBMS,必须创建一个 [PDO] 对象。你可以使用 [PDO] 类的构造函数,或者从 PHP 8.4 起使用它的静态方法 [connect]。两者接受相同的参数:

$connection = new PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null);
$connection = PDO::connect(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null);

这些参数的含义如下:

参数
含义
[$dsn]
(Data Source Name,数据源名称)一个字符串,指明 DBMS 的性质及其在网络上的位置。字符串 ["mysql:host=localhost"] 表示这是在本机上运行的 MySQL DBMS。该字符串还可以包含其他以分号分隔的参数,例如 DBMS 的监听端口、要连接的数据库名称,以及通信所用的字符集:["mysql:host=localhost;port=3306;dbname=dbpersons;charset=utf8mb4"]
[$username]
登录用户的用户名
[$password]
其密码
[$options]
DBMS 驱动程序的选项数组

只有第一个参数是必需的。以这种方式创建的对象随后将作为对所连接数据库执行的所有操作的基础。如果无法创建该对象,就会抛出 [PDOException] 异常。

下面是一个 [mysql-01.php] 连接的示例:

<?php

// 严格遵守所声明的函数参数和结果的类型
declare(strict_types=1);

// 连接到本地 MySQL 数据库
// 用户的身份是 (admpersons, nobody)
const ID = "admpersons";
const PWD = "nobody";
const HOST = "localhost";

try {
    // 连接
    $connection = PDO::connect("mysql:host=" . HOST, ID, PWD);
    print "Successful connection\n";
    // 所得到对象的类
    print "The connection is an object of type [" . $connection::class . "]\n";
    // 关闭连接
    $connection = null;
} catch (PDOException $ex) {
    print "Error: " . $ex->getMessage() . "\n";
    exit(1);
}

说明

  • 第 8–10 行:连接参数;
  • 第 14 行:连接到 DBMS。这里使用 [connect] 方法,参数如下:
    • 一个字符串,指明 DBMS 的类型及其位置。字符串 ["mysql:host=localhost"] 表示这是在本机上运行的 MySQL DBMS。没有指定端口,因此默认使用端口 3306。数据库名称同样没有指定。于是将建立到 MySQL DBMS 的连接,稍后再选择特定的数据库;
    • 用户标识;
    • 其密码;
  • 第 17 行:[$connection::class] 是对象 [$connection] 的类名。从 PHP 8.4 起,每个 PDO 驱动程序都有自己的类,派生自类 [PDO]:[Pdo\Mysql] 用于 MySQL,[Pdo\Pgsql] 用于 PostgreSQL,[Pdo\Sqlite] 用于 SQLite。[PDO::connect] 方法创建与 DSN 对应的类的对象。使用 [new PDO(...)] 时,我们将得到 [PDO] 类本身的对象,它的工作方式相同,但无法访问驱动程序特有的方法。由于我们只会使用所有驱动程序共有的方法,因此两种写法都可以接受;
  • 第 19 行:通过移除对最初创建的 PDO 对象的最后一个引用来关闭连接。这在脚本结束时会自动发生;
  • 第 20 行:到 DBMS 的连接可能失败。这时会抛出 [PDOException] 异常。该异常派生自 PHP 异常 [RuntimeException];
  • 第 21 行:显示异常的错误消息;
  • 第 22 行:脚本以非零退出代码终止,向任何调用它的程序表明失败。

结果:

1
2
3
> php mysql-01.php
Successful connection
The connection is an object of type [Pdo\Mysql]

我们把常量 [PWD] 设为错误的密码,重新运行脚本。结果如下:

> php mysql-01.php
Error: SQLSTATE[HY000] [1045] Access denied for user 'admpersons'@'localhost' (using password: YES)

该消息包含三项信息:代码 SQLSTATE [HY000],这是所有 DBMS 通用的标准化错误代码(HY000 = 一般错误);MySQL 特有的错误代码 [1045];以及 MySQL 的消息。

12.3. 创建表

脚本 [mysql-02.php] 演示了如何在数据库中创建表:

<?php

// 严格遵守所声明的函数参数和结果的类型
declare(strict_types=1);

// 数据库的标识
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// 用户的凭据
const ID = "admpersons";
const PWD = "nobody";

try {
    // 连接到 MySQL 数据库
    $connection = PDO::connect(DSN, ID, PWD);
    // 如果 [persons] 表存在,则将其删除
    $sql = "drop table if exists persons";
    $connection->exec($sql);
    // 创建 [persons] 表
    $sql = "create table persons (first_name varchar(30) not null, last_name varchar(30) not null, "
        . "age integer not null, primary key(last_name, first_name))";
    $connection->exec($sql);
    print "The [persons] table has been created\n";
} catch (PDOException $ex) {
    // 显示错误
    print "Error: " . $ex->getMessage() . "\n";
} finally {
    // 必要时断开连接
    $connection = null;
}
// 结束
print "Done\n";

说明

  • 第 7 行:这一次,DSN 指向数据库 [dbpersons] ([dbname=dbpersons])。参数 [charset=utf8mb4] 告诉 DBMS,与它交换的字符串以 UTF-8 编码,这是我们的(PHP)脚本所使用的编码。这确保带重音的字符能被正确存入数据库并被正确取回;
  • 第 14 行:连接到数据库。这始终是第一步。连接的结果是一个 [PDO] 对象,数据库操作将通过它来执行;
  • 第 16 行:SQL 命令 [drop table] 删除表 [persons] (位于数据库 [dbpersons] 中)。子句 [if exists] 可避免在表 [persons] 不存在时出错;
  • 第 17 行:在数据库 [dbpersons] 上使用方法 [PDO::exec] 执行前一条 SQL 命令。这次执行可能触发 [PDOException], 它将被 [catch] 子句拦截;
  • 第 19–20 行:这条 SQL 命令创建一个名为 [persons] 的表。表包含行和列。列构成所谓的表结构,行构成表的内容。一个数据库可以包含一个或多个表。这里,表 [persons] 将有三列:
    • first_name:一个人的名,字符串,最多 30 个字符;
    • last_name:同一个人的姓,字符串,最多 30 个字符;
    • age:这个人的年龄,整数;
    • 列上的 [not null] 属性要求该列必须有值。如果不提供值,就会导致 [PDOException] 错误;
    • [primary key(last_name, first_name)] 为表 [persons] 定义主键。主键对表中的每一行都具有唯一的值。这里,主键由该行的列 [last_name] 和 [first_name] 连接而成。这个约束确保表中不会有两个名和姓都相同的人,也就是两个全名相同的人。创建与表中另一个人同名的人会触发 [PDOException];
  • 第 23–25 行:如果发生 [PDOException] 错误 ,就显示相应的错误消息;
  • 第 26–28 行:[finally] 子句在任何情况下都会执行,无论是否发生异常,用于关闭到数据库的连接。

发生错误时,用 [PDO] 对象执行的 SQL 操作,可能返回一个特定值(通常是布尔值 [false])而不报告任何信息,也可能抛出异常。这种行为由连接的 [PDO::ATTR_ERRMODE] 属性控制。在 PHP 7 中,默认模式是第一种:错误是静默的,PHP 7 课程的做法是用下面的语句请求第二种模式:

$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

从 PHP 8.0 起,异常模式是默认模式:任何 DBMS 错误都会触发 [PDOException]。因此这条指令不再必要,我们以后也不再写它。这也是我们写 [drop table if exists] 的原因:在 PHP 7 中,对不存在的表执行 [drop table persons] 命令会静默失败;在 PHP 8 中,它会抛出异常。

结果:

1
2
3
> php mysql-02.php
The [persons] table has been created
Done

如果脚本运行时没有错误,你可以使用 [HeidiSQL]、[phpMyAdmin],或者更简单地使用 [mysql] 客户端来验证表的存在及其结构。SQL 命令 [describe] 显示表的结构:

1
2
3
4
5
6
7
8
9
> mysql -u admpersons -pnobody dbpersons -e "describe persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(30) | NO   | PRI | NULL    |       |
| last_name  | varchar(30) | NO   | PRI | NULL    |       |
| age        | int         | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
  • 第 6–8 行:表的三列及其类型(Type 列);
  • Null 列:三列都不能为空;
  • Key 列:列 [first_name] 和 [last_name] 是主键(PRI)的组成部分。

SQL 查询 [show index] 显示表的索引。索引使你能够比顺序扫描表的各行更快地找到具有特定索引值的行。主键始终属于索引,但索引不一定是主键:

1
2
3
4
5
6
7
8
> mysql -u admpersons -pnobody dbpersons -e "show index from persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| persons |          0 | PRIMARY  |            1 | last_name   | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| persons |          0 | PRIMARY  |            2 | first_name  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  • 这里的索引就是主键(Key_name 列 = PRIMARY);
  • 它由每行的 [last_name, first_name] 列按此顺序组成(Seq_in_index 列和 Column_name 列)。

现在,我们来看看如果分别在数据库名、用户名和密码中引入错误会发生什么。

如果我们输入一个不存在的数据库名([dbname=dbpersons2]):

1
2
3
> php mysql-02.php
Error: SQLSTATE[HY000] [1044] Access denied for user 'admpersons'@'localhost' to database 'dbpersons2'
Done

如果我们输入一个不存在的用户名:

1
2
3
> php mysql-02.php
Error: SQLSTATE[HY000] [1045] Access denied for user 'admpersons2'@'localhost' (using password: YES)
Done

如果你输入了错误的密码:

1
2
3
> php mysql-02.php
Error: SQLSTATE[HY000] [1045] Access denied for user 'admpersons'@'localhost' (using password: YES)
Done

请注意,MySQL 不会指出是用户名还是密码不正确:这是一项安全措施,目的是不给任何试图猜测凭据的人提供线索。同样,用户 [admpersons] 也无从得知数据库 [dbpersons2] 是否存在:他只知道自己无法访问它。

12.4. 填充表

我们将编写一个 PHP 脚本,执行下面这个文本文件 [creation.txt] 中的 SQL 命令:

1
2
3
4
5
6
7
8
9
drop table if exists persons
create table persons (first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, primary key (last_name,first_name))
insert into persons (first_name, last_name, age) values('Paul','Langevin',48)
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70)
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70)
insert into persons (first_name, last_name, age) values ('Pierre','Nicazou',35)
insert into persons (first_name, last_name, age) values ('Géraldine','Colou',26)
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56)
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56)

说明

  • SQL 语言(Structured Query Language,结构化查询语言)的 SQL 命令不区分大小写(大写和小写字母);
  • 第 1 行:如果表 [persons] 存在,就删除它;
  • 第 2 行:创建带有三列(first_name、last_name、age)和主键(last_name, first_name)的表 [persons] ;
  • 第 3–9 行:向表 [persons] 中插入 7 行;
  • 第 5 行:这条插入语句应当失败,因为它尝试的插入与上一行相同。主键约束应当阻止这次插入:不能有两个名和姓都相同的人;
  • 第 9 行:这条插入语句也应当因同样的原因失败;
  • 第 7 行:名字 [Géraldine] 包含一个带重音的字符。文件 [creation.txt] 与课程中的所有文件一样,采用 UTF-8 编码:这是 [VS Code] 的默认编码,显示在其状态栏中。PHP 7 课程在文件中添加了 MySQL 特有的命令,以告知 DBMS 所接收字符的编码。这已不再必要:编码由 DSN 的 [charset=utf8mb4] 参数一次性设定。

负责执行这个文本文件中 SQL 命令的 PHP 脚本如下:[mysql-03.php]:

<?php

// 严格遵守所声明的函数参数和结果的类型
declare(strict_types=1);

// 数据库的标识
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// 用户的凭据
const ID = "admpersons";
const PWD = "nobody";
// 要执行的 SQL 命令的文本文件的标识
const SQL_COMMANDS_FILENAME = __DIR__ . "/creation.txt";

// 打开到 MySQL 数据库的连接
try {
    $connection = PDO::connect(DSN, ID, PWD);
} catch (PDOException $ex) {
    // 显示错误
    print "Error: " . $ex->getMessage() . "\n";
    exit(1);
}
// 执行 SQL 语句文件
$errors = executeCommands($connection, SQL_COMMANDS_FILENAME, tracking: true, stop: false);
// 关闭连接
$connection = null;
// 显示错误
printf("\n-----------------------\nThere were %d error(s)\n", count($errors));
foreach ($errors as $error) {
    print "$error\n";
}
// 结束了
print "Done\n";

// ---------------------------------------------------------------------------------
function executeCommands(PDO $connection, string $sqlFileName, bool $tracking = false, bool $stop = true): array
{
    // 使用连接 $connection
    // 执行文本文件 $sqlFileName 中包含的 SQL 命令
    // 该文件是要执行的 SQL 命令文件,每行一条
    // 如果 $tracking 为 true,则显示每条 SQL 语句的执行及其成功或失败
    // 如果 $stop 为 true,则函数在遇到第 1 个错误时停止,否则执行所有 SQL 命令
    // 函数返回错误消息数组(没有错误时为空)

    // 我们把文件的行放入数组,去掉行尾标记和空行
    $queries = is_readable($sqlFileName)
        ? file($sqlFileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES)
        : false;
    // 出错?
    if ($queries === false) {
        return ["The file [$sqlFileName] does not exist or could not be read"];
    }
    // 我们逐条执行查询 - 开始时没有错误
    $errors = [];
    foreach ($queries as $query) {
        // 我们去掉行首和行尾的空格
        $query = trim($query);
        // 查询为空?
        if ($query === "") {
            // 我们转到下一条查询
            continue;
        }
        try {
            // 执行查询 - 出错时抛出异常
            $connection->exec($query);
            // 是否在屏幕上跟踪?
            if ($tracking) {
                print "$query: Successful execution\n";
            }
        } catch (PDOException $ex) {
            // 发生了错误
            addError($errors, $query, $ex->getMessage(), $tracking);
            // 是否停止?
            if ($stop) {
                break;
            }
        }
    }
    // 结果
    return $errors;
}

function addError(array &$errors, string $query, string $msg, bool $tracking): void
{
    // 我们添加一条错误消息
    $msg = "$query: Error ($msg)";
    $errors[] = $msg;
    // 是否在屏幕上跟踪?
    if ($tracking) {
        print "$msg\n";
    }
}

说明

  • 函数 [executeCommands] (第 35–80 行)负责执行它在文本文件 [$sqlFileName] (参数 2)中找到的 SQL 命令。为了执行它们,它使用与 MySQL 服务器已打开的连接 [$connection] (参数 1)。第三个参数 [$tracking], 是控制屏幕输出的布尔值:设为 [true] 时,所执行的 SQL 命令会连同其成功或失败的情况一起显示在屏幕上;否则,SQL 命令的执行是静默的。第四个参数 [$stop], 控制 SQL 命令失败时该怎么做:如果为 [true],则表示必须停止执行 SQL 命令;否则继续执行。函数 [executeCommands] 返回一个错误消息数组;如果没有发生错误,该数组为空;
  • 第 12 行:[creation.txt] 文件的路径由常量 [__DIR__] 构造,该常量是脚本所在的目录。这确保无论从哪个目录运行脚本,它都能找到自己的文件;
  • 第 15–20 行:打开到 MySQL 数据库 [dbpersons] 的连接。如果连接失败,就显示错误消息并终止脚本;
  • 第 23 行:调用函数 [executeCommands] 来执行 SQL 命令文件。向它传递一个已打开的连接。最后两个实参按名称传递([tracking: true, stop: false]):这些是 命名参数 (PHP 8.0)。调用 [executeCommands($connection, SQL_COMMANDS_FILENAME, true, false)] 与之等价但可读性较差:不看函数签名,就不清楚 [true] 和 [false] 是什么意思;
  • 第 25 行:关闭连接;
  • 第 27–30 行:显示函数 [executeCommands] 返回的错误;
  • 第 45–47 行:使用 [file] 函数把 SQL 命令放入数组 [$queries] 中。 [FILE_IGNORE_NEW_LINES] 选项会删除所读每一行的行尾字符,而 [FILE_SKIP_EMPTY_LINES] 选项会忽略空行。这两个选项通过 [|] 运算符(按位或)组合。如果文件不可读,就不会调用 [file](那会发出警告),而 [$queries] 等于 [false];
  • 第 49–51 行:如果无法读取 SQL 命令文件,就返回一个只包含一条消息的错误数组;
  • 第 53 行:错误累积在数组 [$errors] 中;
  • 第 54 行:我们遍历所有查询;
  • 第 56 行:函数 [trim] 删除 SQL 命令文本前后的空白。所谓“空白”,指空格字符、制表符 \t、回车符 \r、换行符 \n 等。因此只包含空白的行会变为空:它被忽略(第 60 行);
  • 第 64 行:把 SQL 命令发送到 MySQL 服务器。如果执行失败,方法 [PDO::exec] 会抛出异常。如果执行成功,并且参数 [$tracking] 设为 [true],则在屏幕上显示该 SQL 命令;
  • 第 71 行:如果发生错误,错误消息被添加到错误数组中;
  • 第 73–75 行:如果参数 [$stop] 设为 [true],则使用语句 [break] 退出循环;
  • 第 79 行:执行完所有 SQL 命令后,返回错误数组 [$errors]。

函数 [addError] (第 82–91 行)向错误数组 [$errors] 中添加一个错误。它有 4 个参数:

  • 参数 [$errors] 按引用传递([&$errors])。这是因为我们想修改作为参数传入的数组,而不是它的副本;
  • 参数 [$query] 是失败命令的 SQL 文本;
  • 参数 [$msg] 是与失败命令相关的错误消息;
  • 布尔值 [$tracking] 指示是否应在控制台上显示错误消息([true])或不显示([false])。

请注意,函数 [executeCommands] 和 [addError] 是在使用它们的代码之后定义的。这是可行的,因为 PHP 在开始执行脚本之前,就会识别出该脚本顶层定义的所有函数。

屏幕输出:

> php mysql-03.php
drop table if exists persons: Successful execution
create table persons (first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, primary key (last_name,first_name)): Successful execution
insert into persons (first_name, last_name, age) values('Paul','Langevin',48): Successful execution
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70): Successful execution
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'persons.PRIMARY')
insert into persons (first_name, last_name, age) values ('Pierre','Nicazou',35): Successful execution
insert into persons (first_name, last_name, age) values ('Géraldine','Colou',26): Successful execution
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56): Successful execution
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'persons.PRIMARY')

-----------------------
There were 2 error(s)
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'persons.PRIMARY')
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'persons.PRIMARY')
Done
  • 第 6 行和第 10 行:两次同名插入都失败了:主键 'Lefur-Sylvie'(以及相应的 'Girond-Paulette')已存在(Duplicate entry)。代码 SQLSTATE [23000] 表示违反了完整性约束;代码 [1062] 是 MySQL 的错误代码;
  • 第 12–16 行:错误摘要。

使用客户端 [mysql] 可以看到已执行的插入:

> mysql -u admpersons -pnobody dbpersons -e "select * from persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-----------+-----+
| first_name | last_name | age |
+------------+-----------+-----+
| Géraldine  | Colou     |  26 |
| Paulette   | Girond    |  56 |
| Paul       | Langevin  |  48 |
| Sylvie     | Lefur     |  70 |
| Pierre     | Nicazou   |  35 |
+------------+-----------+-----+

请注意,行是按主键(姓、名)的顺序返回的,这并不是插入的顺序。在 SQL 中,由 [select] 返回的行,其顺序只有在使用 [order by] 子句指定时才能得到保证。

12.5. 执行任意 SQL 命令

下面的脚本演示如何执行下面这个文本文件 [sql.txt] 中的 SQL 命令:

1
2
3
4
5
6
7
8
9
select * from persons
select last_name,first_name from persons order by last_name asc, first_name desc
select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc
insert into persons values('Josette','Bruneau',46)
update persons set age=47 where last_name='Bruneau'
select * from persons where last_name='Bruneau'
delete from persons where last_name='Bruneau'
select * from persons where last_name='Bruneau'
xselect * from persons where last_name='Bruneau'

在这些 SQL 命令中,有从数据库返回结果的 [select] 命令;有修改数据库但不返回任何结果的 [insert]、[update]、[delete], 最后还有像最后一条那样的错误命令([xselect])。脚本 [mysql-04.php] 基于脚本 [mysql-03.php],只是更改了 SQL 命令文件的名称([__DIR__ . "/sql.txt"])。只修改了函数 [executeCommands] ,并新增了函数 [displayInfo] :

function executeCommands(PDO $connection, string $sqlFileName, bool $tracking = false, bool $stop = true): array
{
    // 使用连接 $connection
    // 执行文本文件 $sqlFileName 中包含的 SQL 命令
    // 该文件是要执行的 SQL 命令文件,每行一条
    // 如果 $tracking 为 true,则显示每条 SQL 语句的执行及其成功或失败
    // 如果 $stop 为 true,则函数在遇到第 1 个错误时停止,否则执行所有 SQL 命令
    // 函数返回错误消息数组(没有错误时为空)

    // 我们把文件的行放入数组,去掉行尾标记和空行
    $queries = is_readable($sqlFileName)
        ? file($sqlFileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES)
        : false;
    // 出错?
    if ($queries === false) {
        return ["The file [$sqlFileName] does not exist or could not be read"];
    }
    // 我们逐条执行查询 - 开始时没有错误
    $errors = [];
    foreach ($queries as $query) {
        // 我们去掉行首和行尾的空格
        $query = trim($query);
        // 查询为空?
        if ($query === "") {
            // 我们转到下一条查询
            continue;
        }
        // SQL 命令的名称:查询的第一个单词,小写
        $command = strtolower(explode(" ", $query, 2)[0]);
        try {
            // 执行查询 - 出错时抛出异常
            // SELECT 语句返回行,其他语句返回被修改的行数
            $result = $command === "select"
                ? $connection->query($query, PDO::FETCH_NUM)
                : $connection->exec($query);
            // 是否在屏幕上跟踪?
            if ($tracking) {
                print "[$query]: Successful execution\n";
            }
            // 我们显示执行结果
            displayInfo($command, $result);
        } catch (PDOException $ex) {
            // 发生了错误
            addError($errors, $query, $ex->getMessage(), $tracking);
            // 是否停止?
            if ($stop) {
                break;
            }
        }
    }
    // 结果
    return $errors;
}

function addError(array &$errors, string $query, string $msg, bool $tracking): void
{
    // 我们添加一条错误消息
    $msg = "[$query]: Error ($msg)";
    $errors[] = $msg;
    // 是否在屏幕上跟踪?
    if ($tracking) {
        print "$msg\n";
    }
}

// ---------------------------------------------------------------------------------
function displayInfo(string $command, PDOStatement|int $result): void
{
    // 显示 SQL 查询的结果 $result
    if ($result instanceof PDOStatement) {
        // select 的情形:我们显示列名
        $columns = [];
        for ($i = 0; $i < $result->columnCount(); $i++) {
            $columns[] = $result->getColumnMeta($i)["name"];
        }
        $title = implode(",", $columns);
        print "$title\n";
        // 与标题等长的分隔线
        print str_repeat("-", mb_strlen($title)) . "\n";
        // 结果的行:每一行是一个列值数组
        foreach ($result as $row) {
            print implode(",", $row) . "\n";
        }
        return;
    }
    // insert、update、delete 语句的情形:$result 是被修改的行数
    if (in_array($command, ["insert", "update", "delete"], true)) {
        print " $result row(s) has (have) been modified\n";
    }
}

说明

  • 第 35–87 行:[executeCommands] 函数略有修改:[select] 命令的执行方式与其他 SQL 命令不同。这条命令是唯一返回表作为结果的命令,也就是来自数据库的一组行和列;
  • 第 63 行:我们分离出 SQL 命令的第一个单词。函数 [explode] 在空格处把查询最多拆分为 2 部分(第 3 个参数);第一段([[0]])是命令名,由函数 [strtolower] 转换为小写。PHP 7 课程使用的是正则表达式;
  • 第 67–69 行:如果 SQL 命令是 [select],则使用 [PDO::query] 方法;否则使用 [PDO::exec] 方法执行该 SQL 命令。两种情况下,如果执行失败,都会抛出异常,并由 [catch] 子句捕获。如果执行成功,则由 [displayInfo] 函数(第 75 行)显示其结果:
    • [query] 方法返回一个 [PDOStatement] 类型的对象,通过它可以访问 [select] 返回的行。它的第二个参数 [PDO::FETCH_NUM] 指定每一行以按列号 0、1、2……索引的数组形式给出;
    • [exec] 方法返回一个整数:即该 SQL 命令所修改的行数;
  • 第 101–124 行:[displayInfo] 函数显示 SQL 命令执行结果的相关信息。参数 [$result] 的类型为 [PDOStatement|int]:这是一个联合类型(PHP 8.0),可接受 [PDOStatement] 对象或整数;
  • 第 104 行:处理 [select] 的情形。[instanceof] 运算符检查 [$result] 对象的类型;
  • 第 107 行:[PDOStatement::columnCount] 方法返回 [select] 结果表中的列数;
  • 第 108 行:[PDOStatement::getColumnMeta] 方法返回一个数组,其中包含 [select] 结果表中第 [$i] 列的信息。在该数组中,与键 'name' 关联的值就是列名。列名被收集到数组 [$columns] 中;
  • 第 110 行:[implode] 函数将列名连接起来,以逗号分隔;
  • 第 113 行:使用 [str_repeat] 函数构造与标题等长的分隔线。[mb_strlen] 函数统计 UTF-8 字符串中的字符数;[strlen] 函数统计的是字节数,带重音的字符占两个字节;
  • 第 115–117 行:[PDOStatement] 类型的对象可以用 [foreach] 循环遍历。每次迭代得到的元素是 [select] 结果表中的一行,形式为包含该行各列值的数组。这些值以逗号分隔显示;
  • 第 121–123 行:执行 [insert]、[update] 或 [delete] 命令的结果是该命令所修改的行数。PHP 7 课程在此处使用了 [switch] 语句,其中一个子句被错误地写成了 [case "delete";] ,用分号代替了冒号。PHP 容忍了这种写法;从 PHP 8.5 起,这种语法已被弃用。

屏幕输出:

> php mysql-04.php
[select * from persons]: Successful execution
first_name,last_name,age
------------------------
Géraldine,Colou,26
Paulette,Girond,56
Paul,Langevin,48
Sylvie,Lefur,70
Pierre,Nicazou,35
[select last_name,first_name from persons order by last_name asc, first_name desc]: Successful execution
last_name,first_name
--------------------
Colou,Géraldine
Girond,Paulette
Langevin,Paul
Lefur,Sylvie
Nicazou,Pierre
[select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc]: Successful execution
first_name,last_name,age
------------------------
Pierre,Nicazou,35
Géraldine,Colou,26
[insert into persons values('Josette','Bruneau',46)]: Successful execution
 1 row(s) has (have) been modified
[update persons set age=47 where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
Josette,Bruneau,47
[delete from persons where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from persons where last_name='Bruneau'' at line 1)

-----------------------
There were 1 error(s)
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from persons where last_name='Bruneau'' at line 1)
Done
  • 第 33–35 行:最后一条 [select] 没有返回任何行:人员 [Bruneau] 已被前一条 [delete] 命令删除;
  • 第 36 行:命令 [xselect] 不存在。代码 SQLSTATE [42000] 表示语法错误或访问冲突;MySQL 代码 [1064] 表示语法错误。

12.6. 使用预先准备的 SQL 命令

12.6.1. 示例 1

我们来看下面的脚本 [mysql-05.php]:

<?php

// 严格遵守所声明的函数参数和结果的类型
declare(strict_types=1);

// 数据库的标识
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// 用户的凭据
const ID = "admpersons";
const PWD = "nobody";

try {
    // 连接到 MySQL 数据库
    $connection = PDO::connect(DSN, ID, PWD);
    // 我们清空 persons 表
    $connection->exec("delete from persons");
    // 一个人员列表
    $persons = [
        ["last_name" => "Langevin", "first_name" => "Paul", "age" => 47],
        ["last_name" => "Lefur", "first_name" => "Sylvie", "age" => 28],
    ];
    // 我们将用预处理 SQL 语句把这些人放入数据库
    $statement = $connection->prepare("insert into persons (last_name, first_name, age) values (:last_name, :first_name, :age)");
    foreach ($persons as $person) {
        // 参数 :last_name、:first_name、:age 的值取自关联数组
        $statement->execute($person);
        print "{$statement->rowCount()} row inserted: {$person["first_name"]} {$person["last_name"]}\n";
    }
    // 第三个人,使用 [bindValue] 方法
    $statement->bindValue("last_name", "Nicazou");
    $statement->bindValue("first_name", "Pierre");
    $statement->bindValue("age", 35, PDO::PARAM_INT);
    $statement->execute();
    print "{$statement->rowCount()} row inserted: Pierre Nicazou\n";
} catch (PDOException $ex) {
    // 显示错误
    print "Error: " . $ex->getMessage() . "\n";
} finally {
    // 关闭连接
    $connection = null;
}
// 结束了
print "Done\n";

说明

这里我们关注的是向 [dbpersons] 数据库的 people 表中插入人员的那些行。

  • 第 16 行:不带 [where] 子句的 [delete] SQL 命令会删除表中的所有行;
  • 第 23 行:“准备”一条带参数的 SQL 命令。参数以冒号开头:[:last_name], [:first_name], [:age]。要“准备”一条 SQL 命令,使用 [PDO::prepare] 方法。结果是一个 [PDOStatement] 类型的对象。“准备”不等于执行:数据库中不会有任何修改;
  • 第 26 行:使用 [PDOStatement::execute] 方法执行预处理语句。为此,必须为参数 [:last_name], [:first_name], and [:age]赋值。有多种方式可以做到这一点。这里我们使用一个关联数组,其键是预处理语句各参数的名称,并将它传给 [PDOStatement::execute] 方法;
  • 第 27 行:[PDOStatement::rowCount] 方法返回预处理语句最近一次执行所修改的行数。在用双引号括起的字符串中,方法调用被花括号 [{...}] 包围,以便 PHP 对其求值。数组元素 [{$person["first_name"]}] 也是如此;
  • 第 30–33 行:另一种方式是在不带任何参数调用 [execute] 之前,使用 [PDOStatement::bindValue] 方法为参数赋值。[bindValue] 的第三个可选参数指定值的 SQL 类型([PDO::PARAM_INT] 表示整数,[PDO::PARAM_STR]——默认值——表示字符串)。缺点是每个参数都需要一条语句。关联数组方式通常更方便。

这里执行插入所用的方法:

  • 一个预处理语句;
  • 对该预处理语句执行 n 次;

在执行时间上比执行 n 条不同的 SQL 语句更高效。更重要的是,它还有另一个决定性的优点:参数值与 SQL 语句文本是分开传给 DBMS 的,因此它们无法改变命令的含义。如果 SQL 命令是通过拼接构造的——例如 ["insert into persons values ('$first_name', '$last_name', $age)"],那么含有撇号的名字(“Jean-Michel d'Ormesson”)会导致语法错误,恶意用户还可以夹带自己选择的 SQL 命令:这就是所谓的 SQL 注入攻击。因此,只要查询中含有来自脚本之外的值,就应当使用预处理查询。它们可用于 SQL 命令 [select]、[update]、[delete] 和 [insert]。对于 SQL 命令 [select],在用 [PDOStatement::execute] 执行之后,使用 [PDOStatement::fetch] 或 [PDOStatement::fetchAll] 方法获取结果行。

结果:

1
2
3
4
5
> php mysql-05.php
1 row inserted: Paul Langevin
1 row inserted: Sylvie Lefur
1 row inserted: Pierre Nicazou
Done

12.6.2. 示例 2

下面的脚本 [mysql-06.php],演示了如何对 SQL [select] 操作使用预处理语句,以及获取该操作所返回行的各种方式:

<?php

// 严格遵守所声明的函数参数和结果的类型
declare(strict_types=1);

// 数据库的标识
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// 用户的凭据
const ID = "admpersons";
const PWD = "nobody";

// 一个类,其属性与 [persons] 表的列同名
class Person
{
    private string $last_name;
    private string $first_name;
    private int $age;

    public function __toString(): string
    {
        return "Person[$this->last_name,$this->first_name,$this->age]";
    }
}

// 由 [persons] 表的一行构建的不可变类
final readonly class ImmutablePerson
{
    public function __construct(
        public string $last_name,
        public string $first_name,
        public int $age,
    ) {
    }
}

try {
    // 连接到 MySQL 数据库 - 读取的行默认是关联数组
    $connection = PDO::connect(DSN, ID, PWD, [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
    // 我们清空 persons 表
    $connection->exec("delete from persons");
    // 我们把 10 个人放入数据库
    $statement = $connection->prepare("insert into persons (last_name, first_name, age) values (:last_name, :first_name, :age)");
    for ($i = 0; $i < 10; $i++) {
        $statement->execute(["last_name" => "lastName$i", "first_name" => "firstName$i", "age" => $i * 10]);
    }
    // 我们查询数据库
    $statement = $connection->prepare("select last_name, first_name, age from persons order by last_name");
    $statement->execute();
    // 第 1 行:连接的默认模式(关联数组)
    $row = $statement->fetch();
    var_dump($row);
    // 第 2 行:同时以列名和列序号为索引的数组
    $row = $statement->fetch(PDO::FETCH_BOTH);
    var_dump($row);
    // 第 3 行:[stdClass] 对象
    $row = $statement->fetch(PDO::FETCH_OBJ);
    var_dump($row);
    // 第 4 行:[Person] 对象
    $statement->setFetchMode(PDO::FETCH_CLASS, Person::class);
    $row = $statement->fetch();
    var_dump($row);
    // 顺序读取其余的行
    while ($person = $statement->fetch()) {
        print "$person\n";
    }
    // 一次取回所有行,每一行转换为一个 [ImmutablePerson] 对象
    $statement = $connection->prepare("select last_name, first_name, age from persons where age >= :age order by last_name");
    $statement->execute(["age" => 70]);
    $persons = array_map(fn(array $row): ImmutablePerson => new ImmutablePerson(...$row), $statement->fetchAll());
    var_dump($persons);
} catch (PDOException $ex) {
    // 显示错误
    print "Error: " . $ex->getMessage() . "\n";
} finally {
    // 关闭连接
    $connection = null;
}
// 结束了
print "Done\n";

说明

  • 第 13–23 行:一个名为 [Person] 的类,其带类型的私有属性与 [persons] 表的列同名;
  • 第 26–34 行:一个不可变类 [ImmutablePerson] ,其构造函数的参数以 [persons] 表的列命名;
  • 第 38 行:[PDO::connect] 的第四个参数是连接选项数组。选项 [PDO::ATTR_DEFAULT_FETCH_MODE] 决定读取的行默认以何种形式给出:这里是 [PDO::FETCH_ASSOC],即使用以列名为索引的关联数组。没有该选项时,默认模式是 [PDO::FETCH_BOTH] (见下文);
  • 第 43–45 行:使用预处理查询向 [persons] 表中插入 10 行。值数组直接在 [execute] 的调用中构造;
  • 第 47–48 行:“准备”一条 SQL [select] 命令,然后执行;
  • 第 50 行:使用 [PDOStatement::fetch] 方法获取已执行的 SQL [select] 操作结果中的下一行——这里是第一行。[fetch] 方法可以以多种方式返回行,脚本演示了其中几种。不带任何参数时,它使用连接的默认模式,这里即关联数组。请注意,age 是以整数形式给出的([int(0)])。这是 PHP 8.1 的新特性:此前 MySQL 驱动将所有值都以字符串形式给出(PHP 7 中为 [string(1) "0"] )。[var_dump] 显示的结果如下:
2
3
4
5
6
7
8
9
array(3) {
  ["last_name"]=>
  string(9) "lastName0"
  ["first_name"]=>
  string(10) "firstName0"
  ["age"]=>
  int(0)
}
  • 第 53 行:参数 [PDO::FETCH_BOTH] 使返回的行成为同时以列名和列号为索引的数组:
array(6) {
  ["last_name"]=>
  string(9) "lastName1"
  [0]=>
  string(9) "lastName1"
  ["first_name"]=>
  string(10) "firstName1"
  [1]=>
  string(10) "firstName1"
  ["age"]=>
  int(10)
  [2]=>
  int(10)
}
  • 第 56 行:参数 [PDO::FETCH_OBJ] 使返回的行成为 [stdClass] 类型的对象,其属性就是表的列名:
object(stdClass)#2 (3) {
  ["last_name"]=>
  string(9) "lastName2"
  ["first_name"]=>
  string(10) "firstName2"
  ["age"]=>
  int(20)
}
  • 第 59 行:使用 [PDOStatement::setFetchMode] 方法设置 [fetch] 方法的读取模式。该模式随后成为 [$statement] 命令的默认模式,直到它被改变——要么通过再次调用 [setFetchMode],要么像前面那样把模式作为参数传给 [fetch] 方法。操作 [setFetchMode(PDO::FETCH_CLASS, Person::class)] 指定读取的行必须放入 [Person] 类型的对象中。[Person::class] 是字符串 "Person",即该类的全名。PDO 创建该对象,然后将同名列的值赋给其属性,即使这些属性是私有的。下面一行显示结果:
object(Person)#4 (3) {
  ["last_name":"Person":private]=>
  string(9) "lastName3"
  ["first_name":"Person":private]=>
  string(10) "firstName3"
  ["age":"Person":private]=>
  int(30)
}
  • 第 63–65 行:演示如何依次处理 [select] 的其余行。没有更多行时,[fetch] 方法返回 [false] ,从而结束循环。[$person] 的显示使用了 [__toString] 方法,该方法来自 [Person] 类:
Person[lastName4,firstName4,40]
Person[lastName5,firstName5,50]
Person[lastName6,firstName6,60]
Person[lastName7,firstName7,70]
Person[lastName8,firstName8,80]
Person[lastName9,firstName9,90]
  • 第 67–68 行:一条带参数的 [select]:我们只想要年龄至少 70 岁的人;
  • 第 69 行:[PDOStatement::fetchAll] 方法一次性返回结果的所有行,这里是关联数组形式(连接的默认模式)。[array_map] 函数把其中每一行转换为一个 [ImmutablePerson] 对象。表达式 [new ImmutablePerson(...$row)] 对关联数组使用展开运算符 [...] :每个键成为一个命名参数(PHP 8.1)。因此,行 [["last_name" => "lastName7", "first_name" => "firstName7", "age" => 70]] 会产生调用 [new ImmutablePerson(last_name: "lastName7", first_name: "firstName7", age: 70)]。这种技术适用于属性由构造函数初始化的不可变类,而 [PDO::FETCH_CLASS] 模式——直接给属性赋值——对于 [ImmutablePerson] 这种构造函数需要参数的类会失败。它还有一个好处,就是由 PHP 验证每个值的类型。显示的结果如下:
array(3) {
  [0]=>
  object(ImmutablePerson)#2 (3) {
    ["last_name"]=>
    string(9) "lastName7"
    ["first_name"]=>
    string(10) "firstName7"
    ["age"]=>
    int(70)
  }
  [1]=>
  object(ImmutablePerson)#6 (3) {
    ["last_name"]=>
    string(9) "lastName8"
    ["first_name"]=>
    string(10) "firstName8"
    ["age"]=>
    int(80)
  }
  [2]=>
  object(ImmutablePerson)#7 (3) {
    ["last_name"]=>
    string(9) "lastName9"
    ["first_name"]=>
    string(10) "firstName9"
    ["age"]=>
    int(90)
  }
}

下表总结了所遇到的读取模式:

模式
一行以如下形式给出
[PDO::FETCH_ASSOC]
关联数组 [column => value]
[PDO::FETCH_NUM]
以列号 0、1、2…… 为索引的数组
[PDO::FETCH_BOTH]
同时以列名和列号为索引的数组(PDO 的默认模式)
[PDO::FETCH_OBJ]
一个 [stdClass] 对象,其属性为列名
[PDO::FETCH_CLASS]
指定类的对象,其属性具有列名

从 PHP 8.4 起,每个驱动程序还有自己的选项,这些选项归入其自己的类中。例如,每次连接到 MySQL 时执行一条 SQL 命令的选项写作 [Pdo\Mysql::ATTR_INIT_COMMAND]。许多文档中常见的旧常量 [PDO::MYSQL_ATTR_INIT_COMMAND] 自 PHP 8.5 起已被弃用。

12.7. 使用事务

事务允许你把一系列 SQL 命令组合成单个执行单元:要么所有命令都成功,要么其中一条失败,此时之前所有的 SQL 命令都会被回滚。换句话说,当你使用事务执行 SQL 命令时,事务结束后,数据库处于稳定状态:

  • 要么处于由事务中所有 SQL 命令成功执行而产生的新状态;
  • 要么处于事务开始执行之前的状态。

让我们重新看一下“执行任意 SQL 命令”一节中讨论的、执行文本文件中所含 SQL 命令的示例。我们将把这次执行放入一个事务中。SQL 命令将包含在下面的 [sql2.txt] 文件中:

select * from persons
select last_name,first_name from persons order by last_name asc, first_name desc
select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc
insert into persons values('Josette','Bruneau',46)
update persons set age=47 where last_name='Bruneau'
select * from persons where last_name='Bruneau'
delete from persons where last_name='Bruneau'
select * from persons where last_name='Bruneau'
insert into persons values('Josette','Bruneau',46)
select * from persons where last_name='Bruneau'
xselect * from persons where last_name='Bruneau'

第 11 行的错误命令将导致整个事务失败。因此数据库应当恢复到事务之前的状态。在上面的例子中,第 9 行插入的行不应出现在表中。该脚本与 [mysql-04.php] 脚本相差很小。SQL 命令文件的名称变为 [__DIR__ . "/sql2.txt"],并且 [executeCommands] 函数去掉了它的第四个参数:

// 执行 SQL 语句文件
$errors = executeCommands($connection, SQL_COMMANDS_FILENAME, tracking: true);

这是因为 SQL 命令在事务中运行,任何错误都会使事务终止。[executeCommands] 函数变为如下形式:

function executeCommands(PDO $connection, string $sqlFileName, bool $tracking = false): array
{
    // 使用连接 $connection
    // 执行文本文件 $sqlFileName 中包含的 SQL 命令
    // 该文件是要执行的 SQL 命令文件,每行一条
    // 如果 $tracking 为 true,则显示每条 SQL 语句的执行及其成功或失败
    // SQL 命令在一个事务中执行:如果其中一条失败,事务就会回滚
    // 数据库恢复到事务之前的状态
    // 函数返回错误消息数组(没有错误时为空)

    // 我们把文件的行放入数组,去掉行尾标记和空行
    $queries = is_readable($sqlFileName)
        ? file($sqlFileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES)
        : false;
    // 出错?
    if ($queries === false) {
        return ["The file [$sqlFileName] does not exist or could not be read"];
    }
    // 查询将在一个事务中执行
    $connection->beginTransaction();
    // 我们逐条执行查询 - 开始时没有错误
    $errors = [];
    foreach ($queries as $query) {
        // 我们去掉行首和行尾的空格
        $query = trim($query);
        // 查询为空?
        if ($query === "") {
            // 我们转到下一条查询
            continue;
        }
        // SQL 命令的名称:查询的第一个单词,小写
        $command = strtolower(explode(" ", $query, 2)[0]);
        try {
            // 执行查询 - 出错时抛出异常
            // SELECT 语句返回行,其他语句返回被修改的行数
            $result = $command === "select"
                ? $connection->query($query, PDO::FETCH_NUM)
                : $connection->exec($query);
            // 是否在屏幕上跟踪?
            if ($tracking) {
                print "[$query]: Successful execution\n";
            }
            // 我们显示执行结果
            displayInfo($command, $result);
        } catch (PDOException $ex) {
            // 发生了错误
            addError($errors, $query, $ex->getMessage(), $tracking);
            // 我们在第 1 个错误处停止
            break;
        }
    }
    // 事务结束
    if ($errors === []) {
        // 没有错误:我们提交事务
        $connection->commit();
    } else {
        // 出现了错误:我们回滚事务
        $connection->rollBack();
        // 添加一个错误
        addError($errors, "", "Transaction rolled back", $tracking);
    }
    // 结果
    return $errors;
}

说明

  • 第 41–42 行:提醒事务的作用;
  • 第 54 行:使用 [PDO::beginTransaction] 方法开始一个事务。从此刻起,在 [foreach] 循环中执行的任何 SQL 命令都在该事务中执行;
  • 第 82–83 行:遇到第一个错误时就退出循环:没有必要再执行后面的命令,因为事务将被回滚;
  • 第 87–95 行:如果没有发生错误,数组 [$errors] 为空。此时使用 [PDO::commit] 方法提交事务:SQL 命令所做的更改变为永久性的。否则,使用 [PDO::rollBack] 方法回滚事务:事务中 SQL 命令所做的更改被撤销。然后事务错误被添加到错误列表中。

在执行 [mysql-07.php] 脚本之前,[persons] 表包含由 [mysql-06.php] 脚本插入的十行:

> mysql -u admpersons -pnobody dbpersons -e "select * from persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-----------+-----+
| first_name | last_name | age |
+------------+-----------+-----+
| firstName0 | lastName0 |   0 |
| firstName1 | lastName1 |  10 |
| firstName2 | lastName2 |  20 |
| firstName3 | lastName3 |  30 |
| firstName4 | lastName4 |  40 |
| firstName5 | lastName5 |  50 |
| firstName6 | lastName6 |  60 |
| firstName7 | lastName7 |  70 |
| firstName8 | lastName8 |  80 |
| firstName9 | lastName9 |  90 |
+------------+-----------+-----+

运行 [mysql-07.php] 脚本。屏幕显示如下:

> php mysql-07.php
[select * from persons]: Successful execution
first_name,last_name,age
------------------------
firstName0,lastName0,0
firstName1,lastName1,10
firstName2,lastName2,20
firstName3,lastName3,30
firstName4,lastName4,40
firstName5,lastName5,50
firstName6,lastName6,60
firstName7,lastName7,70
firstName8,lastName8,80
firstName9,lastName9,90
[select last_name,first_name from persons order by last_name asc, first_name desc]: Successful execution
last_name,first_name
--------------------
lastName0,firstName0
lastName1,firstName1
lastName2,firstName2
lastName3,firstName3
lastName4,firstName4
lastName5,firstName5
lastName6,firstName6
lastName7,firstName7
lastName8,firstName8
lastName9,firstName9
[select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc]: Successful execution
first_name,last_name,age
------------------------
firstName4,lastName4,40
firstName3,lastName3,30
firstName2,lastName2,20
[insert into persons values('Josette','Bruneau',46)]: Successful execution
 1 row(s) has (have) been modified
[update persons set age=47 where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
Josette,Bruneau,47
[delete from persons where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
[insert into persons values('Josette','Bruneau',46)]: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
Josette,Bruneau,46
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from persons where last_name='Bruneau'' at line 1)
[]: Error (Transaction rolled back)

-----------------------
There were 2 error(s)
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from persons where last_name='Bruneau'' at line 1)
[]: Error (Transaction rolled back)
Done
  • 第 47–52 行:行 [Josette, Bruneau, 46] 在事务中被成功插入表中;
  • 第 53 行:命令 [xselect] 出错;
  • 第 54 行:事务随后被回滚。

如果我们检查数据库状态,会发现它与脚本运行之前的状态相同。特别是,我们看不到上面结果第 52 行中的 [Josette, Bruneau, 46] 行:

1
2
3
4
5
6
7
> mysql -u admpersons -pnobody dbpersons -e "select count(*) from persons where last_name='Bruneau'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        0 |
+----------+

小结:

  • 事务以 [PDO::beginTransaction] 方法开始;
  • 使用 [PDO::commit] 方法使其成功完成;
  • 失败时使用 [PDO::rollBack] 方法终止它;
  • [PDO::inTransaction] 方法指示当前是否有事务正在进行。

使用数据库时,良好的做法是把任何 SQL 操作都放在事务中,以便将其与数据库的其他用户隔离(这也是事务的目的之一)。事务应当尽可能短。因此,务必根据情况用 [commit] 或 [rollBack] 结束它。最后请注意:在 MySQL 中,修改数据库结构的命令([create table]、[drop table] 等)无法回滚。它们会隐式提交当前事务。这就是 [sql2.txt] 文件只包含 [select]、[insert]、[update] 和 [delete] 命令的原因。