# MySQL 数据库建设规范（强制性）

## 📋 文档说明

**版本**: v1.0  
**制定时间**: 2025-01-08  
**适用范围**: 整个项目的所有数据库建表操作  
**重要性**: ⚠️ **强制性规范** - 任何数据库相关开发都必须严格遵循本规范

---

## 🎯 总体原则

### 1. 一致性优先

-   所有业务表必须遵循统一的字段设计规范
-   统一使用标准的命名规范和数据类型
-   保持与现有数据库模型的高度一致性

### 2. 性能导向

-   优化查询性能，合理设计索引
-   控制表结构复杂度，避免过度冗余
-   考虑数据增长趋势，预留扩展空间

### 3. 安全可控

-   支持多租户数据隔离
-   统一的权限控制字段
-   完整的操作审计追踪

### 4. 可扩展性

-   预留扩展字段和机制
-   支持业务模块的独立发展
-   兼容未来功能需求

### 5. 可维护性

-   完整的中文注释说明
-   标准化的文档记录
-   便于理解和维护的结构设计

---

## 🔴 核心原则（强制执行）

### ⚠️ 强制性要求

1. **所有业务表**必须包含标准基础字段
2. **统一使用雪花 ID**作为主键
3. **强制使用逻辑删除**，禁止物理删除
4. **所有表必须支持多租户**
5. **完整的中文注释**是必须的
6. **禁止使用数据库外键约束**，关联关系通过应用层维护

---

## 🗄️ 数据库环境配置

### 基础配置要求

-   **MySQL 版本**: 5.7.36+ (推荐 8.0+)
-   **字符集**: utf8mb4
-   **排序规则**: utf8mb4_general_ci
-   **存储引擎**: InnoDB
-   **时区**: Asia/Shanghai

### 数据库连接配置

```yaml
spring:
    datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/hzdyjs_xg?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: ${DB_USERNAME:root}
        password: ${DB_PASSWORD:password}
```

---

## 📊 表结构设计规范

### 🔴 强制性基础字段（所有业务表必须包含）

每个业务表都**必须**包含以下基础字段：

```sql
-- 主键字段（必须）
`id` bigint(20) NOT NULL COMMENT '主键ID',

-- 多租户字段（必须）
`tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',

-- 审计字段（必须）
`create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',

-- 逻辑删除字段（必须）
`deleted` tinyint(1) DEFAULT 0 COMMENT '删除标志（0代表存在 1代表删除）',

-- 主键定义（必须）
PRIMARY KEY (`id`) USING BTREE
```

### 字段设计细则

#### 主键字段 (id)

-   **类型**: `bigint(20) NOT NULL`
-   **策略**: 使用雪花 ID (ASSIGN_ID)
-   **注释**: 主键 ID
-   **强制要求**: 所有表必须使用此设计

#### 租户字段 (tenant_id)

-   **类型**: `varchar(20) NOT NULL`
-   **默认值**: '000000'
-   **用途**: 多租户数据隔离
-   **索引**: 必须创建索引

#### 审计字段

-   **create_dept**: 创建部门 ID，记录数据创建的部门
-   **create_by**: 创建人 ID，记录数据创建者
-   **create_time**: 创建时间，自动记录数据创建时间
-   **update_by**: 更新人 ID，记录最后更新者
-   **update_time**: 更新时间，自动记录数据更新时间

#### 逻辑删除字段 (deleted)

-   **类型**: `tinyint(1) DEFAULT 0`
-   **含义**: 0-存在，1-已删除
-   **强制要求**: 禁止物理删除，必须使用逻辑删除

---

## 📝 字段设计规范

### 数据类型选择

#### 字符串类型

-   **varchar(n)**: 变长字符串，n≤255 时使用
-   **text**: 长文本内容，如富文本、描述等
-   **char(1)**: 固定长度字符，如状态标识

#### 数值类型

-   **bigint(20)**: ID 类型字段（主键、外键）
-   **int(11)**: 普通整数字段
-   **decimal(m,n)**: 精确小数，如金额、评分

#### 时间类型

-   **datetime**: 标准时间字段
-   **timestamp**: 自动更新时间戳（特殊需求）

#### 布尔类型

-   **tinyint(1)**: 布尔值，0-否，1-是

### 强制规则

#### 字段命名规范

