PHP: полный мануал с примерами, функциями и ООП

Как устроена база и зачем связывать таблицы

Магазин должен помнить покупателя, его заказы и товары в каждом заказе. Здесь сначала строим понятную модель хранения, затем выполняем запросы и видим таблицы результата. Базовое подключение PHP к MySQL уже разобрано отдельно; текущая глава объясняет саму реляционную базу, а не требует знать SQL заранее.

Сервер MySQL — отдельная работающая программа, обычно обслуживающая множество соединений. База (schema) объединяет таблицы и другие объекты. Таблица описывает строки одного вида, столбцы задают именованные свойства и типы. Строка — один товар или один заказ; ячейка — одно значение конкретной колонки. PHP-массив появляется после чтения ответа и не является всей базой. Закрытие PHP-процесса не стирает обычную сохранённую таблицу.

Удобная аналогия — несколько связанных журналов: покупатели, заказы, позиции. Она ограничена: сервер не перебирает обязательно каждый лист подряд, а пользуется индексами, оптимизатором, буфером страниц и механизмом конкурентного доступа. Запись COMMIT — не обещание, что каждый байт тут же переписан в произвольный файл: InnoDB использует журналирование и собственные правила устойчивости, зависящие от конфигурации.

Реляционный подход связывает данные ключами и задаёт операции над наборами строк. Сначала определите сущности и их смысл, затем связи и ограничения, и лишь потом форму ответа для конкретного экрана. Это небольшой практический путь к SQL, а не объявление полного отдельного справочника MySQL: темы администрирования, репликации и глубокой оптимизации требуют собственных глав.

Запуск, входные таблицы и сохранённый результат

Все самостоятельные архивы создают свои входные таблицы в отдельной локальной учебной базе. В них лежат example.php, sql106-helper.php и README.txt. Конфигурация соединения читается из переменных окружения; паролей в архиве нет. База и ограниченный пользователь готовятся по инструкции подключения. Для этих SQL-примеров нужны CREATE/DROP/ALTER/INDEX/REFERENCES и операции чтения/записи на учебную базу.

Сначала дан короткий SQL и разбор; полный PHP-код ниже раскрывается отдельно. Скрипт создаёт таблицы с префиксом mysql106_sql_ и удаляет только созданные текущим запуском таблицы в finally, дочерние раньше родителей. Существующая одноимённая таблица вызовет ошибку и не будет удалена. Это обычные таблицы, потому что TEMPORARY в InnoDB не поддерживает FK. Если процесс был принудительно прерван, оставшиеся собственные таблицы следует исследовать вручную; не запускайте параллельно один архив.

Обычная команда: php example.php. Только эксперимент на 100 000 строк запускайте как php -d memory_limit=512M example.php: повышенный лимит позволяет показать намеренно плохой вариант, а не рекомендуется как исправление рабочего сайта. Результат — локальные result.html и result.json; диагностика отдельно сохраняет версии, реальные планы и измерения.

Создать таблицу с типами и прочитать две строки

Каталогу нужны два товара с названием, ценой и остатком. Сохраним их так, чтобы сервер знал, где число, где текст и как отличить один товар от другого. После INSERT чтение вернёт две показанные ниже строки, включая нулевой остаток кофе.

Исходные данные

Вход известен целиком; никаких файлов и уже существующих таблиц не требуется. До CREATE таблицы этого примера нет.

Что получится

Товары после INSERT

SQL: смысловая часть примера

Фрагмент выполняется в контексте таблиц этого самостоятельного примера; полная подготовка входа находится в архиве.

