// Tutorial //

Использование расширения PDO PHP для выполнения транзакций MySQL на PHP в Ubuntu 18.04

Published on April 29, 2020
Default avatar
By FRANCIS NDUNGU
Developer and author at DigitalOcean.
Русский
Использование расширения PDO PHP для выполнения транзакций MySQL на PHP в Ubuntu 18.04

Автор выбрал организацию Open Sourcing Mental Illness для получения пожертвований в рамках программы Write for DOnations.

Введение

Транзакция MySQL представляет собой группу логически связанных команд SQL, выполняемых в базе данных как единое целое. Транзакции используются для обеспечения соблюдения приложением принципов ACID (атомарность, единообразие, изоляция и долговечность). Эти принципы лежат в основе стандартов, определяющих надежность операций в базе данных.

Атомарность обеспечивает успех связанных транзакций или полную неудачу в случае ошибки. Единообразие гарантирует корректность данных, отправленных в базу данных, в соответствии с заданной бизнес-логикой. Изоляция — это правильное выполнение параллельных транзакций так, чтобы разные клиенты, подключенные к базе данных, не влияли друг на друга. Долговечность обеспечивает постоянное сохранение в базе данных логически связанных транзакций.

Команды SQL в составе транзакции обрабатываются в комплексе с успешным или неуспешным результатом. В случае неудачного результата любого запроса MySQL откатывает изменения, и они не записываются в базу данных.

В качестве примера работы транзакций MySQL можно рассмотреть сайт электронной коммерции. Когда клиент делает заказ, приложение вставляет записи в несколько таблиц в зависимости от бизнес-логики, например, в таблицы orders и orders_products. Записи в нескольких таблицах, связанные с одним заказом, должны атомарно отправляться в базу данных как одна логическая единица.

Другой пример использования транзакций — банковское приложение. Когда клиент переводит деньги, в базу данных передается несколько транзакций. Одна транзакция отвечает за списание денежных средств со счета отправителя, а другая — за их начисление на счет получателя. Эти две транзакции должны обрабатываться одновременно. Если одна из транзакций будет неуспешной, база данных вернется в исходное состояние, и на диске не будут сохранены никакие изменения.

В этом обучающем руководстве мы будем использовать расширение PDO PHP, обеспечивающее интерфейс работы с базами данных в PHP для выполнения транзакций MySQL на сервере Ubuntu 18.04.

Предварительные требования

Прежде чем мы начнем, нам потребуется следующее:

Шаг 1 — Создание тестовой базы данных и таблиц

Прежде чем начать работу с транзакциями MySQL, мы создадим образец базы данных и добавим несколько таблиц. Вначале войдите на сервер MySQL как root:

  1. sudo mysql -u root -p

Введите пароль root для MySQL в соответствующем диалоге и нажмите ENTER, чтобы продолжить. Затем создайте базу данных, которую мы назовем sample_store для целей этого обучающего руководства:

  1. CREATE DATABASE sample_store;

Результат будет выглядеть следующим образом:

Output
Query OK, 1 row affected (0.00 sec)

Создайте для базы данных пользователя с именем sample_user. Обязательно замените PASSWORD на более надежный пароль:

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

Предоставьте пользователю полные права доступа к базе данных sample_store:

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

В заключение перезагрузите права доступа MySQL:

  1. FLUSH PRIVILEGES;

После создания пользователя вы увидите следующие результаты:

Output
Query OK, 0 rows affected (0.01 sec) . . .

Мы создали базу данных и пользователя и теперь создадим несколько таблиц, чтобы продемонстрировать работу транзакций MySQL.

Выполните выход из сервера MySQL:

  1. QUIT;

После выхода из системы вы увидите следующий экран:

Output
Bye.

Войдите в систему с учетными данными пользователя sample_user, которого мы только что создали:

  1. sudo mysql -u sample_user -p

Введите пароль пользователя sample_user и нажмите ENTER, чтобы продолжить.

