オンライン売買システムのデータベース設計: MySQL 8 によるゼロからプロレベルまでの 100 の例
オンラインショップの多くは、データベースの問題です。顧客はログインし、商品にはサイズと色があり、在庫は決してマイナスになってはならず、支払いが二重に届くこともあり、二人の買い手が同じ秒に最後の一点を購入しようとすることもあります。この記事では、そのようなショップのデータベースを段階的に設計します。オンラインショップに必要なテーブルを、主キー、外部キー、インデックスとその理由とともに示します。各テーブルのサンプルデータを示した後、重要なトランザクションの内部で何が起きるかを説明します。注文の確定、在庫の引き当て、支払いの成功と失敗、キャンセル、返金、二人の買い手による最後の一点の取り合いです。トランザクションがない場合に何が問題になるかも示します。商品一覧、商品ページ、カート、チェックアウト、注文履歴、管理画面の注文一覧のモックアップは、各画面がこれらのテーブルをどう読むかを示します。記事の最後には、MySQL 8 の構文による 100 の番号付きの例があります。DDL、サンプルデータ、クエリ、インデックス、トランザクション、ロック、ビュー、パーティショニング、レポート、実務の作法を扱います。正直な注記: この記事を準備したコンピューターには MySQL 8 がインストールされていなかったため、100 の例はここでは一つも実行していません。練習用データベースで自分で実行し、結果を確認してから頼ってください。
必要なもの
MySQL 8.0 以降を入れたコンピューター（Community Server、または mysql:8.0 の Docker イメージ）、mysql コマンドラインプログラムや MySQL Workbench などのクライアント、そして基本的な SQL（SELECT、INSERT、UPDATE、JOIN）が必要です。学習中は実際のショップのデータベースではなく、練習用のデータベースを使ってください。MySQL 8 の既定であり、トランザクション、行ロック、外部キーに対応する InnoDB エンジンを使います。
例の使い方
SQL の例は InnoDB を使う MySQL 8 向けです。練習用データベース（例: shop_practice）で番号順に実行してください。? はアプリが値を渡すパラメーターです。bash の例はターミナルで実行します。
トピック 1: ショップが答えるべき問いから始める
CREATE TABLE を書く前に、ショップが答えなければならない問いを挙げます。この顧客は何を所有しているか。今いくつ売れるか。梱包待ちの注文はどれか。各テーブルは、ほかと矛盾せずに一つの問いに答えるために存在します。このトピックでは MySQL 8 を安全に設定し、以降のすべてのテーブルが従う規則を示します。
テーブルとその中身
ショップには、顧客、住所、カテゴリー、商品、商品バリエーション、在庫、カート、カート明細、注文、注文明細、支払い、返金、出荷、出荷明細、レビュー、クーポンが必要です。マーケットプレイスなら、販売者と販売者への支払いも必要です。商品は販売するものを表し、バリエーションは SKU と価格を持つ実際に売れる単位です。注文明細は、購入時点の商品名と価格をコピーします。そのため、後で価格を変えても履歴は書き換わりません。
キー、制約、インデックス
すべてのテーブルに主キーがあります。外部キーは、顧客のいない注文のような孤立した行を防ぎます。UNIQUE キーは、メールアドレスや SKU のような値の重複を防ぎます。CHECK 制約は、マイナスの在庫のような不可能な値を拒否します。インデックスは、一人の顧客の注文履歴のような画面のクエリを速くします。この記事のインデックスには、それぞれ理由を併記しています。
MySQL 8 を安全に設定する
タイ語、日本語、中国語、アラビア語、絵文字が正しく保存されるように、utf8mb4 でデータベースを作成します。データの読み書きはできるがスキーマは変更できないアプリ用ユーザーを作り、厳格な SQL モードを有効にします。こうすると、不正な値は黙って変えられるのではなく拒否されます。パスワードは記事やソースコードではなく、シークレットストアに保管してください。
図解
例 1: サーバーのバージョンと既定値を確認
MySQL 8、utf8mb4、分離レベルを確認します。
SELECT VERSION() AS server_version,
@@character_set_server AS charset,
@@collation_server AS collation,
@@transaction_isolation AS isolation_level;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 2: ショップのデータベースを作成
utf8mb4 と照合順序をショップ全体で一つにします。
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 3: 最小権限のアプリ用ユーザーを作成
アプリはデータの読み書きはできますが、スキーマは変更できません。
CREATE USER IF NOT EXISTS 'shop_app'@'localhost' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
-- No DROP, ALTER or GRANT: the application cannot change the schema.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 4: 厳格な SQL モードを有効にする
不正なデータを黙って変えず、拒否します。
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ONLY_FULL_GROUP_BY';
SELECT @@SESSION.sql_mode AS sql_mode;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 5: InnoDB を確認
トランザクションと外部キーには InnoDB が必要です。
SELECT ENGINE, SUPPORT, TRANSACTIONS, XA
FROM information_schema.ENGINES
WHERE ENGINE = 'InnoDB';
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 6: 練習用データベースを初期化
ショップのデータに触れずに練習します。
-- Practice only: this removes the practice copy and nothing else.
DROP DATABASE IF EXISTS shop_practice;
CREATE DATABASE shop_practice CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 2: 顧客と住所
顧客は、一意のメールアドレスと状態を持つアカウントです。住所は顧客に属しますが、注文は配送先住所の控えを自分で持ちます。顧客は後で住所を編集または削除するかもしれないからです。このトピックでは、テーブル、サンプルデータ、そして各顧客の既定の住所は最大一つという規則を扱います。
アカウントごとに一行
customers テーブルは、自動採番の customer_id を主キーとし、email に UNIQUE キーを持ちます。MySQL 8 の utf8mb4_0900_ai_ci 照合順序では、メールアドレスの大文字と小文字は区別されないため、[email protected] と [email protected] は同じアカウントです。保存するのはパスワードのハッシュだけで、パスワードそのものは保存しません。status 列により、履歴を消さずにアカウントを停止できます。
住所は顧客に属する
addresses テーブルは customers を外部キーと ON DELETE CASCADE で参照するため、顧客を削除すると保存済みの住所も削除されます。orders テーブルはこれに依存しません。注文は受取人、住所、郵便番号を自分でコピーするからです。生成列を使うと、顧客ごとに既定の住所を一つに制限できます。UNIQUE キーは NULL を複数許すためです。
プライバシー: カード情報をデータベースに入れない
ショップがカード番号の全体を持つ必要はありません。保存するのは決済サービスのトークンと下四桁だけにし、カード情報は決済サービスに任せます。個人情報を入れるテーブルは最小限にし、バックアップ、ログ、レポートにも同じ個人情報が含まれることを忘れないでください。同じ保護が必要です。
例 7: 顧客テーブルを作成
メールアドレスが一意で、状態を持つアカウントです。
CREATE TABLE customers (
customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(254) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NULL,
status ENUM('active','blocked') NOT NULL DEFAULT 'active',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (customer_id),
UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 8: 住所テーブルを作成
一人の顧客が複数の住所を持て、外部キーで結ばれます。
CREATE TABLE addresses (
address_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
label VARCHAR(40) NOT NULL DEFAULT 'Home',
recipient_name VARCHAR(120) NOT NULL,
line1 VARCHAR(200) NOT NULL,
line2 VARCHAR(200) NULL,
city VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
country_code CHAR(2) NOT NULL,
is_default TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (address_id),
KEY idx_addresses_customer (customer_id),
CONSTRAINT fk_addresses_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 9: 顧客のサンプルデータを投入
テスト用のサンプルアカウントです。
INSERT INTO customers (email, password_hash, full_name, phone) VALUES
('[email protected]', '<bcrypt-hash-1>', 'Mali Sun', '0812345678'),
('[email protected]', '<bcrypt-hash-2>', 'Niran Chai', '0898765432'),
('[email protected]', '<bcrypt-hash-3>', 'Ploy Wong', NULL);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 10: 住所のサンプルデータを投入
顧客ごとに既定の住所は一つです。
INSERT INTO addresses
(customer_id, label, recipient_name, line1, city, postal_code, country_code, is_default) VALUES
(1, 'Home', 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', 1),
(1, 'Office', 'Mali Sun', '99 Rama IX Road', 'Bangkok', '10310', 'TH', 0),
(2, 'Home', 'Niran Chai', '45 Moo 2', 'Chiang Mai', '50000', 'TH', 1);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 11: 既定の住所を探す
複合インデックスを使う検索です。
SELECT address_id, recipient_name, line1, city
FROM addresses
WHERE customer_id = 1 AND is_default = 1;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 12: 顧客ごとに既定の住所は一つだけ
生成列で一意制約を実現します。
-- A generated column is NULL unless is_default = 1, and UNIQUE allows many NULLs.
ALTER TABLE addresses
ADD COLUMN default_owner BIGINT UNSIGNED
GENERATED ALWAYS AS (CASE WHEN is_default = 1 THEN customer_id END) STORED,
ADD UNIQUE KEY uq_one_default_address (default_owner);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 13: 顧客ごとの住所数を数える
LEFT JOIN で住所のない顧客も含めます。
SELECT c.customer_id, c.full_name, COUNT(a.address_id) AS address_count
FROM customers AS c
LEFT JOIN addresses AS a ON a.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY address_count DESC, c.customer_id;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 14: 外部キーと利用停止
親のない行は拒否され、利用停止は UPDATE です。
-- Fails with error 1452 (foreign key constraint) because customer 999 does not exist.
INSERT INTO addresses (customer_id, recipient_name, line1, city, postal_code, country_code)
VALUES (999, 'Nobody', 'Unknown', 'Nowhere', '00000', 'TH');
UPDATE customers SET status = 'blocked' WHERE customer_id = 3;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 3: 商品、カテゴリー、バリエーション
カタログは三つの階層を持ちます。カテゴリーは商品をまとめ、商品はショップが売るもの、バリエーションはSKU、サイズ、色、価格を持つ正確な単位です。在庫とカートは必ず商品ではなくバリエーションを指します。黒の M サイズの T シャツと白の L サイズの T シャツは、数える単位として別物だからです。
商品とバリエーション
products テーブルには、名前、slug、説明、基本価格、状態（draft、active、archived）を保存します。product_variants テーブルには、SKU、M / Black のようなオプション名、価格、is_active フラグを保存します。(product_id, option_label) に UNIQUE キーを置くと、同じ商品に同一のバリエーションが二つできるのを防げます。下書きの商品は、状態が active になるまで一覧に表示されません。
木構造のカテゴリー
categories テーブルは parent_id によって自分自身を参照するため、衣類の中に T シャツを含めることができます。外部キーには ON DELETE SET NULL を使います。親を削除すると、子は最上位のカテゴリーになります。カテゴリー以下のすべての商品を取得するには、再帰的な共通表式を使います。サブカテゴリーも含めて取得できます。
一覧と検索のクエリ
一覧では、各商品の有効なバリエーションの最安値を表示します。前のページの最後の product_id を使う keyset ページングは、大きなカタログでも速く動きます。OFFSET はページが進むほど遅くなります。ngram パーサーを使う FULLTEXT インデックスは、タイ語、日本語、中国語のように単語の間に空白がない言語の検索に対応します。
図解
例 15: 親を持つカテゴリ
同じテーブルを参照する外部キーで木構造を作ります。
CREATE TABLE categories (
category_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
parent_id INT UNSIGNED NULL,
name VARCHAR(100) NOT NULL,
slug VARCHAR(120) NOT NULL,
PRIMARY KEY (category_id),
UNIQUE KEY uq_categories_slug (slug),
KEY idx_categories_parent (parent_id),
CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id)
REFERENCES categories (category_id) ON DELETE SET NULL
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 16: 商品テーブルを作成
名前、スラッグ、基本価格、状態を持ちます。
CREATE TABLE products (
product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id INT UNSIGNED NOT NULL,
name VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL,
description TEXT NULL,
base_price DECIMAL(10,2) NOT NULL,
status ENUM('draft','active','archived') NOT NULL DEFAULT 'draft',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (product_id),
UNIQUE KEY uq_products_slug (slug),
KEY idx_products_category_status (category_id, status),
CONSTRAINT fk_products_category FOREIGN KEY (category_id)
REFERENCES categories (category_id) ON DELETE RESTRICT,
CONSTRAINT chk_products_price CHECK (base_price >= 0)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 17: バリエーションテーブルを作成
実際に販売する単位です。SKU、オプション、価格を持ちます。
CREATE TABLE product_variants (
variant_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
sku VARCHAR(40) NOT NULL,
option_label VARCHAR(80) NOT NULL,
price DECIMAL(10,2) NOT NULL,
weight_grams INT UNSIGNED NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (variant_id),
UNIQUE KEY uq_variants_sku (sku),
UNIQUE KEY uq_variants_option (product_id, option_label),
CONSTRAINT fk_variants_product FOREIGN KEY (product_id)
REFERENCES products (product_id) ON DELETE RESTRICT,
CONSTRAINT chk_variants_price CHECK (price >= 0)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 18: カテゴリと商品のサンプルデータ
カタログのサンプルデータです。
INSERT INTO categories (category_id, parent_id, name, slug) VALUES
(1, NULL, 'Clothing', 'clothing'),
(2, 1, 'T-shirts', 't-shirts'),
(3, NULL, 'Accessories', 'accessories');
INSERT INTO products (product_id, category_id, name, slug, description, base_price, status) VALUES
(1, 2, 'Cotton Crew T-Shirt', 'cotton-crew-tshirt', 'Soft combed cotton, regular fit.', 390.00, 'active'),
(2, 3, 'Canvas Tote Bag', 'canvas-tote-bag', 'Heavy canvas with an inner pocket.', 590.00, 'active'),
(3, 2, 'Linen Henley Shirt', 'linen-henley', 'Summer linen, long sleeve.', 890.00, 'draft');
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 19: バリエーションのサンプルデータ
五件のうち一件は無効です。
INSERT INTO product_variants
(variant_id, product_id, sku, option_label, price, weight_grams, is_active) VALUES
(1, 1, 'TS-CREW-M-BLK', 'M / Black', 390.00, 180, 1),
(2, 1, 'TS-CREW-L-BLK', 'L / Black', 390.00, 190, 1),
(3, 1, 'TS-CREW-M-WHT', 'M / White', 390.00, 180, 1),
(4, 2, 'TOTE-NAT', 'Natural', 590.00, 420, 1),
(5, 3, 'LIN-HEN-M-BLU', 'M / Blue', 890.00, 200, 0);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 20: keyset ページングによる商品一覧
OFFSET を使わず、次のページを速く取得します。
SELECT p.product_id, p.name, MIN(v.price) AS from_price
FROM products AS p
JOIN product_variants AS v
ON v.product_id = p.product_id AND v.is_active = 1
WHERE p.status = 'active'
AND p.category_id = 2
AND p.product_id > ? -- last product_id from the previous page
GROUP BY p.product_id, p.name
ORDER BY p.product_id
LIMIT 20;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 21: 商品ページのバリエーション
一つの商品の有効なバリエーションだけを取得します。
SELECT v.variant_id, v.sku, v.option_label, v.price, v.weight_grams
FROM product_variants AS v
WHERE v.product_id = 1
AND v.is_active = 1
ORDER BY v.option_label;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 22: 全文検索
ngram パーサーを使う FULLTEXT インデックスです。
ALTER TABLE products
ADD FULLTEXT KEY ft_products_name_desc (name, description) WITH PARSER ngram;
SELECT product_id, name
FROM products
WHERE status = 'active'
AND MATCH (name, description) AGAINST ('cotton' IN NATURAL LANGUAGE MODE);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 4: 在庫と在庫台帳
在庫は、間違えると直接お金を失う部分です。この設計では、バリエーションごとに三つの数値を持ちます。on_hand は実際に倉庫にある数、reserved は未払いまたは未出荷の注文に約束した数、available は on_hand から reserved を引いた販売可能数です。台帳テーブルはすべての変更を記録するため、いつでも履歴と照合できます。
CHECK 規則つきのカウンター
inventory テーブルは、バリエーションごとに一行を持ちます。CHECK 制約により、on_hand は 0 以上、reserved は 0 から on_hand の間でなければなりません。MySQL 8.0.16 以降はサーバーが CHECK 制約を強制するため、在庫をマイナスにする UPDATE はエラー 3819 で失敗します。不正な値が黙って書き込まれることはありません。
台帳
inventory_movements テーブルは、各変更を記録します。on_hand と reserved それぞれについて符号付きの差分、reserve、release、sale、cancel、return、restock、adjustment といった理由、そして関連する注文を持ちます。台帳は追記のみです。誤りは古い行を編集するのではなく、新しい行を追加して訂正します。これにより、ショップから出たすべての商品に監査証跡が残ります。
カウンターと台帳の照合
夜間ジョブは、バリエーションごとに台帳を合計し、カウンターと比べます。不一致はバグか手作業の編集を意味するため、警告を出すべきです。初期データは、台帳に期首残高として入れておきます。そうすれば照合はゼロから始まります。
例 23: 在庫テーブルを作成
手持ちと引当済みの数量。CHECK 制約付きです。
CREATE TABLE inventory (
variant_id BIGINT UNSIGNED NOT NULL,
on_hand INT NOT NULL DEFAULT 0,
reserved INT NOT NULL DEFAULT 0,
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (variant_id),
CONSTRAINT fk_inventory_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_inventory_on_hand CHECK (on_hand >= 0),
CONSTRAINT chk_inventory_reserved CHECK (reserved >= 0 AND reserved <= on_hand)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 24: 在庫の台帳テーブルを作成
在庫の変化ごとに一行を残します。
CREATE TABLE inventory_movements (
movement_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
variant_id BIGINT UNSIGNED NOT NULL,
delta_on_hand INT NOT NULL DEFAULT 0,
delta_reserved INT NOT NULL DEFAULT 0,
reason ENUM('adjustment','restock','reserve','release','sale','cancel','return') NOT NULL,
order_id BIGINT UNSIGNED NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (movement_id),
KEY idx_movements_variant_time (variant_id, created_at),
KEY idx_movements_order (order_id),
CONSTRAINT fk_movements_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 25: 在庫と期首残高のサンプルデータ
在庫数と、対応する台帳の行です。
INSERT INTO inventory (variant_id, on_hand, reserved) VALUES
(1, 20, 0),
(2, 8, 0),
(3, 0, 0), -- sold out
(4, 50, 0);
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason) VALUES
(1, 20, 0, 'adjustment'),
(2, 8, 0, 'adjustment'),
(4, 50, 0, 'adjustment');
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 26: 販売可能数
手持ちから引当済みを引きます。
SELECT variant_id, on_hand - reserved AS available
FROM inventory
WHERE on_hand - reserved > 0
ORDER BY variant_id;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 27: 在庫不足レポート
補充が必要なバリエーションです。
SELECT v.sku, i.on_hand - i.reserved AS available
FROM inventory AS i
JOIN product_variants AS v ON v.variant_id = i.variant_id
WHERE i.on_hand - i.reserved BETWEEN 0 AND 5
ORDER BY available, v.sku;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 28: 台帳行を伴う補充
在庫と記録を一つのトランザクションで更新します。
START TRANSACTION;
UPDATE inventory
SET on_hand = on_hand + 30
WHERE variant_id = 3;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason)
VALUES (3, 30, 0, 'restock');
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 29: 負の在庫を拒否する
CHECK 制約は不可能な値を防ぎます。
-- Fails with error 3819 (check constraint violated) on MySQL 8.0.16 and later.
UPDATE inventory SET on_hand = -1 WHERE variant_id = 2;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 30: 在庫と台帳の照合
履歴と一致しないカウンターを見つけます。
SELECT i.variant_id, i.on_hand, COALESCE(SUM(m.delta_on_hand), 0) AS ledger_on_hand
FROM inventory AS i
LEFT JOIN inventory_movements AS m ON m.variant_id = i.variant_id
GROUP BY i.variant_id, i.on_hand
HAVING i.on_hand <> COALESCE(SUM(m.delta_on_hand), 0);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 5: カート
カートは、買い手が買うつもりの商品を保持します。ログインしていない訪問者のカートはセッショントークンで識別され、顧客のカートは customer_id で識別されます。カートは注文ではありません。在庫は引き当てず、カートに表示された価格はチェックアウト時に読み直します。このトピックでは、カートのテーブル、商品を安全に追加する方法、ログイン後にゲストのカートを統合する方法を扱います。
ゲストのカートと顧客のカート
carts テーブルには、任意の customer_id と session_token があります。ゲストがログインすると、カートの customer_id が設定され、ゲストのカートの商品は顧客の開いているカートに統合されます。cart_items テーブルは (cart_id, variant_id) に UNIQUE キーを持つため、一つのバリエーションはカートごとに一度だけ現れ、重複する代わりに数量が更新されます。
一文でカートに追加
INSERT ... AS new ON DUPLICATE KEY UPDATE は、数量の加算を一つの不可分な文で行います。同じボタンを二度押しても二行にはならず、数量は CHECK 制約と更新時の LEAST によって 99 までに抑えられます。
カートは注文ではない
カートに商品を入れても在庫は引き当てません。毎日大量のカートが放置されるからです。引き当ては、顧客が注文を確定したときに初めて始まります。そのためカートのページでは、在庫を再確認し、売り切れの商品があれば明確なメッセージを表示する必要があります。
図解
例 31: カートテーブルを作成
ゲストはセッショントークン、顧客は ID を使います。
CREATE TABLE carts (
cart_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NULL,
session_token CHAR(36) NULL,
status ENUM('open','converted','abandoned') NOT NULL DEFAULT 'open',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (cart_id),
UNIQUE KEY uq_carts_session (session_token),
KEY idx_carts_customer_status (customer_id, status),
KEY idx_carts_status_updated (status, updated_at),
CONSTRAINT fk_carts_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 32: カートの明細テーブルを作成
カートごと、バリエーションごとに一行です。
CREATE TABLE cart_items (
cart_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
cart_id BIGINT UNSIGNED NOT NULL,
variant_id BIGINT UNSIGNED NOT NULL,
quantity INT NOT NULL,
added_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (cart_item_id),
UNIQUE KEY uq_cart_variant (cart_id, variant_id),
KEY idx_cart_items_variant (variant_id),
CONSTRAINT fk_cart_items_cart FOREIGN KEY (cart_id)
REFERENCES carts (cart_id) ON DELETE CASCADE,
CONSTRAINT fk_cart_items_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_cart_quantity CHECK (quantity BETWEEN 1 AND 99)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 33: カートのサンプルデータ
顧客のカートとゲストのカートです。
INSERT INTO carts (cart_id, customer_id, session_token, status) VALUES
(1, 1, NULL, 'open'),
(2, NULL, 'b0a1c2d3-0000-4000-8000-000000000002', 'open');
INSERT INTO cart_items (cart_id, variant_id, quantity) VALUES
(1, 1, 1),
(1, 4, 2),
(2, 2, 1);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 34: カートに追加
一つの文で挿入するか、数量を増やします。
INSERT INTO cart_items (cart_id, variant_id, quantity)
VALUES (1, 1, 2) AS new
ON DUPLICATE KEY UPDATE
quantity = LEAST(cart_items.quantity + new.quantity, 99);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 35: ログイン後にゲストのカートを統合
一つのトランザクションで商品を移動します。
START TRANSACTION;
INSERT INTO cart_items (cart_id, variant_id, quantity)
SELECT 1, variant_id, quantity
FROM cart_items
WHERE cart_id = 2
ON DUPLICATE KEY UPDATE quantity = LEAST(quantity + VALUES(quantity), 99);
UPDATE carts SET status = 'abandoned' WHERE cart_id = 2;
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 36: 現在の価格と在庫を含むカート
カートを表示するときに価格と在庫を読みます。
SELECT ci.variant_id, v.sku, p.name, v.price, ci.quantity,
v.price * ci.quantity AS line_total,
COALESCE(i.on_hand - i.reserved, 0) AS available
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
LEFT JOIN inventory AS i ON i.variant_id = ci.variant_id
WHERE ci.cart_id = 1;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 37: 放置されたカートを中止扱いにする
状態のインデックスを使う定期ジョブです。
UPDATE carts
SET status = 'abandoned'
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 38: カート検索の実行計画を確認
EXPLAIN でインデックスが使われるか分かります。
EXPLAIN SELECT cart_id
FROM carts
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 6: 注文と注文明細
注文は購入の記録です。合計、状態、配送先住所の控えを保存します。各注文明細は SKU、商品名、単価をコピーするため、後でカタログが変わっても注文の意味は変わりません。このトピックでは、合計が常に一致しなければならないという規則と、顧客の履歴を素早くページ送りする方法も扱います。
注文時点の情報を残す
order_items は、注文の作成時に product_variants からコピーした sku_snapshot、product_name_snapshot、unit_price を保存します。line_total 列は生成列で、unit_price に quantity を掛けた値です。入力と食い違うことはありません。variant_id の外部キーは ON DELETE RESTRICT を使うため、一度でも売れたバリエーションは削除できません。代わりに無効化します。
合計は必ず一致する
orders テーブルの CHECK 制約は、grand_total = subtotal - discount_total + shipping_fee を要求します。これらの数値のどれかを変えるコードは、同じトランザクションで他の数値も変えなければなりません。そうしないと文が失敗します。注文を確定する前に、合計は注文明細から再計算されるため、ヘッダーは常に明細と一致します。
注文履歴のページ送り
複合インデックス (customer_id, created_at) は、一人の顧客の最新の注文を取得するクエリに対応します。(created_at, order_id) による keyset ページングは、前のページの行を読み直さずに次のページを返します。EXPLAIN でインデックスが使われていることを確認し、並べ替えの順序をインデックスに合わせてください。
図解
例 39: 注文テーブルを作成
購入のヘッダーです。合計と住所の控えを持ちます。
CREATE TABLE orders (
order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_number VARCHAR(20) NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
status ENUM('pending_payment','paid','packed','shipped','delivered','cancelled','refunded')
NOT NULL DEFAULT 'pending_payment',
currency CHAR(3) NOT NULL DEFAULT 'THB',
subtotal DECIMAL(12,2) NOT NULL,
discount_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
shipping_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00,
grand_total DECIMAL(12,2) NOT NULL,
coupon_id INT UNSIGNED NULL,
ship_recipient VARCHAR(120) NOT NULL,
ship_line1 VARCHAR(200) NOT NULL,
ship_city VARCHAR(100) NOT NULL,
ship_postal_code VARCHAR(20) NOT NULL,
ship_country_code CHAR(2) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (order_id),
UNIQUE KEY uq_orders_number (order_number),
KEY idx_orders_customer_created (customer_id, created_at),
KEY idx_orders_status_created (status, created_at),
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE RESTRICT,
CONSTRAINT chk_orders_total CHECK (grand_total = subtotal - discount_total + shipping_fee)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 40: 注文明細テーブルを作成
購入時の価格を保存し、明細の合計は自動計算します。
CREATE TABLE order_items (
order_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
variant_id BIGINT UNSIGNED NOT NULL,
sku_snapshot VARCHAR(40) NOT NULL,
product_name_snapshot VARCHAR(200) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
quantity INT UNSIGNED NOT NULL,
line_total DECIMAL(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED,
PRIMARY KEY (order_item_id),
UNIQUE KEY uq_order_variant (order_id, variant_id),
KEY idx_order_items_variant (variant_id),
CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT,
CONSTRAINT fk_order_items_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_order_items_qty CHECK (quantity > 0)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 41: カートを注文にコピー
チェックアウト時の価格を保存します。
-- @order_id is the id of the order created just before this statement.
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT @order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = 1;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 42: 三件の注文のサンプルデータ
異なる状態の注文が三件です。
INSERT INTO orders
(order_id, order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code, created_at) VALUES
(1, 'SH-20261001-0001', 1, 'delivered', 780.00, 0.00, 50.00, 830.00, 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', '2026-10-01 10:12:00'),
(2, 'SH-20261020-0002', 2, 'paid', 590.00, 0.00, 50.00, 640.00, 'Niran Chai', '45 Moo 2', 'Chiang Mai', '50000', 'TH', '2026-10-20 18:40:00'),
(3, 'SH-20261029-0003', 1, 'pending_payment', 390.00, 39.00, 50.00, 401.00, 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', '2026-10-29 09:05:00');
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 43: 注文明細のサンプルデータ
三件の注文の明細です。
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity) VALUES
(1, 1, 'TS-CREW-M-BLK', 'Cotton Crew T-Shirt', 390.00, 2),
(2, 4, 'TOTE-NAT', 'Canvas Tote Bag', 590.00, 1),
(3, 2, 'TS-CREW-L-BLK', 'Cotton Crew T-Shirt', 390.00, 1);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 44: keyset ページングによる注文履歴
一人の顧客の、新しい順の注文です。
SELECT order_id, order_number, status, grand_total, created_at
FROM orders
WHERE customer_id = 1
AND (created_at, order_id) < (?, ?) -- last row of the previous page
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 45: 注文の詳細
ヘッダーと明細を一つのクエリで取得します。
SELECT o.order_number, o.status, oi.sku_snapshot, oi.product_name_snapshot,
oi.unit_price, oi.quantity, oi.line_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.order_id = 1
ORDER BY oi.order_item_id;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 46: 管理画面のキュー: 支払い済みの注文
倉庫が次に梱包する注文です。
SELECT order_id, order_number, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at
LIMIT 50;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 7: 支払いと返金
支払いは、注文に対して代金を請求する一回の試行です。一つの注文に複数の試行があることもあります。たとえば、カードが拒否されて最初は失敗し、次は成功する場合です。決済サービスは webhook も送ってきます。webhook は二度届いたり、順序が前後したりすることがあります。payments テーブルと refunds テーブルは、再試行や重複したメッセージで二重請求や二重の売上記録が起きないように設計されています。
冪等性キー
各支払いの試行には、UNIQUE な idempotency_key があります。ネットワークのタイムアウトでアプリがリクエストを再送しても、二回目の INSERT は新しい請求を作る代わりに既存の行を見つけます。プロバイダーごとに一意な provider_ref 列は、その行を決済サービス側の取引 ID と結び付けます。
支払い行と webhook
webhook は、支払いが成功または失敗したことを伝えます。ハンドラーは provider と provider_ref で支払いを探し、状態が pending の場合だけ変更します。すでに最終状態なら、更新は零行しか変えず、ハンドラーはそれ以上何もしません。これにより、同じ webhook が再送されても害はありません。
返金の規則
返金は支払いを参照し、独自の状態を持ちます。返金可能額は、支払い額から失敗していない返金の合計を引いた額です。返金の行はプロバイダーを呼ぶ前に書き込み、状態は後で更新します。データベースのトランザクションが行ロックを保持している間は、プロバイダーを呼びません。遅いネットワーク呼び出しが他の買い手を待たせてしまうからです。
例 47: 支払いテーブルを作成
請求の試行ごとに一行。冪等性キーを持ちます。
CREATE TABLE payments (
payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
provider VARCHAR(30) NOT NULL,
provider_ref VARCHAR(100) NULL,
idempotency_key VARCHAR(64) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (payment_id),
UNIQUE KEY uq_payments_idempotency (idempotency_key),
UNIQUE KEY uq_payments_provider_ref (provider, provider_ref),
KEY idx_payments_order (order_id),
CONSTRAINT fk_payments_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT,
CONSTRAINT chk_payments_amount CHECK (amount > 0)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 48: 返金テーブルを作成
各返金は一つの支払いを指し、独自の状態を持ちます。
CREATE TABLE refunds (
refund_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payment_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
reason VARCHAR(200) NULL,
provider_ref VARCHAR(100) NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (refund_id),
KEY idx_refunds_payment (payment_id),
CONSTRAINT fk_refunds_payment FOREIGN KEY (payment_id)
REFERENCES payments (payment_id) ON DELETE RESTRICT,
CONSTRAINT chk_refunds_amount CHECK (amount > 0)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 49: 支払いの試行を安全に記録
同じキーで再試行しても同じ行が返ります。
INSERT INTO payments
(order_id, provider, provider_ref, idempotency_key, amount, status)
VALUES
(3, 'sample-gateway', NULL, 'order-3-attempt-1', 401.00, 'pending')
ON DUPLICATE KEY UPDATE updated_at = updated_at;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 50: 成功した支払いのサンプルデータ
注文 1 と 2 の支払いです。
-- No explicit payment_id: example 49 may already have used the first auto-increment value.
INSERT INTO payments
(order_id, provider, provider_ref, idempotency_key, amount, status) VALUES
(1, 'sample-gateway', 'test-ch-0001', 'order-1-attempt-1', 830.00, 'succeeded'),
(2, 'sample-gateway', 'test-ch-0002', 'order-2-attempt-1', 640.00, 'succeeded');
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 51: 返金可能額
支払額から、失敗していない返金額を引いた金額です。
SELECT p.payment_id,
p.amount - COALESCE(SUM(CASE WHEN r.status <> 'failed' THEN r.amount END), 0) AS refundable
FROM payments AS p
LEFT JOIN refunds AS r ON r.payment_id = p.payment_id
WHERE p.payment_id = ?
GROUP BY p.payment_id, p.amount;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 52: 支払いと注文の不一致
注文がまだ待機中なのに支払いが成功しているケースを探します。
SELECT p.payment_id, p.order_id, o.status
FROM payments AS p
JOIN orders AS o ON o.order_id = p.order_id
WHERE p.status = 'succeeded'
AND o.status = 'pending_payment';
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 8: 出荷と発送
発送は、商品を梱包して送る作業です。一つの注文が、たとえば一部の商品の在庫切れのために、複数の荷物に分かれることがあります。shipments テーブルは荷物ごとに一行で、shipment_items はどの注文明細がどの荷物に何個入っているかを示します。状態列は一方向にしか進まないため、配達済みの荷物を準備中に戻すことはできません。
荷物と分割出荷
shipments は orders を参照し、shipment_items は shipments と order_items を参照します。(shipment_id, order_item_id) の UNIQUE キーにより、同じ明細が一つの荷物に二度入ることを防ぎます。この二つのテーブルで、注文をどのように分割する場合でも表現できます。
状態は一方向に進む
荷物を preparing から in_transit へ、in_transit から delivered へ変えるには、WHERE 句に古い状態を入れた UPDATE を使います。順序が前後した更新は零行しか変えず、アプリは状態を上書きする代わりに競合を報告します。
数量を追跡する
ある明細の未出荷数は、注文数から、その明細に対する shipment_items の合計を引いた値です。HAVING 句で、まだ完了していない明細を探します。管理画面は、この結果を使って一部出荷された注文を表示します。
図解
例 53: 出荷テーブルを作成
注文の荷物です。配送業者と追跡番号を持ちます。
CREATE TABLE shipments (
shipment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
carrier VARCHAR(60) NOT NULL,
tracking_number VARCHAR(60) NULL,
status ENUM('preparing','in_transit','delivered','returned') NOT NULL DEFAULT 'preparing',
shipped_at DATETIME(3) NULL,
delivered_at DATETIME(3) NULL,
PRIMARY KEY (shipment_id),
UNIQUE KEY uq_shipments_tracking (tracking_number),
KEY idx_shipments_order (order_id),
CONSTRAINT fk_shipments_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 54: 出荷明細テーブルを作成
どの注文明細がどの荷物に入るかを示し、分割出荷に対応します。
CREATE TABLE shipment_items (
shipment_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
shipment_id BIGINT UNSIGNED NOT NULL,
order_item_id BIGINT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL,
PRIMARY KEY (shipment_item_id),
UNIQUE KEY uq_shipment_line (shipment_id, order_item_id),
KEY idx_shipment_items_line (order_item_id),
CONSTRAINT fk_shipment_items_shipment FOREIGN KEY (shipment_id)
REFERENCES shipments (shipment_id) ON DELETE CASCADE,
CONSTRAINT fk_shipment_items_line FOREIGN KEY (order_item_id)
REFERENCES order_items (order_item_id) ON DELETE RESTRICT,
CONSTRAINT chk_shipment_items_qty CHECK (quantity > 0)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 55: 配達済みの出荷のサンプルデータ
注文 1 の出荷データです。
INSERT INTO shipments
(shipment_id, order_id, carrier, tracking_number, status, shipped_at, delivered_at) VALUES
(1, 1, 'Sample Courier', 'SC0000000001', 'delivered', '2026-10-02 09:00:00', '2026-10-04 15:20:00');
INSERT INTO shipment_items (shipment_id, order_item_id, quantity) VALUES (1, 1, 2);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 56: 梱包して出荷を作成
注文の状態と荷物を一つのトランザクションで更新します。
START TRANSACTION;
UPDATE orders SET status = 'packed' WHERE order_id = 2 AND status = 'paid';
-- ROW_COUNT() = 0 means the order was not in the paid state: ROLLBACK.
INSERT INTO shipments (order_id, carrier, status) VALUES (2, 'Sample Courier', 'preparing');
SET @shipment_id = LAST_INSERT_ID();
INSERT INTO shipment_items (shipment_id, order_item_id, quantity)
SELECT @shipment_id, order_item_id, quantity
FROM order_items
WHERE order_id = 2;
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 57: 配送業者へ引き渡し、配達を記録
出荷の状態は一方向にだけ進みます。
UPDATE shipments
SET status = 'in_transit', tracking_number = 'SC0000000002', shipped_at = NOW(3)
WHERE shipment_id = 2 AND status = 'preparing';
UPDATE shipments
SET status = 'delivered', delivered_at = NOW(3)
WHERE shipment_id = 2 AND status = 'in_transit';
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 58: まだ出荷していない数量
明細ごとに注文数から出荷済み数を引きます。
SELECT oi.order_item_id, oi.quantity AS ordered,
COALESCE(SUM(si.quantity), 0) AS shipped
FROM order_items AS oi
LEFT JOIN shipment_items AS si ON si.order_item_id = oi.order_item_id
WHERE oi.order_id = 2
GROUP BY oi.order_item_id, oi.quantity
HAVING shipped < ordered;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 9: レビューとクーポン
レビューは信頼を作り、クーポンは売上を伸ばしますが、どちらも悪用されうるものです。レビューは実際に配達された購入からだけ来るべきで、クーポンは期間と利用上限の中でだけ使えるべきです。このトピックの制約は、両方の規則をデータベースの中で守ります。そのため、画面のコードのバグがこれらを壊すことはありません。
購入が確認されたレビュー
reviews テーブルは、商品だけでなく order_items を参照します。order_item_id の UNIQUE キーにより、購入した明細ごとに一件のレビューだけが書けます。INSERT 文は、注文の状態が delivered の場合にのみ明細を選ぶため、キャンセルされた注文や未払いの注文にはレビューを書けません。
上限つきのクーポン
クーポンは、コード、割合または定額の割引、最低金額、開始日と終了日、任意で最大利用回数を持ちます。CHECK 制約は、終了日が開始日より後であること、割引が正の値であること、used_count が max_uses を超えないことを要求します。
条件つきの利用
二人の買い手が、同時にクーポンの最後の一回を使おうとすることがあります。WHERE 句で日付と used_count を確認する条件つき UPDATE なら安全に処理できます。アプリは ROW_COUNT が 1 のときだけ割引を適用します。
例 59: レビューテーブルを作成
注文明細ごとに一件、評価は 1 から 5 です。
CREATE TABLE reviews (
review_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
order_item_id BIGINT UNSIGNED NOT NULL,
rating TINYINT UNSIGNED NOT NULL,
body TEXT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (review_id),
UNIQUE KEY uq_reviews_order_item (order_item_id),
KEY idx_reviews_product_created (product_id, created_at),
CONSTRAINT fk_reviews_product FOREIGN KEY (product_id)
REFERENCES products (product_id) ON DELETE RESTRICT,
CONSTRAINT fk_reviews_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE,
CONSTRAINT fk_reviews_order_item FOREIGN KEY (order_item_id)
REFERENCES order_items (order_item_id) ON DELETE RESTRICT,
CONSTRAINT chk_reviews_rating CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 60: クーポンテーブルを作成
期間、利用上限、整合性の検査を持ちます。
CREATE TABLE coupons (
coupon_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
code VARCHAR(40) NOT NULL,
discount_type ENUM('percent','fixed') NOT NULL,
discount_value DECIMAL(10,2) NOT NULL,
min_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
starts_at DATETIME(3) NOT NULL,
ends_at DATETIME(3) NOT NULL,
max_uses INT UNSIGNED NULL,
used_count INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (coupon_id),
UNIQUE KEY uq_coupons_code (code),
CONSTRAINT chk_coupons_value CHECK (discount_value > 0),
CONSTRAINT chk_coupons_dates CHECK (ends_at > starts_at),
CONSTRAINT chk_coupons_uses CHECK (max_uses IS NULL OR used_count <= max_uses)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 61: 注文とクーポンを結ぶ
両方のテーブルができた後に追加する外部キーです。
ALTER TABLE orders
ADD CONSTRAINT fk_orders_coupon FOREIGN KEY (coupon_id)
REFERENCES coupons (coupon_id) ON DELETE SET NULL;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 62: クーポンのサンプルデータ
割合のクーポンと定額のクーポンです。
INSERT INTO coupons
(coupon_id, code, discount_type, discount_value, min_subtotal, starts_at, ends_at, max_uses, used_count) VALUES
(1, 'WELCOME10', 'percent', 10.00, 300.00, '2026-01-01 00:00:00', '2027-01-01 00:00:00', NULL, 0),
(2, 'SHIP50', 'fixed', 50.00, 0.00, '2026-10-01 00:00:00', '2026-12-31 23:59:59', 100, 0);
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 63: クーポンを安全に利用
条件付きの UPDATE で過剰な利用を防ぎます。
UPDATE coupons
SET used_count = used_count + 1
WHERE coupon_id = ?
AND starts_at <= NOW(3)
AND ends_at > NOW(3)
AND (max_uses IS NULL OR used_count < max_uses);
-- The application applies the discount only when ROW_COUNT() = 1.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 64: 購入者のみのレビュー
配達済みの注文明細だけがレビューできます。
INSERT INTO reviews (product_id, customer_id, order_item_id, rating, body)
SELECT v.product_id, o.customer_id, oi.order_item_id, 5, 'Fits well and the cotton feels soft.'
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN product_variants AS v ON v.variant_id = oi.variant_id
WHERE oi.order_item_id = 1
AND o.status = 'delivered';
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 10: 販売者とマーケットプレイス
マーケットプレイスでは、多くの販売者が一つのショップで売ります。データベースは、どの商品をどの販売者が所有しているか、どの注文明細の収益がどの販売者のものか、プラットフォームがいくら手数料を取るかを知っている必要があります。この設計では、販売者を各注文明細にコピーします。そのため、後で商品が別の販売者に移っても、元の販売者の売上履歴は変わりません。
販売者が商品を所有する
sellers テーブルには、名前、メール、状態、手数料率を保存します。products.seller_id は、ALTER TABLE で追加する NULL 可能な外部キーです。そのため、前のトピックの単一ショップのテーブルも引き続き動きます。pending の状態にすると、管理者が承認するまで新しい販売者の商品は非表示になります。
各明細に販売者をコピーする
order_items.seller_id は、注文の作成時にコピーされます。後で products から探すことはしません。これにより、商品の所有者が変わっても注文履歴は正しく残り、手数料のレポートは単純な JOIN で作れます。
支払いと手数料
payouts テーブルには、販売者ごと、期間ごとに一行が入ります。総額、手数料、純額を保存します。CHECK 制約は net = gross - commission を要求し、(seller_id, period_start) の UNIQUE キーは同じ期間の支払いが二重に作られるのを防ぎます。
例 65: 販売者テーブルを作成
手数料率を持つマーケットプレイスの販売者です。
CREATE TABLE sellers (
seller_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(120) NOT NULL,
email VARCHAR(254) NOT NULL,
status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
commission_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (seller_id),
UNIQUE KEY uq_sellers_email (email),
CONSTRAINT chk_sellers_commission CHECK (commission_percent BETWEEN 0 AND 100)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 66: 商品は販売者に属する
ALTER で追加する、NULL を許す外部キーです。
ALTER TABLE products
ADD COLUMN seller_id INT UNSIGNED NULL,
ADD KEY idx_products_seller (seller_id),
ADD CONSTRAINT fk_products_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 67: 注文明細に販売者をコピー
商品の持ち主が変わっても履歴は正しく残ります。
ALTER TABLE order_items
ADD COLUMN seller_id INT UNSIGNED NULL,
ADD KEY idx_order_items_seller (seller_id),
ADD CONSTRAINT fk_order_items_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT;
UPDATE order_items AS oi
JOIN product_variants AS v ON v.variant_id = oi.variant_id
JOIN products AS p ON p.product_id = v.product_id
SET oi.seller_id = p.seller_id
WHERE oi.seller_id IS NULL;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 68: 振込テーブルを作成
販売者ごと、期間ごとに一件の振込です。チェック制約付きです。
CREATE TABLE payouts (
payout_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
seller_id INT UNSIGNED NOT NULL,
period_start DATE NOT NULL,
period_end DATE NOT NULL,
gross_amount DECIMAL(12,2) NOT NULL,
commission_amount DECIMAL(12,2) NOT NULL,
net_amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','paid') NOT NULL DEFAULT 'pending',
PRIMARY KEY (payout_id),
UNIQUE KEY uq_payouts_period (seller_id, period_start),
CONSTRAINT fk_payouts_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT,
CONSTRAINT chk_payouts_net CHECK (net_amount = gross_amount - commission_amount)
) ENGINE=InnoDB;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 69: 販売者と担当商品のサンプルデータ
二人の販売者と、その商品です。
INSERT INTO sellers (seller_id, name, email, status, commission_percent) VALUES
(1, 'Lanna Threads', '[email protected]', 'active', 12.00),
(2, 'Sample Bags Co', '[email protected]', 'active', 10.00);
UPDATE products SET seller_id = 1 WHERE product_id IN (1, 3);
UPDATE products SET seller_id = 2 WHERE product_id = 2;
UPDATE order_items SET seller_id = 1 WHERE variant_id IN (1, 2, 3);
UPDATE order_items SET seller_id = 2 WHERE variant_id = 4;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 70: 月次の手数料レポート
販売者ごとの総売上と手数料です。
SELECT s.name,
COUNT(DISTINCT oi.order_id) AS orders,
SUM(oi.line_total) AS gross,
ROUND(SUM(oi.line_total) * s.commission_percent / 100, 2) AS commission
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN sellers AS s ON s.seller_id = oi.seller_id
WHERE o.status IN ('paid','packed','shipped','delivered')
AND o.created_at >= '2026-10-01'
AND o.created_at < '2026-11-01'
GROUP BY s.seller_id, s.name, s.commission_percent
ORDER BY gross DESC;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 11: トランザクション: 注文と在庫の引き当て
注文はショップで最も重要なトランザクションです。注文を作成し、明細をコピーし、在庫を引き当て、カートを閉じます。これらの手順は、すべて成功するか、すべて失敗する必要があります。このトピックでは、実行される順に各手順を追い、SQL を示し、手順が一つのトランザクションに包まれていないとどうなるかを説明します。
チェックアウトのトランザクションを一つずつ
1. START TRANSACTION。2. カートの在庫行を、variant_id の昇順で SELECT ... FOR UPDATE によってロックする。3. 現在の価格から小計を計算する。4. 既定の住所をコピーして注文ヘッダーを挿入する。5. 価格のスナップショットを付けて注文明細を挿入する。6. order_items と結合する一つの UPDATE で在庫を引き当てる。7. 明細ごとに、理由 reserve の台帳行を一つ書く。8. カートを converted に設定する。9. COMMIT。例 76 のストアドプロシージャで、この九つの手順をすべて一か所に示しています。
トランザクションがないと何が起きるか
トランザクションがなければ、手順 4 の後でシステムがクラッシュすると、在庫が引き当てられていない注文が残り、同じ一点が二度売れてしまいます。手順 6 の後、手順 7 の前にクラッシュすると、台帳では説明できないカウンターが残ります。注文を作った後、カートを閉じる前に失敗すると、同じカートの代金を顧客が二度支払うことになりかねません。どれも特殊な事故ではなく、よくある障害です。
ロック順序と短いトランザクション
どこでも同じ順序で行をロックします。あるチェックアウトが variant 4 をロックしてから variant 1 をロックし、別のチェックアウトが variant 1 をロックしてから variant 4 をロックすると、両者はデッドロックするおそれがあり、MySQL はエラー 1213 でどちらかを巻き戻します。トランザクションは短く保ちます。その中で決済サービスを呼んだり、メールを送ったり、利用者を待ったりしてはいけません。引き当ては一定時間有効な約束であり、支払い結果によって販売になるかどうかが決まります。
図解
例 71: チェックアウト: 在庫行をロック
昇順に SELECT FOR UPDATE します。
-- Cart 1 holds three of variant 1 and two of variant 4 (after example 34).
START TRANSACTION;
SELECT variant_id, on_hand, reserved
FROM inventory
WHERE variant_id IN (1, 4)
ORDER BY variant_id
FOR UPDATE;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 72: チェックアウト: 注文ヘッダーを作成
合計と既定の住所の控えです。
-- Subtotal: 3 x 390.00 + 2 x 590.00 = 2350.00. Shipping 50.00. No discount.
INSERT INTO orders
(order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code)
SELECT 'SH-20261030-0004', 1, 'pending_payment', 2350.00, 0.00, 50.00, 2400.00,
a.recipient_name, a.line1, a.city, a.postal_code, a.country_code
FROM addresses AS a
WHERE a.customer_id = 1 AND a.is_default = 1;
SET @order_id = LAST_INSERT_ID();
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 73: チェックアウト: カートの明細をコピー
購入した時点の価格です。
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT @order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = 1;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 74: チェックアウト: 合計を再計算
合計は明細から求めます。カートからは求めません。
UPDATE orders AS o
JOIN (
SELECT order_id, SUM(line_total) AS lines_total
FROM order_items
WHERE order_id = @order_id
GROUP BY order_id
) AS t ON t.order_id = o.order_id
SET o.subtotal = t.lines_total,
o.grand_total = t.lines_total - o.discount_total + o.shipping_fee;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 75: チェックアウト: 在庫を引き当てて台帳に記録
CHECK 規則により在庫不足は失敗します。
-- If a line asks for more than is on hand, the CHECK constraint fails this statement with error 3819.
-- The whole statement is undone, and the transaction is rolled back by the caller.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved + oi.quantity
WHERE oi.order_id = @order_id;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, quantity, 'reserve', @order_id
FROM order_items
WHERE order_id = @order_id;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 76: ストアドプロシージャで注文
チェックアウト全体を行い、どこかで失敗したら巻き戻します。
DELIMITER $$
CREATE PROCEDURE place_order(
IN p_customer_id BIGINT UNSIGNED,
IN p_cart_id BIGINT UNSIGNED,
IN p_order_number VARCHAR(20),
OUT p_order_id BIGINT UNSIGNED)
BEGIN
DECLARE v_subtotal DECIMAL(12,2) DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
-- 1 lock the stock rows of the cart in ascending variant_id order
SELECT i.variant_id
FROM inventory AS i
JOIN cart_items AS ci ON ci.variant_id = i.variant_id
WHERE ci.cart_id = p_cart_id
ORDER BY i.variant_id
FOR UPDATE;
-- 2 the cart must be open and belong to this customer
IF NOT EXISTS (SELECT 1 FROM carts
WHERE cart_id = p_cart_id AND customer_id = p_customer_id AND status = 'open') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cart is not open for this customer';
END IF;
-- 3 subtotal from the current prices
SELECT COALESCE(SUM(v.price * ci.quantity), 0) INTO v_subtotal
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
WHERE ci.cart_id = p_cart_id;
IF v_subtotal = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cart is empty';
END IF;
-- 4 order header with the default address
INSERT INTO orders
(order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code)
SELECT p_order_number, p_customer_id, 'pending_payment', v_subtotal, 0.00, 50.00, v_subtotal + 50.00,
a.recipient_name, a.line1, a.city, a.postal_code, a.country_code
FROM addresses AS a
WHERE a.customer_id = p_customer_id AND a.is_default = 1;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'customer has no default address';
END IF;
SET p_order_id = LAST_INSERT_ID();
-- 5 order lines with the price snapshot
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT p_order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = p_cart_id;
-- 6 reserve stock; the CHECK constraint stops oversell
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved + oi.quantity
WHERE oi.order_id = p_order_id;
-- 7 ledger and cart status
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, quantity, 'reserve', p_order_id
FROM order_items
WHERE order_id = p_order_id;
UPDATE carts SET status = 'converted' WHERE cart_id = p_cart_id;
COMMIT;
END$$
DELIMITER ;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 12: トランザクション: 支払いの成功、失敗、キャンセル、返金
注文が確定した後、支払いの結果によって引き当てがどうなるかが決まります。成功すれば引き当ては販売になり、失敗すれば解放されます。キャンセルや返金は商品を戻します。これらのトランザクションはすべて、決済サービスからの同じメッセージが二度届いても、二度目には何も変えないように書かれています。
支払いの成功
webhook のハンドラーはトランザクションを開始し、支払いを pending から succeeded に更新します。変更された行が零なら、この webhook は重複であり、ハンドラーは ROLLBACK して 200 を返します。そうでなければ、注文を paid にし、引き当てを on_hand と reserved の両方から差し引いて販売として確定させ、明細ごとに sale の行を書きます。例 77 に完全な流れがあります。
支払いの失敗とキャンセル
支払いが失敗すると、支払いを failed にし、pending_payment の注文をキャンセルし、予約数を reserved から差し引いて引き当てを解放します。解放は注文の更新が一行だけ変えたときにだけ行うため、二重に解放されることはありません。梱包前に支払い済みの注文をキャンセルすると、数量を on_hand に戻し、cancel の行を書きます。例 79 のとおりです。
行ロックを保持しない返金
返金は、プロバイダーの呼び出しを挟んで二つの短いトランザクションで行います。最初のトランザクションは保留中の返金を記録して COMMIT します。プロバイダーは、ロックを保持しない状態で呼ばれます。二つ目のトランザクションは、プロバイダーの参照番号を保存し、返金を成功にし、成功した返金が支払い額を満たしたときだけ注文を refunded にします。例 81 は支払い行をロックし、二つの返金要求が返金可能額の検査を同時に通らないようにします。
図解
例 77: 支払い成功の Webhook
各ステップに条件を付けます。同じ Webhook が再送されても変化しません。
START TRANSACTION;
UPDATE payments
SET status = 'succeeded'
WHERE provider = 'sample-gateway' AND provider_ref = ? AND status = 'pending';
-- ROW_COUNT() = 0: this webhook was already handled. ROLLBACK and answer 200 to the gateway.
UPDATE orders
SET status = 'paid'
WHERE order_id = ? AND status = 'pending_payment';
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.on_hand = i.on_hand - oi.quantity,
i.reserved = i.reserved - oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, -quantity, -quantity, 'sale', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 78: 支払い失敗で在庫を解放
引き当てを一度だけ解放します。
START TRANSACTION;
UPDATE payments
SET status = 'failed'
WHERE provider = 'sample-gateway' AND provider_ref = ? AND status = 'pending';
-- ROW_COUNT() = 0: already handled, ROLLBACK.
UPDATE orders
SET status = 'cancelled'
WHERE order_id = ? AND status = 'pending_payment';
-- ROW_COUNT() = 0 here too: ROLLBACK, so the reservation is never released twice.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved - oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, -quantity, 'release', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 79: 梱包前の支払い済み注文をキャンセル
在庫を手持ちに戻します。
START TRANSACTION;
UPDATE orders
SET status = 'cancelled'
WHERE order_id = ? AND status = 'paid';
-- ROW_COUNT() = 0: the order was packed or shipped already. ROLLBACK and show a return flow instead.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.on_hand = i.on_hand + oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, quantity, 0, 'cancel', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 80: 決済サービスの呼び出しを挟んだ返金
ネットワーク呼び出し中は行ロックを保持しません。
-- Transaction 1: record the intent, then commit before calling the provider.
START TRANSACTION;
SELECT payment_id FROM payments WHERE payment_id = ? AND status = 'succeeded' FOR UPDATE;
INSERT INTO refunds (payment_id, amount, status, reason)
VALUES (?, ?, 'pending', 'Customer returned the item');
COMMIT;
-- The provider call happens here, outside any database transaction, so no row lock is held.
-- Transaction 2: store the provider result and update the order.
START TRANSACTION;
UPDATE refunds
SET status = 'succeeded', provider_ref = ?
WHERE refund_id = ? AND status = 'pending';
UPDATE orders AS o
JOIN payments AS p ON p.order_id = o.order_id AND p.status = 'succeeded'
SET o.status = 'refunded'
WHERE o.order_id = ?
AND o.status IN ('paid','packed','shipped','delivered')
AND (SELECT COALESCE(SUM(r.amount), 0) FROM refunds AS r
WHERE r.payment_id = p.payment_id AND r.status = 'succeeded') >= p.amount;
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 81: 行ロックで返金上限を守る
二つの返金要求が同時に通ることはありません。
START TRANSACTION;
SELECT amount FROM payments WHERE payment_id = ? FOR UPDATE;
SELECT COALESCE(SUM(CASE WHEN status <> 'failed' THEN amount END), 0) AS already_refunded
FROM refunds
WHERE payment_id = ?;
-- The application compares the requested amount with (amount - already_refunded). ROLLBACK when it is too large.
INSERT INTO refunds (payment_id, amount, status, reason)
VALUES (?, ?, 'pending', ?);
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 82: 返品された商品を在庫に戻す
検品して再販できる場合だけ行います。
START TRANSACTION;
UPDATE inventory
SET on_hand = on_hand + 1
WHERE variant_id = 1;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
VALUES (1, 1, 0, 'return', 1);
COMMIT;
-- Restock only when the goods are inspected and resaleable. Otherwise write an adjustment instead.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 13: 並行処理: 二人の買い手と最後の一点
多くの買い手が、同じ瞬間に同じ在庫を読むことがあります。コードが数値を読み、判断してから書き込むと、二人の買い手が一点を同時に見て、両方が購入してしまいます。このトピックでは、そのバグ、二つの修正方法、MySQL 8 が提供するロックの種類、そしてロックを異なる順序で取ったときに生じるデッドロックを示します。
二人の買い手と一点
買い手 A は available = 1 を読み、買い手 B も available = 1 を読みます。両者とも reserved を一つ増やします。保護がなければ、on_hand が 1 のまま reserved が 2 になります。これは在庫を超えた販売です。CHECK 制約は二つ目の書き込みをエラー 3819 で止めますが、それは安全網であって、完全な解決ではありません。根本的な修正は、確認と書き込みを同時に行う文にあります。
ロック: FOR UPDATE、NOWAIT、SKIP LOCKED
SELECT ... FOR UPDATE は、このトランザクションがコミットするまで他のトランザクションを待たせます。NOWAIT は待つ代わりに、エラー 3572 ですぐに失敗します。利用者に見える画面に向いています。SKIP LOCKED は、ほかのワーカーが保持していない次の仕事をワーカーに取らせます。支払い確認のキューに向いています。SELECT ... FOR SHARE は、行を読んでそれが変わらないことを保証したいときだけ使ってください。
デッドロックと楽観的ロック
デッドロックは、二つのトランザクションがそれぞれ相手の必要とするロックを持っているときに起きます。InnoDB はこれを検出し、エラー 1213 で一方を巻き戻します。そのため、アプリはトランザクション全体を再試行しなければなりません。根本的な対策は、どこでも同じロック順序にすることです。競合がめったに起きない商品編集では、version 列による楽観的ロックを使えば、ロックを保持せずに済みます。UPDATE は読んだときのバージョンを条件に含め、零行しか変わらなければ、ほかの誰かが先に保存したことになります。
例 83: 過剰販売を再現
二人の購入者、一点、保護なしの場合です。
-- Setup: one unit of variant 2 is left.
UPDATE inventory SET on_hand = 1, reserved = 0 WHERE variant_id = 2;
-- Buyer A and buyer B run these two statements at nearly the same time (no locking, no transaction):
SELECT on_hand - reserved AS available FROM inventory WHERE variant_id = 2; -- both read 1
UPDATE inventory SET reserved = reserved + 1 WHERE variant_id = 2; -- both write
-- Without the CHECK constraint, reserved would become 2 while on_hand is 1: an oversell.
-- With it, the second write fails with error 3819.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 84: 最後の一点: アトミックな UPDATE
条件付きの一文で、商品を一人の購入者に渡します。
UPDATE inventory
SET reserved = reserved + 1
WHERE variant_id = 2
AND on_hand - reserved >= 1;
-- ROW_COUNT() = 1: this buyer got the item.
-- ROW_COUNT() = 0: sold out. Show "sold out" and do not create an order.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 85: NOWAIT と SKIP LOCKED
すぐに失敗させるか、ワーカーにロック中の行を飛ばさせます。
-- NOWAIT: error 3572 at once if another transaction holds the row.
START TRANSACTION;
SELECT on_hand, reserved FROM inventory WHERE variant_id = 2 FOR UPDATE NOWAIT;
ROLLBACK;
-- SKIP LOCKED: a worker takes up to 10 pending payment checks that no other worker holds.
START TRANSACTION;
SELECT payment_id FROM payments
WHERE status = 'pending'
ORDER BY payment_id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- ...check each payment with the provider, update its row, then:
COMMIT;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 86: ロックを調べる
performance_schema で誰が何を保持し、何を待っているかを見ます。
SELECT engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'inventory';
SELECT requesting_engine_transaction_id, blocking_engine_transaction_id
FROM performance_schema.data_lock_waits;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 87: デッドロックとその修正
どこでも同じ順序で行をロックします。
-- Session A -- Session B
START TRANSACTION; START TRANSACTION;
UPDATE inventory SET reserved = reserved + 1 UPDATE inventory SET reserved = reserved + 1
WHERE variant_id = 1; WHERE variant_id = 4;
UPDATE inventory SET reserved = reserved + 1 UPDATE inventory SET reserved = reserved + 1
WHERE variant_id = 4; -- waits for B WHERE variant_id = 1; -- deadlock
-- One session gets error 1213 and is rolled back. The fix: both sessions lock in the same order (1, then 4).
-- Inspect the last deadlock with: SHOW ENGINE INNODB STATUS;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 88: バージョン列による楽観的ロック
ロックを保持せず、上書きされた更新を検出します。
ALTER TABLE products ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;
UPDATE products
SET base_price = 420.00, version = version + 1
WHERE product_id = 1 AND version = 0;
-- ROW_COUNT() = 0: someone else changed the product first. Reload it and try again.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 14: インデックスと実行計画
インデックスは、いくつかの列を並べ替えて保存したコピーで、MySQL がテーブル全体を読まずに行を見つけられるようにします。しかしインデックスは書き込みを遅くし、領域も使うため、それぞれに理由が必要です。このトピックでは、列の順序の選び方、EXPLAIN と EXPLAIN ANALYZE の読み方、そして誰も使っていないインデックスの見つけ方を説明します。
複合インデックスの順序を選ぶ
複合インデックスは左から右に働きます。インデックス (customer_id, created_at) は、customer_id で絞り込み、created_at で並べ替えるクエリに対応します。created_at だけで絞り込むクエリには対応しません。等しい条件の列を先に置き、並べ替えの列をその後に置きます。最新の行がもっともよく読まれるなら、(created_at DESC, order_id DESC) のような降順インデックスを使います。
EXPLAIN を読む
EXPLAIN は、アクセス方法、選ばれたインデックス、推定行数、一時テーブルや filesort が必要かどうかを示します。MySQL 8.0.18 から使える EXPLAIN ANALYZE は、クエリを実際に実行し、各ステップの実際の時間と行数を示します。推定と実際を比べてください。大きな差は、統計が古いことを示すことが多く、ANALYZE TABLE で直ります。
未使用と重複のインデックスを見つける
sys スキーマのビュー sys.schema_unused_indexes と sys.schema_redundant_indexes が候補を一覧にします。(status, created_at) がすでにあるなら、(status) のインデックスは重複です。カウンターはサーバーの再起動でリセットされるため、月末の処理のように業務の一周期を通して確認してから、インデックスを削除してください。不可視インデックスを使えば、インデックスを失うことなく削除の影響を試せます。
例 89: 不可視インデックスの検証
インデックスを削除する前に実行計画を確認します。
ALTER TABLE orders ALTER INDEX idx_orders_status_created INVISIBLE;
EXPLAIN SELECT order_id FROM orders WHERE status = 'paid' ORDER BY created_at LIMIT 50;
-- Bring it back if the plans get worse:
ALTER TABLE orders ALTER INDEX idx_orders_status_created VISIBLE;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 90: 最安値のためのカバリングインデックス
インデックスだけでクエリに答えます。
CREATE INDEX idx_variants_product_active_price
ON product_variants (product_id, is_active, price);
EXPLAIN SELECT product_id, MIN(price)
FROM product_variants
WHERE is_active = 1
GROUP BY product_id;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 91: EXPLAIN ANALYZE とヒストグラム
実際の作業量を測り、オプティマイザーを助けます。
EXPLAIN ANALYZE
SELECT order_id, order_number, created_at
FROM orders
WHERE customer_id = 1
ORDER BY created_at DESC
LIMIT 20;
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 92: 未使用・重複のインデックス
sys スキーマで削除候補を見つけます。
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';
-- Usage counters restart when the server restarts. Check over a full business cycle before you drop an index.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 15: ビュー、レポート、パーティショニング
画面とレポートは、同じ問いを何度も発します。ビューはそれらの問いに名前を付け、レポートは売上を要約し、パーティショニングは非常に大きな履歴テーブルを扱いやすくします。このトピックでは、在庫画面と管理者の一覧のためのビュー、合計付きの月次レポート、パーティション分割されたイベントログを扱います。
ビューで結合を隠す
v_available_stock のようなビューは、inventory、product_variants、products を一度だけ結合します。そのため、画面ごとに同じ結合を書く必要がありません。ビューはキャッシュではありません。MySQL はビューの SELECT を毎回実行します。また、複数テーブルの結合で作ったビューは更新できません。レポート用ロールには、基本テーブルではなくビューに対する SELECT 権限を与えてください。
レポート
月次の売上レポートは、支払い済み以降の注文の grand_total を、月ごとに合計します。GROUP BY ... WITH ROLLUP は合計行を加えます。レポートは稼働中のテーブルを直接読むため、重いレポートはリードレプリカか夜間に実行し、チェックアウトを妨げるロックを保持させないでください。
履歴のためのパーティション
PARTITION BY RANGE COLUMNS は、テーブルを期間で分割します。ここでは created_at で分けています。主キーにはパーティションの列を含めなければならないため、(event_id, created_at) になります。古いパーティションを削除すると、一か月分をまとめて取り除けます。長い DELETE で undo ログが膨らむことを避けられます。パーティショニングは大きな履歴テーブルに役立ちますが、良いインデックスの代わりにはなりません。
例 93: 在庫のビュー
画面のコードから結合を隠すビューです。
CREATE OR REPLACE VIEW v_available_stock AS
SELECT v.variant_id, v.sku, p.name AS product_name,
i.on_hand, i.reserved, i.on_hand - i.reserved AS available
FROM inventory AS i
JOIN product_variants AS v ON v.variant_id = i.variant_id
JOIN products AS p ON p.product_id = v.product_id;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 94: 注文サマリーのビュー
管理画面用に注文ごとに一行を返します。
CREATE OR REPLACE VIEW v_order_summary AS
SELECT o.order_id, o.order_number, o.status, c.full_name AS customer_name,
o.grand_total, o.created_at,
COUNT(oi.order_item_id) AS line_count,
COALESCE(SUM(oi.quantity), 0) AS unit_count
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_number, o.status, c.full_name, o.grand_total, o.created_at;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 95: 月次売上と合計行
GROUP BY WITH ROLLUP を使います。
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS orders,
SUM(grand_total) AS revenue
FROM orders
WHERE status IN ('paid','packed','shipped','delivered')
GROUP BY month WITH ROLLUP;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 96: パーティション分割したイベントログ
範囲パーティションにより、古い月を低コストで削除できます。
CREATE TABLE payment_events (
event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payment_id BIGINT UNSIGNED NOT NULL,
event_type VARCHAR(40) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (event_id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
PARTITION p2026h1 VALUES LESS THAN ('2026-07-01'),
PARTITION p2026h2 VALUES LESS THAN ('2027-01-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
-- Later: remove a whole old period at once.
-- ALTER TABLE payment_events DROP PARTITION p2026h1;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
トピック 16: 実務の作法: 移行、監査、バックアップ、権限
本番のデータベースは時間とともに変わり、壊れることもあり、個人情報を持っています。実務の作法とは、変更にバージョンを付けて確認すること、すべての状態変更を記録すること、バックアップを復元して検証すること、そして各プログラムに必要な権限だけを与えることです。このトピックでは、学習用のプロジェクトと、人々が信頼するショップを分ける習慣を扱います。
バージョン付きの移行
スキーマの各変更は、順番に一度だけ実行される番号付きのファイルです。schema_migrations テーブルは、バージョンとファイルのチェックサムを記録するため、編集された移行は検出されます。本番で一度実行された移行は編集しないでください。訂正する新しい移行を書きます。
監査証跡とバックアップ
orders テーブルの AFTER UPDATE トリガーは、すべての状態変更を、ユーザーと時刻とともに order_audit に書きます。mysqldump --single-transaction によるバックアップは、InnoDB テーブルを一貫した状態で、ロックせずに取得します。一度も復元していないバックアップは、バックアップとは言えません。空のテスト用データベースに復元し、行数を比べてください。
最小権限
アプリのユーザーは、ショップのデータに対して SELECT、INSERT、UPDATE、DELETE を持ちますが、DROP や ALTER は持ちません。レポートは、二つのビューだけを読めるロールを使います。移行には管理者アカウントを残し、すべてのパスワードはシークレットストアに保管し、SHOW GRANTS で権限を四半期ごとに確認してください。
例 97: バージョン管理されたマイグレーション
各変更は一度だけ順番に実行され、チェックサムで検証されます。
CREATE TABLE schema_migrations (
version VARCHAR(20) NOT NULL,
checksum CHAR(64) NOT NULL,
applied_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (version)
) ENGINE=InnoDB;
-- File V003__add_order_audit.sql: each migration runs once, in order, and the checksum detects edits.
-- The migration tool inserts ('V003', '<sha256 of the file>') after it succeeds.
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 98: 注文状態の監査トリガー
すべての状態変更を、実行者とともに記録します。
CREATE TABLE order_audit (
audit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
old_status VARCHAR(30) NOT NULL,
new_status VARCHAR(30) NOT NULL,
changed_by VARCHAR(100) NOT NULL,
changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (audit_id),
KEY idx_order_audit_order (order_id, changed_at)
) ENGINE=InnoDB;
DELIMITER $$
CREATE TRIGGER trg_orders_status_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF OLD.status <> NEW.status THEN
INSERT INTO order_audit (order_id, old_status, new_status, changed_by)
VALUES (OLD.order_id, OLD.status, NEW.status, CURRENT_USER());
END IF;
END$$
DELIMITER ;
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 99: バックアップと復元
--single-transaction 付きの mysqldump と、テスト用の復元です。
# Back up: --single-transaction gives a consistent view of InnoDB tables without locking them.
mysqldump --single-transaction --routines --triggers --events --set-gtid-purged=OFF \
-u shop_admin -p shop > shop-2026-10-30.sql
# Restore into an empty test database first, then check row counts before using it.
mysql -u shop_admin -p shop_restore < shop-2026-10-30.sql
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
例 100: 読み取り専用ビューのレポート用ロール
レポートに必要な最小限の権限です。
CREATE ROLE IF NOT EXISTS 'shop_reporting';
GRANT SELECT ON shop.v_order_summary TO 'shop_reporting';
GRANT SELECT ON shop.v_available_stock TO 'shop_reporting';
CREATE USER IF NOT EXISTS 'report_user'@'localhost' IDENTIFIED BY '<strong-password>';
GRANT 'shop_reporting' TO 'report_user'@'localhost';
SET DEFAULT ROLE 'shop_reporting' TO 'report_user'@'localhost';
SHOW GRANTS FOR 'report_user'@'localhost' USING 'shop_reporting';
*この例は、この記事を準備したコンピューターに MySQL 8 がインストールされていなかったため、ここでは実行していません。*
ラボ: ショップのデータベースを作り、一度の注文を実行する
練習用データベースで記事の内容をまとめます。スキーマを作り、サンプルデータを読み込み、支払い成功を含む一回のチェックアウトを実行し、在庫のルールをわざと破ろうとしてみます。以下の各ステップは、あなた自身が確認する作業です。この記事の準備中には、どの例も実行していません。
1. utf8mb4 で shop_practice データベースを作成し、SELECT、INSERT、UPDATE、DELETE だけを持つユーザーを作ってから、例 1 から 6 を実行します。
2. customers、addresses、categories、products、product_variants、inventory、inventory_movements、carts、cart_items のテーブルを例 7 から 32 で作成し、例 9、10、18、19、25、33 のサンプルデータを読み込みます。
3. orders、order_items、payments、refunds のテーブルを例 39、40、47、48 で作成し、例 44 から 46 の注文履歴のクエリを実行します。
4. 顧客 1 とカート 1 について例 76 のチェックアウトを実行し、例 26 と 30 で ROW_COUNT と在庫行を確認します。
5. 同じ provider_ref で例 77 を二回実行し、二回目が何も変えないことを確認します。これは冪等性の検査です。
6. 例 83 と 84 を二つの MySQL セッションで、買い手一人につき一つずつ実行し、最後の一点を得るのが一人だけであることを確認します。
7. 各例の実行結果を書き留め、実際のショップでコードを使う前に、この記事の期待される動作と比べてください。
次のステップ
この記事の後は、これらのトランザクションを呼び出すアプリケーション層を学んでください。REST API、入力の検証、デッドロック後の再試行などです。次に、決済サービスの webhook の文書を読んでください。ショップの実際のバグの多くは webhook のハンドラーに現れます。最後に、MySQL のレプリケーションとリードレプリカを学んでください。レポートをチェックアウトのデータベースから切り離せます。
まとめ
ショップのデータベースは、いくつかの規則を中心に作られます。価格と住所は注文にコピーします。在庫は CHECK 制約つきのカウンターと追記のみの台帳で管理します。すべての支払いと在庫の変更は、安全に繰り返せるトランザクションにします。行のロックは同じ順序で取り、トランザクションは短く保ち、ロックを保持したまま決済サービスを呼んではいけません。インデックスは画面が実際に使うクエリに合わせて選び、EXPLAIN で確認し、すべてのスキーマ変更にバージョンを付けます。この記事を書く間、100 の例は一つも実行していません。使う前に練習用データベースでそれぞれ試してください。