Merancang Basis Data untuk Sistem Jual Beli Online: 100 Contoh dari Nol hingga Profesional dengan MySQL 8
Toko online sebagian besar adalah masalah basis data. Pelanggan masuk ke akun, produk punya ukuran dan warna, stok tidak boleh minus, sebuah pembayaran bisa masuk dua kali, dan dua pembeli bisa menekan tombol beli untuk barang terakhir pada detik yang sama. Artikel ini merancang basis data untuk toko seperti itu langkah demi langkah. Anda akan melihat tabel yang dibutuhkan toko online, lengkap dengan kunci utama, kunci asing, indeks, dan alasan keberadaan masing-masing. Anda juga akan melihat data contoh untuk setiap tabel, lalu apa yang terjadi di dalam transaksi penting saat sistem berjalan: membuat pesanan, mencadangkan stok, pembayaran berhasil dan gagal, pembatalan, pengembalian dana, dan dua pembeli yang memperebutkan barang terakhir, beserta akibatnya jika transaksi tidak dipakai. Mockup layar daftar produk, halaman produk, keranjang, checkout, riwayat pesanan, dan pesanan admin menunjukkan bagaimana layar-layar itu membaca tabel-tabel tersebut. Artikel ditutup dengan 100 contoh bernomor dalam sintaks MySQL 8: DDL, data awal, kueri, indeks, transaksi, penguncian, tampilan, partisi, laporan, dan praktik profesional. Catatan jujur: MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini, sehingga 100 contoh tersebut tidak dijalankan di sini. Jalankan sendiri di basis data latihan dan periksa hasilnya sebelum Anda mengandalkannya.
Yang Anda butuhkan
Komputer dengan MySQL 8.0 atau lebih baru (MySQL Community Server, atau image Docker mysql:8.0), klien seperti program baris perintah mysql atau MySQL Workbench, dan dasar SQL: SELECT, INSERT, UPDATE, dan JOIN. Selama belajar, gunakan basis data latihan, jangan basis data toko sungguhan. Gunakan mesin InnoDB, yang menjadi default di MySQL 8, karena hanya InnoDB yang mendukung transaksi, kunci tingkat baris, dan kunci asing.
Cara memakai contoh
Contoh SQL ditulis untuk MySQL 8 dengan InnoDB. Jalankan berurutan di database latihan (misalnya shop_practice). Tanda ? adalah parameter yang diberikan aplikasi. Contoh bash dijalankan di terminal.
Topik 1: Mulai dari pertanyaan yang harus dijawab toko
Sebelum menulis CREATE TABLE apa pun, tuliskan pertanyaan yang harus dijawab toko: Apa yang dimiliki pelanggan ini? Berapa unit yang bisa saya jual sekarang? Pesanan mana yang menunggu dikemas? Setiap tabel ada untuk menjawab satu pertanyaan tanpa bertentangan dengan yang lain. Topik ini menyiapkan MySQL 8 dengan aman dan menunjukkan aturan yang diikuti setiap tabel di sisa artikel.
Tabel dan isinya
Toko membutuhkan pelanggan, alamat, kategori, produk, varian produk, stok, keranjang, item keranjang, pesanan, item pesanan, pembayaran, pengembalian dana, pengiriman, item pengiriman, ulasan, kupon, dan untuk marketplace juga penjual dan pencairan dana. Produk menggambarkan apa yang dijual. Varian adalah unit yang benar-benar dijual, dengan SKU dan harga. Item pesanan menyalin nama produk dan harga pada saat pembelian, sehingga perubahan harga di kemudian hari tidak menulis ulang riwayat.
Kunci, batasan, dan indeks
Setiap tabel punya kunci utama. Kunci asing mencegah baris yatim, misalnya pesanan tanpa pelanggan. Kunci UNIQUE melindungi nilai seperti email atau SKU dari duplikasi. Batasan CHECK menolak nilai yang mustahil, seperti stok negatif. Indeks mempercepat kueri yang dijalankan layar, misalnya riwayat pesanan satu pelanggan, dan setiap indeks dalam artikel ini disertai alasannya.
Siapkan MySQL 8 dengan aman
Buat basis data dengan utf8mb4 agar teks Thai, Jepang, Mandarin, Arab, dan emoji tersimpan dengan benar. Buat pengguna aplikasi yang bisa membaca dan menulis data tetapi tidak bisa mengubah skema, dan aktifkan mode SQL ketat agar nilai yang salah ditolak, bukan diubah diam-diam. Simpan kata sandi di penyimpanan rahasia, jangan pernah di artikel atau kode sumber.
Ilustrasi
Contoh 1: Periksa versi server dan default
Pastikan MySQL 8, utf8mb4, dan level isolasi.
SELECT VERSION() AS server_version,
@@character_set_server AS charset,
@@collation_server AS collation,
@@transaction_isolation AS isolation_level;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 2: Buat database toko
utf8mb4 dan satu collation untuk seluruh toko.
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 3: Buat pengguna aplikasi dengan hak minimal
Aplikasi bisa membaca dan menulis data, tetapi tidak mengubah skema.
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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 4: Aktifkan mode SQL ketat
Tolak data yang salah, jangan diubah diam-diam.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 5: Pastikan InnoDB
Transaksi dan foreign key memerlukan engine InnoDB.
SELECT ENGINE, SUPPORT, TRANSACTIONS, XA
FROM information_schema.ENGINES
WHERE ENGINE = 'InnoDB';
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 6: Atur ulang database latihan
Berlatih tanpa menyentuh data toko.
-- 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 2: Pelanggan dan alamat
Pelanggan adalah akun dengan email unik dan status. Alamat dimiliki pelanggan, tetapi pesanan menyimpan salinan alamat pengiriman sendiri, karena pelanggan bisa mengubah atau menghapus alamat nanti. Topik ini membahas tabel, data contoh, dan aturan bahwa setiap pelanggan hanya boleh memiliki satu alamat utama.
Satu baris per akun
Tabel customers memakai customer_id bertambah otomatis sebagai kunci utama, dan kunci UNIQUE pada email. Dengan collation utf8mb4_0900_ai_ci di MySQL 8, perbandingan email tidak peka huruf besar-kecil, sehingga [email protected] dan [email protected] adalah akun yang sama. Simpan hash kata sandi saja, jangan kata sandinya. Kolom status memungkinkan Anda memblokir akun tanpa menghapus riwayatnya.
Alamat dimiliki pelanggan
Tabel addresses mengacu ke customers dengan kunci asing dan ON DELETE CASCADE, sehingga menghapus pelanggan juga menghapus alamat tersimpannya. Tabel orders tidak bergantung pada hal ini, karena setiap pesanan menyalin nama penerima, baris alamat, dan kode pos. Kolom hasil generate bisa menegakkan satu alamat utama per pelanggan, karena kunci UNIQUE mengizinkan banyak nilai NULL.
Privasi: jangan simpan data kartu di basis data
Toko tidak perlu nomor kartu lengkap. Simpan token dari penyedia pembayaran dan empat digit terakhir saja, dan serahkan kartu kepada penyedia. Simpan data pribadi di sesedikit mungkin tabel, dan ingat bahwa cadangan, log, dan laporan juga memuatnya, sehingga perlu perlindungan yang sama.
Contoh 7: Buat tabel pelanggan
Akun dengan email unik dan status.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 8: Buat tabel alamat
Beberapa alamat per pelanggan, dengan foreign key.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 9: Isi data pelanggan contoh
Akun contoh untuk pengujian.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 10: Isi data alamat contoh
Alamat contoh, satu alamat utama per pelanggan.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 11: Cari alamat utama
Pencarian yang memakai indeks komposit.
SELECT address_id, recipient_name, line1, city
FROM addresses
WHERE customer_id = 1 AND is_default = 1;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 12: Satu alamat utama per pelanggan
Kolom hasil generate membuat aturan unik mungkin.
-- 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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 13: Hitung alamat per pelanggan
LEFT JOIN tetap menampilkan pelanggan tanpa alamat.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 14: Foreign key dan pemblokiran
Baris yatim ditolak, dan pemblokiran adalah 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 3: Produk, kategori, dan varian
Katalog punya tiga tingkat. Kategori mengelompokkan produk, produk adalah yang dijual toko, dan varian adalah barang persisnya dengan SKU, ukuran, warna, dan harga. Stok dan keranjang selalu menunjuk ke varian, bukan ke produk, karena kaos hitam ukuran M dan kaos putih ukuran L adalah dua hal yang harus dihitung terpisah.
Produk dan varian
products menyimpan nama, slug, deskripsi, harga dasar, dan status (draft, active, atau archived). product_variants menyimpan SKU, label opsi seperti M / Black, harga, dan penanda is_active. Kunci UNIQUE pada (product_id, option_label) mencegah dua varian identik untuk satu produk. Produk draf tetap tersembunyi dari daftar sampai statusnya menjadi active.
Kategori sebagai pohon
categories mengacu ke dirinya sendiri lewat parent_id, sehingga Pakaian dapat berisi Kaos. Kunci asing memakai ON DELETE SET NULL, yang membuat anak kategori menjadi kategori tingkat atas jika induknya dihapus. Untuk menampilkan semua produk di bawah sebuah kategori, termasuk subkategorinya, gunakan common table expression rekursif.
Kueri daftar dan pencarian
Daftar menampilkan harga varian aktif terendah untuk setiap produk. Paging keyset, yang memakai product_id dari baris terakhir halaman sebelumnya, tetap cepat pada katalog besar, sedangkan OFFSET makin lambat di setiap halaman. Indeks FULLTEXT dengan parser ngram mendukung pencarian kata dalam bahasa Thai, Jepang, dan Mandarin, yang kata-katanya tidak dipisahkan spasi.
Ilustrasi
Contoh 15: Kategori dengan induk
Foreign key yang merujuk tabel sendiri membentuk pohon.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 16: Buat tabel produk
Nama, slug, harga dasar, dan status.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 17: Buat tabel varian
Item yang bisa dijual: SKU, pilihan, dan harga.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 18: Isi kategori dan produk contoh
Data katalog contoh.
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');
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 19: Isi varian contoh
Lima varian, salah satunya nonaktif.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 20: Daftar produk dengan paging keyset
Halaman berikutnya cepat tanpa 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 21: Varian di halaman produk
Hanya varian aktif dari satu produk.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 22: Pencarian teks lengkap
Indeks FULLTEXT dengan parser ngram.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 4: Stok dan buku besar stok
Stok adalah bagian toko tempat kesalahan langsung berbiaya uang. Desain ini menyimpan tiga angka per varian: on_hand, yaitu unit yang benar-benar ada di gudang; reserved, yaitu unit yang sudah dijanjikan kepada pesanan yang belum dibayar atau belum dikirim; dan available, yaitu on_hand dikurangi reserved. Tabel buku besar mencatat setiap perubahan, sehingga penghitung selalu bisa dicocokkan dengan riwayatnya.
Penghitung dengan aturan CHECK
Tabel inventory punya satu baris per varian. Batasan CHECK mewajibkan on_hand bernilai nol atau lebih, dan reserved berada di antara nol dan on_hand. Sejak MySQL 8.0.16, server menegakkan batasan CHECK, sehingga UPDATE yang membuat stok negatif gagal dengan error 3819 dan tidak menulis nilai yang salah secara diam-diam.
Buku besar
inventory_movements mencatat setiap perubahan dengan selisih bertanda untuk on_hand dan reserved, alasan seperti reserve, release, sale, cancel, return, restock, atau adjustment, serta pesanan yang terkait. Buku besar hanya boleh ditambah. Kesalahan diperbaiki dengan menambah baris baru, bukan mengedit baris lama. Ini memberi jejak audit untuk setiap unit yang keluar dari toko.
Cocokkan penghitung dengan buku besar
Tugas malam hari menjumlahkan buku besar untuk setiap varian lalu membandingkannya dengan penghitung. Ketidakcocokan berarti ada bug atau pengeditan manual, dan harus memicu peringatan. Data awal dimasukkan sebagai saldo pembuka di buku besar, sehingga rekonsiliasi mulai dari nol.
Contoh 23: Buat tabel stok
Jumlah tersedia dan jumlah dipesan, dengan aturan 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 24: Buat buku besar stok
Setiap perubahan stok meninggalkan satu baris.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 25: Isi stok dan saldo awal
Jumlah stok dengan baris buku besar yang cocok.
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');
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 26: Jumlah yang bisa dijual
Tersedia dikurangi dipesan.
SELECT variant_id, on_hand - reserved AS available
FROM inventory
WHERE on_hand - reserved > 0
ORDER BY variant_id;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 27: Laporan stok menipis
Varian yang perlu diisi ulang.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 28: Isi ulang stok dengan baris buku besar
Satu transaksi untuk stok dan catatannya.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 29: Tolak stok negatif
Constraint CHECK mencegah nilai yang mustahil.
-- Fails with error 3819 (check constraint violated) on MySQL 8.0.16 and later.
UPDATE inventory SET on_hand = -1 WHERE variant_id = 2;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 30: Rekonsiliasi stok dengan buku besar
Temukan penghitung yang tidak sesuai dengan riwayat.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 5: Keranjang
Keranjang menyimpan barang yang ingin dibeli pembeli. Tamu memiliki keranjang yang dikenali oleh token sesi, sedangkan pelanggan memiliki keranjang yang dikenali oleh customer_id. Keranjang bukan pesanan: tidak ada stok yang dicadangkan, dan harga yang tampil di keranjang dibaca ulang saat checkout. Topik ini membahas tabel keranjang, cara menambah barang dengan aman, dan cara menggabungkan keranjang tamu setelah login.
Keranjang tamu dan keranjang pelanggan
Tabel carts memiliki customer_id dan session_token yang opsional. Saat tamu login, customer_id keranjang diatur dan barang di keranjang tamu digabungkan ke keranjang terbuka milik pelanggan. Tabel cart_items punya kunci UNIQUE pada (cart_id, variant_id), sehingga satu varian hanya muncul sekali per keranjang dan jumlahnya diperbarui, bukan diduplikasi.
Tambah ke keranjang dalam satu pernyataan
INSERT ... AS new ON DUPLICATE KEY UPDATE menambah jumlah dalam satu pernyataan atomik. Dua kali klik pada tombol yang sama tidak membuat dua baris, dan jumlahnya dibatasi 99 oleh batasan CHECK serta LEAST pada pembaruan.
Keranjang bukan pesanan
Menambah barang ke keranjang tidak mencadangkan stok, karena ribuan keranjang ditinggalkan setiap hari. Pencadangan dimulai saat pelanggan membuat pesanan. Karena itu halaman keranjang harus memeriksa ketersediaan ulang dan menampilkan pesan jelas jika barang sudah habis.
Ilustrasi
Contoh 31: Buat tabel keranjang
Keranjang tamu memakai token sesi, pelanggan memakai 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 32: Buat tabel baris keranjang
Satu baris per varian per keranjang.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 33: Isi keranjang contoh
Keranjang pelanggan dan keranjang tamu.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 34: Tambah ke keranjang
Sisipkan atau tambah jumlah dalam satu pernyataan.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 35: Gabungkan keranjang tamu setelah login
Pindahkan item dalam satu transaksi.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 36: Keranjang dengan harga dan stok terkini
Harga dan ketersediaan dibaca saat keranjang ditampilkan.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 37: Tandai keranjang yang diam sebagai ditinggalkan
Tugas berkala yang memakai indeks status.
UPDATE carts
SET status = 'abandoned'
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 38: Periksa rencana kueri keranjang
EXPLAIN menunjukkan apakah indeks dipakai.
EXPLAIN SELECT cart_id
FROM carts
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 6: Pesanan dan item pesanan
Pesanan adalah catatan pembelian. Ia menyimpan total, status, dan salinan alamat pengiriman. Setiap baris item menyalin SKU, nama produk, dan harga satuan, sehingga pesanan tetap bermakna meski katalog berubah nanti. Topik ini juga membahas aturan bahwa total harus selalu cocok, dan cara membuka riwayat pelanggan dengan cepat per halaman.
Simpan pesanan seperti saat terjadi
order_items menyimpan sku_snapshot, product_name_snapshot, dan unit_price yang disalin dari product_variants saat pesanan dibuat. Kolom line_total adalah kolom hasil generate, yaitu unit_price dikali quantity, sehingga tidak akan bertentangan dengan masukannya. Kunci asing variant_id memakai ON DELETE RESTRICT, sehingga varian yang pernah terjual tidak bisa dihapus; nonaktifkan saja.
Total harus cocok
Batasan CHECK pada orders mewajibkan grand_total = subtotal - discount_total + shipping_fee. Setiap kode yang mengubah salah satu angka ini harus mengubah angka lain dalam transaksi yang sama, atau pernyataannya gagal. Total dihitung ulang dari item pesanan sebelum pesanan di-commit, sehingga header selalu cocok dengan itemnya.
Paging riwayat pesanan
Indeks komposit (customer_id, created_at) melayani kueri pesanan terbaru satu pelanggan. Paging keyset dengan (created_at, order_id) mengembalikan halaman berikutnya tanpa memindai baris sebelumnya. Gunakan EXPLAIN untuk memastikan indeks dipakai, dan buat urutan sortir sesuai dengan indeks.
Ilustrasi
Contoh 39: Buat tabel pesanan
Kepala pembelian dengan total dan salinan alamat.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 40: Buat tabel baris pesanan
Harga disalin saat pembelian, dengan total baris otomatis.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 41: Salin keranjang ke pesanan
Harga disimpan saat checkout.
-- @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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 42: Isi tiga pesanan contoh
Pesanan dalam tiga status berbeda.
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');
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 43: Isi baris pesanan contoh
Baris di balik tiga pesanan contoh.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 44: Riwayat pesanan dengan paging keyset
Pesanan terbaru lebih dulu untuk satu pelanggan.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 45: Detail pesanan
Kepala dan baris dalam satu kueri.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 46: Antrean admin: pesanan dibayar
Pesanan yang harus dikemas gudang berikutnya.
SELECT order_id, order_number, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at
LIMIT 50;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 7: Pembayaran dan pengembalian dana
Pembayaran adalah satu percobaan menagih pesanan. Satu pesanan bisa punya beberapa percobaan, misalnya percobaan pertama ditolak karena kartu ditolak dan percobaan kedua berhasil. Penyedia pembayaran juga mengirim webhook yang bisa datang dua kali atau tidak berurutan. Tabel payments dan refunds dirancang agar percobaan ulang dan pesan duplikat tidak membuat pelanggan ditagih dua kali atau mencatat penjualan dua kali.
Kunci idempotensi
Setiap percobaan pembayaran punya idempotency_key yang UNIQUE. Jika aplikasi mengulang permintaan karena timeout jaringan, INSERT kedua menemukan baris yang sudah ada alih-alih membuat tagihan baru. Kolom provider_ref, yang unik per penyedia, menghubungkan baris dengan ID transaksi milik penyedia.
Baris pembayaran dan webhook
Webhook memberi tahu bahwa pembayaran berhasil atau gagal. Handler mencari pembayaran berdasarkan provider dan provider_ref, lalu mengubah statusnya hanya jika masih pending. Jika statusnya sudah final, pembaruan mengubah nol baris dan handler tidak melakukan apa pun lagi. Inilah yang membuat webhook yang diulang tidak berbahaya.
Aturan pengembalian dana
Pengembalian dana mengacu ke sebuah pembayaran dan punya status sendiri. Jumlah yang bisa dikembalikan adalah jumlah pembayaran dikurangi semua pengembalian dana yang tidak gagal. Baris pengembalian ditulis sebelum penyedia dipanggil, lalu statusnya diperbarui setelahnya. Penyedia tidak pernah dipanggil saat transaksi basis data sedang memegang kunci baris, karena panggilan jaringan yang lambat akan memblokir pembeli lain.
Contoh 47: Buat tabel pembayaran
Satu baris per percobaan tagihan, dengan kunci idempoten.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 48: Buat tabel pengembalian dana
Setiap pengembalian merujuk satu pembayaran dan punya statusnya sendiri.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 49: Catat percobaan pembayaran dengan aman
Mencoba ulang dengan kunci sama mengembalikan baris yang sama.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 50: Isi pembayaran berhasil contoh
Pembayaran untuk pesanan 1 dan 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');
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 51: Jumlah yang bisa dikembalikan
Jumlah pembayaran dikurangi pengembalian dana yang tidak gagal.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 52: Pembayaran dan pesanan tidak cocok
Temukan pembayaran yang berhasil padahal pesanannya masih menunggu.
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';
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 8: Pengiriman dan pemenuhan pesanan
Pemenuhan pesanan adalah pekerjaan mengemas dan mengirim barang. Satu pesanan bisa dikirim dalam beberapa paket, misalnya saat salah satu barang habis. Tabel shipments berisi satu baris per paket, dan shipment_items menyatakan baris pesanan mana yang ada di paket mana dan berapa unitnya. Kolom status hanya bergerak satu arah, sehingga paket yang sudah terkirim tidak bisa kembali menjadi sedang disiapkan.
Paket dan pengiriman sebagian
shipments mengacu ke orders, dan shipment_items mengacu ke shipments dan order_items. Kunci UNIQUE pada (shipment_id, order_item_id) mencegah baris yang sama muncul dua kali dalam satu paket. Kedua tabel ini bersama-sama memungkinkan pesanan dibagi menjadi paket dengan cara apa pun.
Status hanya maju
Mengubah paket dari preparing ke in_transit, dan dari in_transit ke delivered, memakai UPDATE dengan status lama di klausa WHERE. Pembaruan yang datang tidak berurutan mengubah nol baris, dan aplikasi melaporkan konflik alih-alih menimpa status.
Lacak jumlah
Jumlah yang masih harus dikirim untuk satu baris adalah jumlah yang dipesan dikurangi jumlah dari shipment_items untuk baris itu. Klausa HAVING menemukan baris yang belum lengkap, yang dipakai layar admin untuk menampilkan pesanan yang dikirim sebagian.
Ilustrasi
Contoh 53: Buat tabel pengiriman
Paket untuk satu pesanan, dengan kurir dan nomor lacak.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 54: Buat tabel baris pengiriman
Baris pesanan mana ada di paket mana, agar pengiriman sebagian bisa dilakukan.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 55: Isi pengiriman yang sudah sampai
Data pengiriman contoh untuk pesanan 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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 56: Kemas dan buat pengiriman
Satu transaksi untuk status pesanan dan paket.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 57: Serahkan ke kurir dan catat pengiriman
Status pengiriman hanya maju.
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';
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 58: Jumlah yang belum dikirim
Jumlah dipesan dikurangi jumlah dikirim untuk setiap baris.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 9: Ulasan dan kupon
Ulasan membangun kepercayaan, dan kupon mendorong penjualan, tetapi keduanya bisa disalahgunakan. Ulasan seharusnya hanya berasal dari pembelian nyata yang sudah terkirim, dan kupon seharusnya hanya dipakai dalam tanggal dan batas penggunaannya. Batasan di topik ini melindungi kedua aturan itu di dalam basis data, sehingga bug di kode layar tidak bisa merusaknya.
Ulasan terverifikasi
Tabel reviews mengacu ke order_items, bukan hanya ke products. Kunci UNIQUE pada order_item_id mengizinkan satu ulasan per baris yang dibeli. Pernyataan INSERT hanya memilih baris ketika status pesanan adalah delivered, sehingga ulasan tidak bisa ditulis untuk pesanan yang dibatalkan atau belum dibayar.
Kupon dengan batas
Kupon memiliki kode, diskon persentase atau nominal tetap, subtotal minimum, tanggal mulai dan berakhir, dan jumlah pemakaian maksimum yang opsional. Batasan CHECK mewajibkan tanggal berakhir setelah tanggal mulai, diskon bernilai positif, dan used_count tidak melebihi max_uses.
Penukaran bersyarat
Dua pembeli bisa memakai kesempatan terakhir kupon pada saat yang sama. UPDATE bersyarat yang memeriksa tanggal dan used_count di klausa WHERE menanganinya dengan aman. Aplikasi hanya menerapkan diskon ketika ROW_COUNT bernilai 1.
Contoh 59: Buat tabel ulasan
Satu ulasan per baris pesanan, peringkat 1 sampai 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 60: Buat tabel kupon
Tanggal, batas pemakaian, dan pemeriksaan kewajaran.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 61: Hubungkan pesanan dengan kupon
Foreign key yang ditambahkan setelah kedua tabel ada.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_coupon FOREIGN KEY (coupon_id)
REFERENCES coupons (coupon_id) ON DELETE SET NULL;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 62: Isi kupon contoh
Kupon persentase dan kupon nominal tetap.
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);
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 63: Tukarkan kupon dengan aman
UPDATE bersyarat mencegah pemakaian berlebih.
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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 64: Ulasan pembelian terverifikasi
Hanya baris pesanan yang sudah dikirim yang bisa diulas.
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';
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 10: Penjual dan marketplace
Marketplace membiarkan banyak penjual berjualan di satu toko. Basis data harus tahu penjual mana yang memiliki setiap produk, penjual mana yang mendapat pendapatan dari setiap baris pesanan, dan berapa komisi yang ditahan platform. Desain ini menyalin penjual ke setiap baris pesanan, sehingga jika produk pindah ke penjual lain nanti, riwayat penjualan penjual sebelumnya tidak berubah.
Penjual memiliki produk
sellers menyimpan nama, email, status, dan persentase komisi. products.seller_id adalah kunci asing yang boleh kosong, ditambahkan dengan ALTER TABLE, sehingga tabel toko tunggal dari topik sebelumnya tetap berfungsi. Status pending menjaga produk penjual baru tetap tersembunyi sampai administrator menyetujui penjual itu.
Salin penjual ke setiap baris
order_items.seller_id disalin saat pesanan dibuat, bukan dicari nanti lewat products. Ini menjaga riwayat pesanan tetap benar ketika kepemilikan produk berubah, dan membuat laporan komisi cukup berupa JOIN sederhana.
Pencairan dana dan komisi
payouts menyimpan satu baris per penjual per periode, dengan jumlah kotor, komisi, dan jumlah bersih. Batasan CHECK mewajibkan net = gross - commission, dan kunci UNIQUE pada (seller_id, period_start) mencegah pencairan dibuat dua kali untuk periode yang sama.
Contoh 65: Buat tabel penjual
Penjual marketplace dengan persentase komisi.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 66: Produk milik penjual
Foreign key yang boleh kosong, ditambahkan dengan ALTER.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 67: Salin penjual ke baris pesanan
Riwayat tetap benar meskipun kepemilikan produk berubah.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 68: Buat tabel pencairan dana
Satu pencairan per penjual per periode, dengan pemeriksaan.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 69: Isi penjual dan penugasan
Dua penjual beserta produknya.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 70: Laporan komisi untuk satu bulan
Penjualan kotor dan komisi per penjual.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 11: Transaksi: membuat pesanan dan mencadangkan stok
Membuat pesanan adalah transaksi terpenting di toko. Ia membuat pesanan, menyalin baris, mencadangkan stok, dan menutup keranjang. Semua langkah ini harus berhasil bersama atau gagal bersama. Topik ini menjelaskan setiap langkah sesuai urutan eksekusinya, menampilkan SQL-nya, dan menjelaskan apa yang salah jika langkah-langkah itu tidak dibungkus dalam satu transaksi.
Di dalam transaksi checkout, langkah demi langkah
1. START TRANSACTION. 2. Kunci baris inventory untuk keranjang dengan SELECT ... FOR UPDATE, diurutkan menaik berdasarkan variant_id. 3. Hitung subtotal dari harga saat ini. 4. Sisipkan header pesanan dengan alamat utama yang disalin. 5. Sisipkan baris pesanan dengan salinan harga. 6. Cadangkan stok dengan satu UPDATE yang JOIN ke order_items. 7. Tulis satu baris buku besar per baris dengan alasan reserve. 8. Ubah status keranjang menjadi converted. 9. COMMIT. Prosedur tersimpan pada contoh 76 menampilkan kesembilan langkah itu di satu tempat.
Yang salah tanpa transaksi
Tanpa transaksi, crash setelah langkah 4 meninggalkan pesanan tanpa stok yang dicadangkan, sehingga unit yang sama bisa terjual dua kali. Crash setelah langkah 6 tetapi sebelum langkah 7 meninggalkan penghitung yang tidak bisa dijelaskan oleh buku besar. Kegagalan setelah pesanan dibuat tetapi sebelum keranjang ditutup bisa membuat pelanggan membayar dua kali untuk keranjang yang sama. Semua ini adalah gangguan biasa, bukan kasus eksotis.
Urutan kunci dan transaksi yang singkat
Kunci baris selalu dalam urutan yang sama. Jika satu checkout mengunci varian 4 lalu varian 1, dan checkout lain mengunci varian 1 lalu varian 4, keduanya bisa mengalami deadlock, dan MySQL membatalkan salah satunya dengan error 1213. Jaga transaksi tetap singkat: jangan memanggil penyedia pembayaran, mengirim email, atau menunggu pengguna di dalamnya. Pencadangan adalah janji dengan batas waktu, dan hasil pembayaran yang memutuskan apakah ia menjadi penjualan.
Ilustrasi
Contoh 71: Checkout: kunci baris stok
SELECT FOR UPDATE dengan urutan naik.
-- 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 72: Checkout: buat header pesanan
Total dan salinan alamat utama.
-- 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();
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 73: Checkout: salin baris keranjang
Harga pada saat pembelian.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 74: Checkout: hitung ulang total
Total dihitung dari baris, bukan dari keranjang.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 75: Checkout: pesan stok dan tulis buku besar
Aturan CHECK membuat pemesanan stok kurang gagal.
-- 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 76: Buat pesanan dalam prosedur tersimpan
Seluruh checkout, dibatalkan jika ada kesalahan.
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 ;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 12: Transaksi: pembayaran berhasil, gagal, pembatalan, dan pengembalian dana
Setelah pesanan dibuat, hasil pembayaran menentukan apa yang terjadi pada cadangan. Keberhasilan mengubah cadangan menjadi penjualan, kegagalan melepasnya, dan pembatalan atau pengembalian dana mengembalikan barang. Setiap transaksi ini ditulis agar pesan yang diulang dari penyedia pembayaran tidak mengubah apa pun pada percobaan kedua.
Pembayaran berhasil
Handler webhook memulai transaksi dan memperbarui pembayaran dari pending menjadi succeeded. Jika tidak ada baris yang berubah, ini adalah webhook duplikat, dan handler melakukan ROLLBACK lalu membalas 200. Jika tidak, handler menandai pesanan sebagai paid, memindahkan cadangan ke on_hand dengan mengurangi on_hand dan reserved bersamaan, dan menulis baris sale untuk setiap item. Contoh 77 menampilkan urutan lengkapnya.
Pembayaran gagal dan pembatalan
Pembayaran yang gagal menandai pembayaran sebagai failed, membatalkan pesanan dari pending_payment, dan melepas cadangan dengan mengurangi jumlah yang dicadangkan dari reserved. Pelepasan hanya terjadi jika pembaruan pesanan mengubah tepat satu baris, sehingga cadangan tidak pernah dilepas dua kali. Membatalkan pesanan yang sudah dibayar sebelum dikemas menambahkan jumlahnya kembali ke on_hand dan menulis baris cancel, seperti contoh 79.
Pengembalian dana tanpa menahan kunci
Pengembalian dana berjalan dalam dua transaksi pendek di sekitar panggilan penyedia. Transaksi pertama mencatat pengembalian yang tertunda lalu commit. Penyedia dipanggil tanpa kunci apa pun yang ditahan. Transaksi kedua menyimpan referensi penyedia, menandai pengembalian sebagai berhasil, dan menandai pesanan sebagai refunded hanya ketika pengembalian yang berhasil menutupi pembayaran. Contoh 81 mengunci baris pembayaran agar dua permintaan pengembalian tidak bisa sama-sama lolos pemeriksaan jumlah yang bisa dikembalikan.
Ilustrasi
Contoh 77: Webhook pembayaran berhasil
Langkah yang dijaga: webhook yang diulang tidak mengubah apa pun.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 78: Pembayaran gagal melepas stok
Lepaskan pemesanan tepat sekali.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 79: Batalkan pesanan berbayar sebelum dikemas
Stok kembali ke jumlah fisik.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 80: Pengembalian dana di sekitar panggilan penyedia
Tidak ada kunci baris yang ditahan selama panggilan jaringan.
-- 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 81: Batas pengembalian dana dengan kunci baris
Dua permintaan tidak bisa sama-sama lolos pemeriksaan.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 82: Stok ulang barang retur
Hanya setelah diperiksa dan bisa dijual lagi.
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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 13: Konkurensi: dua pembeli dan barang terakhir
Banyak pembeli bisa membaca stok yang sama pada saat yang sama. Jika kode membaca angka, memutuskan, lalu menulis, dua pembeli bisa melihat satu unit dan keduanya membelinya. Topik ini menunjukkan bug tersebut, dua cara memperbaikinya, jenis kunci yang tersedia di MySQL 8, dan deadlock yang muncul ketika kunci diambil dalam urutan berbeda.
Dua pembeli, satu barang
Pembeli A membaca available = 1, dan pembeli B juga membaca available = 1. Keduanya menaikkan reserved sebanyak satu. Tanpa perlindungan, reserved menjadi 2 sementara on_hand tetap 1, dan ini adalah penjualan melebihi stok. Batasan CHECK menghentikan penulisan kedua dengan error 3819, tetapi itu jaring pengaman, bukan perbaikan utuh. Perbaikan sebenarnya ada pada pernyataan yang memeriksa dan menulis dalam satu langkah.
Kunci: FOR UPDATE, NOWAIT, dan SKIP LOCKED
SELECT ... FOR UPDATE membuat transaksi lain menunggu sampai transaksi ini di-commit. NOWAIT langsung gagal dengan error 3572 alih-alih menunggu, cocok untuk layar yang dilihat pengguna. SKIP LOCKED membiarkan pekerja mengambil pekerjaan berikutnya yang tidak dipegang pekerja lain, cocok untuk antrean pengecekan pembayaran. Gunakan SELECT ... FOR SHARE hanya ketika Anda perlu membaca baris dan memastikan baris itu tidak berubah.
Deadlock dan penguncian optimistis
Deadlock terjadi ketika dua transaksi masing-masing memegang kunci yang dibutuhkan yang lain. InnoDB mendeteksinya dan membatalkan salah satu dengan error 1213, sehingga aplikasi harus mengulang seluruh transaksi. Obatnya adalah urutan kunci yang sama di mana-mana. Untuk edit produk yang jarang diperebutkan, penguncian optimistis dengan kolom version menghindari penahanan kunci sama sekali: UPDATE menyertakan versi yang dibaca, dan jika tidak ada baris yang berubah, berarti orang lain sudah menyimpan lebih dulu.
Contoh 83: Reproduksi penjualan melebihi stok
Dua pembeli, satu unit, dan tanpa perlindungan.
-- 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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 84: Unit terakhir: UPDATE atomik
Satu pernyataan bersyarat memberikan barang kepada satu pembeli.
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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 85: NOWAIT dan SKIP LOCKED
Gagal segera, atau biarkan pekerja melewati baris yang terkunci.
-- 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 86: Periksa kunci
performance_schema menunjukkan siapa menahan dan menunggu apa.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 87: Deadlock dan perbaikannya
Kunci baris dengan urutan yang sama di mana pun.
-- 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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 88: Penguncian optimistis dengan kolom versi
Deteksi pembaruan yang tertimpa tanpa menahan kunci.
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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 14: Indeks dan rencana kueri
Indeks adalah salinan terurut dari beberapa kolom yang memungkinkan MySQL menemukan baris tanpa membaca seluruh tabel. Namun setiap indeks juga memperlambat penulisan dan memakai ruang, sehingga setiap indeks butuh alasan. Topik ini menjelaskan cara memilih urutan kolom, cara membaca EXPLAIN dan EXPLAIN ANALYZE, dan cara menemukan indeks yang tidak dipakai siapa pun.
Pilih urutan indeks komposit
Indeks komposit bekerja dari kiri ke kanan. Indeks (customer_id, created_at) melayani kueri yang memfilter customer_id dan mengurutkan berdasarkan created_at. Indeks itu tidak melayani kueri yang hanya memfilter created_at. Letakkan kolom kesetaraan di depan, lalu kolom pengurutan. Gunakan indeks menurun seperti (created_at DESC, order_id DESC) ketika baris terbaru paling sering dibaca.
Baca EXPLAIN
EXPLAIN menampilkan tipe akses, indeks yang dipilih, jumlah baris perkiraan, dan apakah perlu tabel sementara atau filesort. EXPLAIN ANALYZE, yang tersedia sejak MySQL 8.0.18, benar-benar menjalankan kueri dan menampilkan waktu serta jumlah baris aktual untuk setiap langkah. Bandingkan perkiraan dengan angka aktual. Selisih besar sering berarti statistik sudah usang, dan ANALYZE TABLE memperbaikinya.
Temukan indeks yang tidak dipakai dan berlebihan
Tampilan schema sys sys.schema_unused_indexes dan sys.schema_redundant_indexes mendaftarkan kandidat. Indeks (status) berlebihan jika (status, created_at) sudah ada. Penghitung direset saat server di-restart, jadi periksa sepanjang satu siklus bisnis penuh, misalnya proses akhir bulan, sebelum menghapus indeks. Indeks tak terlihat memungkinkan Anda menguji penghapusan tanpa kehilangan indeksnya.
Contoh 89: Uji indeks tak terlihat
Periksa rencana sebelum menghapus indeks.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 90: Indeks penutup untuk harga terendah
Jawab kueri dari indeks saja.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 91: EXPLAIN ANALYZE dan histogram
Ukur pekerjaan nyata dan bantu pengoptimal.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 92: Indeks tidak terpakai dan berlebihan
Skema sys menemukan kandidat untuk dihapus.
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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 15: Tampilan, laporan, dan partisi
Layar dan laporan terus mengajukan pertanyaan yang sama. Tampilan memberi nama pada pertanyaan itu, laporan merangkum penjualan, dan partisi membuat tabel riwayat yang sangat besar tetap bisa dikelola. Topik ini membahas tampilan untuk layar stok dan daftar admin, laporan bulanan dengan total, dan log peristiwa yang dipartisi.
Tampilan menyembunyikan join
Tampilan seperti v_available_stock menggabungkan inventory, product_variants, dan products sekali saja, sehingga setiap layar tidak perlu mengulang join. Tampilan bukan cache: MySQL menjalankan SELECT tampilan setiap kali, dan tampilan yang berisi join banyak tabel tidak bisa diperbarui. Berikan peran pelaporan hak SELECT pada tampilan, bukan pada tabel dasar.
Laporan
Laporan penjualan bulanan menjumlahkan grand_total untuk pesanan yang sudah dibayar atau berstatus setelahnya, dikelompokkan per bulan. GROUP BY ... WITH ROLLUP menambahkan baris total. Laporan membaca tabel langsung, jadi jalankan laporan berat di replika baca atau malam hari, dan jangan biarkan laporan memegang kunci yang memblokir checkout.
Partisi untuk riwayat
PARTITION BY RANGE COLUMNS membagi tabel menurut periode, di sini berdasarkan created_at. Kunci utama harus menyertakan kolom partisi, sehingga menjadi (event_id, created_at). Menghapus partisi lama menghapus satu bulan penuh sekaligus dan menghindari DELETE panjang yang memenuhi undo log. Partisi membantu tabel riwayat besar, tetapi tidak menggantikan indeks yang baik.
Contoh 93: Tampilan stok
Tampilan yang menyembunyikan join dari kode layar.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 94: Tampilan ringkasan pesanan
Satu baris per pesanan untuk layar admin.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 95: Penjualan bulanan dengan baris total
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 96: Log peristiwa berpartisi
Partisi rentang, dan penghapusan bulan lama yang murah.
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;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Topik 16: Praktik profesional: migrasi, audit, cadangan, dan hak akses
Basis data produksi berubah seiring waktu, bisa rusak, dan menyimpan data pribadi. Praktik profesional berarti perubahan diberi versi dan ditinjau, setiap perubahan status dicatat, cadangan diuji dengan pemulihan, dan setiap program hanya memiliki hak yang dibutuhkannya. Topik ini membahas kebiasaan yang membedakan proyek belajar dari toko yang dipercaya orang.
Migrasi berversi
Setiap perubahan skema adalah berkas bernomor yang berjalan sekali, sesuai urutan. Tabel schema_migrations mencatat versi dan checksum berkas, sehingga migrasi yang diedit akan terdeteksi. Jangan mengedit migrasi yang sudah dijalankan di produksi. Tulis migrasi baru yang memperbaikinya.
Jejak audit dan cadangan
Pemicu AFTER UPDATE pada orders menulis setiap perubahan status, dengan pengguna dan waktunya, ke order_audit. Cadangan dengan mysqldump --single-transaction memberi snapshot yang konsisten untuk tabel InnoDB tanpa mengunci tabel. Cadangan yang tidak pernah dipulihkan bukan cadangan. Pulihkan ke basis data uji yang kosong dan bandingkan jumlah baris.
Hak akses minimal
Pengguna aplikasi memiliki SELECT, INSERT, UPDATE, dan DELETE di toko, tetapi tidak memiliki DROP atau ALTER. Laporan memakai peran yang hanya bisa membaca dua tampilan. Simpan akun administrator untuk migrasi, simpan setiap kata sandi di penyimpanan rahasia, dan tinjau hak akses dengan SHOW GRANTS setiap kuartal.
Contoh 97: Migrasi berversi
Setiap perubahan berjalan sekali, berurutan, dengan checksum.
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.
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 98: Pemicu audit status pesanan
Setiap perubahan status dicatat beserta pelakunya.
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 ;
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 99: Cadangkan dan pulihkan
mysqldump dengan --single-transaction, lalu uji pemulihan.
# 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
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Contoh 100: Peran pelaporan dengan tampilan baca saja
Hak minimal untuk laporan.
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';
*Contoh ini tidak dijalankan di sini karena MySQL 8 tidak terpasang di komputer yang menyiapkan artikel ini.*
Lab: bangun basis data toko dan jalankan satu checkout
Satukan artikel ini dalam basis data latihan. Anda akan membuat skema, memuat data contoh, menjalankan satu checkout dengan pembayaran berhasil, dan sengaja mencoba merusak aturan stok. Setiap langkah di bawah adalah pemeriksaan manual yang Anda jalankan sendiri, karena tidak ada contoh yang dijalankan saat artikel ini disiapkan.
1. Buat basis data shop_practice dengan utf8mb4, lalu buat pengguna yang hanya punya SELECT, INSERT, UPDATE, dan DELETE, dan jalankan contoh 1 sampai 6.
2. Buat tabel customers, addresses, categories, products, product_variants, inventory, inventory_movements, carts, dan cart_items dengan contoh 7 sampai 32, lalu muat data awal dari contoh 9, 10, 18, 19, 25, dan 33.
3. Buat tabel orders, order_items, payments, dan refunds dengan contoh 39, 40, 47, dan 48, lalu jalankan kueri riwayat pesanan pada contoh 44 sampai 46.
4. Jalankan checkout pada contoh 76 untuk pelanggan 1 dan keranjang 1, lalu periksa ROW_COUNT dan baris inventory dengan contoh 26 dan 30.
5. Jalankan contoh 77 dua kali dengan provider_ref yang sama, dan pastikan eksekusi kedua tidak mengubah apa pun. Ini adalah uji idempotensi.
6. Jalankan contoh 83 dan 84 di dua sesi MySQL, satu sesi untuk tiap pembeli, dan pastikan hanya satu pembeli yang mendapat unit terakhir.
7. Catat hasil yang Anda dapat untuk setiap contoh, dan bandingkan dengan perilaku yang diharapkan dalam artikel ini sebelum Anda memakai kode apa pun di toko sungguhan.
Langkah berikutnya
Setelah artikel ini, pelajari lapisan aplikasi yang memanggil transaksi-transaksi ini: REST API, validasi input, dan percobaan ulang setelah deadlock. Lalu pelajari dokumentasi webhook penyedia pembayaran Anda, karena di sanalah sebagian besar bug nyata toko muncul. Terakhir, pelajari replikasi MySQL dan replika baca, yang memindahkan laporan dari basis data checkout.
Ringkasan
Basis data toko dibangun di atas beberapa aturan: salin harga dan alamat ke pesanan, simpan stok sebagai penghitung dengan batasan CHECK dan buku besar yang hanya boleh ditambah, dan jadikan setiap pembayaran serta perubahan stok sebagai transaksi yang aman diulang. Kunci baris selalu dalam urutan yang sama, jaga transaksi tetap singkat, dan jangan pernah memanggil penyedia pembayaran saat memegang kunci. Pilih indeks sesuai kueri yang benar-benar dijalankan layar, periksa dengan EXPLAIN, dan beri versi pada setiap perubahan skema. Tidak ada dari 100 contoh yang dijalankan saat artikel ini ditulis, jadi uji setiap contoh di basis data latihan sebelum Anda mengandalkannya.