-- =====================================================
|
-- 车辆异常运行监控告警功能 - 数据库升级脚本
|
-- 用于更新已存在的表结构
|
-- =====================================================
|
|
-- 检查并修改 tb_vehicle_alert_config 表结构
|
-- 如果表已存在但字段不正确，需要先删除旧字段，再添加新字段
|
|
-- 1. 删除旧的唯一索引
|
ALTER TABLE `tb_vehicle_alert_config` DROP INDEX IF EXISTS `uk_type_target`;
|
|
-- 2. 检查是否存在 target_id 字段，如果存在则删除
|
SET @exist_target_id := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND COLUMN_NAME = 'target_id');
|
|
SET @sql_drop_target_id = IF(@exist_target_id > 0,
|
'ALTER TABLE `tb_vehicle_alert_config` DROP COLUMN `target_id`',
|
'SELECT ''target_id column does not exist''');
|
PREPARE stmt FROM @sql_drop_target_id;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 3. 添加 dept_id 字段（如果不存在）
|
SET @exist_dept_id := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND COLUMN_NAME = 'dept_id');
|
|
SET @sql_add_dept_id = IF(@exist_dept_id = 0,
|
'ALTER TABLE `tb_vehicle_alert_config` ADD COLUMN `dept_id` bigint(20) DEFAULT NULL COMMENT ''部门ID（部门配置时使用）'' AFTER `config_type`',
|
'SELECT ''dept_id column already exists''');
|
PREPARE stmt FROM @sql_add_dept_id;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 4. 添加 vehicle_id 字段（如果不存在）
|
SET @exist_vehicle_id := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND COLUMN_NAME = 'vehicle_id');
|
|
SET @sql_add_vehicle_id = IF(@exist_vehicle_id = 0,
|
'ALTER TABLE `tb_vehicle_alert_config` ADD COLUMN `vehicle_id` bigint(20) DEFAULT NULL COMMENT ''车辆ID（车辆配置时使用）'' AFTER `dept_id`',
|
'SELECT ''vehicle_id column already exists''');
|
PREPARE stmt FROM @sql_add_vehicle_id;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 5. 检查是否存在 notify_roles 字段，如果存在则删除
|
SET @exist_notify_roles := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND COLUMN_NAME = 'notify_roles');
|
|
SET @sql_drop_notify_roles = IF(@exist_notify_roles > 0,
|
'ALTER TABLE `tb_vehicle_alert_config` DROP COLUMN `notify_roles`',
|
'SELECT ''notify_roles column does not exist''');
|
PREPARE stmt FROM @sql_drop_notify_roles;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 6. 修改 enabled 字段为 status（如果存在 enabled）
|
SET @exist_enabled := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND COLUMN_NAME = 'enabled');
|
|
SET @sql_change_enabled = IF(@exist_enabled > 0,
|
'ALTER TABLE `tb_vehicle_alert_config` CHANGE COLUMN `enabled` `status` char(1) DEFAULT ''0'' COMMENT ''状态（0-启用 1-停用）''',
|
'SELECT ''enabled column does not exist, may already be status''');
|
PREPARE stmt FROM @sql_change_enabled;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 7. 删除旧索引
|
ALTER TABLE `tb_vehicle_alert_config` DROP INDEX IF EXISTS `idx_target_id`;
|
ALTER TABLE `tb_vehicle_alert_config` DROP INDEX IF EXISTS `idx_enabled`;
|
|
-- 8. 创建新的唯一索引
|
-- 先检查索引是否存在
|
SET @exist_uk_vehicle := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND INDEX_NAME = 'uk_vehicle_config');
|
|
SET @sql_add_uk_vehicle = IF(@exist_uk_vehicle = 0,
|
'ALTER TABLE `tb_vehicle_alert_config` ADD UNIQUE KEY `uk_vehicle_config` (`config_type`, `vehicle_id`)',
|
'SELECT ''uk_vehicle_config index already exists''');
|
PREPARE stmt FROM @sql_add_uk_vehicle;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exist_uk_dept := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND INDEX_NAME = 'uk_dept_config');
|
|
SET @sql_add_uk_dept = IF(@exist_uk_dept = 0,
|
'ALTER TABLE `tb_vehicle_alert_config` ADD UNIQUE KEY `uk_dept_config` (`config_type`, `dept_id`)',
|
'SELECT ''uk_dept_config index already exists''');
|
PREPARE stmt FROM @sql_add_uk_dept;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 9. 创建新的普通索引
|
SET @exist_idx_dept := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND INDEX_NAME = 'idx_dept_id');
|
|
SET @sql_add_idx_dept = IF(@exist_idx_dept = 0,
|
'ALTER TABLE `tb_vehicle_alert_config` ADD KEY `idx_dept_id` (`dept_id`)',
|
'SELECT ''idx_dept_id index already exists''');
|
PREPARE stmt FROM @sql_add_idx_dept;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exist_idx_vehicle := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND INDEX_NAME = 'idx_vehicle_id');
|
|
SET @sql_add_idx_vehicle = IF(@exist_idx_vehicle = 0,
|
'ALTER TABLE `tb_vehicle_alert_config` ADD KEY `idx_vehicle_id` (`vehicle_id`)',
|
'SELECT ''idx_vehicle_id index already exists''');
|
PREPARE stmt FROM @sql_add_idx_vehicle;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exist_idx_status := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tb_vehicle_alert_config'
|
AND INDEX_NAME = 'idx_status');
|
|
SET @sql_add_idx_status = IF(@exist_idx_status = 0,
|
'ALTER TABLE `tb_vehicle_alert_config` ADD KEY `idx_status` (`status`)',
|
'SELECT ''idx_status index already exists''');
|
PREPARE stmt FROM @sql_add_idx_status;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 10. 更新已有的全局配置记录
|
UPDATE `tb_vehicle_alert_config`
|
SET `dept_id` = NULL, `vehicle_id` = NULL
|
WHERE `config_type` = 'GLOBAL';
|
|
-- 升级完成提示
|
SELECT '数据库升级完成！tb_vehicle_alert_config 表结构已更新' AS 'Status';
|