-   **使用小写字母**和下划线
-   **见名知意**，避免缩写
-   **统一后缀**：
    -   `_id`: ID 类型字段
    -   `_time`: 时间类型字段
    -   `_status`: 状态类型字段
    -   `_flag`: 标志类型字段

#### 字段长度规范

-   **姓名字段**: varchar(50)
-   **电话字段**: varchar(20)
-   **邮箱字段**: varchar(100)
-   **地址字段**: varchar(200)
-   **标题字段**: varchar(200)
-   **编码字段**: varchar(50)

#### NULL 值规范

-   **主键**: NOT NULL
-   **关联字段**: 根据业务需求确定
-   **租户 ID**: NOT NULL
-   **创建相关字段**: 可为 NULL
-   **业务关键字段**: 建议 NOT NULL

---

## 🚫 外键约束规范（重要）

### 禁止使用数据库外键约束

#### 🔴 强制性原则

**项目中严格禁止使用数据库级别的外键约束（FOREIGN KEY），所有表关联关系通过应用层逻辑维护。**

#### 禁用外键约束的原因

1. **性能考虑**

    - 外键约束会降低数据插入、更新、删除的性能
    - 在高并发场景下会产生额外的锁开销
    - 分库分表时外键约束会成为性能瓶颈

2. **灵活性要求**

    - 支持数据库分库分表扩展
    - 便于数据迁移和重构
    - 避免循环依赖问题

3. **维护便利性**
    - 简化数据库结构变更
    - 减少约束冲突导致的部署问题
    - 便于测试数据管理

#### 替代方案

1. **应用层数据一致性**

    ```java
    // 在业务逻辑中维护关联关系
    @Transactional
    public void deleteUser(Long userId) {
        // 检查关联数据
        if (hasRelatedData(userId)) {
            throw new BusinessException("用户存在关联数据，无法删除");
        }
        // 执行删除
        userService.deleteById(userId);
    }
    ```

2. **命名约定标识关联**

    ```sql
    -- 通过字段命名明确标识关联关系
    `user_id` bigint(20) COMMENT '用户ID，关联sys_user表',
    `dept_id` bigint(20) COMMENT '部门ID，关联sys_dept表',
    `role_id` bigint(20) COMMENT '角色ID，关联sys_role表'
    ```

3. **数据校验注解**
    ```java
    // 使用注解进行数据校验
    @Entity
    public class User {
        @NotNull
        @ExistReference(table = "sys_dept", field = "id")
        private Long deptId;
    }
    ```

#### 关联字段设计规范

1. **命名规范**

    - 关联字段必须以 `_id` 结尾
    - 字段名能够明确表达关联关系
    - 例如：`user_id`、`parent_id`、`dept_id`

2. **注释要求**

    ```sql
    `user_id` bigint(20) NOT NULL COMMENT '用户ID，关联sys_user.id',
    `parent_id` bigint(20) DEFAULT NULL COMMENT '父级ID，关联本表id，顶级为0',
    ```

3. **索引设计**
    ```sql
    -- 关联字段必须创建索引，提升查询性能
    KEY `idx_user_id` (`user_id`),
    KEY `idx_dept_id` (`dept_id`),
    ```

#### 数据一致性保障

1. **业务层校验**

    - 在 Service 层添加关联数据校验
    - 删除操作前检查关联数据
    - 使用事务确保操作原子性

2. **定期数据校验**

    ```sql
    -- 定期执行数据一致性检查脚本
    SELECT u.* FROM sys_user u
    LEFT JOIN sys_dept d ON u.dept_id = d.id
    WHERE u.dept_id IS NOT NULL AND d.id IS NULL;
    ```

3. **监控和报警**
    - 建立数据一致性监控
    - 发现数据不一致时及时报警
    - 记录详细的操作审计日志

---

## 🔍 索引设计规范

### 基础索引（必须创建）

每个表都必须创建以下基础索引：

```sql
-- 租户索引（必须）
KEY `idx_tenant_id` (`tenant_id`),

-- 删除标志索引（必须）
KEY `idx_deleted` (`deleted`),

-- 创建时间索引（建议）
KEY `idx_create_time` (`create_time`),

-- 组合索引：租户+删除标志（高效查询）
KEY `idx_tenant_deleted` (`tenant_id`, `deleted`)
```

### 业务索引规范

#### 状态字段索引

```sql
KEY `idx_status` (`status`)
```

#### 时间范围索引

