如何在 Ubuntu 18.04 上使用 PDO PHP 扩展在 PHP 中执行 MySQL 交易

作者选择了开源精神疾病以作为写给捐赠计划的一部分获得捐赠。

介绍

一个 MySQL交易是一组在数据库中作为单个单元执行的逻辑相关的 SQL 命令,用于在应用程序中执行 ACID (Atomicity, Consistency, Isolation, and Durability)的合规性。

Atomicity 保证了相关交易的成功或错误发生时完全失败。一致性保证了根据定义的商业逻辑提交给数据库的数据的有效性。隔离是同时执行的交易的正确执行,确保连接到数据库的不同客户端的效果不会相互影响。

通过交易发布的 SQL 陈述要么成功,要么完全失败,如果任何一个查询都失败,MySQL 会逆转这些更改,而这些更改绝不会被交付给数据库。

了解MySQL交易是如何工作的一个很好的例子是电子商务网站. 当客户订购时,应用程序会将记录插入到几个表中,例如:订单和orders_products,取决于业务逻辑。

另一个用例是在银行应用程序中。当客户转账时,会发送到数据库的几笔交易。发送者的账户被收费,接收者的派对账户被归还。这两笔交易必须同时进行。如果其中一笔交易失败,数据库将返回原始状态,不应将任何更改保存到磁盘上。

在本教程中,您将使用 PDO PHP 扩展,它为 PHP 数据库提供界面,以在 Ubuntu 18.04 服务器上执行 MySQL 交易。

前提条件

在你开始之前,你需要以下几点:

一个 Ubuntu 18.04 服务器通过遵循 Initial Server Setup with Ubuntu 18.04设置,包括一个 sudo 非 root 用户。
Apache、MySQL 和 PHP 安装在您的系统上. 您可以遵循 [如何在 Ubuntu 18.04 上安装 Linux、Apache、MySQL、PHP (LAMP) 堆栈上的指南(https://andsky.com/tech/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-ubuntu-18-04)。

步骤 1 – 创建示例数据库和表

首先,您将创建一个样本数据库,并在开始使用 MySQL 交易之前添加一些表。

1sudo mysql -u root -p

当被提示时,输入您的MySQL根密码并点击ENTER,然后创建数据库,为本教程的目的,我们将称之为数据库sample_store:

1CREATE DATABASE sample_store;

您将看到以下结果:

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

为您的数据库创建一个名为sample_user的用户,请记住用一个强大的值代替PASSWORD:

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

向您的用户发放完整的权限到sample_store数据库:

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

最后,重新加载 MySQL 特权:

1FLUSH PRIVILEGES;

创建用户后,您将看到以下输出:

1[secondary_label Output]
2Query OK, 0 rows affected (0.01 sec)
3. . .

有了数据库和用户,您现在可以创建几个表来展示MySQL交易的运作方式。

从 MySQL 服务器中退出:

1QUIT;

一旦系统退出您,您将看到以下输出:

1[secondary_label Output]
2Bye.

然后,使用您刚刚创建的sample_user的凭证登录:

1sudo mysql -u sample_user -p

输入sample_user的密码,然后按ENTER来继续。

切换到sample_store,使其成为当前选择的数据库:

1USE sample_store;

选择后,您将看到以下输出:

1[secondary_label Output]
2Database Changed.

然后创建一个产品表:

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

此命令创建了一个名为product_id的产品表,您使用一个BIGINT数据类型,可以容纳高达 2^63-1 的大值。

该产品名称字段类型为VARCHAR,最多可包含50字母或数字。对于产品价格,您使用DOUBLE数据类型来满足以十进制数字为价格的浮点格式。

最后,您将使用InnoDB(LINK0)作为ENGINE,因为它可以方便地支持MySQL交易,而不是其他存储引擎(如MyISAM)。

一旦您创建了产品表,您将获得以下输出:

1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)

接下来,通过运行以下命令将一些项目添加到产品表中:

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

您将在每个输入操作后看到类似于以下的输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.02 sec)
3. . .

然后,检查数据是否已添加到产品表:

1SELECT * FROM products;

您将看到您插入的四个产品列表:

 1[secondary_label Output]
 2+------------+-------------------+-------+
 3| product_id | product_name      | price |
 4+------------+-------------------+-------+
 5|          1 | WINTER COAT       |  25.5 |
 6|          2 | EMBROIDERED SHIRT |  13.9 |
 7|          3 | FASHION SHOES     |  45.3 |
 8|          4 | PROXIMA TROUSER   | 39.95 |
 9+------------+-------------------+-------+
104 rows in set (0.01 sec)

接下来,您将创建一个客户表,存储有关客户的基本信息:

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

与产品表一样,您将使用BIGINT数据类型为customer_id,从而确保表可以支持多达2^63-1的客户记录。

