Skill 06: 数据库脚本管理

概述

BK-CI 使用 MySQL/MariaDB 作为主数据库，SQL 脚本按照严格的命名规范和版本管理策略组织。

数据库支持

SQL 文件位置

support-files/sql/
├── 0001_ci_create-database_mysql.sql      # 创建所有数据库
├── 1001_ci_project_ddl_mysql.sql          # Project模块完整DDL
├── 1002_ci_process_ddl_mysql.sql          # Process模块完整DDL
├── 2003_v2.x/                             # v2.x版本增量更新
├── 2004_v3.x/                             # v3.x版本增量更新
├── 2025_v4.x/                             # v4.x版本增量更新
└── 5001_init_dml/                         # 初始化数据

文件命名规范

创建数据库脚本

0001_{系统}_create-database_{db类型}.sql

示例：0001_ci_create-database_mysql.sql

全版本 DDL 脚本

1xxx_{系统}_{模块}_ddl_{db类型}.sql

示例： - 1001_ci_project_ddl_mysql.sql - 1002_ci_process_ddl_mysql.sql - 1003_ci_repository_ddl_mysql.sql

增量版本更新脚本

2xxx_{系统}_{模块}_update_{版本号}_{db类型}.sql

示例： - 2003_ci_process_update_v2.0.0_mysql.sql - 2004_ci_auth_update_v3.0.0_mysql.sql

⚠️ 重要：双轨更新机制

当在增量脚本中添加/修改表结构时，必须同步更新对应的全版本 DDL 脚本！

初始化数据脚本

5001_{系统}_{模块}_dml_{db类型}.sql

示例：5001_ci_project_dml_mysql.sql

双轨更新机制（⚠️ 重要）

核心原则

任何表结构变更（DDL）必须在两个位置同步更新：

1. 增量版本更新脚本（2xxx_*_update_*.sql）- 用于现有环境升级
2. 全版本 DDL 脚本（1xxx_*_ddl_*.sql）- 用于全新安装

为什么需要双轨更新？

• 增量脚本：负责将已部署的旧版本数据库升级到新版本
• 全量脚本：负责全新安装时直接创建最新的完整表结构

如果只更新增量脚本，全新安装时会缺少对应的字段/索引！

标准变更流程

假设需要为 T_PROJECT 表添加索引 IDX_PROJECT_NAME：

步骤 1：更新增量脚本

文件：support-files/sql/2025_v4.x/2040_ci_project-update_v4.1_mysql.sql

USE devops_ci_project;
SET NAMES utf8mb4;

DROP PROCEDURE IF EXISTS ci_project_schema_update;

DELIMITER <CI_UBF>

CREATE PROCEDURE ci_project_schema_update()
BEGIN
    DECLARE db VARCHAR(100);
    SET AUTOCOMMIT = 0;
    SELECT DATABASE() INTO db;

    -- 添加索引
    IF NOT EXISTS(SELECT 1
                  FROM information_schema.STATISTICS
                  WHERE TABLE_SCHEMA = db
                    AND TABLE_NAME = 'T_PROJECT'
                    AND INDEX_NAME = 'IDX_PROJECT_NAME') THEN
        ALTER TABLE `T_PROJECT` ADD INDEX `IDX_PROJECT_NAME` (`PROJECT_NAME`);
    END IF;

    COMMIT;
END <CI_UBF>
DELIMITER ;

CALL ci_project_schema_update();

步骤 2：同步更新全量 DDL 脚本

文件：support-files/sql/1001_ci_project_ddl_mysql.sql

找到 T_PROJECT 表的建表语句，在索引定义区域添加：

