如何在Ubuntu18.04上使用PDOPHP扩展在PHP中执行MySQL事务

作者选择 Open Sourcing Mental Illness 作为 Write for DOnations 计划的一部分来接受捐赠。

介绍

MySQL 事务是一组逻辑相关的 SQL 命令，它们作为一个单元在数据库中执行。事务用于在应用程序中强制执行 ACID（原子性、一致性、隔离性和持久性）合规性。这是一组标准，用于管理数据库中处理操作的可靠性。

原子性确保相关事务成功或发生错误时完全失败。一致性保证了根据定义的业务逻辑提交到数据库的数据的有效性。隔离是并发事务的正确执行，确保连接到数据库的不同客户端的效果不会相互影响。持久性确保逻辑相关的事务永久保留在数据库中。

通过事务发出的 SQL 语句应该成功或完全失败。如果任何查询失败，MySQL 将回滚更改并且它们永远不会提交到数据库。

了解 MySQL 事务如何工作的一个很好的例子是电子商务网站。当客户下订单时，应用程序会根据业务逻辑将记录插入到多个表中，例如：orders 和 orders_products。与单个订单相关的多表记录必须作为单个逻辑单元自动发送到数据库。

另一个用例是银行应用程序。当客户转账时，有几笔交易被发送到数据库。借记发送方的账户，贷记接收方的账户。这两个事务必须同时提交。如果其中一个失败，数据库将恢复到其原始状态，并且不应将任何更改保存到磁盘。

在本教程中，您将使用 PDO PHP 扩展，它提供了使用 PHP 处理数据库的接口，在 Ubuntu 18.04 服务器上执行 MySQL 事务。

先决条件

在开始之前，您将需要以下内容：

按照 Initial Server Setup 和 Ubuntu 18.04 设置一台 Ubuntu 18.04 服务器，包括 sudo 非 root 用户。
您的系统上安装了 Apache、MySQL 和 PHP。您可以按照如何在 Ubuntu 18.04 上安装 Linux、Apache、MySQL、PHP (LAMP) 堆栈的指南进行操作。您可以跳过第 4 步（设置虚拟主机）并直接使用默认的 Apache 设置。

第 1 步 — 创建示例数据库和表

在开始使用 MySQL 事务之前，您将首先创建一个示例数据库并添加一些表。首先，以 root 身份登录到您的 MySQL 服务器：

sudo mysql -u root -p

出现提示时，输入您的 MySQL 根密码并点击 ENTER 继续。然后，创建一个数据库，为了本教程的目的，我们将数据库称为 sample_store：

CREATE DATABASE sample_store;

您将看到以下输出：

OutputQuery OK, 1 row affected (0.00 sec)

为您的数据库创建一个名为 sample_user 的用户。请记住将 PASSWORD 替换为强值：

CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';

为您的用户授予对 sample_store 数据库的完全权限：

GRANT ALL PRIVILEGES ON sample_store.* TO 'sample_user'@'localhost';

最后，重新加载 MySQL 权限：

FLUSH PRIVILEGES;

创建用户后，您将看到以下输出：

OutputQuery OK, 0 rows affected (0.01 sec)
. . .

有了数据库和用户，您现在可以创建几个表来演示 MySQL 事务的工作方式。

从 MySQL 服务器注销：

QUIT;

系统将您注销后，您将看到以下输出：

OutputBye.

然后，使用您刚刚创建的 sample_user 的凭据登录：

sudo mysql -u sample_user -p

输入 sample_user 的密码并点击 ENTER 继续。

切换到 sample_store 使其成为当前选中的数据库：

USE sample_store;

选择后，您将看到以下输出：

OutputDatabase Changed.

接下来，创建一个 products 表：

CREATE TABLE products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE) ENGINE = InnoDB;

此命令创建一个 products 表，其中包含一个名为 product_id 的字段。您使用可容纳高达 2^63-1 的较大值的 BIGINT 数据类型。您使用与 PRIMARY KEY 相同的字段来唯一标识产品。 AUTO_INCREMENT 关键字指示 MySQL 在插入新产品时生成下一个数值。

product_name 字段的类型为 VARCHAR，最多可容纳 50 字母或数字。对于产品 price，您可以使用 DOUBLE 数据类型来满足十进制数价格中的浮点格式。

最后，您将 InnoDB 用作 ENGINE，因为与 MyISAM 等其他存储引擎相比，它可以轻松地支持 MySQL 事务。