Переключитесь на базу данных sample_store, чтобы сделать ее текущей выбранной базой данных:

  1. USE sample_store;

Выбрав базу данных, вы увидите следующий экран:

Output
Database Changed.

Создайте таблицу products:

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

Эта команда создает таблицу products с полем product_id. Мы используем тип данных BIGINT, поддерживающий большие значения до 2^63-1. Для уникальной идентификации продуктов мы используем то же самое поле, что и PRIMARY KEY. Ключевое слово AUTO_INCREMENT предписывает MySQL генерировать следующее числовое значение при вставке новых продуктов.

Поле product_name относится к типу VARCHAR, который позволяет сохранять до 50 буквенно-цифровых символов. Для поля price продукта мы используем тип данных DOUBLE с плавающей запятой, чтобы в этом поле можно было размещать цены в форме десятичных чисел.

Мы используем InnoDB как ENGINE, поскольку эта база данных обеспечивает удобную поддержку транзакций MySQL в отличие от других систем хранения, таких как MyISAM.

После создания таблицы products вы увидите следующий экран:

Output
Query OK, 0 rows affected (0.02 sec)

Добавьте в таблицу products несколько элементов, выполнив следующие команды:

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

После каждой операции INSERT экран будет выглядеть примерно так:

Output
Query OK, 1 row affected (0.02 sec) . . .

Проверьте добавление данных в таблицу products:

  1. 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 для хранения базовых данных о клиентах:

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

Как и в случае с таблицей products, мы используем тип данных BIGINT для customer_id, и благодаря этому таблица может поддерживать до 2^63-1 записей о клиентах. Ключевое слово AUTO_INCREMENT инкрементально увеличивает значение columns после вставки нового клиента.

Поскольку столбец customer_name принимает буквенно-цифровые значения, мы используем тип данных VARCHAR с лимитом 50 символов. Мы снова указываем InnoDB в поле ENGINE как систему хранения для поддержки транзакций.

После запуска предыдущей команды для создания таблицы customers вы увидите следующий экран:

Output
Query OK, 0 rows affected (0.02 sec)

Добавим в таблицу трех клиентов в качестве примера. Запустите следующие команды:

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

После добавления клиентов вы увидите примерно следующий экран:

Output
Query OK, 1 row affected (0.02 sec) . . .

Затем проверьте данные в таблице customers:

  1. 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, выполните следующую команду:

  1. 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, которую мы создали на предыдущем шаге.

Результат будет выглядеть следующим образом:

Output
Query OK, 0 rows affected (0.02 sec)

Поскольку заказ одного клиента может содержать несколько товаров, нам нужна таблица orders_products для хранения этой информации.

Для создания таблицы orders_products запустите следующую команду:

  1. 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 должна сопоставлять другие ранее созданные таблицы, поскольку при использовании транзакций один заказ повлияет на несколько таблиц.

Сообщение на экране подтвердит создание таблицы:

Output
Query OK, 0 rows affected (0.02 sec)

Пока мы не будем добавлять данные в таблицы orders и orders_products, но позднее мы сделаем это с помощью скрипта PHP для выполнения транзакций MySQL.

Выполните выход из сервера MySQL:

  1. QUIT;

Наша схема базы данных готова, и мы заполнили ее несколькими записями. Теперь мы создадим класс PHP для обработки соединений базы данных и транзакций MySQL.

Шаг 2 — Проектирование класса PHP для обработки транзакций MySQL

На этом шаге мы создадим класс PHP, который будет использовать объекты PDO (объекты данных PHP) для обработки транзакций MySQL. Этот класс будет подключаться к нашей базе данных MySQL и атомарно вставлять данные в базу данных.

