176 lines
6.7 KiB
Markdown
176 lines
6.7 KiB
Markdown
# 04 — 数据库表设计规则
|
||
|
||
> **适用**：所有涉及建表、改表、索引设计的 AI 开发任务。
|
||
|
||
---
|
||
|
||
## 命名规则
|
||
|
||
| 元素 | 规则 | 示例（正确 → 错误） |
|
||
| -------- | ------------- | -------------------------------------------------------- |
|
||
| **表名** | 小写，下划线分隔，单数名词 | `task_apply` → ❌ `TaskApply` `task_applies` |
|
||
| **字段名** | 小写，下划线分隔 | `create_time` `task_id` → ❌ `createdAt` `taskId` |
|
||
| **主键** | 统一用 `id` | `id` → ❌ `task_id` `pk_id` |
|
||
| **外键** | `关联表名_id` | `task_id` `user_id` → ❌ `tid` `uid` |
|
||
| **布尔字段** | `del_flag` 或明确业务含义的 `is_` 前缀 | `del_flag` `is_active` → ❌ `deleted` `status` |
|
||
| **时间字段** | `_time` 后缀 | `create_time` `update_time` → ❌ `created_at` `updateTime` |
|
||
| **金额字段** | `_amount` 后缀 | `reward_amount` → ❌ `reward` `price` |
|
||
| **索引名** | `idx_表名_字段` | `idx_task_status` → ❌ `index1` `task_status_idx` |
|
||
| **唯一索引** | `uk_表名_字段` | `uk_user_phone` → ❌ `uq_phone` |
|
||
| **关联表** | 两表名用下划线连接 | `task_apply` `user_role` → ❌ `apply_task` |
|
||
|
||
### 长度控制
|
||
|
||
表名超过 3 个单词或 30 个字符时缩写。前缀也要缩成一个单词。
|
||
|
||
| 完整 | 缩写 | JeecgBoot 实际案例 |
|
||
|------|------|-------------------|
|
||
| department | dept | `sys_depart_role_permission` → `sys_dept_role_perm` |
|
||
| permission | perm | 同上 |
|
||
| announcement | notice | `sys_announcement_send` → `sys_notice_send` |
|
||
| message | msg | `sys_message_template` → `sys_msg_template` |
|
||
|
||
```sql
|
||
-- ❌ JeecgBoot 原版 — 太长
|
||
sys_depart_role_permission -- 26 字符
|
||
sys_permission_data_rule -- 24 字符
|
||
|
||
-- ✅ 缩写后
|
||
sys_dept_role_perm -- 19 字符
|
||
sys_perm_data_rule -- 20 字符
|
||
```
|
||
|
||
---
|
||
|
||
## 字段类型规范
|
||
|
||
| 场景 | 类型 | 说明 |
|
||
|------|------|------|
|
||
| 主键 | `BIGINT` 自增 或 `VARCHAR(32)` | 推荐 BIGINT 自增；分布式用雪花ID |
|
||
| 短文本 | `VARCHAR(N)` | 姓名(50)、标题(200)、URL(500) |
|
||
| 长文本 | `TEXT` / `LONGTEXT` | 文章、JSON、描述 |
|
||
| 金额 | `DECIMAL(12,2)` | **禁止**用 FLOAT/DOUBLE |
|
||
| 状态/类型 | `VARCHAR(20)` 或 `TINYINT` | 枚举值，必须有注释说明 |
|
||
| 布尔 | `TINYINT(1)` | 0=否 1=是，注释必须写清含义 |
|
||
| 时间 | `DATETIME` | **禁止**用 TIMESTAMP（2038 问题） |
|
||
| 日期 | `DATE` | 生日、截止日期 |
|
||
| 数量 | `INT` 或 `BIGINT` | |
|
||
|
||
---
|
||
|
||
## 每张表必须有的字段
|
||
|
||
```sql
|
||
CREATE TABLE xxx (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
|
||
-- [业务字段]
|
||
create_by VARCHAR(32) NULL DEFAULT NULL COMMENT '创建人',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_by VARCHAR(32) NULL DEFAULT NULL COMMENT '更新人',
|
||
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-正常 1-已删除',
|
||
INDEX idx_xxx_create_time (create_time)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表说明';
|
||
```
|
||
|
||
**强制要求**：
|
||
- `create_by` — 创建人，建议保留；系统自动填充时可为空
|
||
- `create_time` — 创建时间，必须有
|
||
- `update_by` — 更新人，建议保留；系统自动填充时可为空
|
||
- `update_time` — 更新时间，必须有（`ON UPDATE CURRENT_TIMESTAMP` 自动更新）
|
||
- `del_flag` — 逻辑删除标记，除非明确不需要软删除
|
||
- 每张表必须有 `COMMENT`
|
||
- 引擎统一 `InnoDB`，字符集 `utf8mb4`
|
||
|
||
---
|
||
|
||
## 索引规则
|
||
|
||
| 规则 | 说明 |
|
||
|------|------|
|
||
| **主键** | 每张表必须有，推荐自增 BIGINT |
|
||
| **外键** | 必须加索引（MySQL 自动加，但显式声明更清晰） |
|
||
| **查询条件** | WHERE 条件字段必须加索引 |
|
||
| **排序字段** | ORDER BY 字段考虑加索引 |
|
||
| **联合索引** | 遵循最左前缀原则，区分度高的在前 |
|
||
| **唯一约束** | 业务唯一字段必须加唯一索引 |
|
||
| **禁止** | ❌ 不加索引的 WHERE / JOIN / ORDER BY |
|
||
| **禁止** | ❌ 在大字段（TEXT/BLOB）上建索引 |
|
||
| **禁止** | ❌ 过多索引（单表建议 ≤ 5 个） |
|
||
|
||
### 索引命名
|
||
|
||
```sql
|
||
INDEX idx_表名_字段 -- 普通索引
|
||
UNIQUE uk_表名_字段 -- 唯一索引
|
||
INDEX idx_表名_字段1_字段2 -- 联合索引
|
||
```
|
||
|
||
---
|
||
|
||
## 字段约束规则
|
||
|
||
| 规则 | 示例 |
|
||
|------|------|
|
||
| 主键 | `PRIMARY KEY` 或 `NOT NULL AUTO_INCREMENT` |
|
||
| 非空 | 业务必填字段 `NOT NULL` |
|
||
| 默认值 | 有默认值的字段 `DEFAULT xxx`，**禁止**依赖代码设默认值 |
|
||
| 唯一 | 业务唯一字段 `UNIQUE` |
|
||
| 外键 | 尽量用逻辑外键（代码维护），**不推荐**物理外键（`FOREIGN KEY`） |
|
||
|
||
---
|
||
|
||
## 禁止清单
|
||
|
||
| ❌ 禁止 | 原因 |
|
||
|---------|------|
|
||
| 表名/字段名用大写或驼峰 | 跨平台兼容 |
|
||
| 金额用 FLOAT/DOUBLE | 精度丢失 |
|
||
| 时间用 TIMESTAMP | 2038 年溢出 |
|
||
| 用物理外键 | 分库分表/数据迁移困难 |
|
||
| 不加注释 | 无人知道字段含义 |
|
||
| 字符串代替布尔 | 空间浪费，索引效率低 |
|
||
| 大表无索引 | 性能灾难 |
|
||
| 字段用 NULL 代替默认值 | 查询需额外处理 `IS NULL` |
|
||
| 在代码里设默认值 | 数据一致性依赖应用层 |
|
||
|
||
---
|
||
|
||
## 修改表规则
|
||
|
||
```sql
|
||
-- ✅ 正确：显式命名约束
|
||
ALTER TABLE task ADD COLUMN priority TINYINT NOT NULL DEFAULT 0 COMMENT '优先级：0-普通 1-紧急';
|
||
ALTER TABLE task ADD INDEX idx_task_priority (priority);
|
||
|
||
-- ❌ 禁止：不写 COMMENT
|
||
ALTER TABLE task ADD COLUMN priority TINYINT;
|
||
|
||
-- ❌ 禁止：不写默认值导致存量数据为 NULL
|
||
ALTER TABLE task ADD COLUMN priority TINYINT NOT NULL;
|
||
```
|
||
|
||
**修改表必须**：
|
||
- 新字段有 `COMMENT`
|
||
- 非空字段有 `DEFAULT`
|
||
- 考虑对存量数据的影响
|
||
- 附带回滚 SQL
|
||
|
||
---
|
||
|
||
## 检查表
|
||
|
||
每涉及建表/改表，逐项自检：
|
||
|
||
- [ ] 表名、字段名全小写+下划线
|
||
- [ ] 有 `create_by` `create_time` `update_by` `update_time`
|
||
- [ ] 软删除字段 `del_flag`（如需要）
|
||
- [ ] 每张表有 `COMMENT`，每个字段有 `COMMENT`
|
||
- [ ] 金额用 `DECIMAL`，时间用 `DATETIME`
|
||
- [ ] 主键 + 外键 + WHERE 条件字段有索引
|
||
- [ ] 非空字段有 `NOT NULL DEFAULT`
|
||
- [ ] 无物理外键
|
||
- [ ] 索引命名 `idx_表名_字段` / `uk_表名_字段`
|
||
- [ ] 附带回滚 SQL
|
||
- [ ] 引擎 InnoDB，字符集 utf8mb4
|