ออกแบบฐานข้อมูลสำหรับระบบซื้อขายออนไลน์: 100 ตัวอย่างตั้งแต่ศูนย์ถึงระดับมืออาชีพด้วย MySQL 8

ออกแบบฐานข้อมูลระบบซื้อขายออนไลน์: 100 ตัวอย่างด้วย MySQL 8 ตั้งแต่ศูนย์ถึงระดับมืออาชีพ

ร้านค้าออนไลน์ส่วนใหญ่คือปัญหาเรื่องฐานข้อมูล ลูกค้าต้องเข้าสู่ระบบ สินค้ามีไซส์และสี สต็อกต้องไม่ติดลบ การชำระเงินอาจส่งมาซ้ำสองครั้ง และผู้ซื้อสองคนอาจกดซื้อชิ้นสุดท้ายในวินาทีเดียวกัน บทความนี้ออกแบบฐานข้อมูลของร้านค้าแบบนี้ทีละขั้น คุณจะเห็นตารางที่ร้านค้าออนไลน์ต้องมี พร้อมคีย์หลัก คีย์นอก ดัชนี และเหตุผลของแต่ละอย่าง จากนั้นเห็นข้อมูลตัวอย่างของทุกตาราง และเห็นว่าภายในธุรกรรมสำคัญแต่ละอย่างเกิดอะไรขึ้น ได้แก่ การสั่งซื้อ การจองสต็อก การชำระเงินสำเร็จและไม่สำเร็จ การยกเลิก การคืนเงิน และผู้ซื้อสองคนที่แย่งชิ้นสุดท้าย พร้อมบอกว่าจะเกิดอะไรขึ้นหากไม่มีธุรกรรม ภาพจำลองหน้าจอรายการสินค้า หน้ารายละเอียดสินค้า ตะกร้า การชำระเงิน ประวัติคำสั่งซื้อ และคำสั่งซื้อของผู้ดูแล แสดงว่าหน้าจอเหล่านี้อ่านตารางใดบ้าง บทความจบด้วยตัวอย่างที่มีหมายเลข 100 ตัวอย่างในไวยากรณ์ MySQL 8 ครอบคลุม DDL ข้อมูลตัวอย่าง คิวรี ดัชนี ธุรกรรม การล็อก วิว การแบ่งพาร์ทิชัน รายงาน และแนวปฏิบัติระดับมืออาชีพ หมายเหตุที่ควรทราบ: เครื่องที่เตรียมบทความนี้ไม่ได้ติดตั้ง MySQL 8 ดังนั้นตัวอย่างทั้ง 100 รายการยังไม่ได้รันที่นี่ ให้คุณรันเองในฐานข้อมูลฝึกและตรวจผลก่อนนำไปใช้

สิ่งที่ต้องเตรียม

คอมพิวเตอร์ที่ติดตั้ง MySQL 8.0 ขึ้นไป (MySQL Community Server หรือ Docker image ชื่อ mysql:8.0) โปรแกรมไคลเอนต์ เช่น โปรแกรม mysql บนเทอร์มินัล หรือ MySQL Workbench และความรู้พื้นฐาน SQL ได้แก่ SELECT, INSERT, UPDATE และ JOIN ระหว่างเรียนให้ใช้ฐานข้อมูลฝึก ไม่ใช่ฐานข้อมูลร้านค้าจริง และใช้เอนจิน InnoDB ซึ่งเป็นค่าเริ่มต้นของ MySQL 8 เพราะมีเพียง InnoDB ที่รองรับธุรกรรม การล็อกระดับแถว และคีย์นอก

วิธีใช้ตัวอย่าง

ตัวอย่าง SQL เขียนสำหรับ MySQL 8 และ InnoDB รันตามลำดับหมายเลขในฐานข้อมูลฝึก (ชื่อ shop_practice) ตัวอย่างที่มี ? คือพารามิเตอร์ที่แอปต้องส่งค่าเข้ามา ตัวอย่างที่เป็น bash รันในเทอร์มินัล

หัวข้อที่ 1: เริ่มจากคำถามที่ร้านค้าต้องตอบ

ก่อนจะเขียน CREATE TABLE ให้ลิสต์คำถามที่ร้านต้องตอบ เช่น ลูกค้าคนนี้เป็นเจ้าของอะไรบ้าง ตอนนี้ขายได้กี่ชิ้น มีคำสั่งซื้อใดรอแพ็กอยู่ ตารางแต่ละตารางมีไว้ตอบคำถามหนึ่งข้อโดยไม่ขัดแย้งกับข้อที่เหลือ หัวข้อนี้ตั้งค่า MySQL 8 อย่างปลอดภัย และแสดงกฎที่ทุกตารางในบทความนี้ยึดถือ

ตารางและข้อมูลที่เก็บ

ร้านค้าต้องมีลูกค้า ที่อยู่ หมวดหมู่ สินค้า ตัวเลือกสินค้า สต็อก ตะกร้า รายการในตะกร้า คำสั่งซื้อ รายการในคำสั่งซื้อ การชำระเงิน การคืนเงิน การจัดส่ง รายการในการจัดส่ง รีวิว คูปอง และถ้าเป็นตลาดกลางก็ต้องมีผู้ขายและการจ่ายเงินให้ผู้ขาย สินค้าอธิบายสิ่งที่ขาย ส่วนตัวเลือกคือรายการที่ขายได้จริงพร้อม SKU และราคา รายการในคำสั่งซื้อคัดลอกชื่อสินค้าและราคาไว้ตอนซื้อ ดังนั้นการเปลี่ยนราคาภายหลังจะไม่แก้ประวัติ

คีย์ ข้อจำกัด และดัชนี

ทุกตารางมีคีย์หลัก คีย์นอกป้องกันแถวกำพร้า เช่น คำสั่งซื้อที่ไม่มีลูกค้า คีย์ UNIQUE ป้องกันค่าอย่างอีเมลหรือ SKU ไม่ให้ซ้ำ ข้อจำกัด CHECK ปฏิเสธค่าที่เป็นไปไม่ได้ เช่น สต็อกติดลบ ดัชนีช่วยให้คิวรีของหน้าจอเร็วขึ้น เช่น ประวัติคำสั่งซื้อของลูกค้าหนึ่งคน และดัชนีแต่ละตัวในบทความนี้มีเหตุผลเขียนกำกับไว้

