12. SGBD 的使用 MySQL

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

在上述架构中，脚本 PHP (1) 不会直接与 SGBD（数据库管理系统）(3) 进行交互。它通过一个名为 SGBD 驱动程序（或称 SGBD 驱动程序）的中介进行交互。 PHP 为这些驱动程序提供了一个标准接口，即 PDO 接口（PHP 数据对象）。该接口由针对每个 SGBD 定制的不同类实现：一个类用于 SGBD MySQL，另一个用于 SGBD PostgreSQL……要切换 SGBD，需更换驱动程序：

驱动程序 PDO 将脚本 PHP (1) 与 SGBD (3, 6) 隔离。由于这些驱动程序实现了标准接口，因此可以预期脚本 PHP (1) 不会发生变化。但在现实中，这种理想情况并不存在。事实上，为了与SGBD进行交互，脚本PHP会发送SQL（标准查询语言）指令。这是一种由所有SGBD实现的语言，但并不完整。因此，SGBD为其添加了专有指令。这是导致SGBD之间不兼容的首要原因。此外，不同 SGBD 数据库中可用的数据类型可能各不相同。例如，PostgreSQL支持的数据类型远多于SGBD和MySQL。这是导致不兼容的第二个原因。另一个原因是自动主键的管理（由 SGBD 生成）：几乎每个 SGBD 都有其自身的策略。等等……不兼容的原因不胜枚举。

如果想避免在从 MySQL (3) 切换到 PostgreSQL (6) 时重写 PHP (1) 脚本，通常需要在脚本 PHP (1) 与驱动程序 PDO (2, 5) 之间插入一个新层，其作用是消除这两个 SGBD 之间的兼容性问题。然而，在我们即将遇到的简单情况下，这一额外层将不再必要。

接下来我们将使用 SGBD MySQL。该组件包含在 Laragon 软件包中（参见链接部分）。

