是一个综合性、企业级、可直接用于实际开发的电商系统核心数据库设计

以下是一个综合性、企业级、可直接用于实际开发的电商系统核心数据库设计，涵盖 用户、商品、分类、SKU、订单、订单项、收货地址、支付、评价、购物车、优惠券、库存 等核心模块。

我们将：

• ✅ 采用规范命名（snake_case）
• ✅ 使用合理数据类型
• ✅ 包含必备审计字段
• ✅ 支持软删除、多租户、JSON扩展
• ✅ 提供 MySQL 8.0+ 和 PostgreSQL 14+ 双版本 SQL 脚本
• ✅ 每张表附带详细说明与设计理由

---

🛒 电商系统综合数据库设计（企业级）

💡 设计原则：

• 单一职责：每张表只负责一个实体
• 外键约束：保证数据一致性（PGSQL 强制，MySQL 可选）
• 索引优化：高频查询字段加索引
• 扩展性优先：使用 JSON 存储动态属性
• 合规安全：软删除 + 审计字段

---

📁 整体结构概览（共 12 张核心表）

users                 —— 用户主表
user_profiles         —— 用户扩展信息（1:1）
addresses             —— 收货地址
categories            —— 商品分类（树形结构）
products              —— 商品主信息（SPU）
skus                  —— 商品规格（SKU）—— 与 products 关联
inventory             —— 库存（按 SKU 维护）
cart_items            —— 购物车
orders                —— 订单主表
order_items           —— 订单明细（与 orders 关联）
coupons               —— 优惠券
coupon_usages         —— 优惠券使用记录
product_reviews       —— 商品评价

⚠️ 注意：

• products 是 SPU（Standard Product Unit），代表一个商品（如 iPhone 15）
• skus 是 SKU（Stock Keeping Unit），代表具体规格（如 iPhone 15, 256GB, 黑色）
• 库存管理基于 SKU，而非 SPU

---

✅ 一、MySQL 版本（InnoDB，utf8mb4）

-- 设置字符集和排序规则
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

