電商類系統資料庫設計實戰：從業務模型到規範建表

專案背景與目標

• 模擬電商類網站核心業務
• 掌握日常建表操作與設計規範
• 完成業務模型到物理表的轉換

核心業務領域劃分

用戶域

• 用戶帳戶（tb_account）

商品域

• 商品分類（tb_goods_category）
• 商品詳情（tb_goods）

交易域

• 訂單主表（tb_order）
• 訂單明細（tb_order_item）

非核心輔助域

• 物流、供應商資訊（暫不建模）

表依賴關係與設計順序

1. tb_account（無依賴，優先建立）
2. tb_goods_category（自關聯，優先建立）
3. tb_goods（依賴分類表）
4. tb_order（依賴用戶表）
5. tb_order_item（依賴訂單表、商品表）

命名與字段設計規範

權限設計原則

• 最小權限：讀寫分離，普通帳號無 GRANT OPTION
• 粒度控制：實例級 > 資料庫級 > 表級 > 欄位級
• 密碼複雜度：線上環境強制隨機強密碼

完整建表 DDL（生產就緒版）

-- 用戶表
CREATE TABLE `tb_account` (
  `account_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
  `nick_name` VARCHAR(20) DEFAULT NULL COMMENT '暱稱',
  `true_name` VARCHAR(20) DEFAULT NULL COMMENT '真實姓名',
  `sex` CHAR(1) DEFAULT NULL COMMENT '性別',
  `mail_address` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
  `phone1` VARCHAR(20) NOT NULL COMMENT '主手機號',
  `phone2` VARCHAR(20) DEFAULT NULL COMMENT '備用手機',
  `password` VARCHAR(30) NOT NULL COMMENT '加密密碼',
  `create_time` DATETIME DEFAULT NULL COMMENT '註冊時間',
  `account_state` TINYINT(4) DEFAULT NULL COMMENT '狀態：1正常 0凍結',
  `last_login_time` DATETIME DEFAULT NULL COMMENT '最後登錄',
  `last_login_ip` VARCHAR(20) DEFAULT NULL COMMENT '最後登錄IP',
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶帳戶表';

-- 商品分類表（支援多級分類）
CREATE TABLE `tb_goods_category` (
  `category_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '分類ID',
  `category_level` SMALLINT(6) NOT NULL COMMENT '層級',
  `category_name` VARCHAR(500) DEFAULT NULL COMMENT '分類名稱',
  `upper_category_id` INT(11) NOT NULL COMMENT '父分類ID',
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分類表';

-- 商品表
CREATE TABLE `tb_goods` (
  `goods_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `goods_name` VARCHAR(100) NOT NULL COMMENT '商品名稱',
  `pic_url` VARCHAR(500) NOT NULL COMMENT '圖片URL',
  `store_quantity` INT(11) NOT NULL COMMENT '庫存數量',
  `goods_note` VARCHAR(800) DEFAULT NULL COMMENT '商品描述',
  `producer` VARCHAR(500) DEFAULT NULL COMMENT '生產廠商',
  `category_id` INT(11) NOT NULL COMMENT '分類ID',
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品詳情表';

-- 訂單主表
CREATE TABLE `tb_order` (
  `order_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
  `account_id` INT(11) NOT NULL COMMENT '用戶ID',
  `create_time` DATETIME DEFAULT NULL COMMENT '下單時間',
  `order_amount` DECIMAL(12,2) DEFAULT NULL COMMENT '訂單金額',
  `order_state` TINYINT(4) DEFAULT NULL COMMENT '狀態：1待付款 2已付款 3發貨 4完成 5取消',
  `update_time` DATETIME DEFAULT NULL COMMENT '最後更新',
  `order_ip` VARCHAR(20) DEFAULT NULL COMMENT '下單IP',
  `pay_method` VARCHAR(20) DEFAULT NULL COMMENT '支付方式',
  `user_notes` VARCHAR(500) DEFAULT NULL COMMENT '用戶備註',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單主表';

-- 訂單明細表
CREATE TABLE `tb_order_item` (
  `order_item_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '明細ID',
  `order_id` BIGINT(20) NOT NULL COMMENT '訂單ID',
  `goods_id` BIGINT(20) NOT NULL COMMENT '商品ID',
  `goods_quantity` INT(11) NOT NULL COMMENT '購買數量',
  `goods_amount` DECIMAL(12,2) DEFAULT NULL COMMENT '行金額',
  PRIMARY KEY (`order_item_id`),
  UNIQUE KEY `uk_order_goods` (`order_id`,`goods_id`) COMMENT '同一訂單不重複商品'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單明細表';

設計亮點解析

• 主鍵策略：業務主鍵 + 自增代理鍵，訂單/明細用 BIGINT 應對高併發
• 唯一約束：uk_order_goods 防止重複下單同一商品
• 冗余設計：訂單表保留 order_amount 避免關聯聚合
• 狀態機：order_state 明確定義流轉邏輯
• 審計欄位：create_time/update_time 全表標配

1. 上一篇文章： Linux 平台 MySQL 啟動與關閉六大方式深度對比
2. 下一篇文章： MySQL SQL 進階應用：排序、分組、連接、子查詢、DML 增強與內建函數實戰

MySQL 綜合教學

MySQL 分庫分表實戰指南：業務拆分、路由策略、中間件選型

前言 單表超 2000 萬行或單庫超 500GB 時，查詢性

Windows 下 MySQL 服務啟動與關閉命令完全指南

前言 在 Windows 環境部署 MySQL 時，透過服務

C++ 連接 MySQL 兩大方案深度解析：ADO 通用模式 vs 原生 API 高性能模式

場景：多數據庫統一訪問層 vs MySQL 專用高性能模塊

MySQL 常用函數詳解：數值、字串、日期處理實戰

數值函數 1. MOD(X, Y) - 餘數 回傳 X 除以