如果读者对数据库和 SQL 语言的概念尚不熟悉，可以阅读 [http://sergetahe.com/cours-tutoriels-de-programmation/cours-tutoriel-sql-avec-le-sgbd-firebird/] 文档。本文档使用的是 Firebird 数据库（而非 MySQL），但介绍了数据库和 SQL 语言的基础知识。与MySQL一样，Firebird提供了一个可自由使用的版本，且内存占用较小。

12.1. 创建数据库

接下来我们将演示如何使用 Laragon 工具创建数据库及用户 MySQL。

启动后，可通过菜单管理 Laragon [1]；
在 [3-5] 中，如果尚未安装，则需安装 [phpMyAdmin] 管理工具；

在 [6] 中，启动 Apache Web 服务器以及 SGBD 和 MySQL；
在 [7] 中，Apache 服务器已启动；
在 [8] 中，启动了 SBD 和 MySQL；

在 [8-10] 中，创建一个名为 [dbpersonnes] [11] 的数据库。我们将构建一个人员数据库；

在 [11] 中，我们将管理刚刚创建的数据库；

操作 [Bases de données] 向 URL [http://localhost/phpmyadmin] 发送了一个 Web 请求。由 Laragon 的 Apache Web 服务器进行响应。 URL [http://localhost/phpmyadmin] 是 URL 工具的 [phpMyAdmin]，该工具是我们之前安装的 [5]。该实用程序用于管理 MySQL 数据库；
默认情况下，数据库管理员的登录凭据为：root [13]（无密码）[14]；

在 [16] 中，即我们之前创建的数据库；

目前我们有一个名为 [dbpersonnes] 的数据库，[17] 目前为空，[18]；

创建用户 [admpersonnes]，密码为 [nobody]，该用户将拥有数据库 [dbpersonnes] 的所有权限：

在 [19] 中，当前定位于数据库 [dbpersonnes]；
在 [20] 中，选择 [Privileges] 选项卡；
在 [21-22] 中，可以看到用户 [root] 对数据库 [dbpersonnes] 拥有所有权限；
在 [23] 中，创建一个新用户；

在 [25-26] 中，该用户的用户名将设置为 [admdbpersonnes]；
在 [27-29] 中，其密码将为 [nobody]；
在 [30] 中，phpMyAdmin 提示该密码强度极低（易被破解）。在生产环境中，建议使用 [31] 生成强密码；
在 [32] 中，指定用户 [admdbpersonnes] 应拥有数据库 [dbpersonnes] 的全部权限；
在 [33] 中，验证所提供的信息；

在 [35] 中，phpMyAdmin 表明用户已创建；
在 [36] 中，基于该数据库生成的 SQL 命令；
在 [37] 中，用户 [admpersonnes] 对数据库 [dbpersonnes] 拥有所有权限；

现在我们有：

一个名为 MySQL 的数据库；
用户 [admpersonnes/nobody] 对该数据库拥有全部权限；

我们将编写脚本 PHP 来利用该数据库。PHP 提供了多种用于管理数据库的库。我们将使用位于 PHP 代码与 SGBD 代码之间的 PDO 库（PHP 数据对象）：

库 PDO 使脚本 PHP 能够忽略所用 SGBD 的具体类型。因此，在上文中， SGBD 和 MySQL 可以替换为 SGBD 和 PostgreSQL，对 PHP 脚本代码的影响极小。该库默认不可用。可通过以下方式检查其可用性：

在 [1-4] 中，检查已启用的 PDO 扩展；
在 [5] 中，可以看到针对 SGBD 和 MySQL 的 PDO 扩展处于激活状态。其余扩展未激活。只需点击即可激活它们；

另一种激活扩展程序的方法是直接修改配置文件 [php.ini]（链接段落），该文件用于配置 PHP：

更改为 [1]，则 MySQL 的扩展 PDO 被启用；
在 [2] 中，Firebird 的 PDO 扩展被禁用；

修改文件 [php.ini] 后，需重新启动 Laragon 的 PHP 进程，以便使更改生效。

12.2. 连接 MySQL 数据库

连接 SGBD 是通过构造 PDO 对象实现的。构造函数支持以下参数：

$dbh=new PDO(string $dsn,string $user,string $passwd,array $driver_options)

各参数的含义如下：

$dsn	(数据源名称) 是一个字符串，用于指定 SGBD 的类型及其在互联网上的位置。字符串“mysql:host=localhost”表示这是一个在本地服务器上运行的 SGBD MySQL。该字符串可能包含其他参数，特别是 SGBD 的监听端口以及要连接的数据库名称：“mysql:host=localhost:port=3306:dbname=dbpersonnes”；
$user	连接用户的用户名；
$passwd	其密码；
$driver_options	SGBD驱动程序的选项数组；

只有第一个参数是必填的。由此构建的对象将作为对已连接数据库进行所有操作的载体。如果无法构建 PDO 对象，则会抛出类型为 PDOException 的异常。

以下是一个 [mysql-01.php] 连接示例：


<?php

// 连接到本地数据库 MySql
// 用户身份为 (admpersonnes,nobody)
const ID = "admpersonnes";
const PWD = "nobody";
const HOTE = "localhost";

try {
  // 连接
  $dbh = new PDO("mysql:host=".HOTE, ID, PWD);
  print "Connexion réussie\n";
  // 关闭连接
  $dbh = NULL;
} catch (PDOException $e) {
  print "Erreur : " . $e->getMessage() . "\n";
  exit();
}

结果：

1	`Connexion réussie`

注释

第 11 行：通过构建 PDO 对象来连接 SGBD。此处的构造函数使用了以下参数：
- 一个字符串，用于指定 SGBD 的类型及其在互联网上的位置。字符串 "mysql:host=localhost" 表示这是一个在本地服务器上运行的 SGBD MySQL。端口未指定，则默认使用 3306 端口。数据库名称也未指定，此时将连接到 SGBD MySQL，具体数据库的选择将在稍后进行；
- 用户名；
- 其密码；
第14行：通过删除最初创建的对象PDO来关闭连接；
第 15 行：连接 SGBD 可能失败。在此情况下，将抛出类型为 PDOException 的异常。该异常继承自 PHP [RuntimeException] 异常；
第 16 行：显示该异常的错误信息；

让我们在第 6 行输入错误的密码，重新运行脚本。结果如下：


Erreur : SQLSTATE[HY000] [1045] Access denied for user 'admpersonnes'@'localhost' (using password: YES)

12.3. 创建表

脚本 [mysql-02.php] 演示了在数据库中创建表的过程：


<?php

// 数据库标识
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 用户凭据
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // 连接数据库 MySql
  $connexion = new PDO(DSN, ID, PWD);
  // 若存在“人员”表则将其删除
  $sql = "drop table personnes";
  $connexion->exec($sql);
  // 创建“人员”表
  $sql = "create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom))";
  $connexion->exec($sql);
} catch (PDOException $ex) {
  // 显示错误
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
  // 如有必要则断开连接
  $connexion = NULL;
}
// 结束
print "Terminé\n";
exit;

注释

第 11 行：连接数据库。这始终是第一步。连接的结果是一个 [PDO] 对象，后续的数据库操作将通过该对象进行；
第 13 行：命令 SQL [drop table personnes] 将从数据库 [dbpersonnes] 中删除表 [personnes]。如果表 [personnes] 不存在，则不会引发错误；
第 14 行：在数据库 [dbpersonnes] 上执行前一个命令 SQL。此执行可能会触发 [PDOException]，该命令将在第 18 行被拦截；
第16行：该命令SQL创建了一个名为[personnes]的表。一个表包含行和列。列构成了所谓的表结构，行则构成了表的内容。一个数据库可以包含一个或多个表。此处的表 [personnes] 将包含三个列：
- 姓：以字符串形式表示的个人名字，长度不超过 30 个字符；
- 姓氏：该人的姓氏，以不超过30个字符的字符串形式表示；
- age：以整数形式表示的该人的年龄；
- NOT 属性（NULL）对某列的约束要求该列必须有值。若未赋值，将引发 [PDOException] 错误；
- [primary key(nom,prenom)] 为表 [personnes] 设置主键。主键在表的每一行中都具有唯一值。在此，主键将通过拼接该行中的 [nom] 和 [prenom] 列来生成。该约束确保表中不会存在两名姓名完全相同的人员，即不会出现同名者。在表中创建某人的同名同姓记录将引发 [PDOException] 错误；
第 17 行：在数据库 [dbpersonnes] 上执行命令 SQL；
第20行：若发生[PDOException]错误，则显示相应的错误信息；
第21-24行：无论是否发生异常，均进入[finally]子句，以关闭数据库连接（第23行）；

结果：

如果脚本执行未出现错误，可在 phpMyAdmin 中看到该表的存在：

在 [3] 中可见数据库；
在 [4] 中，显示该表；
在 [5] 中，表结构显示在 [Structure] 选项卡中；
在 [6-8] 中，表的三个列；
在 [9] 中，这三列均不得为空；

在 [10] 中，列出了表的索引列表。索引可以更快地在表中查找具有特定索引的行，比顺序遍历表中的行更快。主键总是属于索引的一部分，但索引不一定是主键；
在 [11] 中，此处的索引即为主键；
在 [12] 中，索引由每行中的 [nom, prenom] 列组成；

现在，让我们看看如果分别在数据库名、用户名及其密码上制造错误会发生什么：

如果输入一个不存在的数据库名称：


Erreur : SQLSTATE[HY000] [1044] Access denied for user 'admpersonnes'@'%' to database 'dbpersonnes2'

如果输入不存在的用户名：


Erreur : SQLSTATE[HY000] [1045] Access denied for user 'admpersonnes2'@'localhost' (using password: YES)

如果输入错误的密码：


Erreur : SQLSTATE[HY000] [1045] Access denied for user 'admpersonnes'@'localhost' (using password: YES)

12.4. 填充表格

我们将编写一个名为 PHP 的脚本，该脚本执行以下文本文件 [creation.txt] 中找到的 SQL 命令：

drop table if exists personnes
SET NAMES 'utf8'
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
insert into personnes (prenom, nom, age) values('Paul','Langevin',48)
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70)
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70)
insert into personnes (prenom, nom, age) values ('Pierre','Nicazou',35)
insert into personnes (prenom, nom, age) values ('Géraldine','Colou',26)
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56)
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56)

