تصميم قاعدة بيانات نظام البيع والشراء عبر الإنترنت: 100 مثال من الصفر إلى الاحتراف باستخدام MySQL 8
المتجر الإلكتروني في معظمه مشكلة في قاعدة البيانات. يسجّل العملاء الدخول، وللمنتجات مقاسات وألوان، ويجب ألا ينزل المخزون تحت الصفر، وقد تصل دفعة الدفع مرتين، وقد يضغط مشتريان على زر الشراء للقطعة الأخيرة في الثانية نفسها. تصمّم هذه المقالة قاعدة بيانات لمتجر من هذا النوع خطوة بخطوة. سترى الجداول التي يحتاجها المتجر الإلكتروني، مع المفاتيح الأساسية والمفاتيح الأجنبية والفهارس وسبب وجود كل منها. ثم سترى بيانات تجريبية لكل جدول، وما يحدث داخل المعاملات المهمة عند تشغيل النظام: إنشاء الطلب، وحجز المخزون، ونجاح الدفع وفشله، والإلغاء، والاسترداد، وتنافس مشترين على القطعة الأخيرة، مع بيان ما يحدث عند غياب المعاملة. تبيّن النماذج التوضيحية لشاشات قائمة المنتجات وصفحة المنتج والسلة والدفع وسجل الطلبات وطلبات المشرف كيف تقرأ الشاشات هذه الجداول. تنتهي المقالة بـ 100 مثال مرقّم بصياغة MySQL 8، تشمل لغة تعريف البيانات، والبيانات التجريبية، والاستعلامات، والفهارس، والمعاملات، والأقفال، والعروض، والتقسيم إلى أجزاء، والتقارير، والممارسات المهنية. ملاحظة صريحة: لم يكن MySQL 8 مثبتا على الحاسوب الذي أُعدّت به هذه المقالة، لذلك لم تُنفَّذ الأمثلة المئة هنا. شغّلها بنفسك في قاعدة بيانات تدريبية وتحقق من النتائج قبل الاعتماد عليها.
ما الذي تحتاجه
حاسوب عليه MySQL 8.0 أو أحدث (MySQL Community Server، أو صورة Docker باسم 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 حتى تُحفظ العربية والتايلاندية واليابانية والصينية والرموز التعبيرية بشكل صحيح. أنشئ مستخدم تطبيق يقرأ البيانات ويكتبها دون أن يغيّر المخطط، وفعّل وضع SQL الصارم حتى تُرفض القيم الخاطئة بدلا من تعديلها بصمت. احفظ كلمة المرور في مخزن أسرار، ولا تضعها أبدا في المقالة أو في الشيفرة المصدرية.
رسوم توضيحية
مثال 1: التحقق من إصدار الخادم والإعدادات الافتراضية
تأكيد MySQL 8 وutf8mb4 ومستوى العزل.
SELECT VERSION() AS server_version,
@@character_set_server AS charset,
@@collation_server AS collation,
@@transaction_isolation AS isolation_level;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 2: إنشاء قاعدة بيانات المتجر
utf8mb4 وترتيب واحد لكامل المتجر.
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 3: إنشاء مستخدم تطبيق بأقل الصلاحيات
يقرأ التطبيق البيانات ويكتبها، لكنه لا يغيّر المخطط.
CREATE USER IF NOT EXISTS 'shop_app'@'localhost' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
-- No DROP, ALTER or GRANT: the application cannot change the schema.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 4: تفعيل وضع SQL الصارم
رفض البيانات الخاطئة بدلا من تعديلها بصمت.
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ONLY_FULL_GROUP_BY';
SELECT @@SESSION.sql_mode AS sql_mode;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 5: التأكد من محرك InnoDB
تحتاج المعاملات والمفاتيح الأجنبية إلى InnoDB.
SELECT ENGINE, SUPPORT, TRANSACTIONS, XA
FROM information_schema.ENGINES
WHERE ENGINE = 'InnoDB';
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 6: إعادة تعيين قاعدة بيانات للتدريب
التدرب دون المساس ببيانات المتجر.
-- Practice only: this removes the practice copy and nothing else.
DROP DATABASE IF EXISTS shop_practice;
CREATE DATABASE shop_practice CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 2: العملاء والعناوين
العميل حساب له بريد إلكتروني فريد وحالة. تتبع العناوين العميل، لكن الطلب يحتفظ بنسخته الخاصة من عنوان الشحن، لأن العميل قد يعدّل العنوان أو يحذفه لاحقا. يغطي هذا الموضوع الجداول والبيانات التجريبية والقاعدة التي تقضي بأن يكون لكل عميل عنوان افتراضي واحد على الأكثر.
صف واحد لكل حساب
يملك جدول customers مفتاحا أساسيا هو customer_id بترقيم تلقائي، ومفتاح UNIQUE على email. مع ترتيب 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;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 8: إنشاء جدول العناوين
عدة عناوين لكل عميل مع مفتاح أجنبي.
CREATE TABLE addresses (
address_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
label VARCHAR(40) NOT NULL DEFAULT 'Home',
recipient_name VARCHAR(120) NOT NULL,
line1 VARCHAR(200) NOT NULL,
line2 VARCHAR(200) NULL,
city VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
country_code CHAR(2) NOT NULL,
is_default TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (address_id),
KEY idx_addresses_customer (customer_id),
CONSTRAINT fk_addresses_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 9: بيانات عملاء تجريبية
حسابات نموذجية للاختبار.
INSERT INTO customers (email, password_hash, full_name, phone) VALUES
('[email protected]', '<bcrypt-hash-1>', 'Mali Sun', '0812345678'),
('[email protected]', '<bcrypt-hash-2>', 'Niran Chai', '0898765432'),
('[email protected]', '<bcrypt-hash-3>', 'Ploy Wong', NULL);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 10: بيانات عناوين تجريبية
عنوان افتراضي واحد لكل عميل.
INSERT INTO addresses
(customer_id, label, recipient_name, line1, city, postal_code, country_code, is_default) VALUES
(1, 'Home', 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', 1),
(1, 'Office', 'Mali Sun', '99 Rama IX Road', 'Bangkok', '10310', 'TH', 0),
(2, 'Home', 'Niran Chai', '45 Moo 2', 'Chiang Mai', '50000', 'TH', 1);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 11: البحث عن العنوان الافتراضي
استعلام يستخدم الفهرس المركب.
SELECT address_id, recipient_name, line1, city
FROM addresses
WHERE customer_id = 1 AND is_default = 1;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 12: عنوان افتراضي واحد لكل عميل
عمود مولَّد يتيح قاعدة الفرادة.
-- A generated column is NULL unless is_default = 1, and UNIQUE allows many NULLs.
ALTER TABLE addresses
ADD COLUMN default_owner BIGINT UNSIGNED
GENERATED ALWAYS AS (CASE WHEN is_default = 1 THEN customer_id END) STORED,
ADD UNIQUE KEY uq_one_default_address (default_owner);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 13: عدّ العناوين لكل عميل
يُبقي LEFT JOIN العملاء الذين لا عنوان لهم.
SELECT c.customer_id, c.full_name, COUNT(a.address_id) AS address_count
FROM customers AS c
LEFT JOIN addresses AS a ON a.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY address_count DESC, c.customer_id;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 14: المفاتيح الأجنبية والإيقاف
يُرفض الصف اليتيم، والإيقاف تحديث.
-- Fails with error 1452 (foreign key constraint) because customer 999 does not exist.
INSERT INTO addresses (customer_id, recipient_name, line1, city, postal_code, country_code)
VALUES (999, 'Nobody', 'Unknown', 'Nowhere', '00000', 'TH');
UPDATE customers SET status = 'blocked' WHERE customer_id = 3;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 3: المنتجات والفئات والمتغيّرات
يتكون الكتالوج من ثلاثة مستويات. تجمع الفئة المنتجات، والمنتج هو ما يبيعه المتجر، والمتغيّر هو العنصر الدقيق الذي له رمز SKU ومقاس ولون وسعر. يشير المخزون والسلة دائما إلى المتغيّر لا إلى المنتج، لأن قميصا أسود مقاس M وقميصا أبيض مقاس L شيئان مختلفان يجب عدّهما منفصلين.
المنتجات والمتغيّرات
يحفظ جدول products الاسم والمعرّف النصي والوصف والسعر الأساسي والحالة (draft أو active أو archived). ويحفظ جدول product_variants رمز SKU وعنوان الخيار مثل M / Black والسعر وعلامة is_active. يمنع مفتاح UNIQUE على (product_id, option_label) وجود متغيّرين متطابقين لمنتج واحد. يبقى المنتج المسودة مخفيا من القائمة حتى تتغير حالته إلى active.
الفئات كشجرة
يشير جدول categories إلى نفسه عبر parent_id، فيمكن أن تحتوي فئة الملابس على قمصان. يستخدم المفتاح الأجنبي ON DELETE SET NULL، فإذا حُذفت الفئة الأم تصبح الفئة الفرعية فئة من المستوى الأعلى. لعرض كل المنتجات تحت فئة ما، بما فيها فئاتها الفرعية، استخدم تعبيرا جدوليا مشتركا متكررا.
استعلامات القائمة والبحث
تعرض القائمة أقل سعر لمتغيّر نشط لكل منتج. يبقى الترقيم بالمفاتيح keyset، الذي يستخدم product_id لآخر صف في الصفحة السابقة، سريعا في الكتالوجات الكبيرة، بينما يصبح OFFSET أبطأ مع كل صفحة. يدعم فهرس FULLTEXT مع محلل ngram البحث عن كلمات في التايلاندية واليابانية والصينية، حيث لا توجد مسافات بين الكلمات.
رسوم توضيحية
مثال 15: فئات لها فئة أم
مفتاح أجنبي يشير إلى الجدول نفسه يبني شجرة.
CREATE TABLE categories (
category_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
parent_id INT UNSIGNED NULL,
name VARCHAR(100) NOT NULL,
slug VARCHAR(120) NOT NULL,
PRIMARY KEY (category_id),
UNIQUE KEY uq_categories_slug (slug),
KEY idx_categories_parent (parent_id),
CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id)
REFERENCES categories (category_id) ON DELETE SET NULL
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 16: إنشاء جدول المنتجات
الاسم والمعرّف النصي والسعر الأساسي والحالة.
CREATE TABLE products (
product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id INT UNSIGNED NOT NULL,
name VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL,
description TEXT NULL,
base_price DECIMAL(10,2) NOT NULL,
status ENUM('draft','active','archived') NOT NULL DEFAULT 'draft',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (product_id),
UNIQUE KEY uq_products_slug (slug),
KEY idx_products_category_status (category_id, status),
CONSTRAINT fk_products_category FOREIGN KEY (category_id)
REFERENCES categories (category_id) ON DELETE RESTRICT,
CONSTRAINT chk_products_price CHECK (base_price >= 0)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 17: إنشاء جدول المتغيّرات
الوحدة القابلة للبيع: رمز SKU والخيار والسعر.
CREATE TABLE product_variants (
variant_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
sku VARCHAR(40) NOT NULL,
option_label VARCHAR(80) NOT NULL,
price DECIMAL(10,2) NOT NULL,
weight_grams INT UNSIGNED NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (variant_id),
UNIQUE KEY uq_variants_sku (sku),
UNIQUE KEY uq_variants_option (product_id, option_label),
CONSTRAINT fk_variants_product FOREIGN KEY (product_id)
REFERENCES products (product_id) ON DELETE RESTRICT,
CONSTRAINT chk_variants_price CHECK (price >= 0)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 18: بيانات الفئات والمنتجات التجريبية
بيانات كتالوج نموذجية.
INSERT INTO categories (category_id, parent_id, name, slug) VALUES
(1, NULL, 'Clothing', 'clothing'),
(2, 1, 'T-shirts', 't-shirts'),
(3, NULL, 'Accessories', 'accessories');
INSERT INTO products (product_id, category_id, name, slug, description, base_price, status) VALUES
(1, 2, 'Cotton Crew T-Shirt', 'cotton-crew-tshirt', 'Soft combed cotton, regular fit.', 390.00, 'active'),
(2, 3, 'Canvas Tote Bag', 'canvas-tote-bag', 'Heavy canvas with an inner pocket.', 590.00, 'active'),
(3, 2, 'Linen Henley Shirt', 'linen-henley', 'Summer linen, long sleeve.', 890.00, 'draft');
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 19: بيانات المتغيّرات التجريبية
خمسة متغيّرات، أحدها غير مفعّل.
INSERT INTO product_variants
(variant_id, product_id, sku, option_label, price, weight_grams, is_active) VALUES
(1, 1, 'TS-CREW-M-BLK', 'M / Black', 390.00, 180, 1),
(2, 1, 'TS-CREW-L-BLK', 'L / Black', 390.00, 190, 1),
(3, 1, 'TS-CREW-M-WHT', 'M / White', 390.00, 180, 1),
(4, 2, 'TOTE-NAT', 'Natural', 590.00, 420, 1),
(5, 3, 'LIN-HEN-M-BLU', 'M / Blue', 890.00, 200, 0);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 20: قائمة المنتجات بالترقيم keyset
استعلامات سريعة للصفحة التالية دون OFFSET.
SELECT p.product_id, p.name, MIN(v.price) AS from_price
FROM products AS p
JOIN product_variants AS v
ON v.product_id = p.product_id AND v.is_active = 1
WHERE p.status = 'active'
AND p.category_id = 2
AND p.product_id > ? -- last product_id from the previous page
GROUP BY p.product_id, p.name
ORDER BY p.product_id
LIMIT 20;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 21: متغيّرات صفحة المنتج
المتغيّرات المفعّلة لمنتج واحد فقط.
SELECT v.variant_id, v.sku, v.option_label, v.price, v.weight_grams
FROM product_variants AS v
WHERE v.product_id = 1
AND v.is_active = 1
ORDER BY v.option_label;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 22: البحث النصي الكامل
فهرس FULLTEXT مع محلل 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);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 4: المخزون وسجل حركات المخزون
المخزون هو الجزء الذي يكلّف فيه الخطأ مالا مباشرة. يحتفظ هذا التصميم بثلاثة أرقام لكل متغيّر: on_hand وهي الوحدات الموجودة فعلا في المستودع، وreserved وهي الوحدات الموعودة لطلبات لم تُدفع أو لم تُشحن بعد، وavailable وهي on_hand مطروحا منه reserved. يسجّل جدول الحركات كل تغيير، فيمكن دائما مطابقة العدادات بالتاريخ.
عدادات مع قواعد 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;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 24: إنشاء دفتر حركات المخزون
كل تغيير في المخزون يترك صفا.
CREATE TABLE inventory_movements (
movement_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
variant_id BIGINT UNSIGNED NOT NULL,
delta_on_hand INT NOT NULL DEFAULT 0,
delta_reserved INT NOT NULL DEFAULT 0,
reason ENUM('adjustment','restock','reserve','release','sale','cancel','return') NOT NULL,
order_id BIGINT UNSIGNED NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (movement_id),
KEY idx_movements_variant_time (variant_id, created_at),
KEY idx_movements_order (order_id),
CONSTRAINT fk_movements_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 25: بيانات المخزون والرصيد الافتتاحي
كميات المخزون مع صفوف الدفتر المطابقة.
INSERT INTO inventory (variant_id, on_hand, reserved) VALUES
(1, 20, 0),
(2, 8, 0),
(3, 0, 0), -- sold out
(4, 50, 0);
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason) VALUES
(1, 20, 0, 'adjustment'),
(2, 8, 0, 'adjustment'),
(4, 50, 0, 'adjustment');
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 26: الكمية القابلة للبيع
المتوفر مطروحا منه المحجوز.
SELECT variant_id, on_hand - reserved AS available
FROM inventory
WHERE on_hand - reserved > 0
ORDER BY variant_id;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 27: تقرير المخزون المنخفض
المتغيّرات التي تحتاج إلى إعادة التوريد.
SELECT v.sku, i.on_hand - i.reserved AS available
FROM inventory AS i
JOIN product_variants AS v ON v.variant_id = i.variant_id
WHERE i.on_hand - i.reserved BETWEEN 0 AND 5
ORDER BY available, v.sku;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 28: إعادة التوريد مع صف في الدفتر
معاملة واحدة للمخزون وسجله.
START TRANSACTION;
UPDATE inventory
SET on_hand = on_hand + 30
WHERE variant_id = 3;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason)
VALUES (3, 30, 0, 'restock');
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 29: رفض المخزون السالب
قيد CHECK يمنع القيم المستحيلة.
-- Fails with error 3819 (check constraint violated) on MySQL 8.0.16 and later.
UPDATE inventory SET on_hand = -1 WHERE variant_id = 2;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 30: مطابقة المخزون مع الدفتر
العثور على عدادات لا تتفق مع السجل.
SELECT i.variant_id, i.on_hand, COALESCE(SUM(m.delta_on_hand), 0) AS ledger_on_hand
FROM inventory AS i
LEFT JOIN inventory_movements AS m ON m.variant_id = i.variant_id
GROUP BY i.variant_id, i.on_hand
HAVING i.on_hand <> COALESCE(SUM(m.delta_on_hand), 0);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 5: السلال
تحتفظ السلة بالعناصر التي ينوي المشتري شراءها. للزائر غير المسجّل سلة يحددها رمز جلسة، وللعميل سلة يحددها customer_id. السلة ليست طلبا: لا يُحجز فيها مخزون، ويُعاد قراءة السعر المعروض في السلة عند الدفع. يغطي هذا الموضوع جداول السلة، وإضافة المنتجات بأمان، ودمج سلة الزائر بعد تسجيل الدخول.
سلة الزائر وسلة العميل
يحتوي جدول carts على customer_id ورمز session_token اختياريين. عندما يسجّل الزائر دخوله تُعيَّن customer_id للسلة، وتُدمج بنود سلة الزائر في السلة المفتوحة للعميل. يملك جدول cart_items مفتاح UNIQUE على (cart_id, variant_id)، فيظهر المتغيّر مرة واحدة في كل سلة، وتُحدَّث الكمية بدلا من تكرار الصف.
الإضافة إلى السلة بعبارة واحدة
تضيف الصيغة INSERT ... AS new ON DUPLICATE KEY UPDATE الكمية في عبارة ذرّية واحدة. ضغطتان على الزر نفسه لا تنشئان صفين، وتُحدّ الكمية بـ 99 عبر قيد CHECK ودالة LEAST في التحديث.
السلة ليست طلبا
إضافة منتج إلى السلة لا تحجز مخزونا، لأن آلاف السلال تُترك يوميا. يبدأ الحجز فقط عندما يؤكد العميل الطلب. لذلك يجب أن تعيد صفحة السلة التحقق من التوفر، وأن تعرض رسالة واضحة عندما تنفد الكمية.
رسوم توضيحية
مثال 31: إنشاء جدول السلات
السلات المجهولة تستخدم رمز جلسة، والعملاء يستخدمون المعرّف.
CREATE TABLE carts (
cart_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NULL,
session_token CHAR(36) NULL,
status ENUM('open','converted','abandoned') NOT NULL DEFAULT 'open',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (cart_id),
UNIQUE KEY uq_carts_session (session_token),
KEY idx_carts_customer_status (customer_id, status),
KEY idx_carts_status_updated (status, updated_at),
CONSTRAINT fk_carts_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 32: إنشاء جدول بنود السلة
صف واحد لكل متغيّر في كل سلة.
CREATE TABLE cart_items (
cart_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
cart_id BIGINT UNSIGNED NOT NULL,
variant_id BIGINT UNSIGNED NOT NULL,
quantity INT NOT NULL,
added_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (cart_item_id),
UNIQUE KEY uq_cart_variant (cart_id, variant_id),
KEY idx_cart_items_variant (variant_id),
CONSTRAINT fk_cart_items_cart FOREIGN KEY (cart_id)
REFERENCES carts (cart_id) ON DELETE CASCADE,
CONSTRAINT fk_cart_items_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_cart_quantity CHECK (quantity BETWEEN 1 AND 99)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 33: بيانات السلات التجريبية
سلة لعميل وسلة لزائر.
INSERT INTO carts (cart_id, customer_id, session_token, status) VALUES
(1, 1, NULL, 'open'),
(2, NULL, 'b0a1c2d3-0000-4000-8000-000000000002', 'open');
INSERT INTO cart_items (cart_id, variant_id, quantity) VALUES
(1, 1, 1),
(1, 4, 2),
(2, 2, 1);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 34: إضافة إلى السلة
إدراج الكمية أو زيادتها في عبارة واحدة.
INSERT INTO cart_items (cart_id, variant_id, quantity)
VALUES (1, 1, 2) AS new
ON DUPLICATE KEY UPDATE
quantity = LEAST(cart_items.quantity + new.quantity, 99);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 35: دمج سلة الزائر بعد تسجيل الدخول
نقل البنود في معاملة واحدة.
START TRANSACTION;
INSERT INTO cart_items (cart_id, variant_id, quantity)
SELECT 1, variant_id, quantity
FROM cart_items
WHERE cart_id = 2
ON DUPLICATE KEY UPDATE quantity = LEAST(quantity + VALUES(quantity), 99);
UPDATE carts SET status = 'abandoned' WHERE cart_id = 2;
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 36: السلة بالأسعار والمخزون الحاليين
تُقرأ الأسعار والتوفر عند عرض السلة.
SELECT ci.variant_id, v.sku, p.name, v.price, ci.quantity,
v.price * ci.quantity AS line_total,
COALESCE(i.on_hand - i.reserved, 0) AS available
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
LEFT JOIN inventory AS i ON i.variant_id = ci.variant_id
WHERE ci.cart_id = 1;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 37: وضع السلات الخاملة كمتروكة
مهمة دورية تستخدم فهرس الحالة.
UPDATE carts
SET status = 'abandoned'
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 38: فحص خطة استعلام السلة
يوضح EXPLAIN هل يُستخدم الفهرس.
EXPLAIN SELECT cart_id
FROM carts
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 6: الطلبات وبنود الطلبات
الطلب سجل لعملية شراء. يخزّن الإجماليات والحالة ونسخة من عنوان الشحن. وينسخ كل بند في الطلب رمز SKU واسم المنتج وسعر الوحدة، حتى يبقى للطلب معناه حتى لو تغيّر الكتالوج لاحقا. يغطي هذا الموضوع أيضا قاعدة أن الإجماليات يجب أن تتطابق دائما، وكيفية تصفح سجل العميل بسرعة.
احفظ الطلب كما كان لحظة إنشائه
يحفظ جدول order_items القيم 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 للتأكد من استخدام الفهرس، واجعل ترتيب الفرز مطابقا له.
رسوم توضيحية
مثال 39: إنشاء جدول الطلبات
رأس عملية الشراء مع الإجماليات ولقطة العنوان.
CREATE TABLE orders (
order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_number VARCHAR(20) NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
status ENUM('pending_payment','paid','packed','shipped','delivered','cancelled','refunded')
NOT NULL DEFAULT 'pending_payment',
currency CHAR(3) NOT NULL DEFAULT 'THB',
subtotal DECIMAL(12,2) NOT NULL,
discount_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
shipping_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00,
grand_total DECIMAL(12,2) NOT NULL,
coupon_id INT UNSIGNED NULL,
ship_recipient VARCHAR(120) NOT NULL,
ship_line1 VARCHAR(200) NOT NULL,
ship_city VARCHAR(100) NOT NULL,
ship_postal_code VARCHAR(20) NOT NULL,
ship_country_code CHAR(2) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (order_id),
UNIQUE KEY uq_orders_number (order_number),
KEY idx_orders_customer_created (customer_id, created_at),
KEY idx_orders_status_created (status, created_at),
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE RESTRICT,
CONSTRAINT chk_orders_total CHECK (grand_total = subtotal - discount_total + shipping_fee)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 40: إنشاء جدول بنود الطلب
أسعار منسوخة وقت الشراء مع إجمالي بند يُحسب تلقائيا.
CREATE TABLE order_items (
order_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
variant_id BIGINT UNSIGNED NOT NULL,
sku_snapshot VARCHAR(40) NOT NULL,
product_name_snapshot VARCHAR(200) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
quantity INT UNSIGNED NOT NULL,
line_total DECIMAL(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED,
PRIMARY KEY (order_item_id),
UNIQUE KEY uq_order_variant (order_id, variant_id),
KEY idx_order_items_variant (variant_id),
CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT,
CONSTRAINT fk_order_items_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_order_items_qty CHECK (quantity > 0)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 41: نسخ السلة إلى طلب
حفظ السعر عند الدفع.
-- @order_id is the id of the order created just before this statement.
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT @order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = 1;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 42: بيانات ثلاثة طلبات تجريبية
طلبات في ثلاث حالات مختلفة.
INSERT INTO orders
(order_id, order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code, created_at) VALUES
(1, 'SH-20261001-0001', 1, 'delivered', 780.00, 0.00, 50.00, 830.00, 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', '2026-10-01 10:12:00'),
(2, 'SH-20261020-0002', 2, 'paid', 590.00, 0.00, 50.00, 640.00, 'Niran Chai', '45 Moo 2', 'Chiang Mai', '50000', 'TH', '2026-10-20 18:40:00'),
(3, 'SH-20261029-0003', 1, 'pending_payment', 390.00, 39.00, 50.00, 401.00, 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', '2026-10-29 09:05:00');
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 43: بيانات بنود الطلبات التجريبية
البنود الخاصة بالطلبات التجريبية الثلاثة.
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity) VALUES
(1, 1, 'TS-CREW-M-BLK', 'Cotton Crew T-Shirt', 390.00, 2),
(2, 4, 'TOTE-NAT', 'Canvas Tote Bag', 590.00, 1),
(3, 2, 'TS-CREW-L-BLK', 'Cotton Crew T-Shirt', 390.00, 1);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 44: سجل الطلبات بالترقيم keyset
أحدث الطلبات أولا لعميل واحد.
SELECT order_id, order_number, status, grand_total, created_at
FROM orders
WHERE customer_id = 1
AND (created_at, order_id) < (?, ?) -- last row of the previous page
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 45: تفاصيل الطلب
الرأس والبنود في استعلام واحد.
SELECT o.order_number, o.status, oi.sku_snapshot, oi.product_name_snapshot,
oi.unit_price, oi.quantity, oi.line_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.order_id = 1
ORDER BY oi.order_item_id;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 46: قائمة المشرف: الطلبات المدفوعة
ما ينبغي أن يحزمه المستودع لاحقا.
SELECT order_id, order_number, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at
LIMIT 50;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 7: المدفوعات والاسترداد
الدفعة محاولة لتحصيل مبلغ عن طلب. قد يكون للطلب الواحد عدة محاولات، فقد تُرفض البطاقة في المحاولة الأولى وتنجح في الثانية. ترسل مزوّدات الدفع أيضا رسائل webhook قد تصل مرتين أو بترتيب مختلف. صُممت جداول payments وrefunds بحيث لا تؤدي المحاولات المعادة أو الرسائل المكررة إلى تحصيل المبلغ من العميل مرتين أو تسجيل البيع مرتين.
مفاتيح عدم التكرار
لكل محاولة دفع مفتاح idempotency_key فريد. إذا أعاد التطبيق الطلب بسبب انتهاء مهلة الشبكة، يجد الإدراج الثاني الصف الموجود بدلا من إنشاء تحصيل جديد. يربط العمود 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;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 48: إنشاء جدول المبالغ المستردة
كل استرداد يشير إلى دفعة وله حالته الخاصة.
CREATE TABLE refunds (
refund_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payment_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
reason VARCHAR(200) NULL,
provider_ref VARCHAR(100) NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (refund_id),
KEY idx_refunds_payment (payment_id),
CONSTRAINT fk_refunds_payment FOREIGN KEY (payment_id)
REFERENCES payments (payment_id) ON DELETE RESTRICT,
CONSTRAINT chk_refunds_amount CHECK (amount > 0)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 49: تسجيل محاولة دفع بأمان
إعادة المحاولة بالمفتاح نفسه تعيد الصف نفسه.
INSERT INTO payments
(order_id, provider, provider_ref, idempotency_key, amount, status)
VALUES
(3, 'sample-gateway', NULL, 'order-3-attempt-1', 401.00, 'pending')
ON DUPLICATE KEY UPDATE updated_at = updated_at;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 50: بيانات مدفوعات ناجحة تجريبية
مدفوعات الطلبين 1 و2.
-- No explicit payment_id: example 49 may already have used the first auto-increment value.
INSERT INTO payments
(order_id, provider, provider_ref, idempotency_key, amount, status) VALUES
(1, 'sample-gateway', 'test-ch-0001', 'order-1-attempt-1', 830.00, 'succeeded'),
(2, 'sample-gateway', 'test-ch-0002', 'order-2-attempt-1', 640.00, 'succeeded');
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 51: المبلغ القابل للاسترداد
المبلغ المدفوع مطروحا منه المبالغ المستردة التي لم تفشل.
SELECT p.payment_id,
p.amount - COALESCE(SUM(CASE WHEN r.status <> 'failed' THEN r.amount END), 0) AS refundable
FROM payments AS p
LEFT JOIN refunds AS r ON r.payment_id = p.payment_id
WHERE p.payment_id = ?
GROUP BY p.payment_id, p.amount;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 52: عدم تطابق الدفع مع الطلب
العثور على مدفوعات نجحت بينما لا يزال الطلب معلقا.
SELECT p.payment_id, p.order_id, o.status
FROM payments AS p
JOIN orders AS o ON o.order_id = p.order_id
WHERE p.status = 'succeeded'
AND o.status = 'pending_payment';
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 8: الشحن والتنفيذ
التنفيذ هو عمل تغليف البضاعة وإرسالها. قد يُشحن الطلب الواحد في عدة طرود، مثلا عندما يكون أحد المنتجات غير متوفر. يحتوي جدول shipments على صف واحد لكل طرد، ويبين جدول shipment_items أي بنود الطلب في كل طرد وبأي كمية. يتحرك عمود الحالة في اتجاه واحد فقط، فلا يمكن للطرد الذي تم تسليمه أن يعود إلى حالة التحضير.
الطرود والشحن الجزئي
يشير جدول shipments إلى orders، ويشير جدول shipment_items إلى shipments وإلى order_items. يمنع مفتاح UNIQUE على (shipment_id, order_item_id) ظهور البند نفسه مرتين في الطرد الواحد. يسمح هذان الجدولان معا بتقسيم أي طلب إلى طرود بأي شكل.
الحالة تتحرك في اتجاه واحد
لتغيير الطرد من preparing إلى in_transit، ومن in_transit إلى delivered، يُستخدم UPDATE يتضمن الحالة القديمة في شرط WHERE. التحديث الذي يصل بترتيب خاطئ يغيّر صفرا من الصفوف، ويبلّغ التطبيق عن التعارض بدلا من الكتابة فوق الحالة.
تتبّع الكميات
الكمية المتبقية للشحن لبند ما هي الكمية المطلوبة مطروحا منها مجموع صفوف shipment_items لذلك البند. تجد عبارة HAVING البنود غير المكتملة، ويستخدمها شاشة المشرف لعرض الطلبات المشحونة جزئيا.
رسوم توضيحية
مثال 53: إنشاء جدول الشحنات
طرد لطلب ما، مع شركة الشحن ورقم التتبع.
CREATE TABLE shipments (
shipment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
carrier VARCHAR(60) NOT NULL,
tracking_number VARCHAR(60) NULL,
status ENUM('preparing','in_transit','delivered','returned') NOT NULL DEFAULT 'preparing',
shipped_at DATETIME(3) NULL,
delivered_at DATETIME(3) NULL,
PRIMARY KEY (shipment_id),
UNIQUE KEY uq_shipments_tracking (tracking_number),
KEY idx_shipments_order (order_id),
CONSTRAINT fk_shipments_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 54: إنشاء جدول بنود الشحنة
أي بند طلب في أي طرد، لدعم الشحن الجزئي.
CREATE TABLE shipment_items (
shipment_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
shipment_id BIGINT UNSIGNED NOT NULL,
order_item_id BIGINT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL,
PRIMARY KEY (shipment_item_id),
UNIQUE KEY uq_shipment_line (shipment_id, order_item_id),
KEY idx_shipment_items_line (order_item_id),
CONSTRAINT fk_shipment_items_shipment FOREIGN KEY (shipment_id)
REFERENCES shipments (shipment_id) ON DELETE CASCADE,
CONSTRAINT fk_shipment_items_line FOREIGN KEY (order_item_id)
REFERENCES order_items (order_item_id) ON DELETE RESTRICT,
CONSTRAINT chk_shipment_items_qty CHECK (quantity > 0)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 55: بيانات شحنة تم تسليمها
بيانات شحنة نموذجية للطلب 1.
INSERT INTO shipments
(shipment_id, order_id, carrier, tracking_number, status, shipped_at, delivered_at) VALUES
(1, 1, 'Sample Courier', 'SC0000000001', 'delivered', '2026-10-02 09:00:00', '2026-10-04 15:20:00');
INSERT INTO shipment_items (shipment_id, order_item_id, quantity) VALUES (1, 1, 2);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 56: تغليف الطلب وإنشاء شحنة
معاملة واحدة لحالة الطلب والطرد.
START TRANSACTION;
UPDATE orders SET status = 'packed' WHERE order_id = 2 AND status = 'paid';
-- ROW_COUNT() = 0 means the order was not in the paid state: ROLLBACK.
INSERT INTO shipments (order_id, carrier, status) VALUES (2, 'Sample Courier', 'preparing');
SET @shipment_id = LAST_INSERT_ID();
INSERT INTO shipment_items (shipment_id, order_item_id, quantity)
SELECT @shipment_id, order_item_id, quantity
FROM order_items
WHERE order_id = 2;
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 57: التسليم إلى شركة الشحن وتسجيل الوصول
تتقدم حالة الشحنة في اتجاه واحد فقط.
UPDATE shipments
SET status = 'in_transit', tracking_number = 'SC0000000002', shipped_at = NOW(3)
WHERE shipment_id = 2 AND status = 'preparing';
UPDATE shipments
SET status = 'delivered', delivered_at = NOW(3)
WHERE shipment_id = 2 AND status = 'in_transit';
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 58: الكمية المتبقية للشحن
الكمية المطلوبة مطروحا منها المشحونة لكل بند.
SELECT oi.order_item_id, oi.quantity AS ordered,
COALESCE(SUM(si.quantity), 0) AS shipped
FROM order_items AS oi
LEFT JOIN shipment_items AS si ON si.order_item_id = oi.order_item_id
WHERE oi.order_id = 2
GROUP BY oi.order_item_id, oi.quantity
HAVING shipped < ordered;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 9: المراجعات والكوبونات
تبني المراجعات الثقة، وتحفّز الكوبونات المبيعات، لكن كلاهما قابل لإساءة الاستخدام. يجب أن تأتي المراجعة من شراء حقيقي تم تسليمه، ويجب أن يُستخدم الكوبون ضمن تاريخه وحد استخدامه. تحمي القيود في هذا الموضوع القاعدتين داخل قاعدة البيانات، حتى لا يكسرهما خطأ في شيفرة الشاشة.
مراجعات مؤكدة الشراء
يشير جدول reviews إلى order_items وليس إلى المنتجات وحدها. يسمح مفتاح UNIQUE على order_item_id بمراجعة واحدة لكل بند مشترى. تختار عبارة الإدراج البند فقط عندما تكون حالة الطلب 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;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 60: إنشاء جدول القسائم
التواريخ وحد الاستخدام وفحوص السلامة.
CREATE TABLE coupons (
coupon_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
code VARCHAR(40) NOT NULL,
discount_type ENUM('percent','fixed') NOT NULL,
discount_value DECIMAL(10,2) NOT NULL,
min_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
starts_at DATETIME(3) NOT NULL,
ends_at DATETIME(3) NOT NULL,
max_uses INT UNSIGNED NULL,
used_count INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (coupon_id),
UNIQUE KEY uq_coupons_code (code),
CONSTRAINT chk_coupons_value CHECK (discount_value > 0),
CONSTRAINT chk_coupons_dates CHECK (ends_at > starts_at),
CONSTRAINT chk_coupons_uses CHECK (max_uses IS NULL OR used_count <= max_uses)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 61: ربط الطلبات بالقسائم
مفتاح أجنبي يُضاف بعد وجود الجدولين.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_coupon FOREIGN KEY (coupon_id)
REFERENCES coupons (coupon_id) ON DELETE SET NULL;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 62: بيانات قسائم تجريبية
قسيمة بنسبة مئوية وقسيمة بقيمة ثابتة.
INSERT INTO coupons
(coupon_id, code, discount_type, discount_value, min_subtotal, starts_at, ends_at, max_uses, used_count) VALUES
(1, 'WELCOME10', 'percent', 10.00, 300.00, '2026-01-01 00:00:00', '2027-01-01 00:00:00', NULL, 0),
(2, 'SHIP50', 'fixed', 50.00, 0.00, '2026-10-01 00:00:00', '2026-12-31 23:59:59', 100, 0);
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 63: استخدام القسيمة بأمان
تحديث مشروط يمنع تجاوز حد الاستخدام.
UPDATE coupons
SET used_count = used_count + 1
WHERE coupon_id = ?
AND starts_at <= NOW(3)
AND ends_at > NOW(3)
AND (max_uses IS NULL OR used_count < max_uses);
-- The application applies the discount only when ROW_COUNT() = 1.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 64: مراجعة من شراء موثّق
لا يمكن مراجعة إلا بند طلب تم تسليمه.
INSERT INTO reviews (product_id, customer_id, order_item_id, rating, body)
SELECT v.product_id, o.customer_id, oi.order_item_id, 5, 'Fits well and the cotton feels soft.'
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN product_variants AS v ON v.variant_id = oi.variant_id
WHERE oi.order_item_id = 1
AND o.status = 'delivered';
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 10: البائعون والسوق المفتوح
يتيح السوق المفتوح لعدة بائعين البيع في متجر واحد. يجب أن تعرف قاعدة البيانات أي بائع يملك كل منتج، وأي بائع حقق إيراد كل بند طلب، وكم عمولة تحتفظ بها المنصة. يقوم التصميم بنسخ البائع إلى كل بند طلب، حتى لا يغيّر انتقال المنتج إلى بائع آخر لاحقا سجل مبيعات البائع الأول.
البائعون يملكون المنتجات
يحفظ جدول sellers الاسم والبريد والحالة ونسبة العمولة. وproducts.seller_id مفتاح أجنبي يقبل القيمة الفارغة ويُضاف بـ ALTER TABLE، حتى تبقى جداول المتجر الواحد من المواضيع السابقة صالحة. تبقي الحالة 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;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 66: المنتجات تنتمي إلى البائعين
مفتاح أجنبي يقبل القيمة الفارغة ويُضاف بـ 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;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 67: نسخ البائع إلى بنود الطلب
يبقى السجل صحيحا عند تغيّر مالك المنتج.
ALTER TABLE order_items
ADD COLUMN seller_id INT UNSIGNED NULL,
ADD KEY idx_order_items_seller (seller_id),
ADD CONSTRAINT fk_order_items_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT;
UPDATE order_items AS oi
JOIN product_variants AS v ON v.variant_id = oi.variant_id
JOIN products AS p ON p.product_id = v.product_id
SET oi.seller_id = p.seller_id
WHERE oi.seller_id IS NULL;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 68: إنشاء جدول المدفوعات للبائعين
دفعة واحدة لكل بائع في كل فترة، مع فحص.
CREATE TABLE payouts (
payout_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
seller_id INT UNSIGNED NOT NULL,
period_start DATE NOT NULL,
period_end DATE NOT NULL,
gross_amount DECIMAL(12,2) NOT NULL,
commission_amount DECIMAL(12,2) NOT NULL,
net_amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','paid') NOT NULL DEFAULT 'pending',
PRIMARY KEY (payout_id),
UNIQUE KEY uq_payouts_period (seller_id, period_start),
CONSTRAINT fk_payouts_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT,
CONSTRAINT chk_payouts_net CHECK (net_amount = gross_amount - commission_amount)
) ENGINE=InnoDB;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 69: بيانات البائعين وتعيين المنتجات
بائعان ومنتجاتهما.
INSERT INTO sellers (seller_id, name, email, status, commission_percent) VALUES
(1, 'Lanna Threads', '[email protected]', 'active', 12.00),
(2, 'Sample Bags Co', '[email protected]', 'active', 10.00);
UPDATE products SET seller_id = 1 WHERE product_id IN (1, 3);
UPDATE products SET seller_id = 2 WHERE product_id = 2;
UPDATE order_items SET seller_id = 1 WHERE variant_id IN (1, 2, 3);
UPDATE order_items SET seller_id = 2 WHERE variant_id = 4;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 70: تقرير العمولة لشهر واحد
إجمالي المبيعات والعمولة لكل بائع.
SELECT s.name,
COUNT(DISTINCT oi.order_id) AS orders,
SUM(oi.line_total) AS gross,
ROUND(SUM(oi.line_total) * s.commission_percent / 100, 2) AS commission
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN sellers AS s ON s.seller_id = oi.seller_id
WHERE o.status IN ('paid','packed','shipped','delivered')
AND o.created_at >= '2026-10-01'
AND o.created_at < '2026-11-01'
GROUP BY s.seller_id, s.name, s.commission_percent
ORDER BY gross DESC;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 11: المعاملات: إنشاء الطلب وحجز المخزون
إنشاء الطلب هو أهم معاملة في المتجر. ينشئ الطلب، وينسخ البنود، ويحجز المخزون، ويغلق السلة. يجب أن تنجح هذه الخطوات معا أو تفشل معا. يشرح هذا الموضوع كل خطوة بالترتيب الذي تُنفَّذ به، ويعرض SQL، ويبيّن ما يحدث عند عدم تغليف الخطوات في معاملة واحدة.
داخل معاملة الدفع خطوة بخطوة
1. START TRANSACTION. 2. اقفل صفوف المخزون للسلة باستخدام SELECT ... FOR UPDATE مرتبة تصاعديا حسب variant_id. 3. احسب المجموع الفرعي من الأسعار الحالية. 4. أدرج رأس الطلب مع نسخ العنوان الافتراضي. 5. أدرج بنود الطلب مع نسخة الأسعار. 6. احجز المخزون بعبارة UPDATE واحدة تربط order_items. 7. اكتب صف حركة واحدا لكل بند بسبب reserve. 8. اجعل حالة السلة converted. 9. COMMIT. يعرض الإجراء المخزّن في المثال 76 الخطوات التسع في مكان واحد.
ما الذي يحدث بلا معاملة
بدون معاملة، إذا تعطل النظام بعد الخطوة 4 فسيبقى طلب دون مخزون محجوز، فيمكن بيع الوحدة نفسها مرتين. وإذا تعطل بعد الخطوة 6 وقبل الخطوة 7 فسيبقى عداد لا يفسّره السجل. وإذا فشل بعد إنشاء الطلب وقبل إغلاق السلة، فقد يدفع العميل مرتين عن السلة نفسها. كل هذه أعطال عادية، وليست حالات نادرة.
ترتيب الأقفال والمعاملات القصيرة
اقفل الصفوف بالترتيب نفسه في كل مكان. إذا قفل دفع واحد المتغيّر 4 ثم المتغيّر 1، وقفل دفع آخر المتغيّر 1 ثم المتغيّر 4، فقد يحدث بينهما جمود deadlock، ويتراجع MySQL عن أحدهما برمز الخطأ 1213. أبقِ المعاملة قصيرة: لا تستدعِ مزوّد الدفع داخلها، ولا ترسل بريدا، ولا تنتظر المستخدم. الحجز وعد له مدة محددة، ونتيجة الدفع هي التي تقرر إن كان سيصبح بيعا.
رسوم توضيحية
مثال 71: الدفع: قفل صفوف المخزون
SELECT FOR UPDATE بترتيب تصاعدي.
-- Cart 1 holds three of variant 1 and two of variant 4 (after example 34).
START TRANSACTION;
SELECT variant_id, on_hand, reserved
FROM inventory
WHERE variant_id IN (1, 4)
ORDER BY variant_id
FOR UPDATE;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 72: الدفع: إنشاء رأس الطلب
الإجماليات ونسخة من العنوان الافتراضي.
-- Subtotal: 3 x 390.00 + 2 x 590.00 = 2350.00. Shipping 50.00. No discount.
INSERT INTO orders
(order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code)
SELECT 'SH-20261030-0004', 1, 'pending_payment', 2350.00, 0.00, 50.00, 2400.00,
a.recipient_name, a.line1, a.city, a.postal_code, a.country_code
FROM addresses AS a
WHERE a.customer_id = 1 AND a.is_default = 1;
SET @order_id = LAST_INSERT_ID();
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 73: الدفع: نسخ بنود السلة
الأسعار في لحظة الشراء.
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT @order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = 1;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 74: الدفع: إعادة حساب الإجماليات
الإجماليات تأتي من البنود وليس من السلة.
UPDATE orders AS o
JOIN (
SELECT order_id, SUM(line_total) AS lines_total
FROM order_items
WHERE order_id = @order_id
GROUP BY order_id
) AS t ON t.order_id = o.order_id
SET o.subtotal = t.lines_total,
o.grand_total = t.lines_total - o.discount_total + o.shipping_fee;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 75: الدفع: حجز المخزون وكتابة الدفتر
قاعدة CHECK تجعل الحجز مع نقص المخزون يفشل.
-- If a line asks for more than is on hand, the CHECK constraint fails this statement with error 3819.
-- The whole statement is undone, and the transaction is rolled back by the caller.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved + oi.quantity
WHERE oi.order_id = @order_id;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, quantity, 'reserve', @order_id
FROM order_items
WHERE order_id = @order_id;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 76: تنفيذ الطلب كإجراء مخزَّن
الدفع كاملا مع التراجع عند أي خطأ.
DELIMITER $$
CREATE PROCEDURE place_order(
IN p_customer_id BIGINT UNSIGNED,
IN p_cart_id BIGINT UNSIGNED,
IN p_order_number VARCHAR(20),
OUT p_order_id BIGINT UNSIGNED)
BEGIN
DECLARE v_subtotal DECIMAL(12,2) DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
-- 1 lock the stock rows of the cart in ascending variant_id order
SELECT i.variant_id
FROM inventory AS i
JOIN cart_items AS ci ON ci.variant_id = i.variant_id
WHERE ci.cart_id = p_cart_id
ORDER BY i.variant_id
FOR UPDATE;
-- 2 the cart must be open and belong to this customer
IF NOT EXISTS (SELECT 1 FROM carts
WHERE cart_id = p_cart_id AND customer_id = p_customer_id AND status = 'open') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cart is not open for this customer';
END IF;
-- 3 subtotal from the current prices
SELECT COALESCE(SUM(v.price * ci.quantity), 0) INTO v_subtotal
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
WHERE ci.cart_id = p_cart_id;
IF v_subtotal = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cart is empty';
END IF;
-- 4 order header with the default address
INSERT INTO orders
(order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code)
SELECT p_order_number, p_customer_id, 'pending_payment', v_subtotal, 0.00, 50.00, v_subtotal + 50.00,
a.recipient_name, a.line1, a.city, a.postal_code, a.country_code
FROM addresses AS a
WHERE a.customer_id = p_customer_id AND a.is_default = 1;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'customer has no default address';
END IF;
SET p_order_id = LAST_INSERT_ID();
-- 5 order lines with the price snapshot
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT p_order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = p_cart_id;
-- 6 reserve stock; the CHECK constraint stops oversell
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved + oi.quantity
WHERE oi.order_id = p_order_id;
-- 7 ledger and cart status
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, quantity, 'reserve', p_order_id
FROM order_items
WHERE order_id = p_order_id;
UPDATE carts SET status = 'converted' WHERE cart_id = p_cart_id;
COMMIT;
END$$
DELIMITER ;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 12: المعاملات: نجاح الدفع وفشله والإلغاء والاسترداد
بعد إنشاء الطلب، تقرر نتيجة الدفع ما يحدث للحجز. النجاح يحوّل الحجز إلى بيع، والفشل يُفرج عنه، والإلغاء أو الاسترداد يعيد البضاعة. كُتبت كل من هذه المعاملات بحيث لا تغيّر الرسالة المكررة من مزوّد الدفع شيئا في المرة الثانية.
نجاح الدفع
يبدأ معالج webhook معاملة، ويحدّث الدفعة من pending إلى succeeded. إذا لم يتغير أي صف، فهذه رسالة مكررة، فيتراجع المعالج ويعيد 200. وإلا فإنه يضع الطلب في حالة paid، وينقل الحجز إلى on_hand بخصمه من on_hand وreserved معا، ويكتب صف sale لكل بند. يعرض المثال 77 التسلسل كاملا.
فشل الدفع والإلغاء
يؤدي فشل الدفع إلى وضع الدفعة في حالة failed، وإلغاء الطلب من pending_payment، والإفراج عن الحجز بخصم الكمية المحجوزة من reserved. ولا يتم الإفراج إلا إذا غيّر تحديث الطلب صفا واحدا، فلا يُفرج عن الحجز مرتين أبدا. يؤدي إلغاء طلب مدفوع قبل التغليف إلى إعادة الكمية إلى on_hand وكتابة صف cancel، كما في المثال 79.
الاسترداد دون حمل أقفال
يعمل الاسترداد في معاملتين قصيرتين حول استدعاء المزوّد. تسجّل المعاملة الأولى الاسترداد المعلّق وتُثبَّت. يُستدعى المزوّد دون أي قفل مُمسك. تحفظ المعاملة الثانية مرجع المزوّد، وتضع الاسترداد في حالة ناجح، وتضع الطلب في حالة refunded فقط عندما تغطي الاستردادات الناجحة مبلغ الدفعة. يقفل المثال 81 صف الدفعة حتى لا يجتاز طلبا استرداد متزامنان فحص المبلغ القابل للاسترداد معا.
رسوم توضيحية
مثال 77: خطاف نجاح الدفع
خطوات محمية: إعادة الخطاف لا تغيّر شيئا.
START TRANSACTION;
UPDATE payments
SET status = 'succeeded'
WHERE provider = 'sample-gateway' AND provider_ref = ? AND status = 'pending';
-- ROW_COUNT() = 0: this webhook was already handled. ROLLBACK and answer 200 to the gateway.
UPDATE orders
SET status = 'paid'
WHERE order_id = ? AND status = 'pending_payment';
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.on_hand = i.on_hand - oi.quantity,
i.reserved = i.reserved - oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, -quantity, -quantity, 'sale', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 78: فشل الدفع يحرر المخزون
تحرير الحجز مرة واحدة فقط.
START TRANSACTION;
UPDATE payments
SET status = 'failed'
WHERE provider = 'sample-gateway' AND provider_ref = ? AND status = 'pending';
-- ROW_COUNT() = 0: already handled, ROLLBACK.
UPDATE orders
SET status = 'cancelled'
WHERE order_id = ? AND status = 'pending_payment';
-- ROW_COUNT() = 0 here too: ROLLBACK, so the reservation is never released twice.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved - oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, -quantity, 'release', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 79: إلغاء طلب مدفوع قبل التغليف
يعود المخزون إلى الكمية المتوفرة.
START TRANSACTION;
UPDATE orders
SET status = 'cancelled'
WHERE order_id = ? AND status = 'paid';
-- ROW_COUNT() = 0: the order was packed or shipped already. ROLLBACK and show a return flow instead.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.on_hand = i.on_hand + oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, quantity, 0, 'cancel', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 80: استرداد يتم خارج استدعاء مزوّد الدفع
لا يُحتفظ بقفل صف أثناء الاتصال بالشبكة.
-- Transaction 1: record the intent, then commit before calling the provider.
START TRANSACTION;
SELECT payment_id FROM payments WHERE payment_id = ? AND status = 'succeeded' FOR UPDATE;
INSERT INTO refunds (payment_id, amount, status, reason)
VALUES (?, ?, 'pending', 'Customer returned the item');
COMMIT;
-- The provider call happens here, outside any database transaction, so no row lock is held.
-- Transaction 2: store the provider result and update the order.
START TRANSACTION;
UPDATE refunds
SET status = 'succeeded', provider_ref = ?
WHERE refund_id = ? AND status = 'pending';
UPDATE orders AS o
JOIN payments AS p ON p.order_id = o.order_id AND p.status = 'succeeded'
SET o.status = 'refunded'
WHERE o.order_id = ?
AND o.status IN ('paid','packed','shipped','delivered')
AND (SELECT COALESCE(SUM(r.amount), 0) FROM refunds AS r
WHERE r.payment_id = p.payment_id AND r.status = 'succeeded') >= p.amount;
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 81: حد الاسترداد بقفل صف
لا يمكن لطلبي استرداد أن ينجحا معا في الفحص.
START TRANSACTION;
SELECT amount FROM payments WHERE payment_id = ? FOR UPDATE;
SELECT COALESCE(SUM(CASE WHEN status <> 'failed' THEN amount END), 0) AS already_refunded
FROM refunds
WHERE payment_id = ?;
-- The application compares the requested amount with (amount - already_refunded). ROLLBACK when it is too large.
INSERT INTO refunds (payment_id, amount, status, reason)
VALUES (?, ?, 'pending', ?);
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 82: إعادة السلع المرتجعة إلى المخزون
فقط بعد الفحص وعندما تكون قابلة للبيع.
START TRANSACTION;
UPDATE inventory
SET on_hand = on_hand + 1
WHERE variant_id = 1;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
VALUES (1, 1, 0, 'return', 1);
COMMIT;
-- Restock only when the goods are inspected and resaleable. Otherwise write an adjustment instead.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 13: التزامن: مشتريان والقطعة الأخيرة
يستطيع مشترون كثيرون قراءة المخزون نفسه في اللحظة نفسها. إذا قرأ الكود رقما ثم اتخذ قرارا ثم كتب، فقد يرى مشتريان قطعة واحدة ويشتريانها معا. يعرض هذا الموضوع الخلل، وطريقتين للإصلاح، وأنواع الأقفال التي يوفرها MySQL 8، والجمود الذي يظهر عندما تُؤخذ الأقفال بترتيب مختلف.
مشتريان وقطعة واحدة
يقرأ المشتري A القيمة available = 1، ويقرأ المشتري B القيمة available = 1 نفسها. ثم يزيد كلاهما reserved بمقدار واحد. وبدون حماية يصبح reserved القيمة 2 بينما on_hand تبقى 1، وهذا بيع يتجاوز المخزون. يوقف قيد CHECK الكتابة الثانية برمز الخطأ 3819، لكنه شبكة أمان وليس الحل الكامل. يكمن الإصلاح الحقيقي في العبارة التي تفحص وتكتب في الوقت نفسه.
الأقفال: FOR UPDATE وNOWAIT وSKIP LOCKED
تجعل SELECT ... FOR UPDATE المعاملات الأخرى تنتظر حتى تُثبَّت هذه المعاملة. تفشل NOWAIT فورا برمز الخطأ 3572 بدلا من الانتظار، وهذا مناسب لشاشة يراها المستخدم. يتيح SKIP LOCKED للعامل أن يأخذ المهام التالية التي لا يمسكها عامل آخر، وهذا مناسب لطابور فحص الدفعات. استخدم SELECT ... FOR SHARE فقط عندما تحتاج إلى قراءة صف والتأكد من أنه لن يتغير.
الجمود والقفل المتفائل
يحدث الجمود عندما تمسك كل معاملة قفلا تحتاجه الأخرى. يكتشفه InnoDB ويتراجع عن إحدى المعاملتين برمز الخطأ 1213، لذلك على التطبيق أن يعيد المعاملة كلها. والعلاج هو ترتيب الأقفال نفسه في كل مكان. أما في تعديل المنتجات الذي لا يتنافس عليه كثيرون، فيتجنب القفل المتفائل بعمود version حمل الأقفال أصلا: يتضمن UPDATE الإصدار الذي قُرئ، وإذا لم يتغير أي صف فهذا يعني أن شخصا آخر حفظ قبلك.
مثال 83: إعادة إنتاج البيع الزائد
مشتريان ووحدة واحدة دون أي حماية.
-- Setup: one unit of variant 2 is left.
UPDATE inventory SET on_hand = 1, reserved = 0 WHERE variant_id = 2;
-- Buyer A and buyer B run these two statements at nearly the same time (no locking, no transaction):
SELECT on_hand - reserved AS available FROM inventory WHERE variant_id = 2; -- both read 1
UPDATE inventory SET reserved = reserved + 1 WHERE variant_id = 2; -- both write
-- Without the CHECK constraint, reserved would become 2 while on_hand is 1: an oversell.
-- With it, the second write fails with error 3819.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 84: آخر وحدة: تحديث ذري
عبارة مشروطة واحدة تمنح الوحدة لمشترٍ واحد.
UPDATE inventory
SET reserved = reserved + 1
WHERE variant_id = 2
AND on_hand - reserved >= 1;
-- ROW_COUNT() = 1: this buyer got the item.
-- ROW_COUNT() = 0: sold out. Show "sold out" and do not create an order.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 85: NOWAIT وSKIP LOCKED
فشل فوري، أو السماح للعاملين بتخطي الصفوف المقفلة.
-- NOWAIT: error 3572 at once if another transaction holds the row.
START TRANSACTION;
SELECT on_hand, reserved FROM inventory WHERE variant_id = 2 FOR UPDATE NOWAIT;
ROLLBACK;
-- SKIP LOCKED: a worker takes up to 10 pending payment checks that no other worker holds.
START TRANSACTION;
SELECT payment_id FROM payments
WHERE status = 'pending'
ORDER BY payment_id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- ...check each payment with the provider, update its row, then:
COMMIT;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 86: فحص الأقفال
يبين performance_schema من يحمل القفل ومن ينتظره.
SELECT engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'inventory';
SELECT requesting_engine_transaction_id, blocking_engine_transaction_id
FROM performance_schema.data_lock_waits;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 87: الجمود والإصلاح
قفل الصفوف بالترتيب نفسه في كل مكان.
-- Session A -- Session B
START TRANSACTION; START TRANSACTION;
UPDATE inventory SET reserved = reserved + 1 UPDATE inventory SET reserved = reserved + 1
WHERE variant_id = 1; WHERE variant_id = 4;
UPDATE inventory SET reserved = reserved + 1 UPDATE inventory SET reserved = reserved + 1
WHERE variant_id = 4; -- waits for B WHERE variant_id = 1; -- deadlock
-- One session gets error 1213 and is rolled back. The fix: both sessions lock in the same order (1, then 4).
-- Inspect the last deadlock with: SHOW ENGINE INNODB STATUS;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 88: القفل المتفائل بعمود الإصدار
كشف التحديث المفقود دون حمل أقفال.
ALTER TABLE products ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;
UPDATE products
SET base_price = 420.00, version = version + 1
WHERE product_id = 1 AND version = 0;
-- ROW_COUNT() = 0: someone else changed the product first. Reload it and try again.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 14: الفهارس وخطط الاستعلام
الفهرس نسخة مرتبة من بعض الأعمدة تتيح لـ MySQL إيجاد الصفوف دون قراءة الجدول كله. لكن كل فهرس يبطّئ الكتابة ويشغل مساحة، لذلك يحتاج كل فهرس إلى سبب. يشرح هذا الموضوع كيفية اختيار ترتيب الأعمدة، وكيفية قراءة EXPLAIN وEXPLAIN ANALYZE، وكيفية إيجاد الفهارس التي لا يستخدمها أحد.
اختيار ترتيب الفهرس المركب
يعمل الفهرس المركب من اليسار إلى اليمين. الفهرس (customer_id, created_at) يخدم استعلاما يُرشَّح بـ customer_id ويُرتَّب بـ created_at، لكنه لا يخدم استعلاما يُرشَّح بـ created_at وحده. ضع أعمدة المساواة أولا، ثم عمود الترتيب بعدها. واستخدم فهرسا تنازليا مثل (created_at DESC, order_id DESC) عندما تُقرأ الصفوف الأحدث أكثر من غيرها.
قراءة EXPLAIN
يعرض EXPLAIN نوع الوصول والفهرس المختار وعدد الصفوف المقدّر وهل يحتاج الاستعلام إلى جدول مؤقت أو filesort. يشغّل EXPLAIN ANALYZE، المتاح منذ MySQL 8.0.18، الاستعلام فعلا ويعرض الزمن الفعلي وعدد الصفوف الفعلي لكل خطوة. قارن التقدير بالقيمة الفعلية؛ فالفرق الكبير غالبا يعني أن الإحصاءات قديمة، ويعالجه ANALYZE TABLE.
إيجاد الفهارس غير المستخدمة والمكررة
تعرض عروض مخطط sys، مثل sys.schema_unused_indexes وsys.schema_redundant_indexes، المرشحين. يكون الفهرس (status) مكررا إذا كان (status, created_at) موجودا. تُصفَّر العدادات عند إعادة تشغيل الخادم، لذلك تحقق عبر دورة عمل كاملة، مثل تشغيل نهاية الشهر، قبل حذف أي فهرس. يتيح الفهرس غير المرئي اختبار أثر الحذف دون فقدان الفهرس.
مثال 89: اختبار الفهرس غير المرئي
فحص الخطة قبل حذف الفهرس.
ALTER TABLE orders ALTER INDEX idx_orders_status_created INVISIBLE;
EXPLAIN SELECT order_id FROM orders WHERE status = 'paid' ORDER BY created_at LIMIT 50;
-- Bring it back if the plans get worse:
ALTER TABLE orders ALTER INDEX idx_orders_status_created VISIBLE;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 90: فهرس يغطي أدنى سعر
الإجابة من الفهرس وحده.
CREATE INDEX idx_variants_product_active_price
ON product_variants (product_id, is_active, price);
EXPLAIN SELECT product_id, MIN(price)
FROM product_variants
WHERE is_active = 1
GROUP BY product_id;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 91: EXPLAIN ANALYZE والمدرجات التكرارية
قياس العمل الفعلي ومساعدة المحسّن.
EXPLAIN ANALYZE
SELECT order_id, order_number, created_at
FROM orders
WHERE customer_id = 1
ORDER BY created_at DESC
LIMIT 20;
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 92: الفهارس غير المستخدمة والمكررة
يجد مخطط sys المرشحين للحذف.
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';
-- Usage counters restart when the server restarts. Check over a full business cycle before you drop an index.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 15: العروض والتقارير والتقسيم إلى أجزاء
تطرح الشاشات والتقارير الأسئلة نفسها مرارا. تعطي العروض هذه الأسئلة اسما، وتلخّص التقارير المبيعات، ويجعل التقسيم إلى أجزاء جداول السجل الضخمة قابلة للإدارة. يغطي هذا الموضوع عروض شاشة المخزون وقائمة المشرف، وتقريرا شهريا مع المجاميع، وسجل أحداث مقسّما إلى أجزاء.
العروض تخفي عمليات الربط
يربط عرض مثل v_available_stock الجداول inventory وproduct_variants وproducts مرة واحدة، فلا تحتاج كل شاشة إلى تكرار الربط. العرض ليس ذاكرة تخزين مؤقتة: ينفّذ MySQL عبارة SELECT الخاصة بالعرض في كل مرة، ولا يمكن تحديث العرض الذي يربط عدة جداول. امنح دور التقارير صلاحية SELECT على العرض، لا على الجداول الأساسية.
التقارير
يجمع تقرير المبيعات الشهري قيم grand_total للطلبات المدفوعة وما بعدها، مجمّعة حسب الشهر. تضيف GROUP BY ... WITH ROLLUP صف مجموع. تقرأ التقارير الجداول الحية مباشرة، لذلك شغّل التقارير الثقيلة على نسخة قراءة أو ليلا، ولا تدع التقرير يمسك أقفالا تعطل الدفع.
التقسيم للسجلات التاريخية
يقسّم PARTITION BY RANGE COLUMNS الجدول إلى فترات، وهنا حسب created_at. يجب أن يتضمن المفتاح الأساسي عمود التقسيم، فيصبح (event_id, created_at). يزيل حذف جزء قديم شهرا كاملا دفعة واحدة، ويتجنب عبارة DELETE طويلة تملأ سجل التراجع. يساعد التقسيم الجداول التاريخية الكبيرة، لكنه لا يحل محل فهرس جيد.
مثال 93: عرض المخزون
عرض يخفي الربط عن شيفرة الشاشة.
CREATE OR REPLACE VIEW v_available_stock AS
SELECT v.variant_id, v.sku, p.name AS product_name,
i.on_hand, i.reserved, i.on_hand - i.reserved AS available
FROM inventory AS i
JOIN product_variants AS v ON v.variant_id = i.variant_id
JOIN products AS p ON p.product_id = v.product_id;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 94: عرض ملخص الطلبات
صف واحد لكل طلب في شاشة المشرف.
CREATE OR REPLACE VIEW v_order_summary AS
SELECT o.order_id, o.order_number, o.status, c.full_name AS customer_name,
o.grand_total, o.created_at,
COUNT(oi.order_item_id) AS line_count,
COALESCE(SUM(oi.quantity), 0) AS unit_count
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_number, o.status, c.full_name, o.grand_total, o.created_at;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 95: المبيعات الشهرية مع صف الإجمالي
GROUP BY WITH ROLLUP.
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS orders,
SUM(grand_total) AS revenue
FROM orders
WHERE status IN ('paid','packed','shipped','delivered')
GROUP BY month WITH ROLLUP;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 96: سجل أحداث مقسّم
أقسام نطاقية، وحذف الأشهر القديمة بكلفة منخفضة.
CREATE TABLE payment_events (
event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payment_id BIGINT UNSIGNED NOT NULL,
event_type VARCHAR(40) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (event_id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
PARTITION p2026h1 VALUES LESS THAN ('2026-07-01'),
PARTITION p2026h2 VALUES LESS THAN ('2027-01-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
-- Later: remove a whole old period at once.
-- ALTER TABLE payment_events DROP PARTITION p2026h1;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
الموضوع 16: الممارسات المهنية: الترحيل والتدقيق والنسخ الاحتياطي والصلاحيات
تتغيّر قاعدة البيانات في الإنتاج مع الوقت، ويمكن أن تتلف، وتحتفظ ببيانات شخصية. تعني الممارسة المهنية أن التغييرات مؤرخة بإصدارات ومراجَعة، وأن كل تغيير في الحالة مسجَّل، وأن النسخ الاحتياطية تُختبر بالاستعادة، وأن لكل برنامج الصلاحيات التي يحتاجها فقط. يغطي هذا الموضوع العادات التي تفصل مشروع التعلم عن متجر يثق به الناس.
الترحيلات المؤرخة بإصدارات
كل تغيير في المخطط ملف مرقَّم يعمل مرة واحدة وبالترتيب. يسجّل جدول schema_migrations الإصدار ومجموع التحقق checksum للملف، فيُكتشف أي ترحيل مُعدَّل. لا تعدّل ترحيلا سبق تنفيذه في الإنتاج، بل اكتب ترحيلا جديدا يصحّحه.
مسارات التدقيق والنسخ الاحتياطية
يكتب المشغّل AFTER UPDATE على جدول orders كل تغيير في الحالة مع المستخدم والوقت إلى order_audit. تعطي النسخة الاحتياطية بـ mysqldump --single-transaction لقطة متسقة لجداول InnoDB دون قفلها. النسخة التي لم تُستعد قط ليست نسخة احتياطية: استعدها في قاعدة بيانات اختبارية فارغة وقارن عدد الصفوف.
أقل الصلاحيات
يملك مستخدم التطبيق صلاحيات SELECT وINSERT وUPDATE وDELETE على المتجر، لكن لا يملك DROP ولا ALTER. يستخدم التقرير دورا يقرأ عرضين فقط. احتفظ بحسابات المشرف للترحيلات، واحفظ كل كلمات المرور في مخزن أسرار، وراجع الصلاحيات بـ SHOW GRANTS كل ربع سنة.
مثال 97: ترحيلات بإصدارات
كل تغيير يعمل مرة واحدة وبالترتيب، مع مجموع اختباري.
CREATE TABLE schema_migrations (
version VARCHAR(20) NOT NULL,
checksum CHAR(64) NOT NULL,
applied_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (version)
) ENGINE=InnoDB;
-- File V003__add_order_audit.sql: each migration runs once, in order, and the checksum detects edits.
-- The migration tool inserts ('V003', '<sha256 of the file>') after it succeeds.
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 98: مشغّل تدقيق لحالة الطلب
يسجل كل تغيير في الحالة مع من أجراه.
CREATE TABLE order_audit (
audit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
old_status VARCHAR(30) NOT NULL,
new_status VARCHAR(30) NOT NULL,
changed_by VARCHAR(100) NOT NULL,
changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (audit_id),
KEY idx_order_audit_order (order_id, changed_at)
) ENGINE=InnoDB;
DELIMITER $$
CREATE TRIGGER trg_orders_status_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF OLD.status <> NEW.status THEN
INSERT INTO order_audit (order_id, old_status, new_status, changed_by)
VALUES (OLD.order_id, OLD.status, NEW.status, CURRENT_USER());
END IF;
END$$
DELIMITER ;
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 99: النسخ الاحتياطي والاستعادة
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
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مثال 100: دور تقارير بعروض للقراءة فقط
أقل صلاحيات ممكنة للتقارير.
CREATE ROLE IF NOT EXISTS 'shop_reporting';
GRANT SELECT ON shop.v_order_summary TO 'shop_reporting';
GRANT SELECT ON shop.v_available_stock TO 'shop_reporting';
CREATE USER IF NOT EXISTS 'report_user'@'localhost' IDENTIFIED BY '<strong-password>';
GRANT 'shop_reporting' TO 'report_user'@'localhost';
SET DEFAULT ROLE 'shop_reporting' TO 'report_user'@'localhost';
SHOW GRANTS FOR 'report_user'@'localhost' USING 'shop_reporting';
*لم يُشغَّل هذا المثال هنا لأن MySQL 8 لم يكن مثبتا على الحاسوب الذي أُعدّت به هذه المقالة.*
مختبر: بناء قاعدة بيانات المتجر وتنفيذ عملية دفع واحدة
اجمع المقالة في قاعدة بيانات تدريبية. ستنشئ المخطط، وتحمّل البيانات التجريبية، وتنفذ عملية دفع واحدة ناجحة، وتحاول عمدا كسر قواعد المخزون. كل خطوة أدناه فحص يدوي تجريه بنفسك، لأن أيا من الأمثلة لم يُنفَّذ أثناء إعداد هذه المقالة.
1. أنشئ قاعدة البيانات 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 وصفوف المخزون بالأمثلة 26 و30.
5. نفّذ المثال 77 مرتين بالمرجع نفسه للمزوّد، وتأكد من أن التنفيذ الثاني لم يغيّر شيئا. هذا اختبار عدم التكرار idempotency.
6. نفّذ المثالين 83 و84 في جلستين من MySQL، جلسة لكل مشترٍ، وتأكد من أن مشتريا واحدا فقط حصل على القطعة الأخيرة.
7. سجّل ما أظهره تنفيذك لكل مثال، وقارنه بالسلوك المتوقع في المقالة قبل استخدام أي شيفرة منه في متجر حقيقي.
الخطوات التالية
بعد هذه المقالة، اقرأ عن طبقة التطبيق التي تستدعي هذه المعاملات: واجهة REST، والتحقق من المدخلات، وإعادة المحاولة بعد الجمود. ثم ادرس وثائق webhook لمزوّد الدفع، فمعالج webhook هو المكان الذي تظهر فيه معظم الأخطاء الحقيقية في المتاجر. وأخيرا تعلّم نسخ MySQL المتماثلة والنسخ القارئة، فهي تنقل التقارير بعيدا عن قاعدة بيانات الدفع.
الخلاصة
تُبنى قاعدة بيانات المتجر على قواعد قليلة: انسخ الأسعار والعناوين إلى الطلبات، واحتفظ بالمخزون كعدادات مع قيود CHECK وسجل للإضافة فقط، واجعل كل دفعة وكل تغيير في المخزون معاملة آمنة للتكرار. اقفل الصفوف بترتيب واحد، وأبقِ المعاملات قصيرة، ولا تستدعِ مزوّد الدفع أثناء امتلاك الأقفال. اختر الفهارس وفق الاستعلامات التي تشغّلها الشاشات فعلا، وتحقق منها بـ EXPLAIN، وأعطِ كل تغيير في المخطط إصدارا. لم تُنفَّذ الأمثلة المئة أثناء كتابة هذه المقالة، لذلك اختبر كل واحد منها في قاعدة بيانات تدريبية قبل الاعتماد عليه.