PDOPHP拡張機能を使用してUbuntu18.04のPHPでMySQLトランザクションを実行する方法

著者は、 Write for DOnations プログラムの一環として、 Open Sourcing MentalIllnessを選択して寄付を受け取りました。

序章

MySQL トランザクションは、データベース内で単一のユニットとして実行される、論理的に関連するSQLコマンドのグループです。トランザクションは、アプリケーションで ACID（原子性、一貫性、分離、および耐久性）コンプライアンスを実施するために使用されます。これは、データベースでの処理操作の信頼性を管理する一連の標準です。

Atomicityは、関連するトランザクションの成功、またはエラーが発生した場合の完全な失敗を保証します。一貫性は、定義されたビジネスロジックに従ってデータベースに送信されたデータの有効性を保証します。分離とは、データベースに接続しているさまざまなクライアントの影響が相互に影響しないようにする同時トランザクションの正しい実行です。耐久性により、論理的に関連するトランザクションがデータベースに永続的に残ります。

トランザクションを介して発行されたSQLステートメントは、成功するか、完全に失敗する必要があります。クエリのいずれかが失敗した場合、MySQLは変更をロールバックし、データベースにコミットされることはありません。

MySQLトランザクションがどのように機能するかを理解するための良い例は、eコマースWebサイトです。顧客が注文すると、アプリケーションは、ビジネスロジックに応じて、ordersやorders_productsなどのいくつかのテーブルにレコードを挿入します。単一の注文に関連するマルチテーブルレコードは、単一の論理ユニットとしてデータベースにアトミックに送信する必要があります。

もう1つのユースケースは、銀行のアプリケーションです。クライアントが送金しているとき、いくつかのトランザクションがデータベースに送信されます。送信者のアカウントから借方に記入され、受信者のパーティアカウントに貸方記入されます。 2つのトランザクションは同時にコミットする必要があります。それらの1つに障害が発生した場合、データベースは元の状態に戻り、変更をディスクに保存する必要はありません。

このチュートリアルでは、 PDO PHP拡張機能を使用します。これは、PHPでデータベースを操作するためのインターフェイスを提供し、Ubuntu18.04サーバーでMySQLトランザクションを実行します。

前提条件

始める前に、次のものが必要になります。

Ubuntu 18.04を使用した初期サーバーセットアップに従ってセットアップされた1つのUbuntu18.04サーバー（sudo非rootユーザーを含む）。
システムにインストールされているApache、MySQL、およびPHP。 Linux、Apache、MySQL、PHP（LAMP）スタックをUbuntu18.04にインストールする方法に関するガイドに従うことができます。手順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;

このコマンドは、product_idという名前のフィールドを持つproductsテーブルを作成します。最大2^63-1の大きな値に対応できるBIGINTデータ型を使用します。 PRIMARY KEYと同じフィールドを使用して、製品を一意に識別します。 AUTO_INCREMENTキーワードは、新製品が挿入されるときに次の数値を生成するようにMySQLに指示します。

product_nameフィールドのタイプはVARCHARで、最大50の文字または数字を保持できます。製品priceの場合、DOUBLEデータ型を使用して、10進数の価格の浮動小数点形式に対応します。

最後に、 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)
. . .

次に、データが製品テーブルに追加されたことを確認します。

SELECT * FROM products;

挿入した4つの製品のリストが表示されます。

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列は英数字値を受け入れるため、[X104X]文字の制限でVARCHARデータ型を使用します。ここでも、InnoDBストレージENGINEを使用してトランザクションをサポートします。

前のコマンドを実行してcustomersテーブルを作成すると、次の出力が表示されます。

OutputQuery OK, 0 rows affected (0.02 sec)

3つのサンプル顧客をテーブルに追加します。次のコマンドを実行します。

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;

3人の顧客のリストが表示されます。

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)

1人の顧客の注文に複数のアイテムが含まれている可能性があるため、この情報を保持するために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;

これでデータベーススキーマが完成し、いくつかのレコードが入力されました。次に、データベース接続とMySQLトランザクションを処理するためのPHPクラスを作成します。

ステップ2—MySQLトランザクションを処理するためのPHPクラスの設計

このステップでは、PDO（PHPデータオブジェクト）を使用してMySQLトランザクションを処理するPHPクラスを作成します。クラスはMySQLデータベースに接続し、データベースにアトミックにデータを挿入します。

クラスファイルをApacheWebサーバーのルートディレクトリに保存します。これを行うには、テキストエディタを使用して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クラスでデータベース変数を宣言しました。大規模な本番プロジェクトでは、通常、別の構成ファイルを作成し、PHPrequire_onceステートメントを使用してそのファイルからデータベース定数をロードします。

次に、PDOクラスに2つの属性を設定します。

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クラスに3つの主要なメソッドを作成します。 startTransaction、insertTransaction、およびsubmitTransaction。

startTransaction：このメソッドは、トランザクションを開始するようにPDOに指示し、commitコマンドが発行されるまで自動コミットをオフにします。
insertTransaction：このメソッドは2つの引数を取ります。 $sql変数は、実行されるSQLステートメントを保持しますが、$data変数は、プリペアドステートメントを使用しているため、SQLステートメントにバインドされるデータの配列です。データは配列としてinsertTransactionメソッドに渡されます。
submitTransaction：このメソッドは、commit()コマンドを発行することにより、データベースへの変更を永続的にコミットします。ただし、エラーが発生してトランザクションに問題がある場合、メソッドはrollBack()メソッドを呼び出して、PDO例外が発生した場合にデータベースを元の状態に戻します。

DBTransactionクラスは、トランザクションを初期化し、実行するさまざまなSQLコマンドを準備し、問題がない場合は最後にデータベースへの変更をアトミックにコミットします。それ以外の場合、トランザクションはロールバックされます。さらに、このクラスでは、パブリックプロパティlast_insert_idにアクセスして作成したばかりのレコードorder_idを取得できます。

これで、DBTransactionクラスを呼び出して、次に作成するPHPコードで使用できるようになりました。

ステップ3—DBTransactionクラスを使用するためのPHPスクリプトの作成

DBTransactionクラスを実装するPHPスクリプトを作成し、SQLコマンドのグループをMySQLデータベースに送信します。オンラインショッピングカートでの顧客の注文のワークフローを模倣します。

これらのSQLクエリは、ordersテーブルとorders_productsテーブルに影響します。 DBTransactionクラスは、すべてのクエリがエラーなしで実行された場合にのみ、データベースへの変更を許可する必要があります。そうしないと、エラーが返され、変更を試みた場合はロールバックされます。

customer_id1で識別される顧客JOHN DOEの単一の注文を作成しています。顧客の注文には、productsテーブルとは数量が異なる3つの異なるアイテムがあります。 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);

手順2で作成したDBTransactionクラスのインスタンスを初期化するPHPスクリプトを作成しました。

このスクリプトでは、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クラスが期待どおりに機能していることを確認します。

結論

このガイドでは、PHPPDOを使用してMySQLトランザクションを処理しました。これはeコマースソフトウェアの設計に関する決定的な記事ではありませんが、アプリケーションでMySQLトランザクションを使用するための例を提供しています。

MySQL ACIDモデルの詳細については、MySQLの公式WebサイトからInnoDBおよびACIDモデルガイドにアクセスすることを検討してください。私たちをご覧ください MySQLコンテンツページ関連するチュートリアル、記事、およびQ＆Aについては。