SQL速通

发表于 分类于
离职系列 第十三篇
离职系列，回忆过去，在这做个记录。

SQL脚本管理规范：小团队高频发版实践指南

1. 背景与目的

作为一个研发不到10人的团队，从0到1构建SAAS平台，且每周需要发布2-3个版本，所以总有些团队管理等问题会慢慢暴露，我们再慢慢修复，就跟修BUG一样，这一篇就是因为上线出过SQL脚本的问题（阿里云的SQL控制台对一些写的不太规范的sql执行存在兼容性问题会导致SQL执行不符合预期），所以有了这篇SQL规范，先说问题，当前SQL脚本管理存在以下问题：

  • 各开发人员风格不一，缺乏统一规范
  • 脚本分散、无统一管理，难以追踪变更历史
  • 发版频繁导致变更混乱，增加了维护成本
  • 部分脚本质量不高，存在安全隐患

本规范旨在提供一套简单、实用且专业的SQL脚本管理方案，帮助团队高效管理数据库变更，可根据实践情况持续优化。

2. 目录结构

采用精简的目录结构，既能满足版本管理需求，又不过于复杂：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/PG                      # 数据库名称
├── YYYY/                # 年份目录
│   └── MM-DD/           # 日期目录，如04-15表示4月15日
│       ├── release_N/   # 发布版本号，如release_1
│       │   ├── DDL/     # 数据结构变更脚本
│       │   │   ├── 001_create_table_xxx.sql
│       │   │   └── 002_create_index_xxx.sql
│       │   ├── DML/     # 数据操作脚本
│       │   │   └── 001_add_data_xxx.sql
│       │   └── ROLLBACK/ # 回滚脚本（可选）
│       │       └── 001_rollback_xxx.sql
├── dictionary/          # 数据字典
│   ├── base/            # 基础数据
│   │   ├── init_v1.0.sql  # 2025年1月版本
│   │   └── init_v2.0.sql  # 2025年7月版本（整合上半年变更）
│   ├── incremental/       # 按数据字典type
│   │   ├── dict_payment_type.sql  
│   │   └── ...
├── menu/                # 菜单
│   ├── base/    # 基础数据
│   │   ├── init_v1.0.sql  # 2025年1月版本
│   │   └── init_v2.0.sql  # 2025年7月版本（整合上半年变更）
│   ├── ...
│   ├── module_xx/       # 按模块
│   │   ├── crm.sql 
│   │   └── ...

3. 文件命名规范

3.1 脚本文件命名

采用序号_描述[_rollback].sql格式：

  • 序号：确保执行顺序，如001、002
  • 描述：简明表达脚本用途，如create_user_table、add_email_column
  • rollback：回滚脚本添加_rollback后缀

示例：

  • 001_create_user_table.sql
  • 001_create_user_table_rollback.sql

3.2 数据库对象命名

  1. 表命名：

    • 使用小写和下划线
    • 采用前缀区分业务模块，如sys_user、order_item
    • 名称应能清晰表达表的用途

  2. 列命名：

    • 主键统一为id
    • 外键使用entity_id格式，如user_id
    • 创建和更新时间统一为create_time、update_time

  3. 索引命名：

    • 主键索引：pk_表名
    • 唯一索引：uk_表名_列名
    • 普通索引：idx_表名_列名

4. 脚本编写规范

4.1 文件头注释

所有SQL脚本必须包含统一的文件头注释：

1
2
3
4
5
6
-- ========================================
-- 描述: [功能简述]
-- 文件名: [文件名]
-- 作者: [作者]
-- 创建日期: [YYYY-MM-DD]
-- ========================================

4.2 SQL编写原则

  1. 原子性：一个脚本只完成一个独立任务

  2. 幂等性：脚本可以重复执行而不产生副作用

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 好的做法
    CREATE TABLE IF NOT EXISTS users (
      id VARCHAR(36) NOT NULL,
      username VARCHAR(50) NOT NULL,
      PRIMARY KEY (id)
    );

    -- 或者
    DROP TABLE IF EXISTS users;
    CREATE TABLE users (
      id VARCHAR(36) NOT NULL,
      username VARCHAR(50) NOT NULL,
      PRIMARY KEY (id)
    );

  3. 向后兼容：尽量避免破坏性变更

    1
    2
    3
    4
    5
    -- 推荐
    ALTER TABLE users ADD COLUMN email VARCHAR(100) NULL;

    -- 不允许
    ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL;

  4. 安全性：敏感信息不应明文存储

4.3 代码风格

  1. 关键字大写：所有SQL关键字使用大写形式

    1
    SELECT * FROM users WHERE status = 'active';

  2. 适当缩进：使用一致的缩进提高可读性

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT 
        u.id,
        u.username,
        r.name AS role_name
    FROM 
        users u
    JOIN 
        roles r ON u.role_id = r.id
    WHERE 
        u.status = 'active';

  3. 添加注释：为复杂SQL语句添加适当注释

5. 特殊脚本管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
数据字典和菜单：

├── dictionary/          # 数据字典
│   ├── base/            # 基础数据
│   │   ├── init_v1.0.sql  # 2025年1月版本
│   │   └── init_v2.0.sql  # 2025年7月版本（整合上半年变更）
│   ├── incremental/       # 按数据字典type
│   │   ├── dict_payment_type.sql  
│   │   └── ...
├── menu/                # 菜单
│   ├── base/    # 基础数据
│   │   ├── init_v1.0.sql  # 2025年1月版本
│   │   └── init_v2.0.sql  # 2025年7月版本（整合上半年变更）
│   ├── ...
│   ├── module_xx/       # 按模块
│   │   ├── crm.sql 
│   │   └── ...

6. 实施流程

针对高频发版小团队，简化流程但不降低质量要求：

6.1 开发变更流程

1
2
3
4
5
6
7
8
9
10
11
12
graph TD
    A[编写SQL脚本] --> B[本地测试]
    B --> C{通过?}
    C -->|否| A
    C -->|是| D[通知焕涛]
    D --> E[代码评审]
    E --> F{焕涛通过云平台工具测试执行通过?}
    F -->|否| A
    F -->|是| G[测试环境部署]
    G --> H{测试人员验证通过?}
    H -->|否| A
    H -->|是| I[生产环境部署]

6.2执行顺序

为确保数据库变更的安全有序，按以下顺序执行：

  1. DDL脚本（按文件名序号顺序）
  2. DML脚本（按文件名序号顺序）
  3. 特殊配置（数据字典、菜单）

最后

团队应当将本规范视为基础标准，在实践中不断完善和优化，形成最适合团队的工作方式。