ตั้งค่า MySQL 8 อย่างปลอดภัย

สร้างฐานข้อมูลด้วย utf8mb4 เพื่อให้ภาษาไทย ญี่ปุ่น จีน อาหรับ และอีโมจิถูกเก็บได้ถูกต้อง สร้างผู้ใช้แอปที่อ่านและเขียนข้อมูลได้แต่แก้โครงสร้างไม่ได้ และเปิดโหมด strict 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 และ collation เดียวสำหรับทั้งร้าน

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;

ตัวอย่างที่ 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.

ตัวอย่างที่ 4: เปิดโหมด strict 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;

ตัวอย่างที่ 5: ยืนยัน InnoDB

ธุรกรรมและ foreign key ต้องใช้เอนจิน InnoDB

SELECT ENGINE, SUPPORT, TRANSACTIONS, XA
FROM information_schema.ENGINES
WHERE ENGINE = 'InnoDB';

ตัวอย่างที่ 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;

หัวข้อที่ 2: ลูกค้าและที่อยู่

ลูกค้าคือบัญชีที่มีอีเมลไม่ซ้ำและสถานะ ที่อยู่เป็นของลูกค้า แต่คำสั่งซื้อเก็บสำเนาที่อยู่จัดส่งของตัวเอง เพราะลูกค้าอาจแก้ไขหรือลบที่อยู่ในภายหลัง หัวข้อนี้ครอบคลุมตาราง ข้อมูลตัวอย่าง และกฎว่าลูกค้าแต่ละคนมีที่อยู่เริ่มต้นไม่เกินหนึ่งรายการ

หนึ่งแถวต่อหนึ่งบัญชี

ตาราง customers มี customer_id แบบเพิ่มอัตโนมัติเป็นคีย์หลัก และมีคีย์ UNIQUE บน email เมื่อใช้ collation utf8mb4_0900_ai_ci ของ MySQL 8 การเปรียบเทียบอีเมลจะไม่สนใจตัวพิมพ์ใหญ่หรือเล็ก ดังนั้น [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;

ตัวอย่างที่ 8: สร้างตารางที่อยู่

ลูกค้าหนึ่งคนมีหลายที่อยู่ พร้อม 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;

ตัวอย่างที่ 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);

ตัวอย่างที่ 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);

ตัวอย่างที่ 11: หาที่อยู่เริ่มต้น

การค้นหาที่ใช้ composite index

SELECT address_id, recipient_name, line1, city
FROM addresses
WHERE customer_id = 1 AND is_default = 1;

ตัวอย่างที่ 12: ที่อยู่เริ่มต้นได้เพียงหนึ่งรายการต่อลูกค้า

คอลัมน์ที่สร้างอัตโนมัติทำให้กฎ unique เป็นไปได้

-- 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);

ตัวอย่างที่ 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;

ตัวอย่างที่ 14: Foreign key และการระงับบัญชี

แถวกำพร้าถูกปฏิเสธ และการระงับบัญชีคือ 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;

หัวข้อที่ 3: สินค้า หมวดหมู่ และตัวเลือกสินค้า

แคตาล็อกมีสามระดับ หมวดหมู่จัดกลุ่มสินค้า สินค้าคือสิ่งที่ร้านขาย และตัวเลือกคือรายการที่ขายได้จริงพร้อม SKU ไซส์ สี และราคา สต็อกและตะกร้าชี้ไปที่ตัวเลือกเสมอ ไม่ชี้ไปที่สินค้า เพราะเสื้อยืดสีดำไซส์ M กับเสื้อยืดสีขาวไซส์ L เป็นของสองอย่างที่ต้องนับแยกกัน

สินค้าและตัวเลือก

ตาราง products เก็บชื่อ slug คำอธิบาย ราคาพื้นฐาน และสถานะ (draft, active หรือ archived) ตาราง product_variants เก็บ SKU ป้ายตัวเลือกเช่น M / Black ราคา และคอลัมน์ is_active คีย์ UNIQUE บน (product_id, option_label) ป้องกันตัวเลือกที่เหมือนกันสองรายการในสินค้าเดียวกัน สินค้าแบบร่างจะไม่แสดงในรายการจนกว่าสถานะจะเป็น active

หมวดหมู่เป็นโครงสร้างต้นไม้

ตาราง categories อ้างถึงตัวเองผ่าน parent_id ดังนั้น เสื้อผ้า จึงมีเสื้อยืดอยู่ข้างใน คีย์นอกใช้ ON DELETE SET NULL ซึ่งทำให้หมวดหมู่ลูกกลายเป็นหมวดหมู่ระดับบนสุดเมื่อลบหมวดหมู่แม่ ใช้ common table expression แบบเรียกซ้ำเพื่อดึงสินค้าทั้งหมดใต้หมวดหมู่ รวมถึงหมวดหมู่ย่อย

คิวรีรายการสินค้าและการค้นหา

หน้ารายการแสดงราคาต่ำสุดของตัวเลือกที่เปิดใช้งานในแต่ละสินค้า การแบ่งหน้าแบบ keyset ซึ่งใช้ product_id ของแถวสุดท้ายจากหน้าก่อนหน้า ทำงานได้เร็วกับแคตาล็อกขนาดใหญ่ ส่วน OFFSET จะช้าลงทุกหน้า ดัชนี FULLTEXT ที่ใช้ ngram parser รองรับการค้นหาคำในภาษาไทย ญี่ปุ่น และจีน ซึ่งไม่มีการเว้นวรรคระหว่างคำ

ภาพประกอบ

ภาพจำลองหน้ารายการสินค้า: การ์ดแต่ละใบอ่านข้อมูลจาก products และราคาต่ำสุดจาก product_variants

ภาพจำลองหน้ารายละเอียดสินค้า: ตัวเลือกมาจาก product_variants และป้ายสถานะสต็อกมาจากตาราง inventory

ตัวอย่างที่ 15: หมวดหมู่ที่มีหมวดหมู่แม่

foreign key ที่อ้างตารางเดียวกันสร้างโครงสร้างต้นไม้

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;

ตัวอย่างที่ 16: สร้างตารางสินค้า

ชื่อ slug ราคาพื้นฐาน และสถานะ

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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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');

ตัวอย่างที่ 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);

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 22: ค้นหาข้อความแบบเต็ม