CREATE TABLE IF NOT EXISTS `T_PROJECT` (
    `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    `PROJECT_ID` varchar(64) NOT NULL,
    `PROJECT_NAME` varchar(256) NOT NULL,
    -- ... 其他字段
    PRIMARY KEY (`ID`),
    UNIQUE KEY `UNI_PROJECT_ID` (`PROJECT_ID`),
    KEY `IDX_PROJECT_NAME` (`PROJECT_NAME`)  -- ✅ 新增索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常见变更类型示例

检查清单

在提交数据库变更前，确认：

• [ ] 增量脚本已创建（2xxx_*_update_*.sql）
• [ ] 增量脚本具备幂等性（可重复执行）
• [ ] 全量 DDL 脚本已同步更新（1xxx_*_ddl_*.sql）
• [ ] 两处的字段定义、类型、默认值完全一致
• [ ] 索引名称、类型在两处保持一致
• [ ] 本地测试通过（全新安装 + 升级安装）

幂等性要求

所有 SQL 脚本必须支持重复执行（幂等性）：

创建表

-- ✅ 正确：使用 IF NOT EXISTS
CREATE TABLE IF NOT EXISTS `T_PROJECT` (
    `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    `PROJECT_ID` varchar(64) NOT NULL,
    `PROJECT_NAME` varchar(256) NOT NULL,
    PRIMARY KEY (`ID`),
    UNIQUE KEY `UNI_PROJECT_ID` (`PROJECT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ❌ 错误：直接创建
CREATE TABLE `T_PROJECT` (...);

添加列

-- ✅ 正确：使用存储过程检查
DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS add_column_if_not_exists()
BEGIN
    IF NOT EXISTS (
        SELECT * FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'T_PROJECT' 
        AND COLUMN_NAME = 'NEW_COLUMN'
    ) THEN
        ALTER TABLE `T_PROJECT` ADD COLUMN `NEW_COLUMN` varchar(64) DEFAULT NULL;
    END IF;
END$$
DELIMITER ;
CALL add_column_if_not_exists();
DROP PROCEDURE IF EXISTS add_column_if_not_exists;

-- ❌ 错误：直接添加
ALTER TABLE `T_PROJECT` ADD COLUMN `NEW_COLUMN` varchar(64);

插入数据

-- ✅ 正确：使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE
INSERT IGNORE INTO `T_CONFIG` (`KEY`, `VALUE`) VALUES ('config_key', 'config_value');

INSERT INTO `T_CONFIG` (`KEY`, `VALUE`) VALUES ('config_key', 'config_value')
ON DUPLICATE KEY UPDATE `VALUE` = VALUES(`VALUE`);

-- ❌ 错误：直接插入
INSERT INTO `T_CONFIG` (`KEY`, `VALUE`) VALUES ('config_key', 'config_value');

创建索引

-- ✅ 正确：使用存储过程检查
DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS add_index_if_not_exists()
BEGIN
    IF NOT EXISTS (
        SELECT * FROM information_schema.STATISTICS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'T_PROJECT' 
        AND INDEX_NAME = 'IDX_PROJECT_NAME'
    ) THEN
        ALTER TABLE `T_PROJECT` ADD INDEX `IDX_PROJECT_NAME` (`PROJECT_NAME`);
    END IF;
END$$
DELIMITER ;
CALL add_index_if_not_exists();
DROP PROCEDURE IF EXISTS add_index_if_not_exists;

⚠️ 提醒：添加索引后，别忘了同步更新 1xxx_*_ddl_mysql.sql 中的建表语句！

禁止操作

1. ❌ 删除后重建表：可能导致数据丢失
2. ❌ 添加非空无默认值字段：会导致现有数据插入失败
3. ❌ 直接修改字段类型：可能导致数据截断
4. ❌ 删除字段：需要充分评估影响

表命名规范

字段命名规范

索引命名规范

完整建表示例

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `T_PIPELINE_INFO` (
    `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `PROJECT_ID` varchar(64) NOT NULL COMMENT '项目ID',
    `PIPELINE_ID` varchar(34) NOT NULL COMMENT '流水线ID',
    `PIPELINE_NAME` varchar(255) NOT NULL COMMENT '流水线名称',
    `PIPELINE_DESC` varchar(1024) DEFAULT NULL COMMENT '流水线描述',
    `VERSION` int(11) NOT NULL DEFAULT '1' COMMENT '版本号',
    `CREATE_TIME` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    `UPDATE_TIME` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
    `CREATOR` varchar(64) NOT NULL DEFAULT 'system' COMMENT '创建人',
    `MODIFIER` varchar(64) NOT NULL DEFAULT 'system' COMMENT '修改人',
    `DELETE` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`ID`),
    UNIQUE KEY `UNI_PROJECT_PIPELINE` (`PROJECT_ID`, `PIPELINE_ID`),
    KEY `IDX_PROJECT_ID` (`PROJECT_ID`),
    KEY `IDX_CREATE_TIME` (`CREATE_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='流水线信息表';

SET FOREIGN_KEY_CHECKS = 1;

JOOQ 集成

BK-CI 使用 JOOQ 作为数据库访问层，禁止手写 SQL：

// ✅ 正确：使用 JOOQ DSL
dslContext.selectFrom(T_PIPELINE_INFO)
    .where(T_PIPELINE_INFO.PROJECT_ID.eq(projectId))
    .and(T_PIPELINE_INFO.DELETE.eq(false))
    .fetch()

// ❌ 错误：手写 SQL
dslContext.fetch("SELECT * FROM T_PIPELINE_INFO WHERE PROJECT_ID = ?", projectId)

常见错误案例

❌ 错误：仅更新增量脚本

问题场景：开发者只在增量脚本中添加索引，忘记更新全量 DDL

-- 文件：2025_v4.x/2040_ci_project-update_v4.1_mysql.sql
ALTER TABLE `T_PROJECT` ADD INDEX `IDX_PROJECT_NAME` (`PROJECT_NAME`);

后果： - 旧环境升级 ✅ 成功（执行了增量脚本） - 全新安装 ❌ 失败（缺少索引，查询性能问题）

正确做法：同步更新 1001_ci_project_ddl_mysql.sql

❌ 错误：两处定义不一致

增量脚本：

ALTER TABLE `T_PROJECT` ADD COLUMN `NEW_FIELD` varchar(64) DEFAULT NULL;

全量脚本：

`NEW_FIELD` varchar(128) DEFAULT ''  -- ⚠️ 长度和默认值不一致！

后果：升级环境和全新安装的字段定义不同，可能导致数据截断或业务逻辑错误。

正确做法：保持两处完全一致。

✅ 正确示例：完整的双轨更新

需求：为 T_PIPELINE_INFO 添加 TAGS 字段和索引

步骤 1：增量脚本

文件：2025_v4.x/2040_ci_process-update_v4.1_mysql.sql

USE devops_ci_process;
SET NAMES utf8mb4;

DROP PROCEDURE IF EXISTS ci_process_schema_update;

DELIMITER <CI_UBF>

CREATE PROCEDURE ci_process_schema_update()
BEGIN
    DECLARE db VARCHAR(100);
    SET AUTOCOMMIT = 0;
    SELECT DATABASE() INTO db;

    -- 添加字段
    IF NOT EXISTS(SELECT 1
                  FROM information_schema.COLUMNS
                  WHERE TABLE_SCHEMA = db
                    AND TABLE_NAME = 'T_PIPELINE_INFO'
                    AND COLUMN_NAME = 'TAGS') THEN
        ALTER TABLE `T_PIPELINE_INFO` 
        ADD COLUMN `TAGS` varchar(1024) DEFAULT NULL COMMENT '流水线标签';
    END IF;

    -- 添加索引
    IF NOT EXISTS(SELECT 1
                  FROM information_schema.STATISTICS
                  WHERE TABLE_SCHEMA = db
                    AND TABLE_NAME = 'T_PIPELINE_INFO'
                    AND INDEX_NAME = 'IDX_TAGS') THEN
        ALTER TABLE `T_PIPELINE_INFO` ADD INDEX `IDX_TAGS` (`TAGS`(255));
    END IF;

    COMMIT;
END <CI_UBF>
DELIMITER ;

CALL ci_process_schema_update();

步骤 2：全量脚本

文件：1001_ci_process_ddl_mysql.sql

CREATE TABLE IF NOT EXISTS `T_PIPELINE_INFO` (
    `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `PROJECT_ID` varchar(64) NOT NULL COMMENT '项目ID',
    `PIPELINE_ID` varchar(34) NOT NULL COMMENT '流水线ID',
    `PIPELINE_NAME` varchar(255) NOT NULL COMMENT '流水线名称',
    `TAGS` varchar(1024) DEFAULT NULL COMMENT '流水线标签',  -- ✅ 新增字段
    -- ... 其他字段
    PRIMARY KEY (`ID`),
    UNIQUE KEY `UNI_PROJECT_PIPELINE` (`PROJECT_ID`, `PIPELINE_ID`),
    KEY `IDX_PROJECT_ID` (`PROJECT_ID`),
    KEY `IDX_TAGS` (`TAGS`(255))  -- ✅ 新增索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='流水线信息表';

验证：

# 测试升级场景
docker exec -it mysql mysql -u root -p devops_ci_process < 2040_ci_process-update_v4.1_mysql.sql

# 测试全新安装
docker exec -it mysql mysql -u root -p devops_ci_process < 1001_ci_process_ddl_mysql.sql

# 对比两种方式的表结构是否一致
docker exec -it mysql mysql -u root -p -e "SHOW CREATE TABLE devops_ci_process.T_PIPELINE_INFO\G"

数据库分库

BK-CI 支持数据库分库，通过配置可以进行水平扩展：

# 分库配置示例
spring:
  datasource:
    process:
      url: jdbc:mysql://host1:3306/devops_ci_process
    project:
      url: jdbc:mysql://host2:3306/devops_ci_project