注释

SQL 语言（结构化查询语言）对 SQL 命令的大小写不敏感；
第 1 行：如果表 [personnes] 存在，则将其删除；
第2行：告知服务器MySQL，将向其发送采用UTF-8编码的字符。例如，此处必须使用 MySQL 专有的 SQL 指令，才能在第 7 行中将数据库中的 Géraldine 中的 é 正确显示出来。如果不添加第 2 行，é 将会被转换为一串奇怪的两个字符。客户端是使用Netbeans编写的脚本PHP。该脚本将文件编码为下方的UTF-8和[1-4]：

第 3 行：创建表 [personnes]，包含三个字段（名字、姓氏、年龄）和主键（姓氏、名字）；
第4-10行：向表[personnes]中插入7条记录；
第6行：该插入语句应会失败，因为它试图执行与第5行相同的插入操作。主键约束应阻止此插入：系统不允许存在姓名完全相同的两个人；
第10行：该插入语句应会失败，因为它试图执行与第9行相同的插入操作；

负责执行该文本文件中 SQL 命令的脚本 PHP 如下所示：


<?php

// 数据库名称
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 用户凭据
const ID = "admpersonnes";
const PWD = "nobody";
// 待执行的 SQL 命令文本文件的标识
const SQL_COMMANDS_FILENAME = "creation.txt";

// 打开数据库连接 MySql
try {
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // 错误显示
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// 要求每次 SGBD 发生错误时，都抛出一个异常
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 执行命令文件 SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE, FALSE);
// 关闭连接
$connexion = NULL;
//显示错误数量
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// 完成
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE, bool $arrêt = TRUE): array {
// 使用连接 $connexion
// 执行文本文件 SQLFileName 中包含的命令 SQL
// 该文件是一个命令文件，其中每行包含一个待执行的命令
// 如果 $suivi=1，则每次执行 SQL 命令时，都会显示其成功或失败的状态
// 若 $arrêt=1，则函数在遇到第一个错误时停止；否则执行所有 SQL 命令
// 该函数返回一个数组（错误数量、错误1、错误2…）
// 检查文件 SQLFileName 是否存在

  if (!file_exists($SQLFileName)) {
    return ["Le fichier [$SQLFileName] n'existe pas"];
  }

  // 执行 SQLFileName 中包含的 SQL 查询
  // 将其放入数组中
  $requêtes = file($SQLFileName);
  // 出错？
  if ($requêtes === FALSE) {
    return ["Erreur lors de l'exploitation du fichier SQL [$SQLFileName]"];
  }
  // 逐个执行查询——起初没有错误
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // 获取查询文本
    // trim 函数将去除换行符
    $requête = trim($requêtes[$i]);
    // 空请求？
    if (strlen($requête) == 0) {
      // 忽略该请求并转到下一个请求
      $i++;
      continue;
    }
    try {
      // 执行查询——可能抛出异常
      $connexion->exec($requête);
      // 是否进行屏幕跟踪？
      if ($suivi) {
        print "$requête : Exécution réussie\n";
      }
    } catch (PDOException $ex) {
      // 发生错误
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // 是否停止？
      $fini = $arrêt;
    }
    // 下一个请求
    $i++;
  }
  // 结果
  return $erreurs;
}