FULLTEXT index ที่ใช้ ngram parser

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);

หัวข้อที่ 4: คลังสินค้าและสมุดบัญชีการเคลื่อนไหวของสต็อก

สต็อกคือส่วนที่ความผิดพลาดทำให้เสียเงินโดยตรง การออกแบบนี้เก็บตัวเลขสามค่าต่อหนึ่งตัวเลือก ได้แก่ on_hand จำนวนที่มีอยู่จริงในคลัง reserved จำนวนที่สัญญาไว้กับคำสั่งซื้อที่ยังไม่ชำระหรือยังไม่จัดส่ง และ available ซึ่งเท่ากับ on_hand ลบ reserved ตาราง inventory_movements บันทึกทุกการเปลี่ยนแปลง ดังนั้นตัวนับตรวจย้อนกับประวัติได้เสมอ

ตัวนับพร้อมกฎ CHECK

ตาราง inventory มีหนึ่งแถวต่อหนึ่งตัวเลือก ข้อจำกัด CHECK บังคับให้ on_hand มากกว่าหรือเท่ากับศูนย์ และ reserved อยู่ระหว่างศูนย์กับ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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');

ตัวอย่างที่ 26: จำนวนที่ขายได้

คงเหลือลบด้วยที่จองไว้

SELECT variant_id, on_hand - reserved AS available
FROM inventory
WHERE on_hand - reserved > 0
ORDER BY variant_id;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 29: ไม่ยอมให้สต็อกติดลบ

CHECK constraint หยุดค่าที่เป็นไปไม่ได้

-- Fails with error 3819 (check constraint violated) on MySQL 8.0.16 and later.
UPDATE inventory SET on_hand = -1 WHERE variant_id = 2;

ตัวอย่างที่ 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);

หัวข้อที่ 5: ตะกร้า

ตะกร้าเก็บสิ่งที่ผู้ซื้อตั้งใจจะซื้อ ผู้เยี่ยมชมที่ยังไม่ล็อกอินมีตะกร้าที่ระบุด้วย session token ส่วนลูกค้ามีตะกร้าที่ระบุด้วย customer_id ตะกร้าไม่ใช่คำสั่งซื้อ จึงไม่มีการจองสต็อก และราคาที่แสดงในตะกร้าจะถูกอ่านใหม่ตอนชำระเงิน หัวข้อนี้ครอบคลุมตาราง การเพิ่มสินค้าอย่างปลอดภัย และการรวมตะกร้าของผู้เยี่ยมชมหลังล็อกอิน

ตะกร้าของผู้เยี่ยมชมและลูกค้า

ตาราง carts มี customer_id และ session_token ที่เป็นทางเลือก เมื่อผู้เยี่ยมชมล็อกอิน ระบบจะกำหนด customer_id ให้ตะกร้า และรวมรายการจากตะกร้าของผู้เยี่ยมชมเข้ากับตะกร้าที่เปิดอยู่ของลูกค้า ตาราง cart_items มีคีย์ UNIQUE บน (cart_id, variant_id) ดังนั้นตัวเลือกหนึ่งอยู่ในตะกร้าหนึ่งเพียงแถวเดียว และเมื่อซ้ำก็จะปรับจำนวนแทนการเพิ่มแถว

เพิ่มลงตะกร้าด้วยคำสั่งเดียว

INSERT ... AS new ON DUPLICATE KEY UPDATE เพิ่มจำนวนในคำสั่งอะตอมมิกเดียว การกดปุ่มเดิมสองครั้งจึงไม่สร้างสองแถว และจำนวนถูกจำกัดไม่เกิน 99 ด้วยข้อจำกัด CHECK และด้วยฟังก์ชัน LEAST ในการอัปเดต

ตะกร้าไม่ใช่คำสั่งซื้อ

การเพิ่มสินค้าลงตะกร้าไม่จองสต็อก เพราะตะกร้าจำนวนมากถูกทิ้งทุกวัน การจองเริ่มเมื่อลูกค้าสั่งซื้อเท่านั้น ดังนั้นหน้าตะกร้าต้องตรวจความพร้อมขายอีกครั้ง และแสดงข้อความที่ชัดเจนเมื่อสินค้าหมด

ภาพประกอบ

ภาพจำลองหน้าตะกร้า: รายการมาจาก cart_items โดยราคาและจำนวนที่ขายได้ถูกอ่านตอนแสดงหน้า

ตัวอย่างที่ 31: สร้างตารางตะกร้า

ตะกร้าของผู้เยี่ยมชมใช้ session token ลูกค้าใช้ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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);

ตัวอย่างที่ 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);

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 37: ทำเครื่องหมายตะกร้าที่ไม่ได้ใช้ว่าถูกทิ้ง

งานตามรอบที่ใช้ index ของสถานะ

UPDATE carts
   SET status = 'abandoned'
 WHERE status = 'open'
   AND updated_at < NOW(3) - INTERVAL 7 DAY;

ตัวอย่างที่ 38: ตรวจแผนการค้นหาตะกร้า

EXPLAIN แสดงว่าใช้ index หรือไม่

EXPLAIN SELECT cart_id
FROM carts
WHERE status = 'open'
  AND updated_at < NOW(3) - INTERVAL 7 DAY;

หัวข้อที่ 6: คำสั่งซื้อและรายการในคำสั่งซื้อ

คำสั่งซื้อคือบันทึกการซื้อ มีทั้งยอดรวม สถานะ และสำเนาที่อยู่จัดส่ง รายการแต่ละบรรทัดคัดลอก SKU ชื่อสินค้า และราคาต่อหน่วย เพื่อให้คำสั่งซื้อยังมีความหมายแม้แคตาล็อกจะเปลี่ยนในภายหลัง หัวข้อนี้ยังครอบคลุมกฎว่ายอดรวมต้องตรงกันเสมอ และวิธีแบ่งหน้าประวัติของลูกค้าให้เร็ว

บันทึกสิ่งที่เกิดขึ้น ณ ตอนนั้น

order_items เก็บ sku_snapshot, product_name_snapshot และ unit_price ซึ่งคัดลอกจาก product_variants ตอนสร้างคำสั่งซื้อ คอลัมน์ line_total เป็นคอลัมน์ที่สร้างอัตโนมัติ คือ unit_price คูณ quantity จึงไม่มีทางไม่ตรงกับค่าที่ใช้คำนวณ คีย์นอก variant_id ใช้ ON DELETE RESTRICT ดังนั้นตัวเลือกที่เคยขายจะลบไม่ได้ ให้ปิดใช้งานแทน