创建 products 表后，您将获得以下输出：

OutputQuery OK, 0 rows affected (0.02 sec)

接下来，通过运行以下命令将一些项目添加到 products 表中：

INSERT INTO products(product_name, price) VALUES ('WINTER COAT','25.50');
INSERT INTO products(product_name, price) VALUES ('EMBROIDERED SHIRT','13.90');
INSERT INTO products(product_name, price) VALUES ('FASHION SHOES','45.30');
INSERT INTO products(product_name, price) VALUES ('PROXIMA TROUSER','39.95');

在每次 INSERT 操作之后，您将看到类似于以下的输出：

OutputQuery OK, 1 row affected (0.02 sec)
. . .

然后，验证数据是否已添加到 products 表中：

SELECT * FROM products;

您将看到已插入的四种产品的列表：

Output+------------+-------------------+-------+
| product_id | product_name      | price |
+------------+-------------------+-------+
|          1 | WINTER COAT       |  25.5 |
|          2 | EMBROIDERED SHIRT |  13.9 |
|          3 | FASHION SHOES     |  45.3 |
|          4 | PROXIMA TROUSER   | 39.95 |
+------------+-------------------+-------+
4 rows in set (0.01 sec)

接下来，您将创建一个 customers 表来保存有关客户的基本信息：

CREATE TABLE customers (customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) ) ENGINE = InnoDB;

如在 products 表中，您对 customer_id 使用 BIGINT 数据类型，这将确保该表可以支持最多 2^63-1 条记录的大量客户. 插入新客户后，关键字 AUTO_INCREMENT 会增加列的值。

由于 customer_name 列接受字母数字值，因此您使用 VARCHAR 数据类型并限制为 50 个字符。同样，您使用 InnoDB 存储 ENGINE 来支持事务。

运行上一条命令创建 customers 表后，您将看到以下输出：

OutputQuery OK, 0 rows affected (0.02 sec)

您将向表中添加三个示例客户。运行以下命令：

INSERT INTO customers(customer_name) VALUES ('JOHN DOE');
INSERT INTO customers(customer_name) VALUES ('ROE MARY');
INSERT INTO customers(customer_name) VALUES ('DOE JANE');

添加客户后，您将看到类似于以下内容的输出：

OutputQuery OK, 1 row affected (0.02 sec)
. . .

然后，验证 customers 表中的数据：

SELECT * FROM customers;

您将看到三个客户的列表：

Output+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JOHN DOE      |
|           2 | ROE MARY      |
|           3 | DOE JANE      |
+-------------+---------------+
3 rows in set (0.00 sec)

接下来，您将创建一个 orders 表来记录不同客户的订单。要创建 orders 表，请执行以下命令：

CREATE TABLE orders (order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_date DATETIME, customer_id BIGINT, order_total DOUBLE) ENGINE = InnoDB;

您将列 order_id 用作 PRIMARY KEY。 BIGINT 数据类型允许您最多容纳 2^63-1 个订单，并在每次插入订单后自动递增。 order_date 字段将保存下订单的实际日期和时间，因此，您使用 DATETIME 数据类型。 customer_id 与您之前创建的 customers 表相关。

您将看到以下输出：

OutputQuery OK, 0 rows affected (0.02 sec)

由于单个客户的订单可能包含多个项目，因此您需要创建一个 orders_products 表来保存此信息。

要创建 orders_products 表，请运行以下命令：

CREATE TABLE orders_products (ref_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, price DOUBLE, quantity BIGINT) ENGINE = InnoDB;

您将 ref_id 用作 PRIMARY KEY ，这将在每次插入记录后自动递增。 order_id 和 product_id 分别与 orders 和 products 表相关。 price 列的数据类型为 DOUBLE 以适应浮点值。

存储引擎 InnoDB 必须与之前创建的其他表匹配，因为单个客户的订单会同时使用事务影响多个表。

您的输出将确认表的创建：

OutputQuery OK, 0 rows affected (0.02 sec)

您暂时不会向 orders 和 orders_products 表添加任何数据，但稍后您将使用实现 MySQL 事务的 PHP 脚本来执行此操作。

从 MySQL 服务器注销：

QUIT;

您的数据库模式现在已经完成，并且您已经用一些记录填充了它。您现在将创建一个 PHP 类来处理数据库连接和 MySQL 事务。

第 2 步 — 设计一个 PHP 类来处理 MySQL 事务

