-- VIN 车辆绑定字段统一改名：appp_user_id -> app_user_id（含索引与注释）
|
-- 背景：历史拼写错误（三个 p）已废弃，全链路统一为 app_user_id。
|
-- 目标库：charging_pile_account.t_vin_management
|
-- 适用：MySQL（含 Mycat 代理下的 MySQL 8 后端）。Mycat 对 PREPARE/INFORMATION_SCHEMA 变量支持不稳定时，
|
-- 可直接执行下方「直接 DDL」小节并回查。
|
-- 脚本幂等：列/索引若已是 app_user_id 则自动跳过。
|
|
USE `charging_pile_account`;
|
|
SET @has_old_column := (
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND COLUMN_NAME = 'appp_user_id'
|
);
|
SET @has_new_column := (
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND COLUMN_NAME = 'app_user_id'
|
);
|
SET @rename_column_sql := IF(
|
@has_old_column = 1 AND @has_new_column = 0,
|
'ALTER TABLE `t_vin_management` CHANGE COLUMN `appp_user_id` `app_user_id` bigint NULL DEFAULT NULL COMMENT ''绑定的小程序用户ID'' AFTER `del_flag`',
|
'SELECT 1'
|
);
|
PREPARE rename_column_stmt FROM @rename_column_sql;
|
EXECUTE rename_column_stmt;
|
DEALLOCATE PREPARE rename_column_stmt;
|
|
SET @has_old_index := (
|
SELECT COUNT(1) FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND INDEX_NAME = 'idx_appp_user_id'
|
);
|
SET @has_new_index := (
|
SELECT COUNT(1) FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND INDEX_NAME = 'idx_app_user_id'
|
);
|
-- 旧索引改名（MySQL 8 / Mycat 透传不支持时改为 DROP+ADD 两条直接 DDL 手动执行）
|
SET @rename_index_sql := IF(
|
@has_old_index = 1 AND @has_new_index = 0,
|
'ALTER TABLE `t_vin_management` RENAME INDEX `idx_appp_user_id` TO `idx_app_user_id`',
|
'SELECT 1'
|
);
|
PREPARE rename_index_stmt FROM @rename_index_sql;
|
EXECUTE rename_index_stmt;
|
DEALLOCATE PREPARE rename_index_stmt;
|
|
-- 兜底：新列已存在但缺少索引时补建（兼容旧环境索引名不是 idx_app_user_id 的情况）
|
SET @ensure_index_sql := IF(
|
@has_new_column = 1 AND @has_new_index = 0,
|
'ALTER TABLE `t_vin_management` ADD INDEX `idx_app_user_id` (`app_user_id`)',
|
'SELECT 1'
|
);
|
PREPARE ensure_index_stmt FROM @ensure_index_sql;
|
EXECUTE ensure_index_stmt;
|
DEALLOCATE PREPARE ensure_index_stmt;
|
|
-- 注释兜底：确保 app_user_id 注释为「绑定的小程序用户ID」（无「三个p」字样）
|
SET @comment_exists := (
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management'
|
AND COLUMN_NAME = 'app_user_id' AND COLUMN_COMMENT LIKE '%三个p%'
|
);
|
SET @fix_comment_sql := IF(
|
@has_new_column = 1 AND @comment_exists = 1,
|
'ALTER TABLE `t_vin_management` MODIFY COLUMN `app_user_id` bigint NULL DEFAULT NULL COMMENT ''绑定的小程序用户ID'' AFTER `del_flag`',
|
'SELECT 1'
|
);
|
PREPARE fix_comment_stmt FROM @fix_comment_sql;
|
EXECUTE fix_comment_stmt;
|
DEALLOCATE PREPARE fix_comment_stmt;
|
|
-- ========== 直接 DDL 小节（Mycat 下如上面 PREPARE 报错，可按序手工执行以下三条） ==========
|
-- ALTER TABLE `charging_pile_account`.`t_vin_management`
|
-- CHANGE COLUMN `appp_user_id` `app_user_id` bigint NULL DEFAULT NULL COMMENT '绑定的小程序用户ID' AFTER `del_flag`;
|
-- ALTER TABLE `charging_pile_account`.`t_vin_management`
|
-- ADD INDEX `idx_app_user_id` (`app_user_id`);
|