function addError(array &$erreurs, string $requête, string $msg, bool $suivi): void {
  // 添加一条错误信息
  $msg = "$requête : Erreur (" . $msg . ")";
  $erreurs[] = $msg;
  // 是否显示屏幕？
  if ($suivi) {
    print "$msg\n";
  }
}

注释

函数 [exécuterCommandes]（第 36-89 行）负责执行其在文本文件 [$SQLFileName]（参数 2）中找到的 SQL 命令。为执行这些命令，它使用已打开的连接 [$connexion]（参数 1）与服务器 MySQL 进行通信。第三个参数 [$suivi] 是一个布尔值，用于控制屏幕显示：对于 TRUE，已执行的 SQL 命令及其成功或失败状态将显示在屏幕上；否则，SQL 命令的执行将静默进行。第四个参数 [$arrêt] 控制当 SQL 命令失败时应采取的措施：在 TRUE 中，它指示应停止执行 SQL 命令，否则该命令将继续执行。函数 [exécuterCommandes] 返回一个错误消息数组，若未发生错误则该数组为空；
第11-18行：建立与数据库的连接 MySQL [dbpersonnes]。若连接失败，则显示错误信息并终止程序（第14-18行）；
第22行：将已建立的连接传递给函数[exécuterCommandes]。该连接将在函数返回时关闭（第24行）；
第 20 行：在将其传递给 [exécuterCommandes] 函数之前，先配置该连接。若发生错误，SQL 操作（使用 [PDO] 对象）可能会返回布尔值 FALSE（默认值），也可能抛出异常。第 20 行选择了后一种情况。事实上，很容易“忘记”检查 SQL 命令执行后的布尔结果。这会在后续代码的其他位置引发错误，从而难以追溯到错误的原始位置。若发生未处理的异常（即未执行 catch），该异常将向上传播，直至遇到 catch，或直至传播到 PHP 解释器，由其拦截该异常。此时，将显示异常的类型及其在代码中的来源位置；
第22行：调用函数[exécuterCommandes]以执行命令文件SQL [$SQLFileName]；
第 45-47 行：验证命令文件 SQL 是否确实存在。如果不存在，则记录错误并返回该结果；
第 51 行：将 SQL 指令放入 [$requêtes] 数组中。第 53-55 行，如果操作失败，则返回包含单条错误信息的错误数组；
第 57 行：将错误累积到数组 [$erreurs] 中；
第58行：查询编号；
第59行：布尔变量[$fini]控制数组[$requêtes]中命令SQL的执行。当执行到 TRUE 时，执行停止；
第 60 行：遍历所有请求；
第63行：提取第i个SQL命令的文本。函数[trim]将删除SQL命令文本前后的空格。此处的“空格”包括空格字符 \b、回车符 \r、换行符 \n、分页符 \f、制表符 \t……这里需要关注的是，文本 SQL 中的换行符将被移除；
第65-69行：如果文本SQL为空，则忽略该请求并转到下一个；
第 72 行：向服务器发送命令 SQL。如果执行失败，方法 [PDO::exec] 将抛出异常。需要提醒的是，这种行为是由于第 20 行所做的配置所致；
第 79 行：将错误消息添加到错误数组中；
第 81 行：设置控制循环的布尔变量 [$fini]。如果参数 [$arrêt]（第 36 行）的值为 TRUE，则必须停止循环；
第74-76行：如果命令SQL的执行成功，且参数[$suivi]（第36行）的值为TRUE，则将其显示在屏幕上；
第87行：在执行完所有SQL命令后，返回错误表[$erreurs]；

第90-97行的函数[adError]用于向错误数组[$erreurs]中添加一条错误：

第 90 行：该函数接收 4 个参数：
- 参数 [$erreurs] 通过引用传递。实际上，我们希望操作作为参数传递的数组本身，而非其副本；
- 参数 [$requête] 是失败订单的文本 SQL；
- 参数 [$msg] 是与失败订单相关的错误消息；
- 布尔值 [$suivi] 用于指示是否应显示错误消息（$suivi=TRUE）还是不显示（$suivi=FALSE）；

函数 [exécuterCommandes] 由第 3-33 行的脚本调用：

第11-18行：与数据库建立连接 MySQL [dbpersonnes]；
第 20 行：配置连接；
第 22 行：随后执行命令文件 SQL；
第24行：关闭连接；
第26-29行：显示函数[exécuterCommandes]返回的错误；

屏幕显示结果：


drop table if exists personnes : Exécution réussie
SET NAMES 'utf8' : Exécution réussie
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom)) : Exécution réussie
insert into personnes (prenom, nom, age) values('Paul','Langevin',48) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'PRIMARY')
insert into personnes (prenom, nom, age) values ('Pierre','Nicazou',35) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Géraldine','Colou',26) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'PRIMARY')

-----------------------
Il y a eu 2 erreur(s)
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'PRIMARY')
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'PRIMARY')
Terminé

通过 phpMyAdmin 可查看已插入的内容：

12.5. 执行任意 SQL 命令

以下脚本展示了对文本文件 [sql.txt] 中 SQL 命令的执行：

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

在这些 SQL 命令中，包含用于从数据库检索结果的 select 命令，用于修改数据库但不返回结果的 insert、update、delete 命令，以及最后一个（xselect）等错误命令。[mysql-04.php] 脚本如下：