ยอดรวมต้องบวกกันถูกต้อง

ข้อจำกัด CHECK บนตาราง orders กำหนดให้ grand_total เท่ากับ subtotal ลบ discount_total บวก shipping_fee โค้ดส่วนใดที่เปลี่ยนตัวเลขเหล่านี้ต้องเปลี่ยนค่าอื่นในธุรกรรมเดียวกัน ไม่เช่นนั้นคำสั่งจะล้มเหลว ก่อนบันทึกคำสั่งซื้อจะคำนวณยอดรวมใหม่จากรายการ เพื่อให้ส่วนหัวตรงกับรายการเสมอ

แบ่งหน้าประวัติคำสั่งซื้อ

ดัชนีผสม (customer_id, created_at) รองรับคิวรีคำสั่งซื้อล่าสุดของลูกค้าหนึ่งคน การแบ่งหน้าแบบ keyset ด้วย (created_at, order_id) ดึงหน้าถัดไปโดยไม่ต้องสแกนแถวก่อนหน้า ใช้ EXPLAIN เพื่อยืนยันว่าใช้ดัชนี และทำให้ลำดับการเรียงตรงกับดัชนี

ภาพประกอบ

ภาพจำลองหน้าประวัติคำสั่งซื้อ: หนึ่งแถวต่อหนึ่งคำสั่งซื้อจากตาราง orders เรียงจากใหม่ไปเก่า พร้อมลิงก์ไปยังรายการใน order_items

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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');

ตัวอย่างที่ 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);

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 46: คิวของผู้ดูแล: คำสั่งซื้อที่ชำระแล้ว

คำสั่งซื้อที่คลังต้องแพ็กต่อไป

SELECT order_id, order_number, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at
LIMIT 50;

หัวข้อที่ 7: การชำระเงินและการคืนเงิน

การชำระเงินคือความพยายามเรียกเก็บเงินจากคำสั่งซื้อหนึ่งครั้ง คำสั่งซื้อเดียวอาจมีหลายความพยายาม เช่น ครั้งแรกบัตรถูกปฏิเสธและครั้งต่อมาสำเร็จ ผู้ให้บริการชำระเงินยังส่ง webhook ที่อาจมาซ้ำหรือมาไม่ตามลำดับ ตาราง payments และ refunds ออกแบบให้การลองใหม่และข้อความซ้ำไม่ทำให้ลูกค้าถูกเก็บเงินสองครั้งหรือบันทึกการขายสองครั้ง

คีย์ idempotency

ความพยายามชำระเงินแต่ละครั้งมี idempotency_key ที่เป็น UNIQUE หากแอปส่งคำขอซ้ำเพราะเครือข่ายหมดเวลา INSERT ครั้งที่สองจะพบแถวเดิม แทนการสร้างการเรียกเก็บเงินใหม่ คอลัมน์ provider_ref ซึ่งไม่ซ้ำภายในผู้ให้บริการแต่ละราย เชื่อมแถวกับรหัสธุรกรรมของผู้ให้บริการ

แถวการชำระเงินและ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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');

ตัวอย่างที่ 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;

ตัวอย่างที่ 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';

หัวข้อที่ 8: การจัดส่งและการดำเนินการ

การดำเนินการคืองานแพ็กและส่งของ คำสั่งซื้อหนึ่งรายการอาจส่งเป็นหลายพัสดุ เช่น เมื่อสินค้าบางชิ้นหมด ตาราง shipments มีหนึ่งแถวต่อหนึ่งพัสดุ และ shipment_items บอกว่ารายการใดอยู่ในพัสดุใดและกี่ชิ้น คอลัมน์สถานะเดินไปทางเดียว ดังนั้นพัสดุที่ส่งถึงแล้วจะย้อนกลับเป็นกำลังเตรียมไม่ได้

พัสดุและการส่งบางส่วน

shipments อ้างถึง orders และ shipment_items อ้างถึง shipments กับ order_items คีย์ UNIQUE บน (shipment_id, order_item_id) กันไม่ให้รายการเดียวกันปรากฏสองครั้งในพัสดุเดียว ตารางสองตารางนี้รองรับการแบ่งคำสั่งซื้อเป็นพัสดุได้ทุกแบบ

สถานะเดินไปทางเดียว

การเปลี่ยนพัสดุจาก preparing เป็น in_transit และจาก in_transit เป็น delivered ใช้ UPDATE ที่ใส่สถานะเดิมไว้ในเงื่อนไข WHERE การอัปเดตที่มาไม่ตามลำดับจะเปลี่ยนศูนย์แถว และแอปจะรายงานความขัดแย้งแทนการเขียนทับสถานะ

ติดตามจำนวน

จำนวนที่ยังต้องส่งของรายการหนึ่งคือจำนวนที่สั่งลบด้วยผลรวมของ shipment_items สำหรับรายการนั้น ประโยค HAVING ค้นหารายการที่ยังส่งไม่ครบ ซึ่งหน้าจอผู้ดูแลใช้แสดงคำสั่งซื้อที่ส่งไปบางส่วน

ภาพประกอบ

ภาพจำลองหน้าคำสั่งซื้อของผู้ดูแล: คำสั่งซื้อที่ชำระแล้วและรอแพ็ก ตาราง shipments จะถูกอัปเดตเมื่อสร้างพัสดุ

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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);

ตัวอย่างที่ 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;

ตัวอย่างที่ 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';

ตัวอย่างที่ 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;

หัวข้อที่ 9: รีวิวและคูปอง

รีวิวสร้างความเชื่อมั่น และคูปองช่วยขายสินค้า แต่ทั้งสองอย่างถูกใช้ในทางที่ผิดได้ รีวิวควรมาจากการซื้อจริงที่จัดส่งถึงแล้ว และคูปองควรใช้ได้เฉพาะในช่วงวันและจำนวนครั้งที่กำหนด ข้อจำกัดในหัวข้อนี้ปกป้องกฎทั้งสองข้อภายในฐานข้อมูล เพื่อให้บั๊กในโค้ดหน้าจอไม่ทำลายกฎเหล่านี้

รีวิวที่ยืนยันการซื้อแล้ว