CREATE TABLE mysql106_sql_table_products (
    id INT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO mysql106_sql_table_products (id,name,price,stock)
VALUES (1,'Чай',120.50,8),(2,'Кофе',450.00,0);
SELECT id,name,price,stock
FROM mysql106_sql_table_products ORDER BY id;

Как читается запрос и почему он решает задачу

Одинарные кавычки обозначают строковый литерал, десятичная точка — дробную часть SQL-числа; точка с запятой завершает команду. Перенос строки служит читаемости. Полный PHP-пример отправляет каждую команду отдельно через mysqli, поэтому не требует multi_query. В запросах результата DECIMAL оставляем десятичной строкой: превращение её в PHP float снова внесло бы двоичное округление.

Ошибки и границы

В интерфейсе вывод может выглядеть как Excel, но сервер не обязан возвращать строки в порядке вставки. Без ORDER BY этот порядок не является договором. id здесь задан явно для прозрачности; AUTO_INCREMENT может назначать его сервером, но пропуски в таком счётчике допустимы. Имя и тип колонки — часть схемы, а не заголовок HTML.

<?php
declare(strict_types=1);
require __DIR__.'/sql106-helper.php';
$db=sql106Connect(); $created=[]; $blocks=[]; $checks=[]; $diagnostics=[];
try {
sql106Create($db,$created,'mysql106_sql_table_products','id INT PRIMARY KEY, name VARCHAR(60) NOT NULL, price DECIMAL(8,2) NOT NULL, stock INT NOT NULL');
$db->query("INSERT INTO mysql106_sql_table_products (id,name,price,stock) VALUES (1,'Чай',120.50,8),(2,'Кофе',450.00,0)");
$rows=sql106Read($db,'SELECT id,name,price,stock FROM mysql106_sql_table_products ORDER BY id');
$blocks=[sql106Block('Товары после INSERT',['id','name','price','stock'],$rows)];
$checks=['two_rows'=>count($rows)===2,'decimal_exact'=>$rows[0][2]==='120.50','zero_is_value'=>$rows[1][3]==='0'];
    sql106Report('mysql106-sql-table',$blocks,$checks,$db,$diagnostics);
} finally {
    sql106Cleanup($db,$created);
}
echo "table: SQL results saved in result.html and result.json\n";

table: SQL results saved in result.html and result.json

Открыть сохранённый результат реального запуска · Скачать таблицы JSON.

Попробуйте сами

Добавьте Какао с id=3, ценой 210.00 и остатком 4, расширив VALUES. Запрос с ORDER BY id должен вернуть третью строку. Затем измените только список SELECT на name,stock: строки останутся тремя, колонок станет две.

Защитить товар ключом, уникальным артикулом и CHECK

Импортёр может прислать повторный артикул или отрицательный остаток. Правила базы защищают данные, даже если ошибку допустил другой скрипт. Пример сохраняет один товар и отдельно показывает три действительно отклонённые команды.

Исходные данные

Первая команда передаёт только id=1 и sku=TEA. stock берёт DEFAULT 0, note остаётся NULL. Далее независимо проверяем повтор id, повтор sku и stock=-1. Ошибки ловит PHP, чтобы показать все три случая; без обработчика выполнение остановится на первой.

Что получится

Сохранённый товар

Отклонённые записи

SQL: смысловая часть примера

Фрагмент выполняется в контексте таблиц этого самостоятельного примера; полная подготовка входа находится в архиве.

CREATE TABLE mysql106_sql_constraints_products (
    id INT PRIMARY KEY,
    sku VARCHAR(20) NOT NULL UNIQUE,
    stock INT NOT NULL DEFAULT 0,
    note VARCHAR(50) NULL,
    CONSTRAINT mysql106_sql_qty_nonnegative CHECK (stock >= 0)
) ENGINE=InnoDB;
INSERT INTO mysql106_sql_constraints_products (id,sku) VALUES (1,'TEA');
-- Каждая из следующих трёх команд отдельно завершится ошибкой:
INSERT INTO mysql106_sql_constraints_products (id,sku) VALUES (1,'COFFEE');
INSERT INTO mysql106_sql_constraints_products (id,sku) VALUES (2,'TEA');
INSERT INTO mysql106_sql_constraints_products (id,sku,stock) VALUES (3,'COFFEE',-1);

Как читается запрос и почему он решает задачу

PRIMARY KEY даёт идентичность строке, UNIQUE запрещает одинаковые значения альтернативного ключа, CHECK проверяет выражение, DEFAULT подставляет значение только при соответствующем пропуске. Эти задачи различны: уникальный id не мешает двум разным id иметь один SKU без UNIQUE.

CONSTRAINT mysql106_sql_qty_nonnegative задаёт понятное имя ограничения. Код 1062 относится к конфликту уникального ключа, 3819 — нарушению CHECK в используемом MySQL. Внешнему посетителю покажите понятное сообщение, а не полный SQL и конфигурацию. Прикладная проверка нужна для удобного ответа пользователю, ограничения БД — для целостности при любом писателе.

Ошибки и границы

DEFAULT не вычисляет реальный остаток из склада: это выбранное бизнес-правило. NULL означает отсутствие значения; он отличается от 0 и пустой строки. Регистрозависимость SKU определяется collation колонки: UNIQUE с регистронезависимой collation может считать tea и TEA одинаковыми. Выберите это правило осознанно; пример использует один регистр.

<?php
declare(strict_types=1);
require __DIR__.'/sql106-helper.php';
$db=sql106Connect(); $created=[]; $blocks=[]; $checks=[]; $diagnostics=[];
try {
sql106Create($db,$created,'mysql106_sql_constraints_products',"id INT PRIMARY KEY, sku VARCHAR(20) NOT NULL UNIQUE, stock INT NOT NULL DEFAULT 0, note VARCHAR(50) NULL, CONSTRAINT mysql106_sql_qty_nonnegative CHECK (stock>=0)");
$db->query("INSERT INTO mysql106_sql_constraints_products (id,sku) VALUES (1,'TEA')");
$a=sql106Error($db,"INSERT INTO mysql106_sql_constraints_products (id,sku) VALUES (1,'COFFEE')",1062);
$b=sql106Error($db,"INSERT INTO mysql106_sql_constraints_products (id,sku) VALUES (2,'TEA')",1062);
$c=sql106Error($db,"INSERT INTO mysql106_sql_constraints_products (id,sku,stock) VALUES (3,'COFFEE',-1)",3819);
$rows=sql106Read($db,'SELECT id,sku,stock,note FROM mysql106_sql_constraints_products ORDER BY id');
$blocks=[sql106Block('Сохранённый товар',['id','sku','stock','note'],$rows),sql106Block('Отклонённые записи',['Проверка','Код'],[['PRIMARY KEY',(string)$a],['UNIQUE',(string)$b],['CHECK',(string)$c]])];
$checks=['only_valid_row'=>count($rows)===1,'default_zero'=>$rows[0][2]==='0','null_not_empty'=>$rows[0][3]===null,'three_rejections'=>$a===1062 && $b===1062 && $c===3819];
    sql106Report('mysql106-sql-constraints',$blocks,$checks,$db,$diagnostics);
} finally {
    sql106Cleanup($db,$created);
}
echo "constraints: SQL results saved in result.html and result.json\n";

constraints: SQL results saved in result.html and result.json

Открыть сохранённый результат реального запуска · Скачать таблицы JSON.

Попробуйте сами

Если товар без артикула недопустим, оставьте NOT NULL и отдельно запретите пустую строку проверкой ввода или CHECK(sku <> ''). Продумайте, должны ли пробелы и регистр иметь смысл; не меняйте нормализацию уже существующего каталога без проверки коллизий.

Связать покупателя и заказы: один ко многим

У Анны два заказа, у Бориса пока ни одного. Гостевой заказ допустим без аккаунта. База должна запретить заказ на несуществующего клиента и случайное удаление Анны вместе с потерей смысла её истории.

Исходные данные

В отчёте остаются эти же три заказа: ошибочная попытка добавить customer_id=999 не создаёт четвёртую строку.

Что получится

Покупатели

Заказы

Защита связи

SQL: смысловая часть примера

Фрагмент выполняется в контексте таблиц этого самостоятельного примера; полная подготовка входа находится в архиве.

CREATE TABLE mysql106_sql_fk_customers (
    id INT PRIMARY KEY, name VARCHAR(60) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE mysql106_sql_fk_orders (
    id INT PRIMARY KEY,
    customer_id INT NULL,
    CONSTRAINT mysql106_sql_fk_customer FOREIGN KEY (customer_id)
        REFERENCES mysql106_sql_fk_customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
INSERT INTO mysql106_sql_fk_customers (id,name) VALUES (1,'Анна'),(2,'Борис');
INSERT INTO mysql106_sql_fk_orders (id,customer_id) VALUES (101,1),(102,1),(103,NULL);

Как читается запрос и почему он решает задачу

FOREIGN KEY(customer_id) REFERENCES customers(id) связывает значение дочерней колонки с ключом родителя. Здесь тип INT совпадает с INT родителя; при UNSIGNED это свойство тоже должно совпасть. Родителя создают раньше дочерней таблицы и записывают раньше ссылающегося заказа. В MySQL 8.4 используем ссылку на PRIMARY KEY, не полагаясь на исторические послабления для неуникальных индексов.

Отношение 1:N читается так: один покупатель может иметь много заказов, каждый заказ связан максимум с одним покупателем. NULL разрешает ноль связанных покупателей для гостевого заказа. Если каждый заказ обязан принадлежать аккаунту, объявите customer_id NOT NULL. Это решение модели, а не вкусовое оформление SQL.

FK проверяет допустимость сохранения. Он не подставляет имя покупателя в выборку: для этого будет JOIN. JOIN умеет соединять данные и без FK, но без ограничения сервер не предотвращает появление сирот.

Ошибки и границы

Не выключайте foreign_key_checks ради повседневного сохранения: это снимает защиту, а не исправляет порядок записи. InnoDB проверяет FK сразу, не откладывая до COMMIT. Для ON DELETE SET NULL колонка должна разрешать NULL. CASCADE удаляет реальные дочерние данные и подходит не для любой истории; заказы часто сохраняют и используют отдельный статус клиента.

<?php
declare(strict_types=1);
require __DIR__.'/sql106-helper.php';
$db=sql106Connect(); $created=[]; $blocks=[]; $checks=[]; $diagnostics=[];
try {
sql106Create($db,$created,'mysql106_sql_fk_customers','id INT PRIMARY KEY, name VARCHAR(60) NOT NULL');
sql106Create($db,$created,'mysql106_sql_fk_orders','id INT PRIMARY KEY, customer_id INT NULL, CONSTRAINT mysql106_sql_fk_customer FOREIGN KEY (customer_id) REFERENCES mysql106_sql_fk_customers(id) ON DELETE RESTRICT');
$db->query("INSERT INTO mysql106_sql_fk_customers (id,name) VALUES (1,'Анна'),(2,'Борис')");
$db->query('INSERT INTO mysql106_sql_fk_orders (id,customer_id) VALUES (101,1),(102,1),(103,NULL)');
$orphan=sql106Error($db,'INSERT INTO mysql106_sql_fk_orders (id,customer_id) VALUES (104,999)',1452);
$restricted=sql106Error($db,'DELETE FROM mysql106_sql_fk_customers WHERE id=1',1451);
$rows=sql106Read($db,'SELECT id,customer_id FROM mysql106_sql_fk_orders ORDER BY id');
$blocks=[sql106Block('Покупатели',['id','name'],sql106Read($db,'SELECT id,name FROM mysql106_sql_fk_customers ORDER BY id')),sql106Block('Заказы',['id','customer_id'],$rows),sql106Block('Защита связи',['Действие','Код'],[['Заказ с customer_id=999',(string)$orphan],['Удалить покупателя 1',(string)$restricted]])];
$checks=['three_orders'=>count($rows)===3,'guest_allowed'=>$rows[2][1]===null,'parent_preserved'=>count($blocks[0]['rows'])===2,'fk_errors'=>$orphan===1452 && $restricted===1451];
    sql106Report('mysql106-sql-foreign-key',$blocks,$checks,$db,$diagnostics);
} finally {
    sql106Cleanup($db,$created);
}
echo "foreign-key: SQL results saved in result.html and result.json\n";

foreign-key: SQL results saved in result.html and result.json

Открыть сохранённый результат реального запуска · Скачать таблицы JSON.

Попробуйте сами

Предложенное решение для обязательного аккаунта: заменить customer_id INT NULL на INT NOT NULL и убрать гостевой INSERT. Для сохранения заказа после удаления аккаунта можно рассмотреть SET NULL, но тогда имя/адрес заказа следует хранить как осмысленный исторический снимок и соблюдать требования к данным.

Профиль один к одному и позиции заказа многие ко многим

Одному клиенту нужен максимум один профиль, а в заказе может быть несколько товаров; один товар встречается во многих заказах. Покажем обе связи на конкретных ключах, без строки вида product_ids="10,20".

Исходные данные

Что получится

Позиции заказов

Профили после удаления Бориса

SQL: смысловая часть примера

Фрагмент выполняется в контексте таблиц этого самостоятельного примера; полная подготовка входа находится в архиве.

CREATE TABLE mysql106_sql_rel_profiles (
    customer_id INT PRIMARY KEY,
    city VARCHAR(60) NOT NULL,
    FOREIGN KEY(customer_id) REFERENCES mysql106_sql_rel_customers(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE mysql106_sql_rel_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL CHECK(qty>0),
    unit_price DECIMAL(8,2) NOT NULL CHECK(unit_price>=0),
    PRIMARY KEY(order_id,product_id),
    FOREIGN KEY(order_id) REFERENCES mysql106_sql_rel_orders(id) ON DELETE CASCADE,
    FOREIGN KEY(product_id) REFERENCES mysql106_sql_rel_products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

Как читается запрос и почему он решает задачу

Сокращённые имена в схеме соответствуют постоянным учебным таблицам mysql106_sql_rel_customers, _profiles, _orders, _items и _products. Каждая строка order_items хранит одну связь заказа с товаром и свойства именно этой связи: количество и цену покупки.

При удалении Бориса с id=2 CASCADE удаляет его профиль: отдельного смысла без родителя у профиля нет. Анна и её заказы не затронуты. Повтор пары (101,10) отклоняется; повтор product_id=10 в другом заказе допустим.

Нормализация здесь практична: название клиента хранится у клиента, список товарных ID не упаковывается в CSV-строку. Так каждый ID имеет тип и FK, по связям можно искать, считать и соединять. Это не запрет любого JSON или любой копии: неизменяемый адрес доставки и цена покупки могут быть нужными снимками истории. Поле unit_price относится к покупке, products.price — к текущему каталогу.

Ошибки и границы

CASCADE выбирают по смыслу владения данными. Удаление заказа вместе с позициями может быть допустимо для учебного черновика, но оформленный заказ часто требует статуса отмены и аудита. Наличие FK не создаёт автоматически форму редактирования или договор сохранения исторических полей.

<?php
declare(strict_types=1);
require __DIR__.'/sql106-helper.php';
$db=sql106Connect(); $created=[]; $blocks=[]; $checks=[]; $diagnostics=[];
try {
sql106Create($db,$created,'mysql106_sql_rel_customers','id INT PRIMARY KEY, name VARCHAR(60) NOT NULL');
sql106Create($db,$created,'mysql106_sql_rel_profiles','customer_id INT PRIMARY KEY, city VARCHAR(60) NOT NULL, FOREIGN KEY (customer_id) REFERENCES mysql106_sql_rel_customers(id) ON DELETE CASCADE');
sql106Create($db,$created,'mysql106_sql_rel_products','id INT PRIMARY KEY, name VARCHAR(60) NOT NULL, price DECIMAL(8,2) NOT NULL');
sql106Create($db,$created,'mysql106_sql_rel_orders','id INT PRIMARY KEY, customer_id INT NOT NULL, FOREIGN KEY(customer_id) REFERENCES mysql106_sql_rel_customers(id) ON DELETE RESTRICT');
sql106Create($db,$created,'mysql106_sql_rel_items','order_id INT NOT NULL, product_id INT NOT NULL, qty INT NOT NULL CHECK(qty>0), unit_price DECIMAL(8,2) NOT NULL CHECK(unit_price>=0), PRIMARY KEY(order_id,product_id), FOREIGN KEY(order_id) REFERENCES mysql106_sql_rel_orders(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES mysql106_sql_rel_products(id) ON DELETE RESTRICT');
$db->query("INSERT INTO mysql106_sql_rel_customers VALUES (1,'Анна'),(2,'Борис')");
$db->query("INSERT INTO mysql106_sql_rel_profiles VALUES (1,'Москва'),(2,'Тула')");
$db->query("INSERT INTO mysql106_sql_rel_products VALUES (10,'Чай',120.00),(20,'Кофе',450.00)");
$db->query('INSERT INTO mysql106_sql_rel_orders VALUES (101,1),(102,1)');
$db->query('INSERT INTO mysql106_sql_rel_items VALUES (101,10,2,120.00),(101,20,1,450.00),(102,10,1,120.00)');
$duplicate=sql106Error($db,'INSERT INTO mysql106_sql_rel_items VALUES (101,10,1,120.00)',1062);
$db->query('DELETE FROM mysql106_sql_rel_customers WHERE id=2');
$items=sql106Read($db,'SELECT order_id,product_id,qty,unit_price FROM mysql106_sql_rel_items ORDER BY order_id,product_id');
$profiles=sql106Read($db,'SELECT customer_id,city FROM mysql106_sql_rel_profiles ORDER BY customer_id');
$blocks=[sql106Block('Позиции заказов',['order_id','product_id','qty','unit_price'],$items),sql106Block('Профили после удаления Бориса',['customer_id','city'],$profiles)];
$checks=['three_links'=>count($items)===3,'same_product_two_orders'=>$items[0][1]===$items[2][1],'pair_unique'=>$duplicate===1062,'profile_cascade'=>$profiles===[['1','Москва']]];
    sql106Report('mysql106-sql-relations',$blocks,$checks,$db,$diagnostics);
} finally {
    sql106Cleanup($db,$created);
}
echo "relations: SQL results saved in result.html and result.json\n";

relations: SQL results saved in result.html and result.json

Открыть сохранённый результат реального запуска · Скачать таблицы JSON.

Попробуйте сами

Добавьте заказ 103 покупателю 1 и позицию (103,20,2,450.00). Существующие пары не конфликтуют. Затем попробуйте добавить второй профиль customer_id=1: PRIMARY KEY должен отклонить его. Этот отказ не означает, что профиль невозможно изменить — для изменения существующей строки нужен UPDATE.

Ситуация из практического обсуждения: Is storing a delimited list in a database column really that bad?. Автор формы сохранил набор выбранных значений одной строкой с разделителями и спрашивает, как сделать схему поддерживаемой. Наш пример использует отдельные строки связей и FK, а не список товарных ID в строке. Обсуждение помогает выбрать задачу; правила MySQL проверены по первичным источникам, код и данные написаны для этой главы.

Добавить поле и сохранить историческую цену заказа

Каталогу понадобился флаг публикации active. Одновременно цена чая выросла со 120 до 150. После изменения новый каталог показывает 150, но ранее оформленный заказ остаётся на 240 за две штуки.

Исходные данные

В начале колонки active ещё нет. У заказа уже сохранена цена на момент оформления — она не восстанавливается позднее из изменяемого каталога.

Что получится

После добавления active

Текущий каталог

Заказ сохраняет цену покупки

SQL: смысловая часть примера

Фрагмент выполняется в контексте таблиц этого самостоятельного примера; полная подготовка входа находится в архиве.

ALTER TABLE mysql106_sql_mig_products
ADD COLUMN active TINYINT NOT NULL DEFAULT 1;
UPDATE mysql106_sql_mig_products SET price=150.00 WHERE id=10;
SELECT order_id,unit_price,qty,unit_price*qty AS total
FROM mysql106_sql_mig_items;

Как читается запрос и почему он решает задачу

ALTER TABLE изменяет структуру существующей таблицы. ADD COLUMN добавляет колонку active типа TINYINT; NOT NULL требует значение, DEFAULT 1 задаёт начальное правило: все текущие товары активны. Это пример маленькой миграции схемы. UPDATE ... SET price=150.00 WHERE id=10 меняет данные ровно выбранного товара; отсутствие WHERE изменило бы все строки.

Цена заказа берётся из order_items.unit_price и умножается на qty. AS total даёт вычисленному столбцу читаемое имя. Если каждый раз брать products.price, старые заказы внезапно меняли бы стоимость при обновлении прайса. Это обоснованный снимок исторического факта, а не случайное дублирование одной текущей информации.

В полном коде SELECT до и после изменения показывает реальные значения. Для серьёзной миграции сначала выбирают правило для старых строк, оценивают блокировки и объём, обеспечивают резервное восстановление и совместимость версий приложения. Добавление колонки не обязано физически переписывать все строки: MySQL выбирает допустимый алгоритм ALTER; пример демонстрирует результат, не обещает время работы.

Ошибки и границы

CREATE/ALTER/DROP для обычных таблиц вызывают неявные COMMIT. Не рассчитывайте откатить ALTER вместе с заказом через ROLLBACK. Разделяйте миграции структуры и транзакции изменения строк. DEFAULT 1 — конкретное решение для учебного каталога, не универсальный ответ для существующего магазина.

<?php
declare(strict_types=1);
require __DIR__.'/sql106-helper.php';
$db=sql106Connect(); $created=[]; $blocks=[]; $checks=[]; $diagnostics=[];
try {
sql106Create($db,$created,'mysql106_sql_mig_products','id INT PRIMARY KEY, price DECIMAL(8,2) NOT NULL');
sql106Create($db,$created,'mysql106_sql_mig_items','order_id INT PRIMARY KEY, product_id INT NOT NULL, qty INT NOT NULL, unit_price DECIMAL(8,2) NOT NULL, FOREIGN KEY(product_id) REFERENCES mysql106_sql_mig_products(id)');
$db->query('INSERT INTO mysql106_sql_mig_products VALUES (10,120.00)');
$db->query('INSERT INTO mysql106_sql_mig_items VALUES (101,10,2,120.00)');
$db->query('ALTER TABLE mysql106_sql_mig_products ADD COLUMN active TINYINT NOT NULL DEFAULT 1');
$before=sql106Read($db,'SELECT id,price,active FROM mysql106_sql_mig_products');
$db->query('UPDATE mysql106_sql_mig_products SET price=150.00 WHERE id=10');
$after=sql106Read($db,'SELECT id,price,active FROM mysql106_sql_mig_products');
$history=sql106Read($db,'SELECT order_id,unit_price,qty,unit_price*qty AS total FROM mysql106_sql_mig_items');
$blocks=[sql106Block('После добавления active',['id','price','active'],$before),sql106Block('Текущий каталог',['id','price','active'],$after),sql106Block('Заказ сохраняет цену покупки',['order_id','unit_price','qty','total'],$history)];
$checks=['default_backfilled'=>$before[0][2]==='1','new_catalog_price'=>$after[0][1]==='150.00','old_order_price'=>$history[0][1]==='120.00' && $history[0][3]==='240.00'];
    sql106Report('mysql106-sql-migration',$blocks,$checks,$db,$diagnostics);
} finally {
    sql106Cleanup($db,$created);
}
echo "migration: SQL results saved in result.html and result.json\n";

migration: SQL results saved in result.html and result.json

Открыть сохранённый результат реального запуска · Скачать таблицы JSON.

Попробуйте сами

Если новые товары должны быть скрыты до проверки, выберите DEFAULT 0 для новых записей. Отдельно определите, какие существующие строки перевести в 1; не применяйте бездумный UPDATE ко всему рабочему каталогу.

Записать заказ и его позиции атомарно

Заказ имеет заголовок и несколько позиций. Ошибка во второй позиции не должна оставлять «полузаказ». Первая попытка с неизвестным товаром откатывает и заголовок, и первую позицию; вторая сохраняет целый заказ.

Исходные данные

Таблицы orders и order_items сначала пустые. Первая попытка: заказ 101, две штуки товара 10, затем отсутствующий товар 999. Успешная попытка: заказ 102, товары 10 и 20. Таблицы и связи созданы до начала транзакции.

Что получится

Количество связанных записей

Успешный заказ

SQL: смысловая часть примера

Фрагмент выполняется в контексте таблиц этого самостоятельного примера; полная подготовка входа находится в архиве.

START TRANSACTION;
INSERT INTO mysql106_sql_tx_orders VALUES (101);
INSERT INTO mysql106_sql_tx_items VALUES (101,10,2,120.00);
-- Товара 999 нет: следующий INSERT выдаёт ошибку FK.
INSERT INTO mysql106_sql_tx_items VALUES (101,999,1,450.00);
ROLLBACK; -- Выполняет обработчик ошибки, отменяя и заказ, и первую позицию.
-- В успешной ветке вместо ROLLBACK выполняется COMMIT.

Как читается запрос и почему он решает задачу

START TRANSACTION открывает группу изменений; COMMIT подтверждает её, ROLLBACK отменяет. В mysqli это begin_transaction(), commit(), rollback(). Используется одно соединение с InnoDB, иначе это не одна транзакция. При обычном autocommit отдельные удачные команды уже фиксируются — поздний ROLLBACK их не вернёт.

Существенная деталь: отказ одного INSERT по FK сам по себе не означает автоматический откат всех ранее успешных команд транзакции. Наш catch явно выполняет rollback. После успешного сценария сумма двух строк соответствует одному заказу; DDL внутри транзакции отсутствует. Транзакция защищает целостность, но не решает самостоятельно повтор оплаты, отправку письма или согласование с внешним API.

Ошибки и границы

На рабочем сервере возможны deadlock и lock timeout: порядок блокировок и повтор всей идемпотентной операции проектируют отдельно. Здесь нет оплаты и конкурирующих писателей, поэтому пример не выдаётся за готовый платёжный сервис. Отдельная задача атомарного уменьшения остатка разобрана в главе о договоре данных.

<?php
declare(strict_types=1);
require __DIR__.'/sql106-helper.php';
$db=sql106Connect(); $created=[]; $blocks=[]; $checks=[]; $diagnostics=[];
try {
sql106Create($db,$created,'mysql106_sql_tx_products','id INT PRIMARY KEY, price DECIMAL(8,2) NOT NULL');
sql106Create($db,$created,'mysql106_sql_tx_orders','id INT PRIMARY KEY');
sql106Create($db,$created,'mysql106_sql_tx_items','order_id INT NOT NULL, product_id INT NOT NULL, qty INT NOT NULL, unit_price DECIMAL(8,2) NOT NULL, PRIMARY KEY(order_id,product_id), FOREIGN KEY(order_id) REFERENCES mysql106_sql_tx_orders(id), FOREIGN KEY(product_id) REFERENCES mysql106_sql_tx_products(id)');
$db->query('INSERT INTO mysql106_sql_tx_products VALUES (10,120.00),(20,450.00)');
$snapshot=function(string $stage)use($db):array{return [$stage,(string)$db->query('SELECT COUNT(*) AS n FROM mysql106_sql_tx_orders')->fetch_assoc()['n'],(string)$db->query('SELECT COUNT(*) AS n FROM mysql106_sql_tx_items')->fetch_assoc()['n']];};
$rows=[$snapshot('До')]; $code=0;
$db->begin_transaction();
try {
    $db->query('INSERT INTO mysql106_sql_tx_orders VALUES (101)');
    $db->query('INSERT INTO mysql106_sql_tx_items VALUES (101,10,2,120.00)');
    $db->query('INSERT INTO mysql106_sql_tx_items VALUES (101,999,1,450.00)');
    $db->commit();
} catch (mysqli_sql_exception $error) { $db->rollback();$code=$error->getCode();if($code!==1452){throw $error;} }
$rows[]=$snapshot('После ошибки и ROLLBACK');
$db->begin_transaction();
try {
    $db->query('INSERT INTO mysql106_sql_tx_orders VALUES (102)');
    $db->query('INSERT INTO mysql106_sql_tx_items VALUES (102,10,2,120.00),(102,20,1,450.00)');
    $db->commit();
} catch(Throwable $error){$db->rollback();throw $error;}
$rows[]=$snapshot('После COMMIT');
$blocks=[sql106Block('Количество связанных записей',['Этап','orders','order_items'],$rows),sql106Block('Успешный заказ',['order_id','product_id','qty','unit_price'],sql106Read($db,'SELECT order_id,product_id,qty,unit_price FROM mysql106_sql_tx_items ORDER BY product_id'))];
$checks=['first_failed_fk'=>$code===1452,'rollback_parent_and_child'=>$rows[1]===['После ошибки и ROLLBACK','0','0'],'commit_complete'=>$rows[2]===['После COMMIT','1','2']];
    sql106Report('mysql106-sql-atomic-order',$blocks,$checks,$db,$diagnostics);
} finally {
    sql106Cleanup($db,$created);
}
echo "atomic-order: SQL results saved in result.html and result.json\n";

atomic-order: SQL results saved in result.html and result.json

Открыть сохранённый результат реального запуска · Скачать таблицы JSON.

Попробуйте сами

В успешной ветке удалите одну позицию из VALUES: сохранится один заказ с одной позицией. Если бизнес требует минимум две позиции, проверьте это до commit и вызывайте rollback при нарушении; FK обеспечивает существование родителей, а не нужное вашему сервису число строк.

От схемы к данным и запросам

Теперь можно отделить два вида правил: схема определяет допустимые связи и типы, а SELECT формирует результат конкретного экрана. Продолжайте с кодировками, NULL и параметрами PHP, затем с запросами для каталога, заказа и сводки. Для связей не нужно копировать всё в PHP: при известном ключе база сама находит нужные пары и возвращает нужный набор.