-- 刷卡充电 V0.2 定稿：卡片密码配置、充电期间锁定、订单结算幂等扣款（允许负余额）
|
-- 目标库：charging_pile_account（t_charging_card、t_charging_card_balance_record）
|
-- 适用：MySQL（含 Mycat 代理下的 MySQL 8 后端）。Mycat 对 PREPARE/INFORMATION_SCHEMA 变量支持不稳定时，
|
-- 可直接执行下方「直接 DDL 小节」并回查。
|
-- 脚本幂等：字段/索引已存在则自动跳过，可重复执行。
|
|
USE `charging_pile_account`;
|
|
-- ============ 1. t_charging_card 新增 password / charging_flag / charging_order_code ============
|
SET @has_password_col := (
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card' AND COLUMN_NAME = 'password'
|
);
|
SET @add_password_sql := IF(
|
@has_password_col = 0,
|
'ALTER TABLE `t_charging_card` ADD COLUMN `password` varchar(100) DEFAULT NULL COMMENT ''卡密码(BCrypt密文,空=未配置不校验)'' AFTER `card_no`',
|
'SELECT 1'
|
);
|
PREPARE add_password_stmt FROM @add_password_sql;
|
EXECUTE add_password_stmt;
|
DEALLOCATE PREPARE add_password_stmt;
|
|
SET @has_charging_flag_col := (
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card' AND COLUMN_NAME = 'charging_flag'
|
);
|
SET @add_charging_flag_sql := IF(
|
@has_charging_flag_col = 0,
|
'ALTER TABLE `t_charging_card` ADD COLUMN `charging_flag` tinyint NOT NULL DEFAULT 0 COMMENT ''充电锁定:0未充电,1充电中锁定'' AFTER `status`',
|
'SELECT 1'
|
);
|
PREPARE add_charging_flag_stmt FROM @add_charging_flag_sql;
|
EXECUTE add_charging_flag_stmt;
|
DEALLOCATE PREPARE add_charging_flag_stmt;
|
|
SET @has_charging_order_code_col := (
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card' AND COLUMN_NAME = 'charging_order_code'
|
);
|
SET @add_charging_order_code_sql := IF(
|
@has_charging_order_code_col = 0,
|
'ALTER TABLE `t_charging_card` ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT ''充电锁定订单号'' AFTER `charging_flag`',
|
'SELECT 1'
|
);
|
PREPARE add_charging_order_code_stmt FROM @add_charging_order_code_sql;
|
EXECUTE add_charging_order_code_stmt;
|
DEALLOCATE PREPARE add_charging_order_code_stmt;
|
|
-- ============ 2. t_charging_card_balance_record 新增充电订单号（扣款幂等键）与唯一索引 ============
|
SET @has_record_order_col := (
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card_balance_record' AND COLUMN_NAME = 'charging_order_code'
|
);
|
SET @add_record_order_sql := IF(
|
@has_record_order_col = 0,
|
'ALTER TABLE `t_charging_card_balance_record` ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT ''关联订单号(刷卡充电订单结算扣款,幂等键)'' AFTER `remark`',
|
'SELECT 1'
|
);
|
PREPARE add_record_order_stmt FROM @add_record_order_sql;
|
EXECUTE add_record_order_stmt;
|
DEALLOCATE PREPARE add_record_order_stmt;
|
|
SET @has_record_uk := (
|
SELECT COUNT(1) FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card_balance_record' AND INDEX_NAME = 'uk_order_code'
|
);
|
SET @add_record_uk_sql := IF(
|
@has_record_uk = 0,
|
'ALTER TABLE `t_charging_card_balance_record` ADD UNIQUE KEY `uk_order_code` (`charging_order_code`)',
|
'SELECT 1'
|
);
|
PREPARE add_record_uk_stmt FROM @add_record_uk_sql;
|
EXECUTE add_record_uk_stmt;
|
DEALLOCATE PREPARE add_record_uk_stmt;
|
|
-- ========== 直接 DDL 小节（Mycat 下如上面 PREPARE 报错，可按序手工执行以下四条） ==========
|
-- ALTER TABLE `charging_pile_account`.`t_charging_card`
|
-- ADD COLUMN `password` varchar(100) DEFAULT NULL COMMENT '卡密码(BCrypt密文,空=未配置不校验)' AFTER `card_no`;
|
-- ALTER TABLE `charging_pile_account`.`t_charging_card`
|
-- ADD COLUMN `charging_flag` tinyint NOT NULL DEFAULT 0 COMMENT '充电锁定:0未充电,1充电中锁定' AFTER `status`;
|
-- ALTER TABLE `charging_pile_account`.`t_charging_card`
|
-- ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT '充电锁定订单号' AFTER `charging_flag`;
|
-- ALTER TABLE `charging_pile_account`.`t_charging_card_balance_record`
|
-- ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT '关联订单号(刷卡充电订单结算扣款,幂等键)' AFTER `remark`;
|
-- ALTER TABLE `charging_pile_account`.`t_charging_card_balance_record`
|
-- ADD UNIQUE KEY `uk_order_code` (`charging_order_code`);
|