ตาราง reviews อ้างถึง order_items ไม่ใช่แค่ products คีย์ UNIQUE บน order_item_id อนุญาตให้รีวิวได้หนึ่งครั้งต่อรายการที่ซื้อ คำสั่ง INSERT เลือกรายการเฉพาะเมื่อสถานะคำสั่งซื้อเป็น delivered ดังนั้นจะเขียนรีวิวให้คำสั่งที่ยกเลิกหรือยังไม่ชำระไม่ได้

คูปองที่มีขีดจำกัด

คูปองมีรหัส ส่วนลดแบบเปอร์เซ็นต์หรือจำนวนคงที่ ยอดขั้นต่ำ วันเริ่มและวันสิ้นสุด และจำนวนครั้งสูงสุดที่ใช้ได้ (ถ้ามี) ข้อจำกัด CHECK บังคับให้วันสิ้นสุดอยู่หลังวันเริ่ม ส่วนลดเป็นค่าบวก และ used_count ไม่เกิน max_uses

การใช้คูปองแบบมีเงื่อนไข

ผู้ซื้อสองคนอาจใช้ครั้งสุดท้ายของคูปองพร้อมกัน UPDATE แบบมีเงื่อนไขที่ตรวจวันที่และ used_count ใน WHERE จัดการเรื่องนี้ได้อย่างปลอดภัย แอปจะใช้ส่วนลดเฉพาะเมื่อ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 61: เชื่อมคำสั่งซื้อกับคูปอง

foreign key ที่เพิ่มหลังจากทั้งสองตารางมีแล้ว

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_coupon FOREIGN KEY (coupon_id)
    REFERENCES coupons (coupon_id) ON DELETE SET NULL;

ตัวอย่างที่ 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);

ตัวอย่างที่ 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.

ตัวอย่างที่ 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';

หัวข้อที่ 10: ผู้ขายและตลาดกลาง

ตลาดกลางให้ผู้ขายหลายรายขายในร้านเดียวกันได้ ฐานข้อมูลต้องรู้ว่าสินค้าแต่ละชิ้นเป็นของผู้ขายคนใด รายการแต่ละบรรทัดของคำสั่งซื้อเป็นรายได้ของผู้ขายคนใด และแพลตฟอร์มเก็บค่าคอมมิชชันเท่าไร การออกแบบนี้คัดลอกผู้ขายไปไว้ในแต่ละรายการ เพื่อให้เมื่อสินค้าเปลี่ยนเจ้าของในภายหลัง ประวัติการขายของผู้ขายเดิมไม่เปลี่ยน

ผู้ขายเป็นเจ้าของสินค้า

ตาราง sellers เก็บชื่อ อีเมล สถานะ และเปอร์เซ็นต์ค่าคอมมิชชัน products.seller_id เป็นคีย์นอกที่ยอมให้เป็นค่าว่าง และเพิ่มด้วย ALTER TABLE เพื่อให้ตารางของร้านเดียวจากหัวข้อก่อนหน้ายังใช้ได้ สถานะ pending ทำให้สินค้าของผู้ขายใหม่ถูกซ่อนจนกว่าผู้ดูแลจะอนุมัติ

คัดลอกผู้ขายลงในแต่ละรายการ

order_items.seller_id ถูกคัดลอกตอนสร้างคำสั่งซื้อ ไม่ได้ค้นภายหลังผ่าน products สิ่งนี้ทำให้ประวัติคำสั่งซื้อถูกต้องเมื่อสินค้าเปลี่ยนเจ้าของ และทำให้รายงานค่าคอมมิชชันเป็นการ JOIN แบบง่ายๆ

การจ่ายเงินและค่าคอมมิชชัน

ตาราง payouts เก็บหนึ่งแถวต่อผู้ขายต่อช่วงเวลา พร้อมยอดขายรวม ค่าคอมมิชชัน และยอดสุทธิ ข้อจำกัด CHECK บังคับให้ net เท่ากับ gross ลบ commission และคีย์ UNIQUE บน (seller_id, period_start) กันไม่ให้สร้างการจ่ายเงินซ้ำในช่วงเดียวกัน

ตัวอย่างที่ 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;

ตัวอย่างที่ 66: สินค้าเป็นของผู้ขาย

foreign key ที่ยอมให้เป็นค่าว่าง เพิ่มด้วย 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

หัวข้อที่ 11: ธุรกรรม: การสั่งซื้อและการจองสต็อก

การสั่งซื้อคือธุรกรรมที่สำคัญที่สุดของร้าน มันสร้างคำสั่งซื้อ คัดลอกรายการ จองสต็อก และปิดตะกร้า ทุกขั้นตอนต้องสำเร็จพร้อมกันหรือล้มเหลวพร้อมกัน หัวข้อนี้อธิบายแต่ละขั้นตามลำดับที่ทำงาน แสดง SQL และอธิบายว่าจะเกิดอะไรขึ้นหากขั้นตอนเหล่านี้ไม่อยู่ในธุรกรรมเดียว

ภายในธุรกรรมการชำระเงินทีละขั้น

1. START TRANSACTION 2. ล็อกแถว inventory ของตะกร้าด้วย SELECT ... FOR UPDATE โดยเรียงตาม variant_id จากน้อยไปมาก 3. คำนวณยอดรวมจากราคาปัจจุบัน 4. เพิ่มส่วนหัวคำสั่งซื้อพร้อมคัดลอกที่อยู่เริ่มต้น 5. เพิ่มรายการพร้อมสำเนาราคา 6. จองสต็อกด้วย UPDATE หนึ่งคำสั่งที่ JOIN กับ order_items 7. เขียนแถวสมุดบัญชีหนึ่งแถวต่อรายการ โดยมีเหตุผล reserve 8. ตั้งสถานะตะกร้าเป็น converted 9. COMMIT ขั้นตอนเก็บไว้ในตัวอย่างที่ 76 แสดงทั้งเก้าขั้นในที่เดียว

หากไม่มีธุรกรรมจะเกิดอะไรขึ้น