<?php

// 数据库标识
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 用户凭据
const ID = "admpersonnes";
const PWD = "nobody";
// 待执行的 SQL 命令文本文件的标识
const SQL_COMMANDS_FILENAME = "sql.txt";

try {
  // 连接数据库 MySql
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // 错误显示
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// 要求在 SGBD 发生每次错误时抛出异常
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 执行命令文件 SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE, FALSE);
// 关闭连接
$connexion = NULL;
//显示错误数量
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// 完成
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE, bool $arrêt = TRUE): array {
………………………………………………………….
  // 逐个执行查询 - 起初没有错误
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // 获取请求文本
    // trim 函数将去除换行符
    $requête = trim($requêtes[$i]);
    // 空请求？
    if (strlen($requête) == 0) {
      // 忽略该请求并转到下一个请求
      $i++;
      continue;
    }
    // 执行请求
    // 获取其名称
    $commande = "";
    if (preg_match("/^\s*(\S+)/", $requête, $champs)) {
      $commande = strtolower($champs[0]);
    }
    try {
      // 是否为 SELECT 命令？
      if ($commande === "select") {
        $résultat = $connexion->query($requête);
      } else {
        $résultat = $connexion->exec($requête);
      }
      // 是否进行屏幕跟踪？
      if ($suivi) {
        print "[$requête] : Exécution réussie\n";
      }
      // 显示执行结果
      afficherInfos($commande, $résultat);
    } catch (PDOException $ex) {
      // 发生错误
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // 是否停止？
      $fini = $arrêt;
    }
    // 下一个请求
    $i++;
  }
  // 结果
  return $erreurs;
}

function addError(array &$erreurs, string $requête, string $msg, bool $suivi): void {
  …
}

// ---------------------------------------------------------------------------------
function afficherInfos(string $commande, $résultat): void {
  // 显示 SQL 查询的结果 $résultat
  // 这是个SELECT语句吗？
  switch ($commande) {
    case "select" :
      // 显示字段名称
      $titre = "";
      $nbColonnes = $résultat->columnCount();
      for ($i = 0; $i < $nbColonnes; $i++) {
        $infos = $résultat->getColumnMeta($i);
        $titre .= $infos['name'] . ",";
      }
      // 移除最后一个字符 ,
      $titre = substr($titre, 0, strlen($titre) - 1);
      // 显示字段列表
      print "$titre\n";
      // 分隔行
      $séparateurs = "";
      for ($i = 0; $i < strlen($titre); $i++) {
        $séparateurs .= "-";
      }
      print "$séparateurs\n";
      // 数据
      foreach ($résultat as $ligne) {
        $data = "";
        for ($i = 0; $i < $nbColonnes; $i++) {
          $data .= $ligne[$i] . ",";
        }
        // 删除最后一个字符 ,
        $data = substr($data, 0, strlen($data) - 1);
        // 显示
        print "$data\n";
      }
      break;
    case "update":
    case "insert":
    case "delete";
      print " $résultat lignes(s) a (ont) été modifiée(s)\n";
      break;
  }
}

注释

第 36-83 行：函数 [exécuterCommandes] 稍作修改：命令 SQL 和 [select] 的执行方式与其他命令（如 SQL）不同。该命令是唯一返回表格（即数据库中的一组行和列）作为结果的命令；
第55-57行：使用正则表达式提取命令SQL中的第一个单词；
第60-64行：如果命令 SQL 是 [select]，则使用方法 [PDO::query]；否则使用方法 [PDO::exec] 来执行命令 SQL。在两种情况下，如果执行失败，将抛出异常并在第 71-77 行被捕获。如果执行成功，第 70 行将显示其结果；
第90-130行：函数afficherInfos显示关于执行命令SQL的结果信息；
第 94 行：处理 [select] 的情况。其结果是一个 [PDOStatement] 类型的对象；
第 96 行：方法 [PDOStatement::getColumnCount()] 返回 select 结果表的列数；
第98-99行：方法[PDOStatement::getMeta(i)]返回一个字典，其中包含关于select.结果表中第i列的信息。在此字典中，与键 'name' 关联的值即为该列的名称；
第 97-102 行：将 select 结果表中的列名拼接成一个字符串；
第105-110行：构建一条与之前构建的字符串长度相同的分隔线；
第112-121行：可以通过foreach循环遍历PDOStatement类型的对象。每次迭代中，获得的元素是 select 结果表中的一行，其形式为一个值数组，该数组表示该行各列的值。我们使用 for 循环（第 114-116 行）显示所有这些值；
第123-127行：执行insert、update、delete命令的结果是该命令修改的行数；

屏幕结果：