```sql
KEY `idx_time_range` (`start_time`, `end_time`)
```

#### 关联字段索引

```sql
-- 关联字段索引（注意：不使用外键约束）
KEY `idx_parent_id` (`parent_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_dept_id` (`dept_id`)
```

#### 组合索引

```sql
-- 常用查询组合
KEY `idx_user_type_status` (`user_id`, `type`, `status`)
```

### 索引命名规范

-   **单列索引**: `idx_字段名`
-   **组合索引**: `idx_字段1_字段2`
-   **唯一索引**: `uk_字段名` 或 `uk_字段1_字段2`

---

## 🏗️ 建表 SQL 模板

### 标准业务表模板

```sql
-- ----------------------------
-- Table structure for 模块_表名
-- ----------------------------
DROP TABLE IF EXISTS `模块_表名`;
CREATE TABLE `模块_表名` (
  -- 基础字段（必须）
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT 0 COMMENT '删除标志（0代表存在 1代表删除）',

  -- 业务字段
  `业务字段1` varchar(100) NOT NULL COMMENT '业务字段说明',
  `业务字段2` text COMMENT '业务字段说明',

  -- 关联字段（注意：不使用外键约束）
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID，关联sys_user.id',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID，关联sys_dept.id',

  -- 状态和其他字段
  `status` char(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',

  -- 主键和索引（注意：不创建外键约束）
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_business_key` (`tenant_id`, `业务唯一字段`, `deleted`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_dept_id` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表的中文描述';
```

### 系统配置表模板

```sql
-- 系统配置类表（如字典、参数等）
CREATE TABLE `sys_配置表名` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',

  -- 配置相关字段
  `config_key` varchar(100) NOT NULL COMMENT '配置键',
  `config_value` varchar(500) DEFAULT NULL COMMENT '配置值',
  `config_type` char(1) DEFAULT 'N' COMMENT '系统内置（Y是 N否）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',

  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_config_key` (`config_key`, `tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';
```

### 关联关系表模板

```sql
-- 多对多关联表
CREATE TABLE `模块_关联表名` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',

  -- 关联字段（注意：不使用外键约束）
  `主表_id` bigint(20) NOT NULL COMMENT '主表ID，关联主表.id',
  `从表_id` bigint(20) NOT NULL COMMENT '从表ID，关联从表.id',

  -- 主键和索引（注意：不创建外键约束）
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_relation` (`主表_id`, `从表_id`, `tenant_id`),
  KEY `idx_主表_id` (`主表_id`),
  KEY `idx_从表_id` (`从表_id`),
  KEY `idx_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='关联关系表';
```

---

## 🔄 数据迁移规范

### 迁移脚本命名

```
V{版本号}__{功能描述}.sql
例如：V1.0.1__add_user_table.sql
```

### 迁移脚本结构

```sql
-- ==========================================
-- 数据库变更脚本
-- 版本：V1.0.1
-- 功能：新增用户表
-- 作者：开发者姓名
-- 时间：2025-01-08
-- ==========================================

-- 新增表
CREATE TABLE IF NOT EXISTS `new_table` (
  -- 表结构定义
);

-- 修改表（如果需要）
ALTER TABLE `existing_table`
ADD COLUMN `new_column` varchar(100) COMMENT '新字段说明';

-- 数据初始化（如果需要）
INSERT INTO `new_table` VALUES (...);

-- 创建索引（如果需要）
CREATE INDEX `idx_new_column` ON `existing_table` (`new_column`);
```

### 迁移注意事项

1. **向前兼容**：新增字段必须有默认值
2. **数据备份**：重要变更前必须备份
3. **灰度发布**：分步骤执行重大变更
4. **回滚方案**：准备对应的回滚脚本

---

## ⚡ 性能优化规范

### 查询优化

1. **避免 SELECT \***：明确指定需要的字段
2. **合理使用索引**：根据查询模式创建合适索引
3. **分页查询**：大数据量必须分页
4. **避免全表扫描**：WHERE 条件必须使用索引字段

### 表设计优化

1. **垂直分表**：字段过多时考虑分表
2. **水平分表**：数据量过大时考虑分表
3. **冗余设计**：适度冗余提升查询性能
4. **归档策略**：历史数据定期归档

### 索引优化

1. **组合索引**：多条件查询创建组合索引
2. **覆盖索引**：避免回表查询
3. **索引维护**：定期检查和优化索引
4. **避免冗余**：删除不必要的索引

---

## 🔒 安全规范

### 数据安全

1. **敏感数据加密**：密码、身份证等敏感信息
2. **数据脱敏**：测试环境使用脱敏数据
3. **访问控制**：基于角色的数据访问控制
4. **审计日志**：完整的数据变更日志

### 权限控制

1. **最小权限原则**：只授予必要的权限
2. **账号管理**：定期审查数据库账号
3. **密码策略**：强密码策略和定期更换
4. **网络安全**：限制数据库访问网络

### 备份策略

1. **定期备份**：每日全量备份
2. **增量备份**：实时或小时级增量备份
3. **异地备份**：关键数据异地存储
4. **恢复测试**：定期验证备份恢复

---

## ✅ 检查清单

### 新建表检查清单

-   [ ] 包含所有强制基础字段
-   [ ] 主键使用 bigint(20)类型
-   [ ] 租户字段正确设置
-   [ ] 逻辑删除字段存在
-   [ ] 审计字段完整
-   [ ] 表注释完整准确
-   [ ] 字段注释完整
-   [ ] **未使用外键约束**（重要）
-   [ ] 关联字段命名规范（以\_id 结尾）
-   [ ] 关联字段注释明确标识关联表

### 字段设计检查清单

-   [ ] 字段命名符合规范
-   [ ] 数据类型选择合理
-   [ ] 字段长度设置适当
-   [ ] NULL 值设置合理
-   [ ] 默认值设置正确
-   [ ] 字段注释明确

### 索引设计检查清单

-   [ ] 基础索引已创建
-   [ ] 业务索引设计合理
-   [ ] 索引命名规范
-   [ ] 唯一约束正确
-   [ ] 组合索引顺序合理

---

## 🛠️ 工具和脚本

### 建表辅助脚本

```sql
-- 自动生成基础字段的存储过程
DELIMITER $$
CREATE PROCEDURE `add_base_fields`(IN table_name VARCHAR(64))
BEGIN
    SET @sql = CONCAT('ALTER TABLE ', table_name, ' ADD COLUMN IF NOT EXISTS `tenant_id` varchar(20) NOT NULL DEFAULT ''000000'' COMMENT ''租户编号''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 添加其他基础字段...
END$$
DELIMITER ;
```

### 表结构检查脚本

```sql
-- 检查缺失基础字段的表
SELECT
    TABLE_NAME,
    CASE
        WHEN COLUMN_NAME IS NULL THEN CONCAT('缺少字段: ', required_field)
        ELSE '字段存在'
    END AS check_result
FROM (
    SELECT 'tenant_id' AS required_field
    UNION SELECT 'create_time'
    UNION SELECT 'update_time'
    UNION SELECT 'deleted'
) rf
CROSS JOIN INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_NAME = c.TABLE_NAME
    AND c.COLUMN_NAME = rf.required_field
WHERE t.TABLE_SCHEMA = 'hzdyjs_xg'
    AND t.TABLE_TYPE = 'BASE TABLE'
    AND c.COLUMN_NAME IS NULL;
```

---

## 📚 参考示例

### 现有表结构参考

项目中已有的标准表结构可参考：

-   `hq_hall_info` - 报告厅信息表
-   `hq_hall_reservation` - 报告厅预约表
-   `hq_sat_survey` - 问卷调查表
-   `sys_user` - 用户表
-   `sys_dict_data` - 字典数据表

### 命名示例

-   **模块前缀**: `hq_`（后勤）、`sys_`（系统）、`gen_`（代码生成）
-   **表名示例**: `hq_hall_info`、`sys_user_role`
-   **字段示例**: `user_name`、`create_time`、`hall_status`

---

## ⚠️ 重要提醒

1. **强制性要求**：本规范所有标注为"必须"、"强制"的条目都是强制性的
2. **一致性保证**：新建表必须与现有表结构保持一致
3. **文档同步**：表结构变更必须同步更新文档
4. **Code Review**：所有数据库变更都需要进行代码审查
5. **测试验证**：新表结构必须经过充分测试验证

---

## 📞 技术支持

如有疑问或需要技术支持，请联系：

-   **技术负责人**：项目技术负责人
-   **文档维护**：定期更新和维护本规范
-   **培训支持**：为团队成员提供规范培训

---

**最后更新**: 2025-01-08  
**下次审查**: 2025-07-08