หากไม่มีธุรกรรม ระบบขัดข้องหลังขั้นที่ 4 จะทำให้มีคำสั่งซื้อโดยไม่มีสต็อกที่จองไว้ ทำให้ชิ้นเดียวกันขายได้สองครั้ง ระบบขัดข้องหลังขั้นที่ 6 แต่ก่อนขั้นที่ 7 จะทำให้ตัวนับไม่ตรงกับสมุดบัญชี และหากล้มเหลวหลังสร้างคำสั่งซื้อแต่ก่อนปิดตะกร้า ลูกค้าอาจชำระเงินซ้ำสำหรับตะกร้าเดียวกัน ทั้งหมดนี้เป็นความขัดข้องธรรมดา ไม่ใช่กรณีพิเศษ

ลำดับการล็อกและธุรกรรมที่สั้น

ล็อกแถวในลำดับเดียวกันทุกที่ หากการสั่งซื้อหนึ่งล็อกตัวเลือก 4 แล้วจึงล็อกตัวเลือก 1 และอีกรายการล็อกตัวเลือก 1 แล้วจึงล็อก 4 สองธุรกรรมอาจติดดีดล็อกกัน และ MySQL จะย้อนหนึ่งรายการด้วยรหัสข้อผิดพลาด 1213 ให้ธุรกรรมสั้น ไม่เรียกผู้ให้บริการชำระเงิน ไม่ส่งอีเมล และไม่รอผู้ใช้ภายในธุรกรรม การจองเป็นคำมั่นที่มีเวลาจำกัด และผลการชำระเงินเป็นตัวตัดสินว่าจะกลายเป็นการขายหรือไม่

ภาพประกอบ

ภาพจำลองหน้าการชำระเงิน: ยอดรวมคำนวณจากตะกร้า และเมื่อกดสั่งซื้อจะเริ่มธุรกรรมในหัวข้อที่ 11

ตัวอย่างที่ 71: ชำระเงิน: ล็อกแถวสต็อก

SELECT FOR UPDATE เรียงตาม variant_id จากน้อยไปมาก

-- 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;

ตัวอย่างที่ 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();

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 76: สั่งซื้อผ่าน stored procedure

การชำระเงินทั้งหมด ย้อนกลับเมื่อเกิดข้อผิดพลาด

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 ;

หัวข้อที่ 12: ธุรกรรม: การชำระเงินสำเร็จ ล้มเหลว การยกเลิก และการคืนเงิน

หลังจากสั่งซื้อ ผลการชำระเงินจะตัดสินว่าการจองจะเกิดอะไรต่อ ความสำเร็จทำให้การจองกลายเป็นการขาย ความล้มเหลวปล่อยการจอง การยกเลิกหรือคืนเงินนำสินค้ากลับเข้าคลัง ธุรกรรมแต่ละอย่างถูกเขียนให้ข้อความที่ส่งซ้ำจากผู้ให้บริการไม่เปลี่ยนแปลงอะไรในครั้งที่สอง

การชำระเงินสำเร็จ

ตัวจัดการ webhook เริ่มธุรกรรมและอัปเดตการชำระเงินจาก pending เป็น succeeded หากไม่มีแถวเปลี่ยนเลย แสดงว่าเป็น webhook ซ้ำ ตัวจัดการจะ ROLLBACK และตอบ 200 มิฉะนั้นจะทำเครื่องหมายคำสั่งซื้อว่า paid ย้ายการจองเข้าสู่ on_hand โดยลบออกจากทั้ง 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 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.

หัวข้อที่ 13: การทำงานพร้อมกัน: ผู้ซื้อสองคนกับชิ้นสุดท้าย

ผู้ซื้อหลายคนอาจอ่านสต็อกเดียวกันในเวลาเดียวกัน หากโค้ดอ่านตัวเลขแล้วตัดสินใจแล้วจึงเขียน ผู้ซื้อสองคนอาจเห็นสินค้าชิ้นเดียวและซื้อทั้งคู่ หัวข้อนี้แสดงบั๊ก วิธีแก้สองแบบ ชนิดของการล็อกที่ MySQL 8 มี และดีดล็อกที่เกิดขึ้นเมื่อการล็อกถูกเรียงลำดับต่างกัน

ผู้ซื้อสองคน สินค้าหนึ่งชิ้น

ผู้ซื้อ A อ่านค่า available = 1 และผู้ซื้อ B อ่านค่า available = 1 ทั้งคู่จึงเพิ่ม reserved ทีละหนึ่ง หากไม่มีการป้องกัน reserved จะกลายเป็น 2 ขณะที่ on_hand เป็น 1 ซึ่งคือการขายเกินจำนวน ข้อจำกัด CHECK หยุดการเขียนครั้งที่สองด้วยรหัสข้อผิดพลาด 3819 แต่นั่นเป็นตาข่ายนิรภัย ไม่ใช่วิธีแก้ทั้งหมด การแก้ที่แท้จริงอยู่ที่คำสั่งที่ตรวจและเขียนในขั้นตอนเดียว

ล็อก: FOR UPDATE, NOWAIT และ SKIP LOCKED

SELECT ... FOR UPDATE ทำให้ธุรกรรมอื่นรอจนกว่าธุรกรรมนี้จะ COMMIT NOWAIT ล้มเหลวทันทีด้วยรหัสข้อผิดพลาด 3572 แทนการรอ เหมาะกับหน้าจอที่ผู้ใช้เห็น SKIP LOCKED ให้ worker หยิบงานถัดไปที่ไม่มี worker อื่นถืออยู่ เหมาะกับคิวการตรวจสอบการชำระเงิน ใช้ 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.

ตัวอย่างที่ 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.

ตัวอย่างที่ 85: NOWAIT และ SKIP LOCKED

ล้มเหลวทันที หรือให้ worker ข้ามแถวที่ถูกล็อก

-- 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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 87: deadlock และวิธีแก้

ล็อกแถวตามลำดับเดียวกันทุกที่

-- 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;

ตัวอย่างที่ 88: optimistic locking ด้วยคอลัมน์ version

ตรวจพบการเขียนทับโดยไม่ต้องถือล็อก

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.

หัวข้อที่ 14: ดัชนีและแผนการค้นหา

ดัชนีคือสำเนาที่เรียงลำดับของบางคอลัมน์ ทำให้ MySQL หาแถวได้โดยไม่ต้องอ่านทั้งตาราง แต่ดัชนีทุกตัวทำให้การเขียนช้าลงและกินพื้นที่ ดัชนีแต่ละตัวจึงต้องมีเหตุผล หัวข้อนี้อธิบายวิธีเลือกลำดับคอลัมน์ วิธีอ่าน EXPLAIN และ EXPLAIN ANALYZE และวิธีหาดัชนีที่ไม่มีใครใช้