在这一步中，您将创建一个 PHP 类，该类将使用 PDO（PHP 数据对象）来处理 MySQL 事务。该类将连接到您的 MySQL 数据库并以原子方式将数据插入数据库。

将类文件保存在 Apache Web 服务器的根目录中。为此，请使用您的文本编辑器创建一个 DBTransaction.php 文件：

sudo nano /var/www/html/DBTransaction.php

然后，将以下代码添加到文件中。将 PASSWORD 替换为您在步骤 1 中创建的值：

/var/www/html/DBTransaction.php

<?php

class DBTransaction
{
    protected $pdo;
    public $last_insert_id;

    public function __construct()
    {
        define('DB_NAME', 'sample_store');
        define('DB_USER', 'sample_user');
        define('DB_PASSWORD', 'PASSWORD');
        define('DB_HOST', 'localhost');

        $this->pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }

在 DBTransaction 类的开头，PDO 将使用常量（DB_HOST、DB_NAME、DB_USER 和 DB_PASSWORD）来初始化并连接到您在步骤 1 中创建的数据库。

注意： 由于这里是小规模演示 MySQL 事务，所以我们在 DBTransaction 类中声明了数据库变量。在大型生产项目中，您通常会创建一个单独的配置文件并使用 PHP require_once 语句从该文件加载数据库常量。

接下来，为 PDO 类设置两个属性：

ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION：此属性指示 PDO 在遇到错误时抛出异常。可以记录此类错误以进行调试。
ATTR_EMULATE_PREPARES, false：此选项禁用准备语句的模拟，并允许 MySQL 数据库引擎自行准备语句。

现在将以下代码添加到您的文件中，为您的类创建方法：

/var/www/html/DBTransaction.php

. . .
    public function startTransaction()
    {
        $this->pdo->beginTransaction();
    }

    public function insertTransaction($sql, $data)
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        $this->last_insert_id = $this->pdo->lastInsertId();
    }

    public function submitTransaction()
    {
        try {
            $this->pdo->commit();
        } catch(PDOException $e) {
            $this->pdo->rollBack();
            return false;
        }

          return true;
    }
}

按 CTRL + X、Y，然后按 ENTER 保存并关闭文件。

要使用 MySQL 事务，您在 DBTransaction 类中创建三个主要方法； startTransaction、insertTransaction 和 submitTransaction。

startTransaction：此方法指示 PDO 启动事务并关闭自动提交，直到发出提交命令。
insertTransaction ：这个方法有两个参数。 $sql 变量保存要执行的 SQL 语句，而 $data 变量是要绑定到 SQL 语句的数据数组，因为您使用的是准备好的语句。数据作为数组传递给 insertTransaction 方法。
submitTransaction ：此方法通过发出 commit() 命令将更改永久提交到数据库。但是，如果出现错误并且事务有问题，则该方法会调用 rollBack() 方法将数据库恢复到其原始状态，以防引发 PDO 异常。

您的 DBTransaction 类初始化事务，准备要执行的不同 SQL 命令，如果没有问题，最后以原子方式将更改提交到数据库，否则，事务将回滚。此外，该类允许您通过访问公共属性 last_insert_id 来检索刚刚创建的记录 order_id。

DBTransaction 类现在可以被任何 PHP 代码调用和使用，接下来您将创建该类。

第 3 步 — 创建 PHP 脚本以使用 DBTransaction 类

您将创建一个 PHP 脚本，该脚本将实现 DBTransaction 类并将一组 SQL 命令发送到 MySQL 数据库。您将模拟在线购物车中客户订单的工作流程。

这些 SQL 查询将影响 orders 和 orders_products 表。您的 DBTransaction 类应该只允许在所有查询都执行且没有任何错误的情况下更改数据库。否则，您将收到错误消息，并且任何尝试的更改都将回滚。

您正在为使用 customer_id 1 标识的客户 JOHN DOE 创建一个订单。客户的订单包含 products 表中数量不同的三个不同项目。您的 PHP 脚本获取客户的订单数据并将其提交到 DBTransaction 类。

创建 orders.php 文件：

sudo nano /var/www/html/orders.php

然后，将以下代码添加到文件中：

/var/www/html/orders.php

<?php

require("DBTransaction.php");

$db_host = "database_host";
$db_name = "database_name";
$db_user = "database_user";
$db_password = "PASSWORD";

$customer_id = 2;