Сохраните файл класса в корневой директории вашего веб-сервера Apache. Для этого создайте файл DBTransaction.php в текстовом редакторе:

  1. 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 запустить транзакцию и отключает автоматическую запись до отправки команды commit.

  • insertTransaction: этот метод принимает два аргумента. Переменная $sql хранит выражение SQL, выполняемое, пока переменная $data имеет значение массива данных, которые требуется привязать к выражению SQL, поскольку вы используете подготовленные выражения. Данные передаются как массив в метод insertTransaction.

  • submitTransaction: этот метод записывает изменения в базу данных на постоянной основе с помощью команды commit(). При обнаружении ошибки и проблемах с транзакциями этот метод вызывает метод rollBack() для возвращения базы данных в первоначальное состояние в случае создания исключения PDO.

Ваш класс DBTransaction инициализирует транзакцию, готовит разные команды SQL к выполнению и выполняет атомарную запись изменений в базу данных при отсутствии проблем. В противном случае выполняется откат транзакции. Кроме того, этот класс позволяет получить только что созданную запись order_id из общедоступного свойства last_insert_id.

Теперь мы можем вызывать класс DBTransaction и использовать его в любом коде PHP, о создании которого мы поговорим далее.

Шаг 3 — Создание скрипта PHP для использования класса DBTransaction

Мы создадим скрипт PHP, который будет реализовывать класс DBTransaction и отправлять группу команд SQL в базу данных MySQL. Вы сможете имитировать рабочий процесс обработки заказа клиента в онлайн-корзине.

Эти запросы SQL будут влиять на таблицы orders и orders_products. Ваш класс DBTransaction должен допускать изменения базы данных только в случае выполнения всех запросов без ошибок. В ином случае вы получите сообщение об ошибке и будет выполнен откат всех изменений.

Вы создаете один заказ для клиента JOHN DOE с идентификатором customer_id 1. Заказ клиента содержит три разных товара в разном количестве из таблицы products. Скрипт PHP берет данные по заказу клиента и отправляет их в класс DBTransaction.

Создайте файл orders.php:

  1. 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, который инициализирует экземпляр класса DBTransaction, созданный вами на шаге 2.

В этом скрипте мы включаем файл 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.

Команда для вставки в таблицу orders готовится с помощью метода insertTransaction. После этого мы получаем значение общедоступного свойства last_insert_id из класса DBTransaction и используем его как $order_id.

Теперь у нас есть значение $order_id и мы можем использовать уникальный идентификатор для вставки заказанных клиентом товаров в таблицу orders_products.

В заключение мы вызовем метод submitTransaction для записи всех деталей заказа клиента в базу данных в случае отсутствия проблем. При обнаружении проблем метод submitTransaction произведет откат предлагаемых изменений.

Запустим скрипт orders.php в браузере. Введите следующий URL, заменив your-server-IP публичным IP-адресом вашего сервера:

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

Вы увидите подтверждение успешной отправки записей:

Вывод PHP из класса MySQL Transactions

Наш скрипт PHP работает ожидаемым образом и выполнил атомарную отправку в базу данных заказа и товаров в заказе.

Мы запустили файл orders.php в окне браузера. Скрипт вызвал класс DBTransaction, который отправил детали orders в базу данных. На следующем шаге мы проверим сохранение записей в связанных таблицах базы данных.

Шаг 4 — Подтверждение записей в базе данных

На этом шаге мы убедимся, что транзакция заказа клиента, инициированная через браузер, была размещена в таблицах базы данных ожидаемым образом.

Для этого снова выполним вход в базу данных MySQL:

  1. sudo mysql -u sample_user -p

Введите пароль пользователя sample_user и нажмите ENTER, чтобы продолжить.

Переключитесь на базу данных sample_store:

  1. USE sample_store;

Убедитесь, что база данных изменилась, прежде чем продолжить. Экран должен выглядеть следующим образом:

Output
Database Changed.

Затем используйте следующую команду для извлечения записей из таблицы orders:

  1. 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:

  1. 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, ознакомьтесь с руководством по InnoDB и модели ACID на официальном сайте MySQL. Посетите страницу материалов по MySQL, чтобы найти другие обучающие руководства, статьи и ответы на вопросы.


Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar
Developer and author at DigitalOcean.

Default avatar
Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?
Leave a comment

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!