363 lines
31 KiB
SQL
363 lines
31 KiB
SQL
-- thirdparty 支付、聚合支付、任务补偿和商户分账关系统一迁移脚本。
|
|
-- 本文件为统一支付基线迁移；后续功能增量在 db 中独立提供，不通过重跑本文件交付。
|
|
-- 本脚本不删除支付任务、订单、分账关系或历史配置数据；仅在迁移核对通过后清理旧配置字段。
|
|
|
|
USE `pjcloudx_thirdparty`;
|
|
|
|
-- 一、聚合订单码支付会话
|
|
CREATE TABLE IF NOT EXISTS `third_aggregate_pay_session`
|
|
(
|
|
`id` bigint NOT NULL COMMENT '聚合会话主键',
|
|
`aggregate_no` varchar(64) NOT NULL COMMENT '聚合支付会话唯一编号',
|
|
`access_token_hash` char(64) NOT NULL COMMENT '收银URL随机访问令牌SHA-256摘要',
|
|
`access_token_ciphertext` varchar(512) NOT NULL COMMENT '收银URL随机访问令牌Jasypt密文，仅用于幂等复用',
|
|
`biz_type` varchar(32) NOT NULL COMMENT '支付业务类型编码，首版为YXFJ_PAY对应编码',
|
|
`biz_id` varchar(128) NOT NULL COMMENT '调用方业务订单唯一标识',
|
|
`user_id` bigint NOT NULL COMMENT '付款用户ID，匿名消费机订单为-1',
|
|
`mch_id` bigint NOT NULL COMMENT '收款商户ID',
|
|
`price` int NOT NULL COMMENT '应付金额，单位为人民币分',
|
|
`status` varchar(32) NOT NULL COMMENT '聚合会话状态编码',
|
|
`selected_platform` varchar(32) DEFAULT NULL COMMENT '首个有效扫码选定的支付平台编码',
|
|
`pay_no` varchar(128) DEFAULT NULL COMMENT '绑定的third_pay_order.pay_no',
|
|
`payer_identity_hash` char(64) DEFAULT NULL COMMENT '支付宝用户ID或微信服务商公众号sp_openid的SHA-256摘要',
|
|
`expire_time` datetime NOT NULL COMMENT '聚合二维码与第三方子订单服务端失效时间',
|
|
`resolved_time` datetime DEFAULT NULL COMMENT '会话进入成功、关闭、过期或创建失败终态的时间',
|
|
`create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
|
|
`create_time` datetime NOT NULL COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
|
|
`update_time` datetime NOT NULL COMMENT '修改时间',
|
|
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标记，0有效，1删除',
|
|
`tenant_id` bigint NOT NULL COMMENT '租户ID',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_third_aggregate_pay_no` (`tenant_id`, `aggregate_no`),
|
|
UNIQUE KEY `uk_third_aggregate_access_token` (`tenant_id`, `access_token_hash`),
|
|
UNIQUE KEY `uk_third_aggregate_child_pay_no` (`tenant_id`, `pay_no`),
|
|
KEY `idx_third_aggregate_biz` (`tenant_id`, `biz_type`, `biz_id`, `create_time`),
|
|
KEY `idx_third_aggregate_job` (`status`, `expire_time`)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_general_ci
|
|
COMMENT = '支付宝与微信聚合订单码支付会话';
|
|
|
|
-- 二、Quartz 触发的可恢复支付任务和执行尝试
|
|
CREATE TABLE IF NOT EXISTS `third_payment_task`
|
|
(
|
|
`id` bigint NOT NULL COMMENT '任务主键',
|
|
`tenant_id` bigint NOT NULL COMMENT '租户ID',
|
|
`task_type` varchar(48) NOT NULL COMMENT '任务类型编码',
|
|
`task_key` varchar(192) NOT NULL COMMENT '租户内稳定幂等任务键',
|
|
`pay_order_id` bigint DEFAULT NULL COMMENT 'third_pay_order主键',
|
|
`fz_order_id` bigint DEFAULT NULL COMMENT 'third_fz_order主键',
|
|
`fz_relation_id` bigint DEFAULT NULL COMMENT 'third_mch_fz_relation主键',
|
|
`aggregate_session_id` bigint DEFAULT NULL COMMENT 'third_aggregate_pay_session主键',
|
|
`pay_no` varchar(128) DEFAULT NULL COMMENT '平台支付单号',
|
|
`refund_no` varchar(128) DEFAULT NULL COMMENT '平台退款单号',
|
|
`fz_no` varchar(128) DEFAULT NULL COMMENT '平台分账单号',
|
|
`aggregate_no` varchar(128) DEFAULT NULL COMMENT '聚合会话单号',
|
|
`status` varchar(16) NOT NULL COMMENT '任务状态：READY/PROCESSING/RETRY/SUCCESS/FAILED/ALERT/CANCELLED',
|
|
`next_run_time` datetime NOT NULL COMMENT '下一次可执行时间',
|
|
`priority` int NOT NULL DEFAULT 0 COMMENT '任务优先级，数值越大越优先',
|
|
`lease_owner` varchar(128) DEFAULT NULL COMMENT 'Worker实例标识',
|
|
`lease_token` varchar(64) DEFAULT NULL COMMENT '本次执行租约令牌',
|
|
`lease_expire_time` datetime DEFAULT NULL COMMENT '租约失效时间',
|
|
`attempt_count` int NOT NULL DEFAULT 0 COMMENT '累计执行次数',
|
|
`last_external_status` varchar(64) DEFAULT NULL COMMENT '最近一次渠道状态',
|
|
`last_error_code` varchar(128) DEFAULT NULL COMMENT '最近一次错误码',
|
|
`last_error_message` varchar(1024) DEFAULT NULL COMMENT '最近一次错误摘要',
|
|
`finished_time` datetime DEFAULT NULL COMMENT '任务终态时间',
|
|
`create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
|
|
`create_time` datetime NOT NULL COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
|
|
`update_time` datetime NOT NULL COMMENT '更新时间',
|
|
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标记',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_third_payment_task_tenant_key` (`tenant_id`, `task_key`),
|
|
KEY `idx_third_payment_task_due` (`task_type`, `status`, `next_run_time`, `priority`, `id`),
|
|
KEY `idx_third_payment_task_lease` (`task_type`, `status`, `lease_expire_time`),
|
|
KEY `idx_third_payment_task_pay_no` (`tenant_id`, `pay_no`),
|
|
KEY `idx_third_payment_task_fz_no` (`tenant_id`, `fz_no`),
|
|
KEY `idx_third_payment_task_relation` (`tenant_id`, `fz_relation_id`)
|
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '第三方支付任务事实';
|
|
|
|
CREATE TABLE IF NOT EXISTS `third_payment_task_attempt`
|
|
(
|
|
`id` bigint NOT NULL COMMENT '尝试记录主键',
|
|
`task_id` bigint NOT NULL COMMENT '任务主键',
|
|
`tenant_id` bigint NOT NULL COMMENT '租户ID',
|
|
`attempt_no` int NOT NULL COMMENT '任务内尝试序号',
|
|
`trigger_source` varchar(32) NOT NULL COMMENT '触发来源：QUARTZ/CALLBACK/SYNC/MANUAL',
|
|
`worker_id` varchar(128) DEFAULT NULL COMMENT 'Worker实例标识',
|
|
`lease_token` varchar(64) DEFAULT NULL COMMENT '执行租约令牌',
|
|
`request_snapshot` text DEFAULT NULL COMMENT '脱敏请求快照',
|
|
`response_snapshot` text DEFAULT NULL COMMENT '脱敏响应快照',
|
|
`external_status` varchar(64) DEFAULT NULL COMMENT '渠道状态',
|
|
`error_code` varchar(128) DEFAULT NULL COMMENT '错误码',
|
|
`error_message` varchar(1024) DEFAULT NULL COMMENT '错误摘要',
|
|
`started_time` datetime NOT NULL COMMENT '开始时间',
|
|
`finished_time` datetime DEFAULT NULL COMMENT '结束时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_third_payment_attempt_task` (`task_id`, `attempt_no`),
|
|
KEY `idx_third_payment_attempt_tenant_time` (`tenant_id`, `started_time`)
|
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '第三方支付任务执行尝试';
|
|
|
|
-- 三、商户支付分账关系（微信、支付宝共用）
|
|
CREATE TABLE IF NOT EXISTS `third_mch_fz_relation`
|
|
(
|
|
`id` bigint NOT NULL COMMENT '关系主键，应用侧雪花算法生成',
|
|
`tenant_id` bigint NOT NULL COMMENT '租户ID，与mch_manage所属租户一致',
|
|
`mch_id` bigint NOT NULL COMMENT 'mch_manage商户主键',
|
|
`pay_setting_id` bigint NOT NULL COMMENT 'mch_pay_setting支付配置主键',
|
|
`platform_type` varchar(32) NOT NULL COMMENT '支付平台类型：10微信、20支付宝',
|
|
`relation_scene` varchar(64) NOT NULL COMMENT '本地关系场景，默认平台分佣为DEFAULT_PLATFORM_COMMISSION',
|
|
`channel_app_id` varchar(64) NOT NULL COMMENT '微信服务商AppID或支付宝平台AppID快照',
|
|
`channel_sub_mch_id` varchar(64) NOT NULL COMMENT '微信子商户号或历史渠道子商户标识快照',
|
|
`account_key` varchar(64) DEFAULT NULL COMMENT '支付宝命名平台账号键；默认账号及微信为空',
|
|
`app_auth_token` varchar(512) DEFAULT NULL COMMENT '支付宝第三方代调用授权令牌；仅服务端渠道调用使用',
|
|
`receiver_type` varchar(32) NOT NULL COMMENT '渠道接收方类型，如MERCHANT_ID、userId、loginName',
|
|
`receiver_account` varchar(128) NOT NULL COMMENT '平台分佣接收账号；管理端展示时必须脱敏',
|
|
`receiver_name` varchar(128) DEFAULT NULL COMMENT '平台分佣接收方名称快照',
|
|
`fz_ratio` decimal(6,3) NOT NULL DEFAULT '0.000' COMMENT '平台抽佣比例，单位百分比，范围0到100，精确到0.001%',
|
|
`channel_relation_type` varchar(64) DEFAULT NULL COMMENT '渠道关系类型；微信默认SERVICE_PROVIDER，支付宝允许为空',
|
|
`out_request_no` varchar(64) NOT NULL COMMENT '支付宝绑定稳定幂等请求号；微信作为本地稳定审计号',
|
|
`status` varchar(32) NOT NULL COMMENT 'PENDING_BIND、BINDING、BOUND、BIND_FAILED或SUPERSEDED',
|
|
`is_default` char(1) NOT NULL DEFAULT '1' COMMENT '是否为当前支付配置默认关系：0否、1是',
|
|
`bind_req` text DEFAULT NULL COMMENT '脱敏后的关系绑定请求快照',
|
|
`bind_res` text DEFAULT NULL COMMENT '渠道关系绑定或查询响应快照',
|
|
`bind_error` varchar(1000) DEFAULT NULL COMMENT '最近一次绑定错误摘要，不含凭据',
|
|
`last_external_status` varchar(128) DEFAULT NULL COMMENT '最近一次渠道结果码或外部状态',
|
|
`last_query_time` datetime DEFAULT NULL COMMENT '最近一次关系绑定或查询时间',
|
|
`bound_time` datetime DEFAULT NULL COMMENT '渠道明确确认关系已绑定的时间',
|
|
`create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
|
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
|
`update_by` varchar(32) DEFAULT NULL COMMENT '更新人',
|
|
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
|
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标记：0正常、1删除',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_third_mch_fz_relation_identity`
|
|
(`tenant_id`, `pay_setting_id`, `platform_type`, `channel_app_id`, `channel_sub_mch_id`,
|
|
`receiver_type`, `receiver_account`, `relation_scene`),
|
|
KEY `idx_third_mch_fz_relation_default`
|
|
(`tenant_id`, `mch_id`, `platform_type`, `is_default`, `status`, `del_flag`),
|
|
KEY `idx_third_mch_fz_relation_task`
|
|
(`status`, `is_default`, `update_time`, `id`)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_general_ci
|
|
COMMENT = '商户第三方支付分账关系';
|
|
|
|
-- 四、旧环境补齐关系引用字段。每个字段独立判断，避免一个缺表/缺列阻断后续迁移。
|
|
SET @task_relation_column_exists = (
|
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_payment_task' AND COLUMN_NAME = 'fz_relation_id'
|
|
);
|
|
SET @task_relation_column_sql = IF(
|
|
@task_relation_column_exists = 0,
|
|
'ALTER TABLE `third_payment_task` ADD COLUMN `fz_relation_id` bigint DEFAULT NULL COMMENT ''third_mch_fz_relation主键''',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE task_relation_column_stmt FROM @task_relation_column_sql;
|
|
EXECUTE task_relation_column_stmt;
|
|
DEALLOCATE PREPARE task_relation_column_stmt;
|
|
|
|
SET @pay_relation_column_exists = (
|
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND COLUMN_NAME = 'fz_relation_id'
|
|
);
|
|
SET @pay_order_table_exists = (
|
|
SELECT COUNT(1) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order'
|
|
);
|
|
SET @pay_relation_column_sql = IF(
|
|
@pay_order_table_exists = 1 AND @pay_relation_column_exists = 0,
|
|
'ALTER TABLE `third_pay_order` ADD COLUMN `fz_relation_id` bigint DEFAULT NULL COMMENT ''支付创建时绑定的平台分佣关系主键''',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE pay_relation_column_stmt FROM @pay_relation_column_sql;
|
|
EXECUTE pay_relation_column_stmt;
|
|
DEALLOCATE PREPARE pay_relation_column_stmt;
|
|
|
|
SET @relation_auth_column_exists = (
|
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_mch_fz_relation' AND COLUMN_NAME = 'app_auth_token'
|
|
);
|
|
SET @relation_auth_column_sql = IF(
|
|
@relation_auth_column_exists = 0,
|
|
'ALTER TABLE `third_mch_fz_relation` ADD COLUMN `app_auth_token` varchar(512) DEFAULT NULL COMMENT ''支付宝第三方代调用授权令牌；仅服务端渠道调用使用''',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE relation_auth_column_stmt FROM @relation_auth_column_sql;
|
|
EXECUTE relation_auth_column_stmt;
|
|
DEALLOCATE PREPARE relation_auth_column_stmt;
|
|
|
|
SET @relation_ratio_column_exists = (
|
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_mch_fz_relation' AND COLUMN_NAME = 'fz_ratio'
|
|
);
|
|
SET @relation_ratio_column_sql = IF(
|
|
@relation_ratio_column_exists = 0,
|
|
'ALTER TABLE `third_mch_fz_relation` ADD COLUMN `fz_ratio` decimal(6,3) NOT NULL DEFAULT ''0.000'' COMMENT ''平台抽佣比例，单位百分比，范围0到100，精确到0.001%''',
|
|
'ALTER TABLE `third_mch_fz_relation` MODIFY COLUMN `fz_ratio` decimal(6,3) NOT NULL DEFAULT ''0.000'' COMMENT ''平台抽佣比例，单位百分比，范围0到100，精确到0.001%'''
|
|
);
|
|
PREPARE relation_ratio_column_stmt FROM @relation_ratio_column_sql;
|
|
EXECUTE relation_ratio_column_stmt;
|
|
DEALLOCATE PREPARE relation_ratio_column_stmt;
|
|
|
|
-- 五、补齐任务和业务扫描索引。索引已存在时不重复创建。
|
|
SET @index_ddl = IF(EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_payment_task' AND INDEX_NAME = 'idx_third_payment_task_relation'), 'SELECT 1', 'ALTER TABLE `third_payment_task` ADD INDEX `idx_third_payment_task_relation` (`tenant_id`, `fz_relation_id`)');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
|
|
SET @pay_task_scan_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 5 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND COLUMN_NAME IN ('status', 'del_flag', 'pay_platform_type', 'update_time', 'id'));
|
|
SET @pay_fz_scan_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 5 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND COLUMN_NAME IN ('status', 'enable_fz', 'del_flag', 'pay_time', 'id'));
|
|
SET @pay_callback_scan_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 5 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND COLUMN_NAME IN ('status', 'is_biz_callback', 'del_flag', 'update_time', 'id'));
|
|
SET @pay_close_scan_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 5 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND COLUMN_NAME IN ('status', 'biz_type', 'del_flag', 'create_time', 'id'));
|
|
SET @pay_tenant_no_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 3 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND COLUMN_NAME IN ('tenant_id', 'pay_no', 'del_flag'));
|
|
SET @pay_relation_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 2 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND COLUMN_NAME IN ('tenant_id', 'fz_relation_id'));
|
|
SET @fz_task_scan_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 4 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_fz_order' AND COLUMN_NAME IN ('status', 'del_flag', 'update_time', 'id'));
|
|
SET @fz_tenant_no_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 3 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_fz_order' AND COLUMN_NAME IN ('tenant_id', 'pay_no', 'del_flag'));
|
|
SET @aggregate_task_scan_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 4 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_aggregate_pay_session' AND COLUMN_NAME IN ('status', 'del_flag', 'update_time', 'id'));
|
|
SET @aggregate_close_scan_ready = (SELECT COUNT(DISTINCT COLUMN_NAME) = 4 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_aggregate_pay_session' AND COLUMN_NAME IN ('status', 'del_flag', 'create_time', 'id'));
|
|
|
|
SET @index_ddl = IF(@pay_task_scan_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND INDEX_NAME = 'idx_third_pay_order_task_scan'), 'ALTER TABLE `third_pay_order` ADD INDEX `idx_third_pay_order_task_scan` (`status`, `del_flag`, `pay_platform_type`, `update_time`, `id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@pay_fz_scan_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND INDEX_NAME = 'idx_third_pay_order_fz_scan'), 'ALTER TABLE `third_pay_order` ADD INDEX `idx_third_pay_order_fz_scan` (`status`, `enable_fz`, `del_flag`, `pay_time`, `id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@pay_callback_scan_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND INDEX_NAME = 'idx_third_pay_order_callback_scan'), 'ALTER TABLE `third_pay_order` ADD INDEX `idx_third_pay_order_callback_scan` (`status`, `is_biz_callback`, `del_flag`, `update_time`, `id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@pay_close_scan_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND INDEX_NAME = 'idx_third_pay_order_close_scan'), 'ALTER TABLE `third_pay_order` ADD INDEX `idx_third_pay_order_close_scan` (`status`, `biz_type`, `del_flag`, `create_time`, `id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@pay_tenant_no_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND INDEX_NAME = 'idx_third_pay_order_tenant_pay_no'), 'ALTER TABLE `third_pay_order` ADD INDEX `idx_third_pay_order_tenant_pay_no` (`tenant_id`, `pay_no`, `del_flag`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@pay_relation_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_pay_order' AND INDEX_NAME = 'idx_third_pay_order_fz_relation'), 'ALTER TABLE `third_pay_order` ADD INDEX `idx_third_pay_order_fz_relation` (`tenant_id`, `fz_relation_id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@fz_task_scan_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_fz_order' AND INDEX_NAME = 'idx_third_fz_order_task_scan'), 'ALTER TABLE `third_fz_order` ADD INDEX `idx_third_fz_order_task_scan` (`status`, `del_flag`, `update_time`, `id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@fz_tenant_no_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_fz_order' AND INDEX_NAME = 'idx_third_fz_order_tenant_pay_no'), 'ALTER TABLE `third_fz_order` ADD INDEX `idx_third_fz_order_tenant_pay_no` (`tenant_id`, `pay_no`, `del_flag`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@aggregate_task_scan_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_aggregate_pay_session' AND INDEX_NAME = 'idx_third_aggregate_task_scan'), 'ALTER TABLE `third_aggregate_pay_session` ADD INDEX `idx_third_aggregate_task_scan` (`status`, `del_flag`, `update_time`, `id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
SET @index_ddl = IF(@aggregate_close_scan_ready = 1 AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'third_aggregate_pay_session' AND INDEX_NAME = 'idx_third_aggregate_close_scan'), 'ALTER TABLE `third_aggregate_pay_session` ADD INDEX `idx_third_aggregate_close_scan` (`status`, `del_flag`, `create_time`, `id`)', 'SELECT 1');
|
|
PREPARE index_stmt FROM @index_ddl; EXECUTE index_stmt; DEALLOCATE PREPARE index_stmt;
|
|
|
|
-- 六、迁移钱包历史配置。只有关系完整、JSON合法且数据已核对时，才清理旧配置键和旧比例列。
|
|
SET @wallet_table_exists = (
|
|
SELECT COUNT(1) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = 'pjcloudx_wallet' AND TABLE_NAME = 'mch_pay_setting'
|
|
);
|
|
SET @legacy_ratio_column_exists = (
|
|
SELECT COUNT(1) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = 'pjcloudx_wallet' AND TABLE_NAME = 'mch_pay_setting' AND COLUMN_NAME = 'fz_ratio'
|
|
);
|
|
SET @legacy_invalid_json_count = 0;
|
|
SET @legacy_invalid_json_sql = IF(
|
|
@wallet_table_exists = 1,
|
|
'SELECT COUNT(1) INTO @legacy_invalid_json_count FROM `pjcloudx_wallet`.`mch_pay_setting` WHERE platform_type IN (''10'', ''20'') AND setting_json IS NOT NULL AND TRIM(setting_json) <> '''' AND JSON_VALID(setting_json) = 0',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE legacy_invalid_json_stmt FROM @legacy_invalid_json_sql;
|
|
EXECUTE legacy_invalid_json_stmt;
|
|
DEALLOCATE PREPARE legacy_invalid_json_stmt;
|
|
|
|
SET @legacy_channel_update_sql = IF(
|
|
@wallet_table_exists = 1 AND @legacy_invalid_json_count = 0,
|
|
'UPDATE `pjcloudx_thirdparty`.`third_mch_fz_relation` r JOIN `pjcloudx_wallet`.`mch_pay_setting` s ON s.id = r.pay_setting_id AND s.mch_id = r.mch_id AND s.tenant_id = r.tenant_id SET r.channel_sub_mch_id = CASE WHEN NULLIF(TRIM(r.channel_sub_mch_id), '''') IS NULL THEN NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.subMchid''))), ''''), ''null'') ELSE r.channel_sub_mch_id END, r.account_key = CASE WHEN s.platform_type = ''20'' AND NULLIF(TRIM(r.account_key), '''') IS NULL THEN NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.accountKey''))), ''''), ''null'') ELSE r.account_key END, r.app_auth_token = CASE WHEN s.platform_type = ''20'' AND NULLIF(TRIM(r.app_auth_token), '''') IS NULL THEN NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.appAuthToken''))), ''''), ''null'') ELSE r.app_auth_token END WHERE s.platform_type IN (''10'', ''20'') AND JSON_VALID(s.setting_json) = 1 AND r.del_flag = ''0'' AND r.is_default = ''1''',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE legacy_channel_update_stmt FROM @legacy_channel_update_sql;
|
|
EXECUTE legacy_channel_update_stmt;
|
|
DEALLOCATE PREPARE legacy_channel_update_stmt;
|
|
|
|
SET @legacy_ratio_update_sql = IF(
|
|
@wallet_table_exists = 1 AND @legacy_ratio_column_exists = 1,
|
|
'UPDATE `pjcloudx_thirdparty`.`third_mch_fz_relation` r JOIN `pjcloudx_wallet`.`mch_pay_setting` s ON s.id = r.pay_setting_id AND s.mch_id = r.mch_id AND s.tenant_id = r.tenant_id SET r.fz_ratio = LEAST(GREATEST(COALESCE(s.fz_ratio, 0), 0), 100) WHERE s.del_flag = ''0'' AND r.del_flag = ''0'' AND r.is_default = ''1'' AND COALESCE(s.fz_ratio, 0) > 0 AND COALESCE(r.fz_ratio, 0) = 0',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE legacy_ratio_update_stmt FROM @legacy_ratio_update_sql;
|
|
EXECUTE legacy_ratio_update_stmt;
|
|
DEALLOCATE PREPARE legacy_ratio_update_stmt;
|
|
|
|
SET @legacy_channel_unmigrated_count = 0;
|
|
SET @legacy_channel_unmigrated_sql = IF(
|
|
@wallet_table_exists = 1 AND @legacy_invalid_json_count = 0,
|
|
'SELECT COUNT(1) INTO @legacy_channel_unmigrated_count FROM `pjcloudx_wallet`.`mch_pay_setting` s WHERE s.del_flag = ''0'' AND s.platform_type IN (''10'', ''20'') AND JSON_VALID(s.setting_json) = 1 AND (NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.subMchid''))), ''''), ''null'') IS NOT NULL OR NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.accountKey''))), ''''), ''null'') IS NOT NULL OR NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.appAuthToken''))), ''''), ''null'') IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM `pjcloudx_thirdparty`.`third_mch_fz_relation` r WHERE r.tenant_id = s.tenant_id AND r.mch_id = s.mch_id AND r.pay_setting_id = s.id AND r.platform_type = s.platform_type AND r.is_default = ''1'' AND r.del_flag = ''0'' AND (NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.subMchid''))), ''''), ''null'') IS NULL OR r.channel_sub_mch_id = NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.subMchid''))), ''''), ''null'')) AND (s.platform_type <> ''20'' OR NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.accountKey''))), ''''), ''null'') IS NULL OR r.account_key = NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.accountKey''))), ''''), ''null'')) AND (s.platform_type <> ''20'' OR NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.appAuthToken''))), ''''), ''null'') IS NULL OR r.app_auth_token = NULLIF(NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(s.setting_json, ''$.appAuthToken''))), ''''), ''null'')))',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE legacy_channel_unmigrated_stmt FROM @legacy_channel_unmigrated_sql;
|
|
EXECUTE legacy_channel_unmigrated_stmt;
|
|
DEALLOCATE PREPARE legacy_channel_unmigrated_stmt;
|
|
|
|
SET @legacy_ratio_unmigrated_count = 0;
|
|
SET @legacy_ratio_unmigrated_sql = IF(
|
|
@wallet_table_exists = 1 AND @legacy_ratio_column_exists = 1,
|
|
'SELECT COUNT(1) INTO @legacy_ratio_unmigrated_count FROM `pjcloudx_wallet`.`mch_pay_setting` s WHERE COALESCE(s.fz_ratio, 0) > 0 AND s.del_flag = ''0'' AND NOT EXISTS (SELECT 1 FROM `pjcloudx_thirdparty`.`third_mch_fz_relation` r WHERE r.tenant_id = s.tenant_id AND r.mch_id = s.mch_id AND r.pay_setting_id = s.id AND r.is_default = ''1'' AND r.fz_ratio > 0 AND r.del_flag = ''0'')',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE legacy_ratio_unmigrated_stmt FROM @legacy_ratio_unmigrated_sql;
|
|
EXECUTE legacy_ratio_unmigrated_stmt;
|
|
DEALLOCATE PREPARE legacy_ratio_unmigrated_stmt;
|
|
|
|
SET @legacy_json_cleanup_sql = IF(
|
|
@wallet_table_exists = 1 AND @legacy_invalid_json_count = 0 AND @legacy_channel_unmigrated_count = 0 AND @legacy_ratio_unmigrated_count = 0,
|
|
'UPDATE `pjcloudx_wallet`.`mch_pay_setting` SET setting_json = CASE WHEN JSON_LENGTH(JSON_REMOVE(setting_json, ''$.subMchid'', ''$.accountKey'', ''$.appAuthToken'')) = 0 THEN NULL ELSE JSON_REMOVE(setting_json, ''$.subMchid'', ''$.accountKey'', ''$.appAuthToken'') END WHERE platform_type IN (''10'', ''20'') AND JSON_VALID(setting_json) = 1',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE legacy_json_cleanup_stmt FROM @legacy_json_cleanup_sql;
|
|
EXECUTE legacy_json_cleanup_stmt;
|
|
DEALLOCATE PREPARE legacy_json_cleanup_stmt;
|
|
|
|
SET @drop_legacy_ratio_sql = IF(
|
|
@wallet_table_exists = 1 AND @legacy_ratio_column_exists = 1 AND @legacy_invalid_json_count = 0 AND @legacy_channel_unmigrated_count = 0 AND @legacy_ratio_unmigrated_count = 0,
|
|
'ALTER TABLE `pjcloudx_wallet`.`mch_pay_setting` DROP COLUMN `fz_ratio`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE drop_legacy_ratio_stmt FROM @drop_legacy_ratio_sql;
|
|
EXECUTE drop_legacy_ratio_stmt;
|
|
DEALLOCATE PREPARE drop_legacy_ratio_stmt;
|
|
|
|
-- 七、输出迁移结果。出现BLOCKED时不要上线新代码，先处理对应数据或数据库前置问题。
|
|
SET @required_table_count = (
|
|
SELECT COUNT(DISTINCT TABLE_NAME)
|
|
FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME IN ('third_aggregate_pay_session', 'third_payment_task',
|
|
'third_payment_task_attempt', 'third_mch_fz_relation',
|
|
'third_pay_order', 'third_fz_order')
|
|
);
|
|
SET @required_column_count = (
|
|
SELECT COUNT(1)
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND ((TABLE_NAME = 'third_mch_fz_relation' AND COLUMN_NAME IN ('app_auth_token', 'fz_ratio'))
|
|
OR (TABLE_NAME = 'third_payment_task' AND COLUMN_NAME = 'fz_relation_id')
|
|
OR (TABLE_NAME = 'third_pay_order' AND COLUMN_NAME = 'fz_relation_id'))
|
|
);
|
|
SET @skipped_scan_index_count =
|
|
(@pay_task_scan_ready = 0) + (@pay_fz_scan_ready = 0) + (@pay_callback_scan_ready = 0)
|
|
+ (@pay_close_scan_ready = 0) + (@pay_tenant_no_ready = 0) + (@pay_relation_ready = 0)
|
|
+ (@fz_task_scan_ready = 0) + (@fz_tenant_no_ready = 0)
|
|
+ (@aggregate_task_scan_ready = 0) + (@aggregate_close_scan_ready = 0);
|
|
|
|
SELECT CASE
|
|
WHEN @required_table_count <> 6 THEN 'BLOCKED_REQUIRED_THIRD_TABLE_NOT_FOUND'
|
|
WHEN @required_column_count <> 4 THEN 'BLOCKED_REQUIRED_THIRD_COLUMN_NOT_FOUND'
|
|
WHEN @skipped_scan_index_count > 0 THEN 'BLOCKED_SCAN_INDEX_SOURCE_COLUMN_NOT_FOUND'
|
|
WHEN @wallet_table_exists = 0 THEN 'BLOCKED_WALLET_TABLE_NOT_FOUND'
|
|
WHEN @legacy_invalid_json_count > 0 THEN 'BLOCKED_INVALID_SETTING_JSON'
|
|
WHEN @legacy_channel_unmigrated_count > 0 THEN 'BLOCKED_UNMIGRATED_CHANNEL_CONFIG'
|
|
WHEN @legacy_ratio_unmigrated_count > 0 THEN 'BLOCKED_UNMIGRATED_POSITIVE_RATIO'
|
|
ELSE 'SUCCESS_THIRD_PAYMENT_SCHEMA_MIGRATED'
|
|
END AS migration_status,
|
|
@legacy_invalid_json_count AS invalid_setting_json_count,
|
|
@legacy_channel_unmigrated_count AS unmigrated_channel_config_count,
|
|
@legacy_ratio_unmigrated_count AS unmigrated_positive_ratio_count,
|
|
@required_table_count AS required_third_table_count,
|
|
@required_column_count AS required_third_column_count,
|
|
@skipped_scan_index_count AS skipped_scan_index_count;
|
|
|
|
SELECT table_name, column_name, column_type
|
|
FROM information_schema.COLUMNS
|
|
WHERE table_schema = DATABASE()
|
|
AND ((table_name = 'third_mch_fz_relation' AND column_name IN ('app_auth_token', 'fz_ratio'))
|
|
OR (table_name = 'third_payment_task' AND column_name = 'fz_relation_id')
|
|
OR (table_name = 'third_pay_order' AND column_name = 'fz_relation_id'))
|
|
ORDER BY table_name, ordinal_position;
|