national-motion-backend/updateSql.md

-----------------------------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;