[set names 'utf8'] : Exécution réussie
[select * from personnes] : Exécution réussie
prenom,nom,age
--------------
Géraldine,Colou,26
Paulette,Girond,56
Paul,Langevin,48
Sylvie,Lefur,70
Pierre,Nicazou,35
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom,prenom
----------
Colou,Géraldine
Girond,Paulette
Langevin,Paul
Lefur,Sylvie
Nicazou,Pierre
[select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
prenom,nom,age
--------------
Pierre,Nicazou,35
Géraldine,Colou,26
[insert into personnes values('Josette','Bruneau',46)] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[update personnes set age=47 where nom='Bruneau'] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[select * from personnes where nom='Bruneau'] : Exécution réussie
prenom,nom,age
--------------
Josette,Bruneau,47
[delete from personnes where nom='Bruneau'] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[select * from personnes where nom='Bruneau'] : Exécution réussie
prenom,nom,age
--------------
[insert into personnes values('Josette','Bruneau',46)] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[xselect * from personnes where nom='Bruneau'] : Erreur (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 personnes where nom='Bruneau'' at line 1)

-----------------------
Il y a eu 1 erreur(s)
[xselect * from personnes where nom='Bruneau'] : Erreur (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 personnes where nom='Bruneau'' at line 1)
Terminé

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

12.6.1. 示例 1

让我们来看一下以下脚本 [mysql-05.php]：


<?php

// 显示数据库标识
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 用户凭证
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // 连接数据库MySql
  $connexion = new PDO(DSN, ID, PWD);
  // 要求每次 SGBD 发生错误时，都抛出异常
  $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // 清空人员表
  $connexion->exec("delete from personnes");
  // 人员列表
  $personnes = [];
  $personnes[] = ["nom" => "Langevin", "prenom" => "Paul", "age" => 47];
  $personnes[] = ["nom" => "Lefur", "prenom" => "Sylvie", "age" => 28];
  // 将这些人员信息存入数据库
  $statement = $connexion->prepare("insert into personnes (nom, prenom, age) values (:nom, :prenom, :age)");
  for ($i = 0; $i < count($personnes); $i++) {
    $statement->execute($personnes[$i]);
  }
} catch (PDOException $ex) {
  // 显示错误
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
// 关闭连接
  $connexion = NULL;
}

// 完成
print "Terminé\n";
exit;

注释

这里我们关注第 16-24 行，它们将两个人插入到数据库 [dbpersonnes] 的人员表中。

第 21 行：我们“准备”了一个已配置的 SQL 命令。参数前缀为：:姓、:名、:年龄。要“准备”一个 SQL 命令，需使用 [PDO::prepare] 方法。结果类型为 [PDOStatement]。“准备”并非执行：此时没有任何操作被执行；
第23行：使用方法[PDOStatement::execute]执行“准备”好的命令。为此，需为参数:nom、:prenom和:age赋值。实现方式有多种。此处使用了一个字典，其键为已准备订单的参数，并将该字典传递给方法 [PDOStatement::execute]。另一种方法是使用方法 [PDOStatement::bindValue($paramètre,$valeur)] 为参数赋值。例如：


$statement→bindValue(“nom”,”Langevin”);
$statement→bindValue(“prenom”,”Paul”);
$statement→bindValue(“age”,47);
$statement→execute();

其缺点是必须针对每个参数重复执行该指令。因此，字典方法可能更为便捷。如果执行失败，方法 [PDOStatement::execute] 会返回 FALSE；

此处用于插入操作的方法：
- 预编译语句；
- n 次预备语句的执行；

比执行 n 个不同的 SQL 语句更节省执行时间。因此应优先采用此方法。该方法适用于 SQL 语句（包括 select、update、delete、insert）。对于 SQL 和 select 命令，在与 [PDOStatement::execute] 一起执行后，可通过 [PDOStatement::fetchAll] 方法获取结果行；

12.6.2. 示例 2

以下脚本 [mysql-06.php] 演示了如何为 SQL select 操作使用预编译语句，以及检索该操作返回的行数据的不同方法：


<?php

// 数据库标识
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 用户凭证
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // 连接数据库 MySql
  $connexion = new PDO(DSN, ID, PWD);
  // 希望每次 SGBD 出现错误时，都抛出一个异常
  $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // 清空人员表
  $connexion->exec("delete from personnes");
  // 将这些人员信息存入数据库
  $statement = $connexion->prepare("insert into personnes (nom, prenom, age) values (:nom, :prenom, :age)");
  for ($i = 0; $i < 10; $i++) {
    $statement->execute(["nom" => "nom" . $i, "prenom" => "prenom" . $i, "age" => $i * 10]);
  }
  // 查询数据库
  $statement = $connexion->prepare("select nom, prenom, age from personnes");
  $statement->execute();
  // 第1行
  $ligne = $statement->fetch();
  var_dump($ligne);
  // 第2行
  $ligne = $statement->fetch(PDO::FETCH_ASSOC);
  var_dump($ligne);
  // 第3行
  $ligne = $statement->fetch(PDO::FETCH_OBJ);
  var_dump($ligne);
  // 第4行
  $statement->setFetchMode(PDO::FETCH_CLASS, "Person");
  $ligne = $statement->fetch();
  var_dump($ligne);
  // 依次读取所有行
  $statement = $connexion->prepare("select nom, prenom, age from personnes");
  $statement->execute();
  $statement->setFetchMode(PDO::FETCH_CLASS, "Person");
  while ($personne = $statement->fetch()) {
    print "$personne\n";
  }
} catch (PDOException $ex) {
  // 显示错误
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
// 关闭连接
  $connexion = NULL;
}

// 结束
print "Terminé\n";
exit;

class Person {
  private $nom;
  private $prenom;
  private $age;

  public function __toString() {
    return "Personne[$this->nom,$this->prenom,$this->age]";
  }

}

注释

第 17-20 行：向数据库 [admpersonnes] 的表 [personnes] 中插入 10 行：

第22行：我们“准备”一个命令 SQL [select]，并在第23行执行该命令；
第25行：使用方法[PDOStatement::fetch]获取已执行的操作SQL [select]的结果中的一行。方法 [PDOStatement::fetch] 可以通过多种方式获取已准备好的操作 SQL [select] 的结果行。本脚本展示了其中几种方法。无参数的 [PDOStatement::fetch] 方法将 [select] 的当前行以字典形式返回，该字典同时以列号和列名作为索引；
第 26 行：显示以下结果：


array(6) {
  ["nom"]=>
  string(4) "nom0"
  [0]=>
  string(4) "nom0"
  ["prenom"]=>
  string(7) "prenom0"
  [1]=>
  string(7) "prenom0"
  ["age"]=>
  string(1) "0"
  [2]=>
  string(1) "0"
}

第 28-29 行：参数 [PDO::FETCH_ASSOC] 使得返回的行成为一个以表中列名作为索引的字典：

array(3) {
  ["nom"]=>
  string(4) "nom1"
  ["prenom"]=>
  string(7) "prenom1"
  ["age"]=>
  string(2) "10"
}

第 31-32 行：参数 [PDO::FETCH_OBJ] 使得返回的行成为类型为 [stdclass] 的对象，其属性即为表的列名：

object(stdClass)#2 (3) {
  ["nom"]=>
  string(4) "nom2"
  ["prenom"]=>
  string(7) "prenom2"
  ["age"]=>
  string(2) "20"
}

