mysql搭建一个简单的在线商城项目数据库结构设计

users表需设email唯一索引和password_hash加密字段；categories与products须分离并支持无限级分类；orders与order_items必须拆分以保障事务一致性；所有外键列及常用查询字段须建索引。

用户表 users 必须包含邮箱唯一性与密码加密字段

在线商城中，users 表是登录、订单归属和权限控制的基础。不能只存用户名和明文密码——这在现代开发中属于严重安全漏洞。

实操建议：

• email 字段设为 UNIQUE，并加索引，避免重复注册；不要用 username 做主登录凭证，邮箱更稳定、可验证
• password_hash 字段类型用 VARCHAR(255)（足够存 bcrypt 或 Argon2 哈希值），严禁 password 字段
• 加 status 枚举字段（如 'active', 'disabled', 'pending_email_verify'），方便后续做账号生命周期管理
• 别忘了 created_at 和 updated_at，用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  nickname VARCHAR(50),
  status ENUM('active', 'disabled', 'pending_email_verify') DEFAULT 'pending_email_verify',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email)
);

商品与分类需分离为 categories 和 products 两张表

哪怕初期只有“手机”“配件”两个类目，也别把分类名直接写死在 products 表里——否则后期改名、多级分类、统计类目销量时全得硬编码修数据。

实操建议：

• categories 表保留 parent_id 字段（允许为 NULL），支持无限级分类（如 “手机 > iPhone > iPhone 15”）
• products 表不存完整分类路径，只存 category_id 外键，靠 JOIN 查层级
• price 用 DECIMAL(10,2)，不是 FLOAT —— 避免浮点数精度问题导致价格显示为 2999.99999999
• 加 stock 字段并设默认值 0，配合下单逻辑做库存扣减判断

CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  parent_id INT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  category_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  is_on_sale TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

订单表 orders 必须拆出 order_items 子表

一个订单买三件商品，如果把商品 ID、数量、单价全塞进 orders 表的 JSON 字段或逗号分隔字符串里，等于主动放弃数据库的约束能力、查询能力和事务一致性。

实操建议：

• orders 表只存买家 user_id、总金额 total_amount、状态 status（如 'pending', 'paid', 'shipped'）、收货信息等全局字段
• order_items 表每行对应一个商品项，含 order_id、product_id、quantity、unit_price（下单时快照价，防止商品改价影响历史订单）
• 在 order_items 上建联合索引 (order_id, product_id)，查某订单所有商品或某商品被哪些订单买过都快
• 外键务必设 ON DELETE RESTRICT，防止误删订单主记录导致子项孤立

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  status ENUM('pending', 'paid', 'shipped', 'cancelled') DEFAULT 'pending',
  shipping_name VARCHAR(100),
  shipping_phone VARCHAR(20),
  shipping_address TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT,
  FOREIGN KEY (product_id) REFERENCES products(id),
  INDEX idx_order_product (order_id, product_id)
);

上线前必须检查的三个外键与索引盲点

结构设计再合理，没加对索引或漏设外键约束，上线后查订单慢、删分类报错、连表 JOIN 超时，问题都出在细节。

容易被忽略但关键的点：

• 所有外键列（如 orders.user_id、order_items.product_id）必须单独建索引，MySQL 不会自动为外键列建索引
• products.category_id 和 categories.parent_id 都要加索引，否则按分类查商品或递归查子类目时性能断崖式下跌
• 如果计划支持搜索商品名，products.name 别只依赖普通 B-Tree 索引——考虑加 FULLTEXT 索引，或后续接入 Elasticsearch

实际跑通最小闭环：能注册用户 → 添加商品 → 下单 → 查该用户所有订单 → 查某订单明细。其余如购物车、优惠券、评价等，都是在这个骨架上长出来的，不是一开始就要堆满的。