Home Explore Help
Sign In
huangyang
/
national-motion-backend
1
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Branch: master
Branches Tags
national-motion...
/
updateSql.md

updateSql.md 7.7 KB
Permalink History Raw

-----------------------------2025.9.19

建表：（分账详情表） CREATE TABLE nm_profit_sharing_info ( id varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID', profit_sharing_records_id varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分账记录ID', order_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '订单ID', org_code varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分账接收方所属组织', mch_id varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商户号', mch_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商户全称', ratio decimal(10,2) DEFAULT '0.00' COMMENT '分账比例', pre_amount int NOT NULL COMMENT '分账金额（分）', amount int DEFAULT NULL COMMENT '实际分账金额（分）', status varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分账状态', create_time datetime DEFAULT NULL COMMENT '创建时间', update_time datetime DEFAULT NULL COMMENT '修改时间', del_flag int DEFAULT NULL COMMENT '逻辑删除标识(0-正常,1-已删除)', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分账详情表';

CREATE TABLE nm_profit_sharing_records ( id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID', profit_sharing_no varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分账单号', order_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '订单ID', order_amount int NOT NULL COMMENT '订单原金额(分)', amount int DEFAULT NULL COMMENT '分账金额(分)', procedure_fee int DEFAULT NULL COMMENT '微信分账手续费（分）0.06%', status int DEFAULT NULL COMMENT '分账状态（0-处理中 1-分账完成 2-分账失败）', fail_reason varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分账失败原因', profit_sharing_time datetime DEFAULT NULL COMMENT '分账时间', create_time datetime DEFAULT NULL COMMENT '创建时间', update_time datetime DEFAULT NULL COMMENT '修改时间', del_flag int DEFAULT NULL COMMENT '逻辑删除标识(0-正常,1-已删除)', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分账记录表';

sys_depart增加字段： ALTER TABLE sys_depart ADD mch_name varchar(255) COMMENT '商户全称(微信商户号主体)' AFTER org_id_card_num; ALTER TABLE sys_depart ADD mch_id varchar(32) COMMENT '商户号' AFTER org_id_card_num; ALTER TABLE sys_depart ADD appid varchar(64) COMMENT 'appid' AFTER org_id_card_num; ALTER TABLE sys_depart ADD system_type int COMMENT '系统商户类别（0-平台 1-商户 2-门店）' AFTER org_code;

nm_order增加字段： ALTER TABLE nm_order ADD profit_sharing_status tinyint(1) COMMENT '分账状态（0-待分账 1-已分账 2-分账回退）' AFTER or_profit_sharing; ALTER TABLE nm_order ADD profit_sharing_order_id varchar(64) COMMENT '微信分账单号' AFTER transaction_id;

2025-9-30 通过建表语句直接修改表结构：（utf8mb4_unicode_ci ---> utf8mb4_general_ci）

CREATE TABLE nm_order_refunds_info ( id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID', order_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单ID', order_code varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商户订单号', order_pro_info_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款子订单ID', out_refund_no varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商户退款单号', refund_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信支付退款单号', transaction_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信支付订单号', reason varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款原因', accepted_time datetime DEFAULT NULL COMMENT '退款受理时间', success_time datetime DEFAULT NULL COMMENT '退款成功时间', amount decimal(10,2) DEFAULT NULL COMMENT '退款金额（元）', notify_request text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '微信退款回调参数', status varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款状态', create_time datetime DEFAULT NULL COMMENT '创建时间', update_time datetime DEFAULT NULL COMMENT '修改时间', del_flag int DEFAULT '0' COMMENT '逻辑删除标识', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款订单表';

CREATE TABLE nm_profit_sharing_info ( id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID', profit_sharing_records_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '分账记录ID', order_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单ID', org_code varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分账接收方所属组织', mch_id varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商户号', mch_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商户全称', type int DEFAULT NULL COMMENT '账户类型（1-出资方 2-接收方）', ratio decimal(10,2) DEFAULT '0.00' COMMENT '分账比例', pre_amount int NOT NULL COMMENT '分账金额（分）', amount int DEFAULT NULL COMMENT '实际分账金额（分）', status varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '分账状态', description varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述', create_time datetime DEFAULT NULL COMMENT '创建时间', update_time datetime DEFAULT NULL COMMENT '修改时间', del_flag int DEFAULT '0' COMMENT '逻辑删除标识(0-正常,1-已删除)', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='分账详情表';

CREATE TABLE nm_profit_sharing_records ( id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID', profit_sharing_no varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分账单号', order_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单ID', transaction_id varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信交易流水号', profit_sharing_order_id varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信分账单号', price int DEFAULT NULL COMMENT '订单支付金额', order_amount int NOT NULL COMMENT '订单预分账金额（分）', amount int DEFAULT NULL COMMENT '分账金额(分)', procedure_fee int DEFAULT NULL COMMENT '微信分账手续费（分）0.06%', status int DEFAULT NULL COMMENT '分账状态（0-处理中 1-分账完成 2-分账失败）', fail_reason varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '分账失败原因', profit_sharing_time datetime DEFAULT NULL COMMENT '分账时间', create_time datetime DEFAULT NULL COMMENT '创建时间', update_time datetime DEFAULT NULL COMMENT '修改时间', del_flag int DEFAULT '0' COMMENT '逻辑删除标识(0-正常,1-已删除)', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='分账记录表';