第 34 行：使用方法 [PDOStatement::setFetchMode] 设置方法 [fetch] 的搜索模式。只要未通过另一项操作 [PDOStatement::setFetchMode] 进行更改，或者像之前那样将模式作为参数传递给方法 [PDOStatement::fetch]，该模式即成为默认模式。操作 [setFetchMode(PDO::FETCH_CLASS, "Person")] 表示读取的行应被放置在类型为 [Person] 的对象中。该类必须包含与读取行中列名对应的属性。第56至63行定义的[Person]类即符合此要求；
第36行显示以下结果：

object(Person)#4 (3) {
  ["nom":"Person":private]=>
  string(4) "nom3"
  ["prenom":"Person":private]=>
  string(7) "prenom3"
  ["age":"Person":private]=>
  string(2) "30"
}

第38-43行：展示了如何依次利用[select]的结果；
第42行：[$personne]的显示将调用类[Person]中的方法[__toString]；

12.7. 事务的使用

事务可将一组 SQL 命令聚合为一个执行单元：要么所有命令均成功，要么其中一个失败，此时该命令之前的所有 SQL 命令均被撤销。换言之，当使用事务执行 SQL 命令时，事务执行完成后，数据库将处于稳定状态：

要么处于由该事务中所有 SQL 命令成功执行所创建的新状态；
要么处于事务开始执行之前的状态；

我们将继续使用前文“链接”部分中讨论的文本文件所包含的 SQL 命令的执行示例。我们将把该执行操作纳入一个事务中。SQL 命令将包含在以下 [sql2.txt] 文件中：


set names 'utf8'
select * from personnes
select nom,prenom from personnes order by nom asc, prenom desc
select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
insert into personnes values('Josette','Bruneau',46)
update personnes set age=47 where nom='Bruneau'
select * from personnes where nom='Bruneau'
delete from personnes where nom='Bruneau'
select * from personnes where nom='Bruneau'
insert into personnes values('Josette','Bruneau',46)
select * from personnes where nom='Bruneau'
xselect * from personnes where nom='Bruneau'

第 12 行中的错误命令将导致整个事务失败。因此，数据库应恢复到事务执行前的状态。在上述示例中，表中不应显示由第 10 行插入的记录。脚本的改动非常小。不过，我们仍将完整呈现 [mysql-07.php] 的代码：


<?php

// 数据库标识
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 用户凭据
const ID = "admpersonnes";
const PWD = "nobody";
// 待执行的 SQL 命令文本文件的标识
const SQL_COMMANDS_FILENAME = "sql2.txt";