เลือกลำดับของดัชนีผสม

ดัชนีผสมทำงานจากซ้ายไปขวา ดัชนี (customer_id, created_at) รองรับคิวรีที่กรองด้วย customer_id และเรียงด้วย created_at แต่ไม่รองรับคิวรีที่กรองด้วย created_at อย่างเดียว ให้วางคอลัมน์ที่ใช้เท่ากับไว้ก่อน แล้วตามด้วยคอลัมน์ที่ใช้เรียง ใช้ดัชนีแบบเรียงลงเช่น (created_at DESC, order_id DESC) เมื่อแถวใหม่ล่าสุดถูกอ่านบ่อยที่สุด

อ่าน EXPLAIN

EXPLAIN แสดงประเภทการเข้าถึง ดัชนีที่เลือก จำนวนแถวที่คาดการณ์ และว่าต้องใช้ตารางชั่วคราวหรือการเรียงแบบ filesort หรือไม่ EXPLAIN ANALYZE ซึ่งใช้ได้ตั้งแต่ MySQL 8.0.18 รันคิวรีจริงและแสดงเวลาและจำนวนแถวจริงของแต่ละขั้น เปรียบเทียบค่าที่คาดการณ์กับค่าจริง หากต่างกันมาก สถิติมักล้าสมัย และ ANALYZE TABLE แก้ได้

หาดัชนีที่ไม่ได้ใช้และซ้ำซ้อน

วิว sys.schema_unused_indexes และ sys.schema_redundant_indexes ในสคีมา sys แสดงตัวเลือกที่ควรตรวจ ดัชนี (status) ซ้ำซ้อนเมื่อมี (status, created_at) อยู่แล้ว ตัวนับการใช้งานจะรีเซ็ตเมื่อเซิร์ฟเวอร์รีสตาร์ท จึงควรตรวจข้ามรอบธุรกิจเต็ม เช่น งานสิ้นเดือน ก่อนลบดัชนี ดัชนีที่มองไม่เห็น (invisible) ช่วยทดสอบการลบโดยไม่สูญเสียดัชนี

ตัวอย่างที่ 89: ทดสอบ invisible index

ตรวจแผนก่อนลบ index

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;

ตัวอย่างที่ 90: covering index สำหรับราคาต่ำสุด

ตอบคำถามจาก index อย่างเดียว

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;

ตัวอย่างที่ 91: EXPLAIN ANALYZE และ histogram

วัดงานจริงและช่วยตัวปรับแต่งแผนการทำงาน

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;

ตัวอย่างที่ 92: index ที่ไม่ได้ใช้และซ้ำซ้อน

มุมมองใน sys schema ช่วยหาตัวเลือกที่จะลบ

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.

หัวข้อที่ 15: วิว รายงาน และการแบ่งพาร์ทิชัน

หน้าจอและรายงานถามคำถามเดิมซ้ำแล้วซ้ำอีก วิวช่วยตั้งชื่อให้คำถามเหล่านั้น รายงานสรุปยอดขาย และการแบ่งพาร์ทิชันทำให้ตารางประวัติขนาดใหญ่จัดการได้ หัวข้อนี้ครอบคลุมวิวสำหรับหน้าสต็อกและรายการผู้ดูแล รายงานรายเดือนพร้อมยอดรวม และบันทึกเหตุการณ์ที่แบ่งพาร์ทิชัน

วิวซ่อนการ JOIN

วิวอย่าง v_available_stock รวมตาราง inventory, product_variants และ products ครั้งเดียว ดังนั้นแต่ละหน้าจอไม่ต้องเขียน JOIN ซ้ำ วิวไม่ใช่แคช MySQL รัน SELECT ของวิวทุกครั้ง และวิวที่มีการ JOIN หลายตารางแก้ไขผ่านวิวไม่ได้ ให้สิทธิ์ SELECT กับบทบาทรายงานบนวิว ไม่ใช่บนตารางพื้นฐาน

รายงาน

รายงานยอดขายรายเดือนรวม grand_total ของคำสั่งซื้อที่ชำระแล้วหรือหลังจากนั้น จัดกลุ่มตามเดือน GROUP BY ... WITH ROLLUP เพิ่มแถวรวม รายงานอ่านตารางสดโดยตรง จึงควรรันรายงานหนักบนเรพลิกาอ่านอย่างเดียวหรือตอนกลางคืน และไม่ปล่อยให้รายงานถือล็อกที่ขวางการชำระเงิน

พาร์ทิชันสำหรับประวัติ

PARTITION BY RANGE COLUMNS แบ่งตารางเป็นช่วงเวลา ในที่นี้ใช้ created_at คีย์หลักต้องมีคอลัมน์ของพาร์ทิชัน จึงเป็น (event_id, created_at) การลบพาร์ทิชันเก่าทิ้งทั้งเดือนในครั้งเดียว และหลีกเลี่ยงคำสั่ง DELETE ยาวๆ ที่ทำให้ undo log โต พาร์ทิชันช่วยกับตารางประวัติขนาดใหญ่ แต่ไม่ได้แทนที่ดัชนีที่ดี

ตัวอย่างที่ 93: view ของสต็อก

view ที่ซ่อนการ join จากโค้ดหน้าจอ

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;

ตัวอย่างที่ 94: view สรุปคำสั่งซื้อ

หนึ่งแถวต่อคำสั่งซื้อสำหรับหน้าจอผู้ดูแล

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;

ตัวอย่างที่ 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;

ตัวอย่างที่ 96: บันทึกเหตุการณ์แบบแบ่งพาร์ทิชัน

range partition และการลบเดือนเก่าที่ประหยัดต้นทุน

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;

หัวข้อที่ 16: แนวปฏิบัติระดับมืออาชีพ: การย้ายเวอร์ชัน การตรวจสอบ การสำรอง และสิทธิ์