$products[] = [
  'product_id' => 1,
  'price' => 25.50,
  'quantity' => 1
];

$products[] = [
  'product_id' => 2,
  'price' => 13.90,
  'quantity' => 3
];

$products[] = [
  'product_id' => 3,
  'price' => 45.30,
  'quantity' => 2
];

$transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);

您已经创建了一个 PHP 脚本，该脚本初始化了您在步骤 2 中创建的 DBTransaction 类的一个实例。

在此脚本中，您包含 DBTransaction.php 文件并初始化 DBTransaction 类。接下来，您准备客户从商店订购的所有产品的多维数组。您还调用 startTransaction() 方法来启动事务。

接下来添加以下代码以完成您的 orders.php 脚本：

/var/www/html/orders.php

. . .
$order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";
$product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";

$transaction->insertQuery($order_query, [
  'customer_id' => $customer_id,
  'order_date' => "2020-01-11",
  'order_total' => 157.8
]);

$order_id = $transaction->last_insert_id;

foreach ($products as $product) {
  $transaction->insertQuery($product_query, [
    'order_id' => $order_id,
    'product_id' => $product['product_id'],
    'price' => $product['price'],
    'quantity' => $product['quantity']
  ]);
}

$result = $transaction->submit();

if ($result) {
    echo "Records successfully submitted";
} else {
    echo "There was an error.";
}

按 CTRL + X、Y，然后按 ENTER 保存并关闭文件。

您准备通过 insertTransaction 方法插入订单表的命令。之后，您从 DBTransaction 类中检索公共属性 last_insert_id 的值并将其用作 $order_id。

一旦有了 $order_id，就可以使用唯一 ID 将客户的订单商品插入到 orders_products 表中。

最后，如果没有问题，您调用方法 submitTransaction 将整个客户的订单详细信息提交到数据库。否则，方法 submitTransaction 将回滚尝试的更改。

现在您将在浏览器中运行 orders.php 脚本。运行以下命令并将 your-server-IP 替换为您服务器的公共 IP 地址：

http://your-server-IP/orders.php

您将看到记录已成功提交的确认信息：

您的 PHP 脚本按预期工作，订单连同相关订单产品以原子方式提交到数据库。

您已经在浏览器窗口中运行了 orders.php 文件。该脚本调用了 DBTransaction 类，该类又将 orders 详细信息提交到数据库。在下一步中，您将验证记录是否保存到相关的数据库表中。

第 4 步 — 确认数据库中的条目

在此步骤中，您将检查从浏览器窗口为客户订单启动的事务是否已按预期发布到数据库表中。

为此，请再次登录您的 MySQL 数据库：

sudo mysql -u sample_user -p

输入 sample_user 的密码并点击 ENTER 继续。

切换到sample_store数据库：

USE sample_store;

通过确认以下输出，确保在继续之前更改数据库：

OutputDatabase Changed.

然后，发出以下命令从 orders 表中检索记录：

SELECT * FROM orders;

这将显示以下详细说明客户订单的输出：

Output+----------+---------------------+-------------+-------------+
| order_id | order_date          | customer_id | order_total |
+----------+---------------------+-------------+-------------+
|  1       | 2020-01-11 00:00:00 |           2 |       157.8 |
+----------+---------------------+-------------+-------------+
1 row in set (0.00 sec)

接下来，从 orders_products 表中检索记录：

SELECT * FROM orders_products;

您将看到类似于以下内容的输出，其中包含客户订单中的产品列表：

Output+--------+----------+------------+-------+----------+
| ref_id | order_id | product_id | price | quantity |
+--------+----------+------------+-------+----------+
|      1 |  1       |          1 |  25.5 |        1 |
|      2 |  1       |          2 |  13.9 |        3 |
|      3 |  1       |          3 |  45.3 |        2 |
+--------+----------+------------+-------+----------+
3 rows in set (0.00 sec)

输出确认事务已保存到数据库，并且您的助手 DBTransaction 类按预期工作。

结论

在本指南中，您使用 PHP PDO 处理 MySQL 事务。虽然这不是一篇关于设计电子商务软件的结论性文章，但它提供了在应用程序中使用 MySQL 事务的示例。

要了解有关 MySQL ACID 模型的更多信息，请考虑访问 MySQL 官方网站上的 InnoDB 和 ACID 模型指南。访问我们的 MySQL 内容页面获取更多相关教程、文章和问答。