由于customer_name列接受字母数值,所以您使用VARCHAR数据类型(https://dev.mysql.com/doc/refman/8.0/en/char.html)以50字符为限。

在运行之前的命令创建客户表后,您将看到以下输出:

1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)

您将添加三个样本客户到表中. 运行以下命令:

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

一旦客户被添加,您将看到类似于以下的输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.02 sec)
3. . .

然后,检查客户表中的数据:

1SELECT * FROM customers;

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

1[secondary_label Output]
2+-------------+---------------+
3| customer_id | customer_name |
4+-------------+---------------+
5|           1 | JOHN DOE      |
6|           2 | ROE MARY      |
7|           3 | DOE JANE      |
8+-------------+---------------+
93 rows in set (0.00 sec)

接下来,您将创建一个订单表来记录不同客户的订单. 要创建订单表,请执行以下命令:

1CREATE 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数据类型。

您将看到以下结果:

1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)

由于单个客户的订单可能包含多个项目,您需要创建一个订单_产品表来存储此信息。

若要创建 orders_products 表,请运行以下命令:

1CREATE 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分别与订单和产品表有关。

存储引擎InnoDB必须匹配以前创建的其他表,因为单个客户的订单会同时使用交易影响多个表。

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

1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)

您目前不会将任何数据添加到订单和orders_products表中,但您将在稍后使用实现MySQL交易的PHP脚本来完成此操作。

从 MySQL 服务器中退出:

1QUIT;

您的数据库方案现在已经完成,您已经填充了一些记录,您现在将创建一个PHP类来处理数据库连接和MySQL交易。

步骤 2 — 设计一个PHP类来处理MySQL交易

在此步骤中,您将创建一个PHP类,该类将使用PDO(PHP数据对象)来处理MySQL交易。

将类文件保存到您的Apache Web 服务器的根目录中。要做到这一点,请使用您的文本编辑器创建一个DBTransaction.php文件:

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

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

 1[label /var/www/html/DBTransaction.php]
 2<?php
 3
 4class DBTransaction
 5{
 6    protected $pdo;
 7    public $last_insert_id;
 8
 9    public function __construct()
10    {
11        define('DB_NAME', 'sample_store');
12        define('DB_USER', 'sample_user');
13        define('DB_PASSWORD', 'PASSWORD');
14        define('DB_HOST', 'localhost');
15
16        $this->pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
17        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
18        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
19    }

向DBTransaction类的开始,PDO将使用常数(DB_HOST,DB_NAME,DB_USER和DB_PASSWORD)来初始化并连接到您在步骤 1 中创建的数据库。

<$>[注] 注: 由于我们在这里在小规模地演示 MySQL 交易,我们已经在DBTransaction类中宣布了数据库变量。

接下来,您将为 PDO 类设置两个属性:

ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION:此属性指示PDO在遇到错误时抛出一个例外. 此类错误可用于调试。
ATTR_EMULATE_PREPARES, false: 此选项禁用了编制陈述的模拟,并允许MySQL数据库引擎自行准备陈述。

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

 1[label /var/www/html/DBTransaction.php]
 2. . .
 3    public function startTransaction()
 4    {
 5        $this->pdo->beginTransaction();
 6    }
 7
 8    public function insertTransaction($sql, $data)
 9    {
10        $stmt = $this->pdo->prepare($sql);
11        $stmt->execute($data);
12        $this->last_insert_id = $this->pdo->lastInsertId();
13    }
14
15    public function submitTransaction()
16    {
17        try {
18            $this->pdo->commit();
19        } catch(PDOException $e) {
20            $this->pdo->rollBack();
21            return false;
22        }
23
24          return true;
25    }
26}

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

要使用 MySQL 交易,您可以创建DBTransaction类中的三个主要方法:startTransaction,insertTransaction和submitTransaction。

startTransaction: 此方法指示 PDO 启动交易,并将自动交付关闭,直到发出 commit 命令。
insertTransaction: 此方法需要两个参数。 $sql 变量将 SQL 命令执行,而 $data 变量是将数据连接到 SQL 命令的一系列,因为您正在使用已准备的命令。数据作为一个数组被传送到 insertTransaction 方法。
submitTransaction : 这种方法会通过发出 commit() 命令永久地将数据变更执行到数据库中。

您的DBTransaction类会初始化交易,准备执行不同的SQL命令,并在没有问题的情况下最终对数据库进行原子变更,否则该交易会被滚回来。

DBTransaction类现在已经准备好被任何 PHP 代码调用和使用,您将下一步创建。

步骤 3 — 创建一个 PHP 脚本以使用 DBTransaction 类

您将创建一个PHP脚本,该脚本将实现DBTransaction类,并将一组SQL命令发送到MySQL数据库。

