55 lines
2.5 KiB
SQL
55 lines
2.5 KiB
SQL
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
-- 本迁移同时覆盖两种开发阶段状态：
|
||
-- 1. 老库已有 admin_app_configs，但由早期 AutoMigrate 建表，缺少描述和创建时间列；
|
||
-- 2. 新本地库先跑 SQL migrations 再 AutoMigrate，此时表还不存在，后续 ALTER 会直接失败。
|
||
CREATE TABLE IF NOT EXISTS admin_app_configs (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`group` VARCHAR(64) NOT NULL,
|
||
`key` VARCHAR(100) NOT NULL,
|
||
value TEXT,
|
||
description VARCHAR(255) NOT NULL DEFAULT '' COMMENT '描述信息',
|
||
created_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '更新时间，UTC epoch ms',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY idx_admin_app_config_group_key (`group`, `key`),
|
||
KEY idx_admin_app_configs_group (`group`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 老生产库曾由早期 AutoMigrate 建表，缺少当前模型依赖的描述和创建时间列。
|
||
SET @ddl := IF(
|
||
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'admin_app_configs' AND COLUMN_NAME = 'description') = 0,
|
||
'ALTER TABLE admin_app_configs ADD COLUMN description VARCHAR(255) NOT NULL DEFAULT '''' COMMENT ''描述信息'' AFTER value',
|
||
'SELECT 1'
|
||
);
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl := IF(
|
||
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'admin_app_configs' AND COLUMN_NAME = 'created_at_ms') = 0,
|
||
'ALTER TABLE admin_app_configs ADD COLUMN created_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT ''创建时间，UTC epoch ms'' AFTER description',
|
||
'SELECT 1'
|
||
);
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl := IF(
|
||
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'admin_app_banners' AND COLUMN_NAME = 'created_at_ms') = 0,
|
||
'ALTER TABLE admin_app_banners ADD COLUMN created_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT ''创建时间，UTC epoch ms'' AFTER ends_at_ms',
|
||
'SELECT 1'
|
||
);
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl := IF(
|
||
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'admin_app_versions' AND COLUMN_NAME = 'created_at_ms') = 0,
|
||
'ALTER TABLE admin_app_versions ADD COLUMN created_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT ''创建时间，UTC epoch ms'' AFTER description',
|
||
'SELECT 1'
|
||
);
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|