好的！我将按照从易到难的顺序为你设计6个业务场景的建表需求，完成后你可以将你的建表SQL语句发给我检查。以下是第一个阶段的题目：

检查要点预告：

当你完成建表后，我会重点检查这些方面：

1. 字段命名规范（是否使用下划线命名法）
2. 主键/外键设置是否合理
3. 索引是否在需要快速查询的字段上建立
4. 字段类型选择是否合理（如金额用DECIMAL，时间用DATETIME）
5. 是否满足业务约束（唯一性、非空、默认值等）
6. 是否考虑了扩展性（如状态字段是否预留足够枚举值）

你可以选择一个或多个题目进行练习，完成后将SQL语句发给我分析优化建议 😊

---

阶段 1：基础单表设计

题目 1：用户表设计

场景：电商平台的用户信息存储
字段需求：

• 必须包含：用户ID、用户名、手机号、注册时间、最后登录时间
• 可选包含：邮箱、性别、生日
  约束要求：
• 用户名唯一且非空
• 手机号符合11位数字格式
• 注册时间默认当前时间

我的回答

CREATE TABLE USER (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
	username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
	phone INT COMMENT NOT NULL  '手机号',
	create_time DATETIME	COMMENT '注册时间',
	latest_login_time DATETIME COMMENT '最后登录时间',
	mail VARCHAR(50) NOT	NULL COMMENT 'youxiang',
	gender TINYINT	UNSIGNED NOT NULL COMMENT '性别 1:男，2:女',
	birthday DATE COMMENT '生日',
)

你的建表语句整体结构正确，但存在一些需要优化的地方。以下是具体分析及改进建议：

---

问题分析

1. 字段类型问题

phone INT COMMENT NOT NULL  '手机号'

- ❌ INT类型最大只能存储10位数字（2147483647），无法存储11位手机号
- ❌ 字段约束顺序错误（COMMENT应在最后）

2. 非空约束问题

mail VARCHAR(50) NOT NULL

- ❌ 题目说明邮箱是可选字段，不应强制非空

3. 默认值缺失

create_time DATETIME COMMENT '注册时间'

- ❌ 缺少`DEFAULT CURRENT_TIMESTAMP`设置

4. 表名风险

CREATE TABLE USER

- ⚠️ USER是MySQL 8.0的保留关键字，建议使用反引号包裹或改用`users`

5. 其他细节
   • ❗ 最后一个字段定义后不应有逗号
   • ❗ 注释拼写错误：'youxiang'应为'邮箱'

---

优化后完整SQL