ฐานข้อมูลที่ใช้งานจริงมีการเปลี่ยนแปลงตามเวลา อาจเสียหายได้ และเก็บข้อมูลส่วนบุคคล แนวปฏิบัติระดับมืออาชีพหมายความว่าการเปลี่ยนแปลงมีเวอร์ชันและผ่านการตรวจทาน ทุกการเปลี่ยนสถานะถูกบันทึก การสำรองข้อมูลถูกทดสอบด้วยการกู้คืน และแต่ละโปรแกรมมีสิทธิ์เท่าที่จำเป็นเท่านั้น หัวข้อนี้ครอบคลุมนิสัยที่แยกโปรเจกต์ฝึกออกจากร้านที่ผู้คนไว้ใจ

การย้ายเวอร์ชัน

การเปลี่ยนแปลงสคีมาแต่ละครั้งเป็นไฟล์ที่มีหมายเลข รันหนึ่งครั้งตามลำดับ ตาราง schema_migrations บันทึกเวอร์ชันและ checksum ของไฟล์ ดังนั้นการแก้ไขไฟล์ที่รันแล้วจะถูกตรวจพบ อย่าแก้ไฟล์ย้ายที่รันในโปรดักชันแล้ว ให้เขียนไฟล์ใหม่ที่แก้ไขแทน

ร่องรอยการตรวจสอบและการสำรองข้อมูล

ทริกเกอร์ AFTER UPDATE บนตาราง orders บันทึกทุกการเปลี่ยนสถานะพร้อมผู้ใช้และเวลาลงใน order_audit การสำรองด้วย mysqldump --single-transaction ให้ภาพรวมที่สอดคล้องของตาราง InnoDB โดยไม่ต้องล็อกตาราง การสำรองที่ไม่เคยกู้คืนไม่ใช่การสำรองที่ใช้ได้ ให้กู้คืนลงฐานข้อมูลทดสอบที่ว่างเปล่าและเทียบจำนวนแถว

สิทธิ์น้อยที่สุด

ผู้ใช้แอปมี SELECT, INSERT, UPDATE และ DELETE บนร้าน แต่ไม่มี DROP หรือ ALTER รายงานใช้บทบาทที่อ่านได้เฉพาะสองวิว เก็บบัญชีผู้ดูแลไว้สำหรับการย้ายเวอร์ชัน เก็บรหัสผ่านทุกตัวในที่เก็บความลับ และตรวจสิทธิ์ด้วย SHOW GRANTS ทุกไตรมาส

ตัวอย่างที่ 97: migration แบบมีเวอร์ชัน

แต่ละการเปลี่ยนแปลงทำงานหนึ่งครั้ง เรียงตามลำดับ พร้อม 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.

ตัวอย่างที่ 98: trigger การตรวจสอบสถานะคำสั่งซื้อ

บันทึกทุกการเปลี่ยนสถานะพร้อมผู้ทำ

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 ;

ตัวอย่างที่ 99: สำรองและกู้คืนข้อมูล

mysqldump ที่มี --single-transaction แล้วทดสอบการกู้คืน

# 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

ตัวอย่างที่ 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';

แลบ: สร้างฐานข้อมูลร้านค้าและรันการสั่งซื้อหนึ่งครั้ง

นำบทความมารวมกันในฐานข้อมูลฝึก คุณจะสร้างสคีมา โหลดข้อมูลตัวอย่าง รันการสั่งซื้อหนึ่งครั้งพร้อมการชำระเงินที่สำเร็จ และลองทำลายกฎสต็อกโดยตั้งใจ ทุกขั้นด้านล่างเป็นการตรวจสอบด้วยตัวคุณเอง เพราะตัวอย่างทั้งหมดยังไม่ได้รันระหว่างการเตรียมบทความ

1. สร้างฐานข้อมูล shop_practice ด้วย utf8mb4 จากนั้นสร้างผู้ใช้ที่มีสิทธิ์ 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. รันการสั่งซื้อในตัวอย่างที่ 76 สำหรับลูกค้า 1 และตะกร้า 1 จากนั้นตรวจค่า ROW_COUNT และแถวของ inventory ด้วยตัวอย่างที่ 26 และ 30

5. รันตัวอย่างที่ 77 สองครั้งด้วย provider_ref เดียวกัน และยืนยันว่าการรันครั้งที่สองไม่เปลี่ยนแปลงอะไร นี่คือการทดสอบ idempotency

6. รันตัวอย่างที่ 83 และ 84 ในสองเซสชัน MySQL หนึ่งเซสชันต่อผู้ซื้อหนึ่งคน และยืนยันว่ามีผู้ซื้อเพียงคนเดียวได้ชิ้นสุดท้าย

7. จดผลที่คุณได้จากการรันแต่ละตัวอย่าง แล้วเทียบกับพฤติกรรมที่คาดไว้ในบทความ ก่อนนำโค้ดใดไปใช้กับร้านจริง

ขั้นต่อไป

หลังจากบทความนี้ ควรศึกษาชั้นแอปพลิเคชันที่เรียกธุรกรรมเหล่านี้ ได้แก่ REST API การตรวจสอบอินพุต และการลองใหม่หลังเกิดดีดล็อก จากนั้นอ่านเอกสาร webhook ของผู้ให้บริการชำระเงิน เพราะตัวจัดการ webhook คือจุดที่บั๊กจริงของร้านค้ามักปรากฏ สุดท้ายดู replication ของ MySQL และเรพลิกาอ่านอย่างเดียว ซึ่งช่วยย้ายรายงานออกจากฐานข้อมูลที่ใช้ชำระเงิน

สรุป

ฐานข้อมูลร้านค้าสร้างจากกฎไม่กี่ข้อ คัดลอกราคาและที่อยู่ลงในคำสั่งซื้อ เก็บสต็อกเป็นตัวนับที่มีกฎ CHECK และสมุดบัญชีที่เพิ่มได้อย่างเดียว และทำให้ทุกการชำระเงินและการเปลี่ยนแปลงสต็อกเป็นธุรกรรมที่ทำซ้ำได้อย่างปลอดภัย ล็อกแถวตามลำดับเดียวกัน ทำให้ธุรกรรมสั้น และไม่เรียกผู้ให้บริการชำระเงินขณะถือล็อก เลือกดัชนีตามคิวรีที่หน้าจอใช้จริง ตรวจด้วย EXPLAIN และทำเวอร์ชันให้ทุกการเปลี่ยนสคีมา ยังไม่มีการรันตัวอย่างทั้ง 100 รายการระหว่างเขียนบทความ ดังนั้นให้ทดสอบแต่ละรายการในฐานข้อมูลฝึกก่อนนำไปใช้