404 lines
17 KiB
MySQL
404 lines
17 KiB
MySQL
|
-- =====================================================
|
|||
|
|
-- 数据库创建脚本
|
|||
|
|
-- 数据库名称：TestECommerceDB
|
|||
|
|
-- 用途：测试大语言模型的SQL编写能力
|
|||
|
|
-- 设计特点：复杂关联关系、一对多、多对多、自关联
|
|||
|
|
-- 迷惑项：相似字段名、相近表名、含义相近的字段
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
-- 创建数据库
|
|||
|
|
DROP DATABASE IF EXISTS TestECommerceDB;
|
|||
|
|
CREATE DATABASE TestECommerceDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|||
|
|
USE TestECommerceDB;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 1. 用户表 (Users)
|
|||
|
|
-- 存储用户基本信息
|
|||
|
|
-- 迷惑项：user_id、user_email、user_status 字段名相似
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Users (
|
|||
|
|
user_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|||
|
|
user_email VARCHAR(100) NOT NULL UNIQUE,
|
|||
|
|
password_hash VARCHAR(255) NOT NULL,
|
|||
|
|
full_name VARCHAR(100),
|
|||
|
|
phone VARCHAR(20),
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
user_status TINYINT DEFAULT 1 COMMENT '1=活跃, 0=禁用',
|
|||
|
|
last_login DATETIME,
|
|||
|
|
INDEX idx_username (username),
|
|||
|
|
INDEX idx_user_email (user_email),
|
|||
|
|
INDEX idx_user_status (user_status)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 2. 分类表 (Categories)
|
|||
|
|
-- 存储产品分类信息
|
|||
|
|
-- 迷惑项：自关联关系，category_id、category_name、parent_id
|
|||
|
|
-- 特点：树形结构，支持多级分类
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Categories (
|
|||
|
|
category_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
category_name VARCHAR(50) NOT NULL,
|
|||
|
|
category_desc VARCHAR(200),
|
|||
|
|
parent_id INT DEFAULT NULL,
|
|||
|
|
category_order INT DEFAULT 0,
|
|||
|
|
is_active TINYINT DEFAULT 1,
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (parent_id) REFERENCES Categories(category_id) ON DELETE SET NULL,
|
|||
|
|
INDEX idx_parent_id (parent_id),
|
|||
|
|
INDEX idx_category_name (category_name),
|
|||
|
|
INDEX idx_is_active (is_active)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 3. 产品表 (Products)
|
|||
|
|
-- 存储产品信息
|
|||
|
|
-- 迷惑项：product_id、product_name、product_desc、product_status
|
|||
|
|
-- 含义相近字段：price vs cost_price
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Products (
|
|||
|
|
product_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
product_name VARCHAR(100) NOT NULL,
|
|||
|
|
product_desc TEXT,
|
|||
|
|
product_specs VARCHAR(255) COMMENT '产品规格JSON字符串',
|
|||
|
|
price DECIMAL(10,2) NOT NULL COMMENT '销售价格',
|
|||
|
|
cost_price DECIMAL(10,2) COMMENT '成本价格',
|
|||
|
|
stock_quantity INT DEFAULT 0,
|
|||
|
|
stock_alert_level INT DEFAULT 10,
|
|||
|
|
category_id INT,
|
|||
|
|
brand VARCHAR(50),
|
|||
|
|
product_status TINYINT DEFAULT 1 COMMENT '1=上架, 0=下架, 2=缺货',
|
|||
|
|
view_count INT DEFAULT 0,
|
|||
|
|
sales_count INT DEFAULT 0,
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE SET NULL,
|
|||
|
|
INDEX idx_product_name (product_name),
|
|||
|
|
INDEX idx_price (price),
|
|||
|
|
INDEX idx_category_id (category_id),
|
|||
|
|
INDEX idx_product_status (product_status),
|
|||
|
|
INDEX idx_brand (brand),
|
|||
|
|
INDEX idx_sales_count (sales_count)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 4. 产品-分类关联表 (ProductCategories)
|
|||
|
|
-- 实现产品和分类的多对多关系
|
|||
|
|
-- 迷惑项：表名与Products、Categories相似
|
|||
|
|
-- 特点：支持产品属于多个分类，设置主分类
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE ProductCategories (
|
|||
|
|
product_id INT NOT NULL,
|
|||
|
|
category_id INT NOT NULL,
|
|||
|
|
is_primary TINYINT DEFAULT 0 COMMENT '1=主分类, 0=副分类',
|
|||
|
|
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
PRIMARY KEY (product_id, category_id),
|
|||
|
|
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE CASCADE,
|
|||
|
|
INDEX idx_category_id (category_id),
|
|||
|
|
INDEX idx_is_primary (is_primary)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 5. 订单表 (Orders)
|
|||
|
|
-- 存储订单信息
|
|||
|
|
-- 迷惑项：order_id、order_date、order_status
|
|||
|
|
-- 含义相近字段：total_amount
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Orders (
|
|||
|
|
order_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
order_number VARCHAR(30) NOT NULL UNIQUE COMMENT '订单编号',
|
|||
|
|
user_id INT NOT NULL,
|
|||
|
|
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
|
|||
|
|
discount_amount DECIMAL(10,2) DEFAULT 0 COMMENT '折扣金额',
|
|||
|
|
shipping_fee DECIMAL(10,2) DEFAULT 0 COMMENT '运费',
|
|||
|
|
payment_method VARCHAR(30) COMMENT '支付方式',
|
|||
|
|
payment_status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/paid/failed/refunded',
|
|||
|
|
order_status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/confirmshipped/delivered/cancelled/completed',
|
|||
|
|
shipping_addr_id INT,
|
|||
|
|
billing_addr_id INT,
|
|||
|
|
shipping_addr VARCHAR(500) COMMENT '收货地址快照',
|
|||
|
|
billing_addr VARCHAR(500) COMMENT '账单地址快照',
|
|||
|
|
customer_remark VARCHAR(500),
|
|||
|
|
internal_remark VARCHAR(500),
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE RESTRICT,
|
|||
|
|
INDEX idx_order_number (order_number),
|
|||
|
|
INDEX idx_user_id (user_id),
|
|||
|
|
INDEX idx_order_date (order_date),
|
|||
|
|
INDEX idx_order_status (order_status),
|
|||
|
|
INDEX idx_payment_status (payment_status)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 6. 订单详情表 (OrderItems)
|
|||
|
|
-- 存储订单中每个产品的详细信息
|
|||
|
|
-- 迷惑项：order_item_id、order_id
|
|||
|
|
-- 含义相近字段：item_price vs price，item_total vs total_amount
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE OrderItems (
|
|||
|
|
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
order_id INT NOT NULL,
|
|||
|
|
product_id INT NOT NULL,
|
|||
|
|
quantity INT NOT NULL,
|
|||
|
|
item_price DECIMAL(10,2) NOT NULL COMMENT '下单时的产品单价',
|
|||
|
|
item_cost DECIMAL(10,2) COMMENT '成本单价',
|
|||
|
|
item_discount DECIMAL(10,2) DEFAULT 0 COMMENT '该项折扣',
|
|||
|
|
item_total DECIMAL(10,2) NOT NULL COMMENT '该项总金额',
|
|||
|
|
item_status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/shipped/delivered/returned',
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE RESTRICT,
|
|||
|
|
INDEX idx_order_id (order_id),
|
|||
|
|
INDEX idx_product_id (product_id),
|
|||
|
|
INDEX idx_item_status (item_status)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 7. 地址表 (Addresses)
|
|||
|
|
-- 存储用户的多个地址
|
|||
|
|
-- 迷惑项：address_id、address_line1、address_line2
|
|||
|
|
-- 一对多关系：一个用户有多个地址
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Addresses (
|
|||
|
|
address_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
user_id INT NOT NULL,
|
|||
|
|
address_label VARCHAR(50) COMMENT '地址标签，如：家、公司',
|
|||
|
|
recipient_name VARCHAR(100) NOT NULL,
|
|||
|
|
recipient_phone VARCHAR(20) NOT NULL,
|
|||
|
|
country VARCHAR(50) DEFAULT '中国',
|
|||
|
|
province VARCHAR(50),
|
|||
|
|
city VARCHAR(50) NOT NULL,
|
|||
|
|
district VARCHAR(50),
|
|||
|
|
address_line1 VARCHAR(200) NOT NULL,
|
|||
|
|
address_line2 VARCHAR(200),
|
|||
|
|
postal_code VARCHAR(20),
|
|||
|
|
address_type VARCHAR(20) DEFAULT 'shipping' COMMENT 'shipping/billing/both',
|
|||
|
|
is_default TINYINT DEFAULT 0,
|
|||
|
|
is_active TINYINT DEFAULT 1,
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
|
|||
|
|
INDEX idx_user_id (user_id),
|
|||
|
|
INDEX idx_address_type (address_type),
|
|||
|
|
INDEX idx_is_default (is_default),
|
|||
|
|
INDEX idx_city (city)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 8. 评论表 (Reviews)
|
|||
|
|
-- 存储用户对产品的评论
|
|||
|
|
-- 迷惑项：review_id、review_text、review_date
|
|||
|
|
-- 多对一关系：评论关联用户和产品
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Reviews (
|
|||
|
|
review_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
user_id INT NOT NULL,
|
|||
|
|
product_id INT NOT NULL,
|
|||
|
|
order_id INT COMMENT '订单ID，可选',
|
|||
|
|
rating TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
|||
|
|
review_title VARCHAR(100),
|
|||
|
|
review_text TEXT,
|
|||
|
|
is_anonymous TINYINT DEFAULT 0,
|
|||
|
|
is_verified TINYINT DEFAULT 0 COMMENT '是否购买后评论',
|
|||
|
|
helpful_count INT DEFAULT 0,
|
|||
|
|
reply_count INT DEFAULT 0,
|
|||
|
|
review_status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/approved/rejected',
|
|||
|
|
review_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE SET NULL,
|
|||
|
|
INDEX idx_user_id (user_id),
|
|||
|
|
INDEX idx_product_id (product_id),
|
|||
|
|
INDEX idx_rating (rating),
|
|||
|
|
INDEX idx_review_status (review_status),
|
|||
|
|
INDEX idx_review_date (review_date)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 9. 购物车表 (CartItems)
|
|||
|
|
-- 存储用户当前购物车中的商品
|
|||
|
|
-- 迷惑项：cart_id、product_id、quantity
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE CartItems (
|
|||
|
|
cart_item_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
user_id INT NOT NULL,
|
|||
|
|
product_id INT NOT NULL,
|
|||
|
|
quantity INT NOT NULL DEFAULT 1,
|
|||
|
|
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE,
|
|||
|
|
UNIQUE KEY uk_user_product (user_id, product_id),
|
|||
|
|
INDEX idx_user_id (user_id),
|
|||
|
|
INDEX idx_product_id (product_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 10. 产品浏览历史表 (ProductViews)
|
|||
|
|
-- 存储用户的浏览历史
|
|||
|
|
-- 迷惑项：view_id、product_id、view_date
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE ProductViews (
|
|||
|
|
view_id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
user_id INT,
|
|||
|
|
product_id INT NOT NULL,
|
|||
|
|
view_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
session_id VARCHAR(100),
|
|||
|
|
view_duration INT DEFAULT 0 COMMENT '浏览时长（秒）',
|
|||
|
|
source_type VARCHAR(30) COMMENT '来源：search/category/recommend/direct',
|
|||
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE SET NULL,
|
|||
|
|
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE,
|
|||
|
|
INDEX idx_user_id (user_id),
|
|||
|
|
INDEX idx_product_id (product_id),
|
|||
|
|
INDEX idx_view_date (view_date)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 11. 优惠券表 (Coupons)
|
|||
|
|
-- 存储优惠券信息
|
|||
|
|
-- 迷惑项：coupon_id、coupon_code、coupon_type
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Coupons (
|
|||
|
|
coupon_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
coupon_code VARCHAR(30) NOT NULL UNIQUE,
|
|||
|
|
coupon_name VARCHAR(100) NOT NULL,
|
|||
|
|
coupon_desc VARCHAR(255),
|
|||
|
|
coupon_type VARCHAR(20) DEFAULT 'percentage' COMMENT 'percentage/fixed',
|
|||
|
|
discount_value DECIMAL(10,2) NOT NULL COMMENT '折扣值（百分比或固定金额）',
|
|||
|
|
min_order_amount DECIMAL(10,2) DEFAULT 0,
|
|||
|
|
max_discount_amount DECIMAL(10,2) COMMENT '最大折扣金额',
|
|||
|
|
usage_limit INT COMMENT '总使用次数限制',
|
|||
|
|
usage_limit_per_user INT DEFAULT 1,
|
|||
|
|
usage_count INT DEFAULT 0,
|
|||
|
|
valid_from DATETIME NOT NULL,
|
|||
|
|
valid_until DATETIME NOT NULL,
|
|||
|
|
is_active TINYINT DEFAULT 1,
|
|||
|
|
applicable_product_ids TEXT COMMENT '适用的产品ID列表，JSON格式',
|
|||
|
|
applicable_category_ids TEXT COMMENT '适用的分类ID列表，JSON格式',
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
INDEX idx_coupon_code (coupon_code),
|
|||
|
|
INDEX idx_valid_dates (valid_from, valid_until),
|
|||
|
|
INDEX idx_is_active (is_active)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 12. 用户优惠券关联表 (UserCoupons)
|
|||
|
|
-- 存储用户领取的优惠券
|
|||
|
|
-- 迷惑项：user_coupon_id、user_id、coupon_id
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE UserCoupons (
|
|||
|
|
user_coupon_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
user_id INT NOT NULL,
|
|||
|
|
coupon_id INT NOT NULL,
|
|||
|
|
status VARCHAR(20) DEFAULT 'available' COMMENT 'available/used/expired',
|
|||
|
|
received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
used_at DATETIME,
|
|||
|
|
order_id INT,
|
|||
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (coupon_id) REFERENCES Coupons(coupon_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE SET NULL,
|
|||
|
|
INDEX idx_user_id (user_id),
|
|||
|
|
INDEX idx_coupon_id (coupon_id),
|
|||
|
|
INDEX idx_status (status)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 13. 产品收藏表 (Wishlists)
|
|||
|
|
-- 存储用户收藏的产品
|
|||
|
|
-- 迷惑项：wishlist_id、product_id、user_id
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Wishlists (
|
|||
|
|
wishlist_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
user_id INT NOT NULL,
|
|||
|
|
product_id INT NOT NULL,
|
|||
|
|
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
note VARCHAR(255),
|
|||
|
|
priority INT DEFAULT 0 COMMENT '优先级排序',
|
|||
|
|
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
|
|||
|
|
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE,
|
|||
|
|
UNIQUE KEY uk_user_product (user_id, product_id),
|
|||
|
|
INDEX idx_user_id (user_id),
|
|||
|
|
INDEX idx_product_id (product_id),
|
|||
|
|
INDEX idx_added_at (added_at)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 14. 支付记录表 (Payments)
|
|||
|
|
-- 存储订单支付信息
|
|||
|
|
-- 迷惑项：payment_id、order_id、payment_amount
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Payments (
|
|||
|
|
payment_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
order_id INT NOT NULL,
|
|||
|
|
payment_method VARCHAR(30) NOT NULL,
|
|||
|
|
payment_amount DECIMAL(10,2) NOT NULL,
|
|||
|
|
transaction_id VARCHAR(100) UNIQUE,
|
|||
|
|
payment_status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/success/failed/refunded',
|
|||
|
|
payment_date DATETIME,
|
|||
|
|
refund_amount DECIMAL(10,2) DEFAULT 0,
|
|||
|
|
refund_reason VARCHAR(255),
|
|||
|
|
gateway_response TEXT COMMENT '支付网关响应',
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE RESTRICT,
|
|||
|
|
INDEX idx_order_id (order_id),
|
|||
|
|
INDEX idx_transaction_id (transaction_id),
|
|||
|
|
INDEX idx_payment_status (payment_status),
|
|||
|
|
INDEX idx_payment_date (payment_date)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 15. 管理员表 (Admins)
|
|||
|
|
-- 存储管理员信息
|
|||
|
|
-- 迷惑项：admin_id、admin_email、admin_role
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
CREATE TABLE Admins (
|
|||
|
|
admin_id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
admin_email VARCHAR(100) NOT NULL UNIQUE,
|
|||
|
|
admin_name VARCHAR(50) NOT NULL,
|
|||
|
|
password_hash VARCHAR(255) NOT NULL,
|
|||
|
|
admin_role VARCHAR(30) DEFAULT 'staff' COMMENT 'super_admin/admin/staff',
|
|||
|
|
is_active TINYINT DEFAULT 1,
|
|||
|
|
last_login DATETIME,
|
|||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
INDEX idx_admin_email (admin_email),
|
|||
|
|
INDEX idx_admin_role (admin_role)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
-- =====================================================
|
|||
|
|
-- 添加外键约束到Orders表的shipping_addr_id和billing_addr_id
|
|||
|
|
-- =====================================================
|
|||
|
|
|
|||
|
|
ALTER TABLE Orders
|
|||
|
|
ADD CONSTRAINT fk_orders_shipping_addr
|
|||
|
|
FOREIGN KEY (shipping_addr_id) REFERENCES Addresses(address_id) ON DELETE SET NULL;
|
|||
|
|
|
|||
|
|
ALTER TABLE Orders
|
|||
|
|
ADD CONSTRAINT fk_orders_billing_addr
|
|||
|
|
FOREIGN KEY (billing_addr_id) REFERENCES Addresses(address_id) ON DELETE SET NULL;
|