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

📋 文档说明

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

🎯 总体原则

1. 一致性优先

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

2. 性能导向

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

3. 安全可控

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

4. 可扩展性

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

5. 可维护性

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

🔴 核心原则（强制执行）

⚠️ 强制性要求

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

🗄️ 数据库环境配置

基础配置要求

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

数据库连接配置

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}

📊 表结构设计规范

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

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

-- 主键字段（必须）
`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），所有表关联关系通过应用层逻辑维护。

禁用外键约束的原因

性能考虑
- 外键约束会降低数据插入、更新、删除的性能
- 在高并发场景下会产生额外的锁开销
- 分库分表时外键约束会成为性能瓶颈
灵活性要求
- 支持数据库分库分表扩展
- 便于数据迁移和重构
- 避免循环依赖问题
维护便利性
- 简化数据库结构变更
- 减少约束冲突导致的部署问题
- 便于测试数据管理

替代方案

应用层数据一致性

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

命名约定标识关联

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

数据校验注解

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

关联字段设计规范

命名规范

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

注释要求

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

索引设计

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

数据一致性保障

业务层校验

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

定期数据校验

-- 定期执行数据一致性检查脚本
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;

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

🔍 索引设计规范

基础索引（必须创建）

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

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

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

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

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

业务索引规范

状态字段索引

KEY `idx_status` (`status`)

时间范围索引

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

关联字段索引

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

组合索引

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

索引命名规范

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

🏗️ 建表 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='表的中文描述';

系统配置表模板

-- 系统配置类表（如字典、参数等）
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='系统配置表';

关联关系表模板

-- 多对多关联表
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

迁移脚本结构

-- ==========================================
-- 数据库变更脚本
-- 版本：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`);

迁移注意事项

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

⚡ 性能优化规范

查询优化

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

表设计优化

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

索引优化

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

🔒 安全规范

数据安全

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

权限控制

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

备份策略

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

✅ 检查清单

新建表检查清单

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

字段设计检查清单

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

索引设计检查清单

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

🛠️ 工具和脚本

建表辅助脚本

-- 自动生成基础字段的存储过程
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 ;

表结构检查脚本

-- 检查缺失基础字段的表
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

⚠️ 重要提醒

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

📞 技术支持

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

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

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

mysql-database-standard.md 18 KB Permalänk Historik Rå