这些 SQL 查询会影响订单和orders_products表. 您的DBTransaction类只能允许对数据库进行更改,如果所有查询都没有执行任何错误。

您正在为客户创建一个单一的订单 JOHN DOE 标识为 customer_id 1. 客户的订单有三个不同的项目,从‘产品’表的不同数量。

创建orders.php文件:

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

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

 1[label /var/www/html/orders.php]
 2<?php
 3
 4require("DBTransaction.php");
 5
 6$db_host = "database_host";
 7$db_name = "database_name";
 8$db_user = "database_user";
 9$db_password = "PASSWORD";
10
11$customer_id = 2;
12
13$products[] = [
14  'product_id' => 1,
15  'price' => 25.50,
16  'quantity' => 1
17];
18
19$products[] = [
20  'product_id' => 2,
21  'price' => 13.90,
22  'quantity' => 3
23];
24
25$products[] = [
26  'product_id' => 3,
27  'price' => 45.30,
28  'quantity' => 2
29];
30
31$transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);

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

在此脚本中,您包括了DBTransaction.php文件,并初始化了DBTransaction类别。接下来,您准备了一组多维的产品,客户正在从商店订购。

接下来,添加以下代码来完成你的orders.php脚本:

 1[label /var/www/html/orders.php]
 2. . .
 3$order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";
 4$product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";
 5
 6$transaction->insertQuery($order_query, [
 7  'customer_id' => $customer_id,
 8  'order_date' => "2020-01-11",
 9  'order_total' => 157.8
10]);
11
12$order_id = $transaction->last_insert_id;
13
14foreach ($products as $product) {
15  $transaction->insertQuery($product_query, [
16    'order_id' => $order_id,
17    'product_id' => $product['product_id'],
18    'price' => $product['price'],
19    'quantity' => $product['quantity']
20  ]);
21}
22
23$result = $transaction->submit();
24
25if ($result) {
26    echo "Records successfully submitted";
27} else {
28    echo "There was an error.";
29}

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

您通过insertTransaction方法准备将命令插入命令表,然后从DBTransaction类中获取公共属性last_insert_id的值,并将其用作$order_id。

一旦您有$order_id,您将使用该独特的ID将客户的订单项目插入到orders_products表中。

最后,您将调用提交交易方法,以便将整个客户的订单详细信息发送到数据库中,如果没有任何问题,否则提交交易方法将反弹所尝试的更改。

现在,您将在浏览器中运行orders.php脚本. 运行以下操作,并用您的服务器的公共 IP 地址代替您的服务器-IP:

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

您将看到确认记录已成功提交的证件:

PHP Output from MySQL Transactions Class

您的 PHP 脚本按预期工作,并将订单和相关订单产品原子提交到数据库。

您已在浏览器窗口中运行了orders.php文件. 该脚本召唤了DBTransaction类,从而向数据库提交了订单细节。

步骤 4 – 确认数据库中的条目

在此步骤中,您将检查客户订单的浏览器窗口启动的交易是否按预期发布到数据库表中。

要做到这一点,请再次登录您的MySQL数据库:

1sudo mysql -u sample_user -p

输入sample_user的密码,然后按ENTER继续。

切換到「sample_store」資料庫:

1USE sample_store;

在继续之前,通过确认以下输出来确保数据库被更改:

1[secondary_label Output]
2Database Changed.

然后,发出以下命令来从订单表中获取记录:

1SELECT * FROM orders;

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

1[secondary_label Output]
2+----------+---------------------+-------------+-------------+
3| order_id | order_date          | customer_id | order_total |
4+----------+---------------------+-------------+-------------+
5|  1       | 2020-01-11 00:00:00 |           2 |       157.8 |
6+----------+---------------------+-------------+-------------+
71 row in set (0.00 sec)

接下来,从orders_products表中获取记录:

1SELECT * FROM orders_products;

您将看到类似于客户订单的产品列表的输出:

1[secondary_label Output]
2+--------+----------+------------+-------+----------+
3| ref_id | order_id | product_id | price | quantity |
4+--------+----------+------------+-------+----------+
5|      1 |  1       |          1 |  25.5 |        1 |
6|      2 |  1       |          2 |  13.9 |        3 |
7|      3 |  1       |          3 |  45.3 |        2 |
8+--------+----------+------------+-------+----------+
93 rows in set (0.00 sec)

输出确认交易已保存到数据库,您的辅助类DBTransaction按预期工作。

结论

在本指南中,您使用了PHP PDO来处理MySQL交易,虽然这不是关于设计电子商务软件的最终文章,但它提供了在您的应用程序中使用MySQL交易的例子。

要了解更多关于 MySQL ACID 模型的信息,请参阅官方 MySQL 网站的 InnoDB 和 ACID 模型指南,参阅我们的 MySQL 内容页面以获取更多相关教程、文章和问答。