-- 充电桩订单（t_charging_order）新增「白名单企业账户充电」相关列
|
-- 目的：
|
-- 1) max_end_time：授权截止时间（监控回传超时前 1 分钟主动停充）
|
-- 2) locked_amount：建单时锁定的企业账户额度（共享=公司余额锁定，分配=白名单账户余额锁定）
|
-- 3) account_mode：账户模式快照（1=共享，2=分配）
|
-- 4) discount_electrovalence / discount_service_charge：企业优惠合计（原价-优惠价），与 whitelist_* 三列快照双写
|
-- 目标库：charging_pile_order（表 t_charging_order，TBPARTITION BY MOD_HASH(app_user_id) TBPARTITIONS 20，测试环境实际 50 个物理分表）
|
-- 执行方式：
|
-- 1) 首选「小节 A」经数据库代理（MyCat 8066）执行，代理会把 DDL 广播到全部物理分表；
|
-- 2) 代理不支持分片表 DDL 时，改用「小节 B」直连后端 MySQL 实例执行（存储过程，逐物理分表，幂等）；
|
-- 3) 最后执行「小节 C」回查校验。
|
-- 幂等说明：小节 A 为裸 ALTER，重复执行会报 Duplicate column，忽略即可（以小节 C 回查为准）；
|
-- 小节 B 先查 information_schema 再变更，可重复执行。
|
-- 部署提示：必须先执行本脚本再发布订单服务代码，否则白名单企业账户建单会报 Unknown column 'account_mode'。
|
-- 依赖：account 库 t_app_user_whitelist.locked_amount（见同目录 20260929_whitelist_locked_amount.sql）、
|
-- other 库 t_group_company.locked_amount（见 doc/ddlA_other.sql，VIN 充电改造已包含）
|
|
-- ============ 小节 A：经代理执行（推荐） ============
|
USE `charging_pile_order`;
|
|
ALTER TABLE `t_charging_order`
|
ADD COLUMN `max_end_time` datetime DEFAULT NULL COMMENT '最大充电结束时间（授权截止时间，全天授权为空）';
|
|
ALTER TABLE `t_charging_order`
|
ADD COLUMN `locked_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '申请时锁定金额（共享=企业锁定额，分配=白名单账户锁定额）';
|
|
ALTER TABLE `t_charging_order`
|
ADD COLUMN `account_mode` tinyint DEFAULT NULL COMMENT '账户模式快照（1=共享，2=分配）';
|
|
ALTER TABLE `t_charging_order`
|
ADD COLUMN `discount_electrovalence` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '优惠电费合计（原始电费-最终电费）';
|
|
ALTER TABLE `t_charging_order`
|
ADD COLUMN `discount_service_charge` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '优惠服务费合计（原始服务费-最终服务费）';
|
|
-- ============ 小节 B：直连后端 MySQL 实例执行（代理不可用时分表执行，幂等） ============
|
USE `charging_pile_order`;
|
|
DROP PROCEDURE IF EXISTS `add_charging_whitelist_enterprise_columns`;
|
DELIMITER $$
|
CREATE PROCEDURE `add_charging_whitelist_enterprise_columns`()
|
BEGIN
|
DECLARE done INT DEFAULT 0;
|
DECLARE target_table VARCHAR(64);
|
DECLARE column_exists INT DEFAULT 0;
|
|
-- 覆盖逻辑表（若物理存在）与 TBPARTITION 物理分表 t_charging_order_0..N
|
DECLARE table_cursor CURSOR FOR
|
SELECT t.table_name
|
FROM information_schema.tables t
|
WHERE t.table_schema = DATABASE()
|
AND t.table_name REGEXP '^t_charging_order(_[0-9]+)?$';
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
|
|
OPEN table_cursor;
|
table_loop: LOOP
|
FETCH table_cursor INTO target_table;
|
IF done = 1 THEN
|
LEAVE table_loop;
|
END IF;
|
|
SELECT COUNT(*) INTO column_exists
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = target_table
|
AND column_name = 'max_end_time';
|
IF column_exists = 0 THEN
|
SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `max_end_time` datetime DEFAULT NULL COMMENT ''最大充电结束时间（授权截止时间，全天授权为空）''');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
|
SELECT COUNT(*) INTO column_exists
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = target_table
|
AND column_name = 'locked_amount';
|
IF column_exists = 0 THEN
|
SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `locked_amount` decimal(12,2) NOT NULL DEFAULT ''0.00'' COMMENT ''申请时锁定金额（共享=企业锁定额，分配=白名单账户锁定额）''');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
|
SELECT COUNT(*) INTO column_exists
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = target_table
|
AND column_name = 'account_mode';
|
IF column_exists = 0 THEN
|
SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `account_mode` tinyint DEFAULT NULL COMMENT ''账户模式快照（1=共享，2=分配）''');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
|
SELECT COUNT(*) INTO column_exists
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = target_table
|
AND column_name = 'discount_electrovalence';
|
IF column_exists = 0 THEN
|
SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `discount_electrovalence` decimal(12,2) NOT NULL DEFAULT ''0.00'' COMMENT ''优惠电费合计（原始电费-最终电费）''');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
|
SELECT COUNT(*) INTO column_exists
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = target_table
|
AND column_name = 'discount_service_charge';
|
IF column_exists = 0 THEN
|
SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `discount_service_charge` decimal(12,2) NOT NULL DEFAULT ''0.00'' COMMENT ''优惠服务费合计（原始服务费-最终服务费）''');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
END LOOP;
|
CLOSE table_cursor;
|
END$$
|
DELIMITER ;
|
|
CALL `add_charging_whitelist_enterprise_columns`();
|
DROP PROCEDURE `add_charging_whitelist_enterprise_columns`;
|
|
-- ============ 小节 C：回查校验 ============
|
SELECT t.table_name, c.column_name, c.column_type, c.column_comment
|
FROM information_schema.tables t
|
JOIN information_schema.columns c
|
ON c.table_schema = t.table_schema
|
AND c.table_name = t.table_name
|
WHERE t.table_schema = DATABASE()
|
AND t.table_name REGEXP '^t_charging_order(_[0-9]+)?$'
|
AND c.column_name IN ('max_end_time', 'locked_amount', 'account_mode', 'discount_electrovalence', 'discount_service_charge')
|
ORDER BY t.table_name, c.column_name;
|
|
SELECT COUNT(DISTINCT t.table_name) AS order_shard_count,
|
SUM(CASE WHEN c.column_name = 'max_end_time' THEN 1 ELSE 0 END) AS max_end_time_columns,
|
SUM(CASE WHEN c.column_name = 'locked_amount' THEN 1 ELSE 0 END) AS locked_amount_columns,
|
SUM(CASE WHEN c.column_name = 'account_mode' THEN 1 ELSE 0 END) AS account_mode_columns,
|
SUM(CASE WHEN c.column_name = 'discount_electrovalence' THEN 1 ELSE 0 END) AS discount_electrovalence_columns,
|
SUM(CASE WHEN c.column_name = 'discount_service_charge' THEN 1 ELSE 0 END) AS discount_service_charge_columns
|
FROM information_schema.tables t
|
JOIN information_schema.columns c
|
ON c.table_schema = t.table_schema
|
AND c.table_name = t.table_name
|
WHERE t.table_schema = DATABASE()
|
AND t.table_name REGEXP '^t_charging_order(_[0-9]+)?$'
|
AND c.column_name IN ('max_end_time', 'locked_amount', 'account_mode', 'discount_electrovalence', 'discount_service_charge');
|