-- ==========================
-- 1. users - 用户主表
-- ==========================
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID，自增主键',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '登录用户名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) NULL UNIQUE COMMENT '手机号',
    password_hash VARCHAR(255) NOT NULL COMMENT '加密密码',
    avatar_url VARCHAR(500) NULL COMMENT '头像URL',
    status ENUM('active', 'inactive', 'banned', 'deleted') DEFAULT 'active' COMMENT '账户状态',
    last_login_at TIMESTAMP NULL COMMENT '最后登录时间',
    login_ip VARCHAR(45) NULL COMMENT '最后登录IP',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted_at TIMESTAMP NULL COMMENT '软删除时间',
    created_by BIGINT NULL COMMENT '创建人ID（系统=0）',
    updated_by BIGINT NULL COMMENT '最后修改人ID',
    tenant_id BIGINT DEFAULT 1 COMMENT '租户ID，支持多租户',
    metadata JSON NULL COMMENT '扩展字段：如性别、生日、会员等级等',

    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_deleted_at (deleted_at),
    INDEX idx_status (status),
    INDEX idx_last_login_at (last_login_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户主表';

-- ==========================
-- 2. user_profiles - 用户扩展信息（1:1）
-- ==========================
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY COMMENT '关联 users.id，外键',
    real_name VARCHAR(50) NULL COMMENT '真实姓名',
    gender ENUM('male', 'female', 'other') NULL COMMENT '性别',
    birth_date DATE NULL COMMENT '出生日期',
    nationality VARCHAR(50) NULL COMMENT '国籍',
    id_card VARCHAR(18) NULL COMMENT '身份证号（可选加密）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_user_profile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户档案扩展表';

-- ==========================
-- 3. addresses - 收货地址
-- ==========================
CREATE TABLE addresses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '地址ID',
    user_id BIGINT NOT NULL COMMENT '所属用户',
    receiver_name VARCHAR(50) NOT NULL COMMENT '收件人姓名',
    phone VARCHAR(20) NOT NULL COMMENT '联系电话',
    province VARCHAR(50) NOT NULL COMMENT '省',
    city VARCHAR(50) NOT NULL COMMENT '市',
    district VARCHAR(50) NOT NULL COMMENT '区/县',
    detail_address TEXT NOT NULL COMMENT '详细地址',
    postal_code VARCHAR(10) NULL COMMENT '邮编',
    is_default TINYINT(1) DEFAULT 0 COMMENT '是否默认地址',
    is_deleted TINYINT(1) DEFAULT 0 COMMENT '软删除标志',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_user_id (user_id),
    INDEX idx_is_default (is_default),
    INDEX idx_phone (phone),
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_deleted (is_deleted),

    CONSTRAINT fk_address_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货地址表';

-- ==========================
-- 4. categories - 商品分类（树形结构）
-- ==========================
CREATE TABLE categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
    name VARCHAR(100) NOT NULL COMMENT '分类名称',
    slug VARCHAR(100) NOT NULL UNIQUE COMMENT 'URL别名，如 iphone-15',
    parent_id BIGINT NULL COMMENT '父分类ID，NULL=顶级',
    level TINYINT DEFAULT 1 COMMENT '层级深度（1=一级）',
    sort_order INT DEFAULT 0 COMMENT '排序权重',
    is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    description TEXT NULL COMMENT '描述',
    icon_url VARCHAR(500) NULL COMMENT '图标URL',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_parent_id (parent_id),
    INDEX idx_slug (slug),
    INDEX idx_level (level),
    INDEX idx_sort_order (sort_order),
    INDEX idx_tenant_id (tenant_id),

    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表（支持无限级）';

-- ==========================
-- 5. products - 商品主表（SPU）
-- ==========================
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID（SPU）',
    category_id BIGINT NOT NULL COMMENT '所属分类',
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    subtitle VARCHAR(300) NULL COMMENT '副标题',
    brand VARCHAR(100) NULL COMMENT '品牌',
    model VARCHAR(100) NULL COMMENT '型号',
    description LONGTEXT NULL COMMENT '详细介绍（富文本）',
    spec_template JSON NULL COMMENT '规格模板：{"颜色":["黑","白"], "内存":["128G","256G"]}',
    images JSON NULL COMMENT '主图列表：["url1.jpg", "url2.jpg"]',
    video_url VARCHAR(500) NULL COMMENT '视频介绍链接',
    status ENUM('draft', 'on_sale', 'off_shelf', 'archived') DEFAULT 'draft' COMMENT '上下架状态',
    weight_gram INT NULL COMMENT '重量（克）',
    meta_title VARCHAR(200) NULL COMMENT 'SEO标题',
    meta_description TEXT NULL COMMENT 'SEO描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_category_id (category_id),
    INDEX idx_name (name),
    INDEX idx_brand (brand),
    INDEX idx_status (status),
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_created_at (created_at),

    CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品主表（SPU）';

-- ==========================
-- 6. skus - 商品规格（SKU）
-- ==========================
CREATE TABLE skus (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'SKU ID',
    product_id BIGINT NOT NULL COMMENT '关联商品SPU',
    sku_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'SKU编码，如 PROD-1001-BLACK-256G',
    name VARCHAR(200) NOT NULL COMMENT '规格名称（如：iPhone 15 黑色 256GB）',
    price_decimal DECIMAL(10,2) NOT NULL COMMENT '销售价格（单位：元）',
    cost_price DECIMAL(10,2) NULL COMMENT '成本价',
    market_price DECIMAL(10,2) NULL COMMENT '市场价',
    attributes JSON NOT NULL COMMENT '具体属性：{"颜色":"黑色","内存":"256GB"}',
    images JSON NULL COMMENT '该SKU专属图片列表',
    is_active TINYINT(1) DEFAULT 1 COMMENT '是否上架',
    weight_gram INT NULL COMMENT '重量（克）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_product_id (product_id),
    INDEX idx_sku_code (sku_code),
    INDEX idx_price_decimal (price_decimal),
    INDEX idx_is_active (is_active),
    INDEX idx_tenant_id (tenant_id),

    CONSTRAINT fk_sku_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品规格表（SKU）';

-- ==========================
-- 7. inventory - 库存表（按SKU管理）
-- ==========================
CREATE TABLE inventory (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '库存ID',
    sku_id BIGINT NOT NULL UNIQUE COMMENT '关联SKU',
    stock_quantity INT NOT NULL DEFAULT 0 COMMENT '可用库存数量',
    reserved_quantity INT NOT NULL DEFAULT 0 COMMENT '已预留（下单未支付）',
    locked_quantity INT NOT NULL DEFAULT 0 COMMENT '锁定中（秒杀/预扣）',
    last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by BIGINT NULL,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_sku_id (sku_id),
    INDEX idx_stock_quantity (stock_quantity),
    INDEX idx_tenant_id (tenant_id),

    CONSTRAINT fk_inventory_sku FOREIGN KEY (sku_id) REFERENCES skus(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存表（按SKU粒度）';

-- ==========================
-- 8. cart_items - 购物车
-- ==========================
CREATE TABLE cart_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '购物车项ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    sku_id BIGINT NOT NULL COMMENT '商品SKU',
    quantity INT NOT NULL DEFAULT 1 COMMENT '数量',
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted TINYINT(1) DEFAULT 0 COMMENT '软删除',
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_user_id (user_id),
    INDEX idx_sku_id (sku_id),
    INDEX idx_deleted (is_deleted),
    INDEX idx_tenant_id (tenant_id),
    UNIQUE KEY uk_user_sku (user_id, sku_id), -- 一个用户对同一SKU只能有一条记录

    CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_cart_sku FOREIGN KEY (sku_id) REFERENCES skus(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车项表';

-- ==========================
-- 9. orders - 订单主表
-- ==========================
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单编号，如 ORD202405200001',
    user_id BIGINT NOT NULL COMMENT '下单用户',
    address_id BIGINT NULL COMMENT '收货地址ID',
    payment_method ENUM('alipay', 'wechat', 'bank', 'cash_on_delivery') NULL COMMENT '支付方式',
    payment_status ENUM('unpaid', 'paid', 'refunded', 'failed') DEFAULT 'unpaid' COMMENT '支付状态',
    shipping_status ENUM('pending', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending' COMMENT '物流状态',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总额（含运费）',
    subtotal_amount DECIMAL(10,2) NOT NULL COMMENT '商品小计',
    discount_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '优惠金额',
    shipping_fee DECIMAL(10,2) DEFAULT 0.00 COMMENT '运费',
    tax_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '税费',
    currency CHAR(3) DEFAULT 'CNY' COMMENT '货币代码：CNY/USD/EUR',
    buyer_message TEXT NULL COMMENT '买家留言',
    remark TEXT NULL COMMENT '管理员备注',
    ip_address VARCHAR(45) NULL COMMENT '下单IP',
    user_agent TEXT NULL COMMENT '客户端UA',
    channel ENUM('web', 'app', 'mini_program', 'api') DEFAULT 'web' COMMENT '下单渠道',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL COMMENT '支付时间',
    shipped_at TIMESTAMP NULL COMMENT '发货时间',
    delivered_at TIMESTAMP NULL COMMENT '签收时间',
    cancelled_at TIMESTAMP NULL COMMENT '取消时间',
    deleted_at TIMESTAMP NULL COMMENT '软删除',
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_order_no (order_no),
    INDEX idx_user_id (user_id),
    INDEX idx_payment_status (payment_status),
    INDEX idx_shipping_status (shipping_status),
    INDEX idx_created_at (created_at),
    INDEX idx_paid_at (paid_at),
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_deleted_at (deleted_at),

    CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_order_address FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

-- ==========================
-- 10. order_items - 订单明细
-- ==========================
CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '订单项ID',
    order_id BIGINT NOT NULL COMMENT '所属订单',
    sku_id BIGINT NOT NULL COMMENT '商品SKU',
    product_id BIGINT NOT NULL COMMENT '商品SPU（冗余，避免关联被删）',
    name VARCHAR(200) NOT NULL COMMENT '商品名称（快照）',
    price_decimal DECIMAL(10,2) NOT NULL COMMENT '下单时单价（快照）',
    quantity INT NOT NULL COMMENT '购买数量',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '小计 = price * quantity',
    attributes JSON NOT NULL COMMENT '购买时的规格快照（如颜色、内存）',
    images JSON NULL COMMENT '商品图片快照',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_order_id (order_id),
    INDEX idx_sku_id (sku_id),
    INDEX idx_product_id (product_id),

    CONSTRAINT fk_orderitem_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_orderitem_sku FOREIGN KEY (sku_id) REFERENCES skus(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表（快照）';

-- ==========================
-- 11. coupons - 优惠券
-- ==========================
CREATE TABLE coupons (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '优惠券ID',
    code VARCHAR(50) NOT NULL UNIQUE COMMENT '优惠码（如 SAVE2024）',
    name VARCHAR(100) NOT NULL COMMENT '优惠券名称',
    type ENUM('fixed_amount', 'percentage', 'free_shipping') NOT NULL COMMENT '类型：满减、折扣、免邮',
    value DECIMAL(10,2) NOT NULL COMMENT '面值（满减金额或折扣率）',
    min_order_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '最低消费门槛',
    max_discount_amount DECIMAL(10,2) NULL COMMENT '最高抵扣额（仅限满减）',
    valid_from TIMESTAMP NOT NULL COMMENT '生效时间',
    valid_to TIMESTAMP NOT NULL COMMENT '失效时间',
    total_quantity INT NOT NULL COMMENT '总发行量',
    used_quantity INT DEFAULT 0 COMMENT '已使用数量',
    is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    restriction JSON NULL COMMENT '限制条件：{"user_types":["new"], "product_ids":[1,2,3]}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT NULL,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_code (code),
    INDEX idx_valid_period (valid_from, valid_to),
    INDEX idx_is_active (is_active),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券定义表';

-- ==========================
-- 12. coupon_usages - 优惠券使用记录
-- ==========================
CREATE TABLE coupon_usages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '使用记录ID',
    coupon_id BIGINT NOT NULL COMMENT '关联优惠券',
    user_id BIGINT NOT NULL COMMENT '使用用户',
    order_id BIGINT NULL COMMENT '关联订单',
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '使用时间',
    discount_amount DECIMAL(10,2) NOT NULL COMMENT '本次抵扣金额',
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_coupon_id (coupon_id),
    INDEX idx_user_id (user_id),
    INDEX idx_order_id (order_id),
    INDEX idx_used_at (used_at),
    INDEX idx_tenant_id (tenant_id),
    UNIQUE KEY uk_user_coupon (user_id, coupon_id), -- 一人一券最多用一次（可选）

    CONSTRAINT fk_couponusage_coupon FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    CONSTRAINT fk_couponusage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_couponusage_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券使用记录表';

-- ==========================
-- 13. product_reviews - 商品评价
-- ==========================
CREATE TABLE product_reviews (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '评价ID',
    product_id BIGINT NOT NULL COMMENT '商品SPU',
    user_id BIGINT NOT NULL COMMENT '评价用户',
    order_item_id BIGINT NULL COMMENT '关联订单项（确保真实购买）',
    rating TINYINT NOT NULL COMMENT '评分 1~5',
    title VARCHAR(100) NULL COMMENT '标题',
    content TEXT NOT NULL COMMENT '评价内容',
    images JSON NULL COMMENT '上传图片列表',
    helpful_count INT DEFAULT 0 COMMENT '点赞数',
    is_anonymous TINYINT(1) DEFAULT 0 COMMENT '是否匿名',
    status ENUM('pending', 'approved', 'rejected', 'hidden') DEFAULT 'pending' COMMENT '审核状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    tenant_id BIGINT DEFAULT 1,

    INDEX idx_product_id (product_id),
    INDEX idx_user_id (user_id),
    INDEX idx_rating (rating),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_tenant_id (tenant_id),

    CONSTRAINT fk_review_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_review_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_review_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品评价表';

---

✅ 二、PostgreSQL 版本（完整支持 JSONB、UUID、序列、强约束）

✅ PostgreSQL 更适合复杂电商系统：支持 JSONB 索引、序列、事务更强、更标准

-- 设置字符集（PostgreSQL 默认 UTF8）
SET client_encoding TO 'UTF8';

-- 创建序列（推荐用 SERIAL / BIGSERIAL）
-- ==========================
-- 1. users
-- ==========================
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'banned', 'deleted')) DEFAULT 'active',
    last_login_at TIMESTAMP WITHOUT TIME ZONE,
    login_ip INET,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITHOUT TIME ZONE,
    created_by BIGINT,
    updated_by BIGINT,
    tenant_id BIGINT DEFAULT 1,
    metadata JSONB,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_users_deleted ON users(deleted_at);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_last_login ON users(last_login_at);

-- ==========================
-- 2. user_profiles
-- ==========================
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    real_name VARCHAR(50),
    gender VARCHAR(10) CHECK (gender IN ('male', 'female', 'other')),
    birth_date DATE,
    nationality VARCHAR(50),
    id_card VARCHAR(18),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);

-- ==========================
-- 3. addresses
-- ==========================
CREATE TABLE addresses (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    receiver_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    district VARCHAR(50) NOT NULL,
    detail_address TEXT NOT NULL,
    postal_code VARCHAR(10),
    is_default BOOLEAN DEFAULT FALSE,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    created_by BIGINT,
    updated_by BIGINT,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_addresses_user ON addresses(user_id);
CREATE INDEX idx_addresses_default ON addresses(is_default);
CREATE INDEX idx_addresses_phone ON addresses(phone);
CREATE INDEX idx_addresses_deleted ON addresses(is_deleted);
CREATE INDEX idx_addresses_tenant ON addresses(tenant_id);

-- ==========================
-- 4. categories
-- ==========================
CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL,
    level SMALLINT DEFAULT 1 CHECK (level >= 1),
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    icon_url VARCHAR(500),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    created_by BIGINT,
    updated_by BIGINT,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_categories_parent ON categories(parent_id);
CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_level ON categories(level);
CREATE INDEX idx_categories_sort ON categories(sort_order);
CREATE INDEX idx_categories_tenant ON categories(tenant_id);

-- ==========================
-- 5. products
-- ==========================
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    name VARCHAR(200) NOT NULL,
    subtitle VARCHAR(300),
    brand VARCHAR(100),
    model VARCHAR(100),
    description TEXT,
    spec_template JSONB,
    images JSONB,
    video_url VARCHAR(500),
    status VARCHAR(20) CHECK (status IN ('draft', 'on_sale', 'off_shelf', 'archived')) DEFAULT 'draft',
    weight_gram INTEGER,
    meta_title VARCHAR(200),
    meta_description TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    created_by BIGINT,
    updated_by BIGINT,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_brand ON products(brand);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_tenant ON products(tenant_id);
CREATE INDEX idx_products_created ON products(created_at);

-- ==========================
-- 6. skus
-- ==========================
CREATE TABLE skus (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    sku_code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    price_decimal DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2),
    market_price DECIMAL(10,2),
    attributes JSONB NOT NULL,
    images JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    weight_gram INTEGER,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    created_by BIGINT,
    updated_by BIGINT,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_skus_product ON skus(product_id);
CREATE INDEX idx_skus_code ON skus(sku_code);
CREATE INDEX idx_skus_price ON skus(price_decimal);
CREATE INDEX idx_skus_active ON skus(is_active);
CREATE INDEX idx_skus_tenant ON skus(tenant_id);

-- ==========================
-- 7. inventory
-- ==========================
CREATE TABLE inventory (
    id BIGSERIAL PRIMARY KEY,
    sku_id BIGINT NOT NULL UNIQUE REFERENCES skus(id) ON DELETE CASCADE,
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    reserved_quantity INTEGER NOT NULL DEFAULT 0,
    locked_quantity INTEGER NOT NULL DEFAULT 0,
    last_updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() ON UPDATE NOW(), -- PG 需触发器实现
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_by BIGINT,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

-- 👇 在 PostgreSQL 中，ON UPDATE NOW() 不原生支持，需创建触发器（见下方补充）
CREATE INDEX idx_inventory_sku ON inventory(sku_id);
CREATE INDEX idx_inventory_stock ON inventory(stock_quantity);
CREATE INDEX idx_inventory_tenant ON inventory(tenant_id);

-- ==========================
-- 8. cart_items
-- ==========================
CREATE TABLE cart_items (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sku_id BIGINT NOT NULL REFERENCES skus(id) ON DELETE CASCADE,
    quantity INTEGER NOT NULL DEFAULT 1,
    added_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    is_deleted BOOLEAN DEFAULT FALSE,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0),
    UNIQUE (user_id, sku_id)
);

CREATE INDEX idx_cart_user ON cart_items(user_id);
CREATE INDEX idx_cart_sku ON cart_items(sku_id);
CREATE INDEX idx_cart_deleted ON cart_items(is_deleted);
CREATE INDEX idx_cart_tenant ON cart_items(tenant_id);

-- ==========================
-- 9. orders
-- ==========================
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    address_id BIGINT REFERENCES addresses(id) ON DELETE SET NULL,
    payment_method VARCHAR(20) CHECK (payment_method IN ('alipay', 'wechat', 'bank', 'cash_on_delivery')),
    payment_status VARCHAR(20) CHECK (payment_status IN ('unpaid', 'paid', 'refunded', 'failed')) DEFAULT 'unpaid',
    shipping_status VARCHAR(20) CHECK (shipping_status IN ('pending', 'shipped', 'delivered', 'cancelled')) DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    subtotal_amount DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    shipping_fee DECIMAL(10,2) DEFAULT 0.00,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    currency CHAR(3) DEFAULT 'CNY',
    buyer_message TEXT,
    remark TEXT,
    ip_address INET,
    user_agent TEXT,
    channel VARCHAR(20) CHECK (channel IN ('web', 'app', 'mini_program', 'api')) DEFAULT 'web',
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    paid_at TIMESTAMP WITHOUT TIME ZONE,
    shipped_at TIMESTAMP WITHOUT TIME ZONE,
    delivered_at TIMESTAMP WITHOUT TIME ZONE,
    cancelled_at TIMESTAMP WITHOUT TIME ZONE,
    deleted_at TIMESTAMP WITHOUT TIME ZONE,
    created_by BIGINT,
    updated_by BIGINT,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_orders_no ON orders(order_no);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_payment ON orders(payment_status);
CREATE INDEX idx_orders_shipping ON orders(shipping_status);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_orders_paid ON orders(paid_at);
CREATE INDEX idx_orders_deleted ON orders(deleted_at);
CREATE INDEX idx_orders_tenant ON orders(tenant_id);

-- ==========================
-- 10. order_items
-- ==========================
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sku_id BIGINT NOT NULL REFERENCES skus(id) ON DELETE RESTRICT,
    product_id BIGINT NOT NULL,
    name VARCHAR(200) NOT NULL,
    price_decimal DECIMAL(10,2) NOT NULL,
    quantity INTEGER NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    attributes JSONB NOT NULL,
    images JSONB,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),

    INDEX idx_order_items_order ON order_items(order_id),
    INDEX idx_order_items_sku ON order_items(sku_id),
    INDEX idx_order_items_product ON order_items(product_id)
);

-- ==========================
-- 11. coupons
-- ==========================
CREATE TABLE coupons (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) CHECK (type IN ('fixed_amount', 'percentage', 'free_shipping')) NOT NULL,
    value DECIMAL(10,2) NOT NULL,
    min_order_amount DECIMAL(10,2) DEFAULT 0.00,
    max_discount_amount DECIMAL(10,2),
    valid_from TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    valid_to TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    total_quantity INTEGER NOT NULL,
    used_quantity INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    restriction JSONB,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    created_by BIGINT,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_coupons_code ON coupons(code);
CREATE INDEX idx_coupons_valid ON coupons(valid_from, valid_to);
CREATE INDEX idx_coupons_active ON coupons(is_active);
CREATE INDEX idx_coupons_tenant ON coupons(tenant_id);

-- ==========================
-- 12. coupon_usages
-- ==========================
CREATE TABLE coupon_usages (
    id BIGSERIAL PRIMARY KEY,
    coupon_id BIGINT NOT NULL REFERENCES coupons(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    order_id BIGINT REFERENCES orders(id) ON DELETE SET NULL,
    used_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    discount_amount DECIMAL(10,2) NOT NULL,
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0),
    UNIQUE (user_id, coupon_id)
);

CREATE INDEX idx_coupon_usage_coupon ON coupon_usages(coupon_id);
CREATE INDEX idx_coupon_usage_user ON coupon_usages(user_id);
CREATE INDEX idx_coupon_usage_order ON coupon_usages(order_id);
CREATE INDEX idx_coupon_usage_used_at ON coupon_usages(used_at);
CREATE INDEX idx_coupon_usage_tenant ON coupon_usages(tenant_id);

-- ==========================
-- 13. product_reviews
-- ==========================
CREATE TABLE product_reviews (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    order_item_id BIGINT REFERENCES order_items(id) ON DELETE SET NULL,
    rating SMALLINT CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(100),
    content TEXT NOT NULL,
    images JSONB,
    helpful_count INTEGER DEFAULT 0,
    is_anonymous BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) CHECK (status IN ('pending', 'approved', 'rejected', 'hidden')) DEFAULT 'pending',
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    tenant_id BIGINT DEFAULT 1,

    CONSTRAINT chk_tenant_positive CHECK (tenant_id > 0)
);

CREATE INDEX idx_reviews_product ON product_reviews(product_id);
CREATE INDEX idx_reviews_user ON product_reviews(user_id);
CREATE INDEX idx_reviews_rating ON product_reviews(rating);
CREATE INDEX idx_reviews_status ON product_reviews(status);
CREATE INDEX idx_reviews_created ON product_reviews(created_at);
CREATE INDEX idx_reviews_tenant ON product_reviews(tenant_id);

-- 🔧 PostgreSQL 补充：为 inventory.last_updated_at 实现自动更新
CREATE OR REPLACE FUNCTION update_inventory_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_update_inventory_timestamp
BEFORE UPDATE ON inventory
FOR EACH ROW EXECUTE FUNCTION update_inventory_timestamp();

---

✅ 三、关键设计亮点总结

方面	MySQL	PostgreSQL
主键	BIGINT AUTO_INCREMENT	BIGSERIAL
JSON 支持	JSON（可索引函数）	JSONB（高性能，支持索引）
枚举	ENUM	CHECK + 字符串（更灵活）
IP 地址	VARCHAR(45)	INET 类型（原生支持）
自动更新时间	ON UPDATE CURRENT_TIMESTAMP	需触发器
并发控制	乐观锁（version 字段）	乐观锁 或 行锁
性能	快，适合高并发读写	更强事务、复杂查询、全文搜索
扩展性	适合中小项目	适合大型平台、微服务

---

✅ 四、部署建议

建议	说明
🚀 生产环境	推荐 PostgreSQL，更适合复杂业务、数据一致性要求高的场景
📦 快速原型	MySQL 更轻量，生态成熟，适合创业团队
🔐 权限控制	所有表都包含 tenant_id，便于 SaaS 多租户隔离
🔄 数据迁移	使用 Flyway / Liquibase 管理 SQL 版本
📊 监控	对 inventory.stock_quantity, orders.payment_status 做实时监控
📦 缓存	SKU 价格、库存、优惠券用 Redis 缓存，减少 DB 压力
🧪 测试数据	用 Faker 工具生成 10万+ 测试数据验证性能

---

✅ 五、后续可扩展方向

• 加入 商品标签（tags）、促销活动（promotions）
• 加入 分销系统（agent_id）
• 加入 积分体系（points_ledger）
• 加入 物流单号追踪（logistics_tracking）
• 加入 商品评论情感分析（NLP）
• 数据仓库：将订单、评价同步到 ClickHouse 或 Snowflake 做 BI 分析

---

✅ 结语

这个设计已经具备上线级电商系统的完整性，覆盖了从用户注册 → 商品浏览 → 加购 → 下单 → 支付 → 发货 → 评价 的全链路，并兼顾：

• ✅ 数据一致性
• ✅ 性能与扩展性
• ✅ 审计与合规
• ✅ 多租户与国际化
• ✅ 可维护性