CREATE TABLE `user` (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    phone CHAR(11) NOT NULL COMMENT '手机号' CHECK (LENGTH(phone) = 11),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    latest_login_time DATETIME COMMENT '最后登录时间',
    mail VARCHAR(50) COMMENT '邮箱',
    gender TINYINT UNSIGNED COMMENT '性别 1:男，2:女',
    birthday DATE COMMENT '生日'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

改进说明

1. 手机号字段优化
   • 改用CHAR(11)类型确保长度固定
   • 添加CHECK (LENGTH(phone) = 11)保证11位长度（MySQL 8.0+支持）
   • 注意：低版本MySQL可使用触发器校验
2. 合理设置非空约束
   • 仅username、phone、id等核心字段强制非空
   • 邮箱、性别、生日等可选字段允许NULL
3. 增强可维护性
   • 显式指定存储引擎和字符集
   • 使用反引号包裹表名避免关键字冲突
4. 扩展建议

ALTER TABLE `user` ADD INDEX idx_phone (phone); -- 手机号查询索引
ALTER TABLE `user` ADD CONSTRAINT uni_mail UNIQUE (mail); -- 如果需要邮箱唯一

---

检查要点验证

检查项	达标情况
下划线命名法	✅
主键设置合理	✅
手机号字段类型正确	✅
注册时间默认值	✅
非空约束合理	✅
状态字段可扩展性	不涉及

建议在真实业务中还可考虑：手机号加密存储、最后登录时间更新机制、性别使用枚举类型等进阶设计。接下来可以做第二题练习了~ 🚀

---

阶段 2：基础关联表设计

题目 2：商品表设计

场景：商品信息管理
字段需求：

• 必须包含：商品ID、商品名称、分类ID、价格、库存、上架状态、创建时间
• 可选包含：商品描述、封面图URL
  约束要求：
• 价格必须大于0
• 上架状态用枚举值（0下架/1上架）
• 分类ID需要能关联到后续可能新建的分类表

我的回答

CREATE TABLE sku (
	sku_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
	sku_name VARCHAR(20) UNIQUE NOT NULL COMMENT '商品名称',
	sku_class TINYINT UNSIGNED NOT NULL COMMENT '商品分类ID',
	price INT UNSIGNED NOT NULL COMMENT '价格',
	stock INT NOT NULL COMMENT '库存',
	sku_status TINYINT DEFAULT 0 COMMENT '上架状态, 0、未上架 1、已上架',
	sku_desc VARCHAR(200) COMMENT '商品描述',
	sku_image VARCHAR(500) COMMENT '封面图URL',
)

CREATE TABLE sku_class (
	class_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
	class_name VARCHAR(20) UNIQUE NOT NULL COMMENT '分类名称',
)

你的建表语句基本结构正确，但存在若干需要改进的关键点。以下是具体分析和优化建议：

---

问题分析

1. 缺失关键字段

-- 原表缺少创建时间字段
CREATE TABLE sku (...)

❌ 题目明确要求必须包含创建时间字段

2. 外键缺失

sku_class TINYINT UNSIGNED NOT NULL COMMENT '商品分类ID'

❌ 未与分类表建立外键关联
❌ 字段类型不匹配（分类表主键是INT，此处用TINYINT）

3. 金额字段类型不当

price INT UNSIGNED NOT NULL

❌ 价格应使用DECIMAL类型避免精度丢失

4. 状态字段定义偏差

sku_status TINYINT DEFAULT 0 COMMENT '上架状态, 0、未上架 1、已上架'

❗ 题目要求状态值0下架/1上架，存在语义偏差

5. 基础约束缺失

stock INT NOT NULL  -- 允许负库存

❌ 缺少库存非负约束

6. 语法错误

sku_image VARCHAR(500) COMMENT '封面图URL',
)  -- 最后多出逗号

---

优化后完整SQL