try {
  // 连接数据库 MySql
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // 错误显示
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// 要求在 SGBD 发生每次错误时抛出异常
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 执行命令文件 SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE);
// 关闭连接
$connexion = NULL;
//显示错误数量
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// 完成
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE): array {
// 使用连接 $connexion
// 执行文本文件 SQLFileName 中包含的命令 SQL
// 该文件是一个命令文件，其中每行包含一个待执行的命令 SQL
// SQL 命令在事务中执行
// 如果其中一个命令执行失败，事务将被回滚，数据库将恢复到事务开始前的状态
// 若 $suivi=1，则每次执行 SQL 命令时，都会显示其成功或失败的状态
// 该函数返回一个数组（错误数量、错误1、错误2…）
//
// 检查文件 SQLFileName 是否存在
  if (!file_exists($SQLFileName)) {
    return ["Le fichier [$SQLFileName] n'existe pas"];
  }
  // 执行 SQLFileName 中包含的 SQL 请求
  // 将它们放入数组中
  $requêtes = file($SQLFileName);
  // 错误？
  if ($requêtes === FALSE) {
    return ["Erreur lors de l'exploitation du fichier SQL [$SQLFileName]"];
  }
  // 这些查询将被放入一个事务中
  $connexion->beginTransaction();
  // 逐个执行查询——起初没有错误
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // 获取查询文本
    // trim 函数将去除换行符
    $requête = trim($requêtes[$i]);
    // 查询为空？
    if (strlen($requête) == 0) {
      // 忽略该请求并转到下一个请求
      $i++;
      continue;
    }
    // 执行请求
    // 获取其名称
    $commande = "";
    if (preg_match("/^\s*(\S+)/", $requête, $champs)) {
      $commande = strtolower($champs[0]);
    }
    try {
      // 是否为 SELECT 命令？
      if ($commande === "select") {
        $résultat = $connexion->query($requête);
      } else {
        $résultat = $connexion->exec($requête);
      }
      // 是否进行屏幕跟踪？
      if ($suivi) {
        print "[$requête] : Exécution réussie\n";
      }
      // 显示执行结果
      afficherInfos($commande, $résultat);
    } catch (PDOException $ex) {
      // 发生错误
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // 在下一轮暂停
      $fini = TRUE;
    }
    // 下一个请求
    $i++;
  }
  // 事务结束
  if (!$fini) {
    // 未发生错误：事务已提交
    $connexion->commit();
  } else {
    // 发生错误：取消交易
    $connexion->rollBack();
    // 添加错误
    addError($erreurs, "", "Transaction annulée", $suivi);
  }
  // 结果
  return $erreurs;
}

function addError(array &$erreurs, string $requête, string $msg, bool $suivi): void {
  …
}

// ---------------------------------------------------------------------------------
function afficherInfos(string $commande, $résultat): void {
  …
}

注释

我们已对原始脚本 [mysql-04.php] 的修改部分进行了标注。

第 22、36 行：函数 [exécuterCommandes] 去掉了其第四个参数 [$arrêt=TRUE]。实际上，由于 SQL 命令是在事务中执行的，因此任何错误都会导致事务中止；
第 40-41 行：调用事务函数；
第57行：启动事务。从此时起，在第62-99行循环中执行的所有SQL命令均在此事务内执行；
第101-109行：布尔值[$fini]在发生错误时（第95行）将变为TRUE。当其值为 FALSE 时，表示未发生错误，此时将提交事务（第 103 行）。当其值为 TRUE 时，表示发生错误，此时将撤销事务（第 106 行），并将事务错误添加到错误列表中（第 108 行）；

结果

在执行脚本之前，数据库 [admpersonnes] 的状态如下：

执行脚本 [mysql-07.php]。此时屏幕显示如下：


[set names 'utf8'] : Exécution réussie
[select * from personnes] : Exécution réussie
prenom,nom,age
--------------
prenom0,nom0,0
prenom1,nom1,10
prenom2,nom2,20
prenom3,nom3,30
prenom4,nom4,40
prenom5,nom5,50
prenom6,nom6,60
prenom7,nom7,70
prenom8,nom8,80
prenom9,nom9,90
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom,prenom
----------
nom0,prenom0
nom1,prenom1
nom2,prenom2
nom3,prenom3
nom4,prenom4
nom5,prenom5
nom6,prenom6
nom7,prenom7
nom8,prenom8
nom9,prenom9
[select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
prenom,nom,age
--------------
prenom4,nom4,40
prenom3,nom3,30
prenom2,nom2,20
[insert into personnes values('Josette','Bruneau',46)] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[update personnes set age=47 where nom='Bruneau'] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[select * from personnes where nom='Bruneau'] : Exécution réussie
prenom,nom,age
--------------
Josette,Bruneau,47
[delete from personnes where nom='Bruneau'] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[select * from personnes where nom='Bruneau'] : Exécution réussie
prenom,nom,age
--------------
[insert into personnes values('Josette','Bruneau',46)] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[select * from personnes where nom='Bruneau'] : Exécution réussie
prenom,nom,age
--------------
Josette,Bruneau,46
[xselect * from personnes where nom='Bruneau'] : Erreur (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 personnes where nom='Bruneau'' at line 1)
[] : Erreur (Transaction annulée)

-----------------------
Il y a eu 2 erreur(s)
[xselect * from personnes where nom='Bruneau'] : Erreur (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 personnes where nom='Bruneau'' at line 1)
[] : Erreur (Transaction annulée)
Terminé

第 53 行：在命令 [xselect] 上发生错误；
第 54 行：事务被回滚；

若检查数据库状态，发现其状态与执行脚本前相同。特别是，未显示上述结果第52行中的[Josette, Bruneau, 46]行。

摘要

事务以方法 [PDO::beginTransaction] 开始；
使用方法 [PDO::commit] 成功结束事务；
若交易失败，则通过方法 [PDO::rollback] 结束；

在操作数据库时，将所有 SQL 操作置于事务中以隔离其他数据库用户（该方法本身也具有此作用）是一种良好的习惯。事务应尽可能简短。因此，请务必根据具体情况，使用 [commit] 或 QZXW2HTMLP002373ZX 来结束事务。