391 lines
16 KiB
SQL
391 lines
16 KiB
SQL
-- =====================================================
|
||
-- 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;
|