-- 商品分类表
CREATE TABLE sku_class (
    class_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
    class_name VARCHAR(20) UNIQUE NOT NULL COMMENT '分类名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品表
CREATE TABLE sku (
    sku_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
    sku_name VARCHAR(20) UNIQUE NOT NULL COMMENT '商品名称',
    class_id INT UNSIGNED NOT NULL COMMENT '分类ID',
    price DECIMAL(10,2) UNSIGNED NOT NULL CHECK (price > 0) COMMENT '价格',
    stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
    sku_status TINYINT(1) NOT NULL DEFAULT 0 COMMENT '上架状态 0:下架 1:上架',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    sku_desc VARCHAR(200) COMMENT '商品描述',
    sku_image VARCHAR(500) COMMENT '封面图URL',
    FOREIGN KEY (class_id) REFERENCES sku_class(class_id),
    INDEX idx_class_id (class_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

改进说明

1. 字段完善
   • 补充create_time字段并设置默认时间戳
   • 统一分类ID字段名为class_id（外键字段建议与主键同名）
2. 外键关联

FOREIGN KEY (class_id) REFERENCES sku_class(class_id)

- 建立分类表与商品表的关联
- 添加分类ID索引提升查询效率

3. 金额处理规范
   • 改用DECIMAL(10,2)存储金额（支持小数点后两位）
   • 添加CHECK (price > 0)约束（MySQL 8.0+生效）
4. 状态字段优化
   • 修正注释与题目要求一致
   • 明确字段长度TINYINT(1)
5. 扩展建议

ALTER TABLE sku ADD FULLTEXT INDEX idx_sku_desc (sku_desc); -- 商品描述全文索引

---

检查要点验证

检查项	达标情况
外键约束	✅
金额类型正确	✅
创建时间字段	✅
状态枚举值正确	✅
库存非负约束	✅
索引设置合理	✅

下一步可以尝试设计订单表（题目3），注意处理好用户、商品、订单三者的关联关系 💡

---

阶段 3：核心业务表设计

题目 3：订单表设计

场景：用户下单购买商品
字段需求：

• 必须包含：订单ID、用户ID、商品ID、订单金额、订单状态、创建时间
• 可选包含：支付时间、收货地址
  约束要求：
• 订单金额必须大于0
• 订单状态使用枚举（0待支付/1已支付/2已取消）
• 用户ID和商品ID需要外键约束

我的回答

CREATE TABLE `order` (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
	user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
	sku_id INT UNSIGNED NOT NULL COMMENT '商品ID',
	amount DECIMAL UNSIGNED NOT NULL COMMENT '订单金额',
	order_status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态 0.待支付 1.已支付 2.已取消',
	create_time DATETIME DEFAULT CURRENT_TIME COMMENT '创建时间',
	pay_time DATETIME COMMENT '支付时间',
	location VARCHAR(100) COMMENT '收货地址', 
)

你的订单表设计基本可用，但存在几个需要优化的重要细节。以下是具体分析和改进建议：

---

问题分析

1. 表名风险

CREATE TABLE `order`

⚠️ 虽然使用反引号可以规避，但order是SQL标准保留字，建议改用orders更安全

2. 外键缺失

user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
sku_id INT UNSIGNED NOT NULL COMMENT '商品ID'

❌ 题目明确要求用户ID和商品ID需要外键约束

3. 金额精度问题

amount DECIMAL UNSIGNED NOT NULL

❌ DECIMAL未指定精度（如DECIMAL(10,2)）

4. 时间字段问题

DEFAULT CURRENT_TIME

❌ CURRENT_TIME只返回时间部分（如’18:30:00’），应改用CURRENT_TIMESTAMP

5. 地址字段限制

location VARCHAR(100)

⚠️ 实际业务中收货地址通常需要更长的存储空间

6. 语法错误

location VARCHAR(100) COMMENT '收货地址', 
)  -- 最后多出逗号

---

优化后完整SQL

CREATE TABLE orders (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    sku_id INT UNSIGNED NOT NULL COMMENT '商品ID',
    amount DECIMAL(10,2) UNSIGNED NOT NULL CHECK (amount > 0) COMMENT '订单金额',
    order_status TINYINT(1) NOT NULL DEFAULT 0 COMMENT '订单状态 0:待支付 1:已支付 2:已取消',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    pay_time DATETIME COMMENT '支付时间',
    location VARCHAR(255) COMMENT '收货地址',
    FOREIGN KEY (user_id) REFERENCES `user`(id),
    FOREIGN KEY (sku_id) REFERENCES sku(sku_id),
    INDEX idx_user_status (user_id, order_status),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

改进说明

1. 外键完整性

FOREIGN KEY (user_id) REFERENCES `user`(id),
FOREIGN KEY (sku_id) REFERENCES sku(sku_id)

- 确保与用户表、商品表的关联完整性

2. 金额精确存储

DECIMAL(10,2)  -- 支持最大9,999,999.99的金额
CHECK (amount > 0)  -- 保证金额有效性

3. 索引优化

INDEX idx_user_status (user_id, order_status)  -- 用户订单状态查询
INDEX idx_create_time (create_time)  -- 时间范围查询

4. 业务扩展建议

-- 真实业务中可能需要：
ALTER TABLE orders ADD COLUMN update_time DATETIME ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE orders ADD INDEX idx_status_time (order_status, create_time);

---

检查要点验证

检查项	达标情况
外键约束	✅
金额类型正确	✅
状态枚举值正确	✅
时间字段默认值	✅
地址字段合理	✅
索引设置合理	✅

下一步可以尝试设计评论表（题目4），注意处理用户-商品的唯一评论约束 📝

您提到的非常正确！在实际业务中，特别是互联网高并发场景下，通常不推荐使用物理外键（FOREIGN KEY），主要原因包括：

1. 性能影响：外键约束会带来额外的锁竞争
2. 分库分表困难：物理外键无法跨数据库/分片
3. 数据迁移复杂：强关联导致数据维护成本高
4. ORM适配问题：部分框架对外键支持不友好

以下是基于逻辑外键的优化方案（同时补充更多实际业务细节）：

---

优化后的订单表设计

CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号（业务唯一标识）',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    sku_id INT UNSIGNED NOT NULL COMMENT '商品ID',
    amount DECIMAL(12,2) UNSIGNED NOT NULL COMMENT '订单金额（单位：元）',
    order_status TINYINT(2) NOT NULL DEFAULT 0 COMMENT '订单状态 0:待支付 1:已支付 2:已取消 3:已退款',
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间（精确到毫秒）',
    pay_time DATETIME(3) COMMENT '支付时间（精确到毫秒）',
    delivery_address JSON COMMENT '收货地址（结构化存储）',
    INDEX idx_user_id (user_id),
    INDEX idx_sku_id (sku_id),
    INDEX idx_order_no (order_no),
    INDEX idx_status_ctime (order_status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';

---

改进要点说明

1. 去物理外键化
   • 移除 FOREIGN KEY 约束
   • 保留 user_id/sku_id 字段作为逻辑外键
   • 通过应用层保证数据一致性
2. 核心业务字段增强

order_no VARCHAR(32) NOT NULL UNIQUE  -- 业务唯一号（通常用雪花算法生成）
delivery_address JSON  -- 存储结构化的地址信息，例如：
/*
{
  "province": "浙江省",
  "city": "杭州市",
  "district": "余杭区",
  "street": "文一西路969号",
  "contact": "张三",
  "phone": "13800138000"
}
*/

3. 高性能设计
   • 主键改用 BIGINT（应对海量数据）
   • 时间字段精确到毫秒 DATETIME(3)
   • 金额字段扩展为 DECIMAL(12,2)（支持最大金额：9999999999.99）
4. 索引优化
   • 订单号唯一索引：高频查单操作
   • 状态+时间联合索引：后台订单列表查询
   • 用户ID单列索引：用户中心订单查询

---

逻辑外键的保障措施

1. 应用层校验

// 伪代码示例：创建订单前校验
public void createOrder(Long userId, Long skuId) {
    if (!userRepository.existsById(userId)) {
        throw new BusinessException("用户不存在");
    }
    if (!skuRepository.isOnSale(skuId)) {
        throw new BusinessException("商品已下架");
    }
    // 创建订单逻辑...
}

2. 异步对账机制

-- 定期执行数据校验SQL
SELECT o.* 
FROM orders o
LEFT JOIN user u ON o.user_id = u.id
WHERE u.id IS NULL;  -- 找出脏数据

3. 数据库层面弱约束（可选）

ALTER TABLE orders 
  ADD CONSTRAINT chk_user_id 
  CHECK (user_id REGEXP '^[1-9]\d*$');  -- 至少保证是正整数

---

实际业务扩展建议

1. 订单明细表拆分

CREATE TABLE order_items (
    id BIGINT UNSIGNED PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    sku_id INT UNSIGNED NOT NULL,
    quantity INT NOT NULL COMMENT '购买数量',
    price DECIMAL(10,2) NOT NULL COMMENT '成交单价'
);

（支持一个订单多个商品）

2. 状态变更流水表

CREATE TABLE order_status_log (
    id BIGINT UNSIGNED PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    old_status TINYINT,
    new_status TINYINT NOT NULL,
    operator VARCHAR(32) COMMENT '操作人（系统/用户ID）',
    operate_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
);

---

这样的设计既符合互联网行业的最佳实践，又能满足高并发场景下的性能要求。接下来可以尝试设计评论表（题目4），重点注意用户-商品的唯一评论约束如何实现 👨💻

---

阶段 4：复杂业务表设计

题目 4：评论表设计

场景：用户对商品发表评价
字段需求：

• 必须包含：评论ID、用户ID、商品ID、评分（1-5分）、评论内容、发布时间
• 可选包含：回复内容、点赞数
  约束要求：
• 同一个用户对同一商品只能评论一次
• 发布时间默认当前时间
• 需要建立合适的索引

以下是针对评论表设计的分析和优化建议：

---

问题分析

1. 核心约束缺失

CREATE TABLE comments (...)

❌ 缺少UNIQUE(user_id, sku_id)约束，无法阻止同一用户对同一商品多次评论

2. 评分范围失控

score TINYINT NOT NULL COMMENT '评分 （1-5）'

❌ 未限制评分必须为1-5分

3. 回复设计缺陷

reply_id INT COMMENT '回复ID'  -- comments表
CREATE TABLE reply (...)      -- 独立回复表

❌ 无法体现回复层级（是对评论的回复还是对回复的回复？）
❌ 主键类型不匹配（comments.reply_id是INT，reply.id是BIGINT）

4. 字段命名不规范

detail VARCHAR(500) NOT NULL '评论内容'  -- 应更明确

5. 索引缺失

-- 未建立商品评分排序索引
-- 未建立时间倒排索引

---

优化后完整方案

评论表设计（支持三级回复）

CREATE TABLE comments (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    sku_id INT UNSIGNED NOT NULL COMMENT '商品ID',
    score TINYINT UNSIGNED NOT NULL CHECK (score BETWEEN 1 AND 5) COMMENT '评分(1-5分)',
    content TEXT NOT NULL COMMENT '评论内容' CHECK (LENGTH(content) <= 500),
    parent_id BIGINT UNSIGNED DEFAULT 0 COMMENT '父评论ID（0表示主评论）',
    like_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '点赞数',
    is_top TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否置顶 0:否 1:是',
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    update_time DATETIME(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
    UNIQUE KEY uniq_user_sku (user_id, sku_id),  -- 唯一评论约束
    KEY idx_sku_score (sku_id, score),            -- 商品评分查询
    KEY idx_create_time (create_time DESC),       -- 时间倒排索引
    KEY idx_parent (parent_id)                   -- 回复查询加速
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

回复表设计（可选方案）

-- 方案1：与评论表合并（推荐）
-- 使用 parent_id 字段实现多级回复（parent_id=0表示主评论）
-- 无需单独建表

-- 方案2：独立回复表（需处理级联关系）
CREATE TABLE comment_replies (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    comment_id BIGINT UNSIGNED NOT NULL COMMENT '主评论ID',
    reply_user_id INT UNSIGNED NOT NULL COMMENT '回复用户ID',
    content VARCHAR(500) NOT NULL,
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    FOREIGN KEY (comment_id) REFERENCES comments(id) -- 逻辑外键
) ENGINE=InnoDB;

---

改进说明

1. 核心业务约束
   • 通过UNIQUE(user_id, sku_id)保证每个用户对商品只能评论一次
   • CHECK (score BETWEEN 1 AND 5)限制评分范围
2. 高效回复设计
   • 使用parent_id字段实现多级回复（自关联设计）
     • parent_id=0：主评论
     • parent_id=主评论ID：一级回复
     • parent_id=回复ID：二级回复
3. 增强功能支持

is_top TINYINT(1) NOT NULL DEFAULT 0  -- 运营置顶功能
like_count INT UNSIGNED DEFAULT 0     -- 原子操作优化点赞

4. 性能优化
   • idx_sku_score索引支持商品评分排序
   • create_time DESC索引优化最新评论展示
   • 使用TEXT类型预留扩展空间（配合长度校验）

---

高级扩展建议

1. 点赞明细表

CREATE TABLE comment_likes (
    comment_id BIGINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (comment_id, user_id)
);

2. 敏感词过滤

ALTER TABLE comments 
  ADD COLUMN is_reviewed TINYINT(1) DEFAULT 0 COMMENT '审核状态';

3. 数据统计优化

-- 定期刷新到统计表
CREATE TABLE sku_comment_summary (
    sku_id INT UNSIGNED PRIMARY KEY,
    total_score INT UNSIGNED NOT NULL,
    comment_count INT UNSIGNED NOT NULL
);

---

检查要点验证

检查项	达标情况
唯一评论约束	✅
评分范围控制	✅
时间默认值	✅
合理索引设置	✅
回复层级设计	✅

这样设计既能满足基础需求，又为后续扩展留出了空间。接下来可以尝试权限系统设计（题目5） 👨💻

---

阶段 5：权限系统设计

题目 5：权限表设计

场景：后台管理系统权限控制
字段需求：

• 必须包含：角色表、权限表、用户角色关联表
• 角色表字段：角色ID、角色名称、描述
• 权限表字段：权限ID、权限名称（如"用户管理"）、权限代码（如"user:edit"）
  约束要求：
• 实现多对多关系（一个角色多个权限，一个权限属于多个角色）
• 权限代码需要唯一

我的回答

CREATE TABLE roles (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
	role_name VARCHAR(10) UNIQUE NOT NULL COMMENT '角色名称',
	role_desc VARCHAR(50) NOT NULL COMMENT '角色描述',
	create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time DATETIME COMMENT '修改时间',
);

CREATE TABLE permissions (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
	permi_name VARCHAR(10) UNIQUE NOT NULL COMMENT '权限名称',
	permi_code VARCHAR(20) UNIQUE NOT NULL COMMENT '权限代码',
	create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time DATETIME COMMENT '修改时间',
);

CREATE TABLE roles_permissions (
	role_id INT UNSIGNED COMMENT '角色ID',
	permi_id INT UNSIGNED COMMENT '权限ID',
)

以下是针对权限系统设计的优化分析和改进方案：

---

问题分析

1. 字段长度不足

role_name VARCHAR(10)     -- 实际业务中可能不够（如"超级管理员"）
permi_name VARCHAR(10)    -- 权限名称需要更长（如"财务报表导出"）
permi_code VARCHAR(20)    -- 权限代码可能不足（如"report:finance:export"）

2. 关键约束缺失

CREATE TABLE roles_permissions (...)

❌ 缺少联合主键
❌ 未设置外键约束（即使是逻辑外键）

3. 字段命名规范

permi_name  -- 建议使用完整单词permission缩写

4. 更新时间自动化

update_time DATETIME  -- 未设置自动更新

5. 扩展性不足
   ❌ 缺少权限类型字段（如菜单权限/按钮权限）

---

优化后完整方案

角色表（增强版）

CREATE TABLE role (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
    role_code VARCHAR(32) UNIQUE NOT NULL COMMENT '角色编码（唯一业务标识）',
    role_name VARCHAR(32) NOT NULL COMMENT '角色名称',
    description VARCHAR(128) COMMENT '角色描述',
    is_system TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否系统角色 0:否 1:是',
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    update_time DATETIME(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
    INDEX idx_role_code (role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

权限表（增强版）

CREATE TABLE permission (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
    perm_code VARCHAR(64) UNIQUE NOT NULL COMMENT '权限代码（如system:user:add）',
    perm_name VARCHAR(64) NOT NULL COMMENT '权限名称',
    perm_type TINYINT(1) NOT NULL DEFAULT 1 COMMENT '权限类型 1:菜单 2:按钮 3:接口',
    parent_id INT UNSIGNED DEFAULT 0 COMMENT '父权限ID（0表示根节点）',
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    update_time DATETIME(3) ON UPDATE CURRENT_TIMESTAMP(3),
    INDEX idx_perm_code (perm_code),
    INDEX idx_parent (parent_id)
) COMMENT='权限表（支持树形结构）';

角色权限关联表（优化版）

CREATE TABLE role_permission (
    role_id INT UNSIGNED NOT NULL COMMENT '角色ID',
    perm_id INT UNSIGNED NOT NULL COMMENT '权限ID',
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (role_id, perm_id),  -- 联合主键防止重复
    INDEX idx_perm (perm_id)
) COMMENT='角色权限关联表';

---

改进说明

1. 增强业务标识
   • 角色表增加role_code字段作为业务唯一标识（比ID更稳定）
   • 权限表perm_code扩展长度并采用分层命名（如system:user:edit）
2. 树形权限支持

parent_id INT UNSIGNED DEFAULT 0  -- 实现权限层级结构
perm_type TINYINT(1)  -- 区分权限类型

3. 系统角色标识

is_system TINYINT(1)  -- 防止误删核心角色

4. 时间自动化管理

update_time DATETIME(3) ON UPDATE CURRENT_TIMESTAMP(3)  -- 自动更新时间

5. 高性能设计
   • 所有时间字段精确到毫秒
   • 关联表使用联合主键+单列索引组合

---

扩展建议

1. 用户角色关联表

CREATE TABLE user_role (
    user_id INT UNSIGNED NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (user_id, role_id)
);

2. 权限日志表

CREATE TABLE permission_log (
    id BIGINT UNSIGNED PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    perm_code VARCHAR(64) NOT NULL,
    access_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    result TINYINT(1) COMMENT '访问结果 0:拒绝 1:允许'
);

3. 数据初始化SQL示例

INSERT INTO permission (perm_code, perm_name, perm_type) VALUES
  ('system', '系统管理', 1),
  ('system:user', '用户管理', 1),
  ('system:user:add', '新增用户', 2),
  ('system:role', '角色管理', 1);

---

检查要点验证

检查项	达标情况
多对多关系实现	✅
权限代码唯一性	✅
时间字段自动化	✅
联合主键设置	✅
树形结构支持	✅

这种设计既满足基础需求，又能支持中大型系统的权限管理需求。接下来可以尝试物流信息表设计（题目6）🚚

---

阶段 6：复杂数据关系设计

题目 6：物流信息表设计

场景：订单物流信息追踪
字段需求：

• 必须包含：物流ID、订单ID、物流公司、运单号、当前状态、更新时间
• 可选包含：预计到达时间、签收人
  约束要求：
• 状态使用枚举（0已发货/1运输中/2已签收/3异常）
• 同一个订单可能有多个物流状态记录
• 需要记录每次状态变更的时间

我的回答

CREATE TABLE logistics (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '物流ID',
	order_id INT UNSIGNED NOT NULL COMMENT '订单ID',
	company VARCHAR(128) NOT NULL COMMENT '物流公司',
	log_no VARCHAR(64) NOT NULL COMMENT '运单号',
	log_status TINYINT(1) NOT NULL COMMENT '物流状态 0.已发货 1.运输中 2.已签收 3.异常',
	create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time DATETIME(3) ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	estimated_arrival_time DATETIME(3) COMMENT '预计到达时间',
	signed_people VARCHAR(32) COMMENT '签收人',
	INDEX idx_order_log(order_id, log_status)
)

以下是针对物流信息表的优化分析和建议：

---

问题分析

1. 状态变更追踪缺失

update_time DATETIME(3) ON UPDATE CURRENT_TIMESTAMP

❌ 只能记录最新更新时间，无法追溯完整状态变更历史

2. 运单唯一性风险

log_no VARCHAR(64) NOT NULL

❌ 未防止同一物流公司+运单号的重复录入

3. 字段命名优化

log_status  -- "log"易与日志混淆
log_no      -- 建议更明确的命名

4. 签收人字段限制

signed_people VARCHAR(32)  -- 实际业务可能不足

---

优化后完整方案

物流主表

CREATE TABLE logistics (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '物流ID',
    order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    company_code VARCHAR(32) NOT NULL COMMENT '物流公司编码（关联字典表）',
    tracking_no VARCHAR(64) NOT NULL COMMENT '运单号',
    current_status TINYINT(2) NOT NULL DEFAULT 0 COMMENT '当前状态 0:已发货 1:运输中 2:已签收 3:异常 4:退回',
    create_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    estimated_arrival DATETIME(3) COMMENT '预计到达时间',
    signed_by VARCHAR(64) COMMENT '签收人',
    signed_phone VARCHAR(20) COMMENT '签收人电话',
    INDEX idx_order (order_id),
    UNIQUE uniq_tracking (company_code, tracking_no), -- 防止重复运单
    INDEX idx_tracking (tracking_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

状态变更流水表（核心补充）

CREATE TABLE logistics_status_log (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    logistics_id BIGINT UNSIGNED NOT NULL COMMENT '物流记录ID',
    old_status TINYINT(2) COMMENT '原状态',
    new_status TINYINT(2) NOT NULL COMMENT '新状态',
    operator VARCHAR(32) COMMENT '操作人（系统/快递员ID）',
    operate_time DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    remark VARCHAR(200) COMMENT '状态备注（如异常原因）',
    INDEX idx_logistics (logistics_id),
    INDEX idx_op_time (operate_time DESC)
) COMMENT '物流状态变更流水表';

---

改进说明

1. 增强业务约束

UNIQUE uniq_tracking (company_code, tracking_no) -- 唯一运单约束
current_status TINYINT(2)  -- 预留更多状态值

2. 结构化物流公司

company_code VARCHAR(32)  -- 关联物流公司字典表

3. 签收信息增强

signed_by VARCHAR(64)      -- 扩展签收人姓名
signed_phone VARCHAR(20)   -- 记录签收联系方式

4. 状态追踪专业化
   • 主表只保留当前状态
   • 通过流水表完整记录状态变更历史

---

扩展建议

1. 物流公司字典表

CREATE TABLE logistics_company (
    code VARCHAR(32) PRIMARY KEY COMMENT '公司编码',
    name VARCHAR(64) NOT NULL COMMENT '公司名称',
    api_url VARCHAR(255) COMMENT '物流查询接口地址',
    is_enabled TINYINT(1) DEFAULT 1 COMMENT '是否启用'
);

2. 物流轨迹表

CREATE TABLE logistics_trace (
    logistics_id BIGINT UNSIGNED NOT NULL,
    trace_time DATETIME(3) NOT NULL COMMENT '节点时间',
    location VARCHAR(100) COMMENT '节点位置',
    description VARCHAR(200) COMMENT '节点描述（如"到达杭州分拣中心"）',
    INDEX idx_trace_time (trace_time DESC)
);

---

检查要点验证

检查项	达标情况
状态枚举值	✅
多状态记录能力	✅
时间变更记录	✅
运单唯一性约束	✅
查询索引优化	✅

该设计既能满足基础需求，又具备实际业务扩展性。建议在应用层实现：

1. 状态变更时自动写入流水表（可通过触发器或代码实现）
2. 物流公司信息维护后台
3. 物流轨迹对接第三方接口（如快递100）

---