1876 lines
123 KiB
SQL
1876 lines
123 KiB
SQL
# 本sql文件无法直接执行，文件内容较多，建议在IntelliJ IDEA开发工具打开，右键选择 Folding-->Collapse All 方便查看并且复制
|
||
|
||
# 更换采购订单语言表，订单项语言表主键
|
||
# 为方便查看，这里只采用tmerclub_order_0库作为演示，实际需要更改tmerclub_order_0-7的8个库的分库分表!!!!!
|
||
USE tmerclub_order_0;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_purchase_order_lang_lang_table = CONCAT(
|
||
'delete from purchase_order_lang_', i, ' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_purchase_order_lang_lang_table FROM @delete_purchase_order_lang_lang_table;
|
||
EXECUTE delete_purchase_order_lang_lang_table;
|
||
|
||
SET @delete_purchase_order_item_lang_lang_table = CONCAT(
|
||
'delete from purchase_order_item_lang_', i, ' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_purchase_order_item_lang_lang_table FROM @delete_purchase_order_item_lang_lang_table;
|
||
EXECUTE delete_purchase_order_item_lang_lang_table;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
# 修改分库分表为单库50表
|
||
|
||
# 整体流程：将原本分库分表改为单库50表，需要将原数据都统计到总表中，再建新分表，再通过sql分配到各个分表中，最后删除总表数据
|
||
# 按顺序执行！，执行时只按分块执行，例如第一步执行 1.在总表新建表，就只执行到 DROP TABLE IF EXISTS `purchase_order_lang`;，也即 2.统计表数据到总表，清除分库 之前，
|
||
# 确保执行成功后再执行下一步2，以此类推下述每一步
|
||
# ============================== 1.在总表新建表 ==============================
|
||
# 这里的表结构最好将自己数据库中的0库0表中的数据结构复制过来，这里使用安装doc文档中的字段创建表结构
|
||
# 如果修改过表结构或者sql执行时字段出现位置不一致，会产生不可预计的sql相关bug，所以建议用自己的表结构
|
||
# 特别注意order_invoice和order_refund这两个表结构
|
||
USE tmerclub_order;
|
||
DROP TABLE IF EXISTS `allot_order`;
|
||
CREATE TABLE `allot_order` (
|
||
`allot_order_id` bigint NOT NULL COMMENT '调拨订单id',
|
||
`out_warehouse_id` bigint DEFAULT NULL COMMENT '调出点仓库id',
|
||
`out_stock_point_type` int DEFAULT NULL COMMENT '调出点库存点类型(1:仓库, 2:门店)',
|
||
`in_warehouse_id` bigint DEFAULT NULL COMMENT '调入点仓库id',
|
||
`in_stock_point_type` int DEFAULT NULL COMMENT '调入点库存点类型(1:仓库, 2:门店)',
|
||
`dvy_company_id` bigint DEFAULT NULL COMMENT '物流公司id',
|
||
`dvy_type` int DEFAULT NULL COMMENT '物流方式(1:快递, 2:无需快递)',
|
||
`dvy_order_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`status` int DEFAULT NULL COMMENT '状态(0:作废, 1:待入库, 2:部分入库, 3:已完成)',
|
||
`total_allot_count` int DEFAULT NULL COMMENT '总调拨数量',
|
||
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`sys_type` int DEFAULT NULL COMMENT '系统类型',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`allot_order_id`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||
|
||
DROP TABLE IF EXISTS `allot_order_item`;
|
||
CREATE TABLE `allot_order_item` (
|
||
`allot_order_item_id` bigint NOT NULL COMMENT '调拨订单商品id',
|
||
`allot_order_id` bigint DEFAULT NULL COMMENT '调拨订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品spuId',
|
||
`sku_id` bigint DEFAULT NULL COMMENT '商品skuId',
|
||
`allot_count` int DEFAULT NULL COMMENT '调拨数量',
|
||
`allot_in_count` int DEFAULT NULL COMMENT '调拨入库数量',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`allot_order_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||
|
||
DROP TABLE IF EXISTS `delivery_order`;
|
||
CREATE TABLE `delivery_order` (
|
||
`delivery_order_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单物流包裹id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '快递公司id',
|
||
`delivery_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '快递单号',
|
||
`consignee_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`status` tinyint DEFAULT NULL COMMENT '物流状态 1正常 -1删除',
|
||
`all_count` int DEFAULT NULL COMMENT '包裹商品总数',
|
||
`delete_time` datetime DEFAULT NULL COMMENT '删除时间',
|
||
`delivery_type` int DEFAULT NULL COMMENT '发货方式(1.快递 3.无需物流)',
|
||
PRIMARY KEY (`delivery_order_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单快递信息';
|
||
|
||
DROP TABLE IF EXISTS `delivery_order_item`;
|
||
CREATE TABLE `delivery_order_item` (
|
||
`delivery_order_item_id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`delivery_order_id` bigint DEFAULT NULL COMMENT '订单物流包裹id',
|
||
`img_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`count` int DEFAULT '0' COMMENT '商品数量',
|
||
`supplier_img_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '供应商商品图片',
|
||
`supplier_spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '供应商商品名称',
|
||
PRIMARY KEY (`delivery_order_item_id`),
|
||
KEY `idx_order_delivery_id` (`delivery_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='物流订单项信息';
|
||
|
||
DROP TABLE IF EXISTS `order`;
|
||
CREATE TABLE `order` (
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`order_addr_id` bigint DEFAULT NULL COMMENT '用户订单地址Id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`shop_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '店铺名称',
|
||
`total` bigint NOT NULL COMMENT '总值',
|
||
`actual_total` bigint DEFAULT NULL COMMENT '实际总值',
|
||
`freight_amount` bigint DEFAULT NULL COMMENT '订单运费',
|
||
`order_score` bigint DEFAULT NULL COMMENT '订单使用积分',
|
||
`status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态 1:待付款 2:待发货(待自提) 3:待收货(已发货) 5:成功 6:失败 7:待成团 9.待付尾款',
|
||
`pay_type` tinyint unsigned DEFAULT NULL COMMENT '支付方式 请参考枚举PayType',
|
||
`order_type` tinyint DEFAULT NULL COMMENT '订单类型 1团购订单 2秒杀订单 3积分订单',
|
||
`all_count` int DEFAULT NULL COMMENT '订单商品总数',
|
||
`reduce_amount` bigint NOT NULL DEFAULT '0' COMMENT '优惠总额',
|
||
`score_amount` bigint NOT NULL DEFAULT '0' COMMENT '积分抵扣金额',
|
||
`member_amount` bigint NOT NULL DEFAULT '0' COMMENT '会员折扣金额',
|
||
`platform_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠券优惠金额',
|
||
`shop_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家优惠券优惠金额',
|
||
`discount_amount` bigint NOT NULL DEFAULT '0' COMMENT '满减优惠金额',
|
||
`shop_combo_amount` bigint NOT NULL DEFAULT '0' COMMENT '套餐优惠金额',
|
||
`platform_free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台运费减免金额',
|
||
`free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家运费减免金额',
|
||
`shop_change_free_amount` bigint NOT NULL DEFAULT '0' COMMENT '店铺改价优惠金额',
|
||
`distribution_amount` bigint NOT NULL DEFAULT '0' COMMENT '分销佣金',
|
||
`platform_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠金额',
|
||
`platform_commission` bigint NOT NULL DEFAULT '0' COMMENT '平台佣金',
|
||
`pay_time` datetime DEFAULT NULL COMMENT '付款时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`finally_time` datetime DEFAULT NULL COMMENT '完成时间',
|
||
`settled_time` datetime DEFAULT NULL COMMENT '结算时间',
|
||
`cancel_time` datetime DEFAULT NULL COMMENT '取消时间',
|
||
`book_time` datetime DEFAULT NULL COMMENT '预售发货时间',
|
||
`is_payed` tinyint(1) DEFAULT NULL COMMENT '是否已支付，1.已支付0.未支付',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '订单关闭原因 1-超时未支付 2-退款关闭 4-买家取消 15-已通过货到付款交易',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '订单退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`shop_remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`delete_status` tinyint DEFAULT '0' COMMENT '用户订单删除状态，0：没有删除， 1：回收站， 2：永久删除',
|
||
`version` int DEFAULT NULL COMMENT '订单版本号，每处理一次订单，版本号+1',
|
||
`is_settled` tinyint DEFAULT NULL COMMENT '是否已经进行结算',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`wait_purchase` tinyint NOT NULL DEFAULT '0' COMMENT '是否待采购 1.是 0.否 2.已处理',
|
||
`supplier_delivery_type` tinyint NOT NULL DEFAULT '0' COMMENT '供应商商品发货方式 1.供应商发货 2.仓库发货',
|
||
`purchase_spread_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家采购价差（需要商家从结算金额补上采购价的金额）',
|
||
`pre_sale_type` tinyint DEFAULT '-1' COMMENT '预售类型 0.全款预售类型 1.定金预售',
|
||
`order_mold` tinyint DEFAULT '0' COMMENT '是否为虚拟商品订单 1.是 0.否',
|
||
`write_off_status` tinyint(1) DEFAULT NULL COMMENT '订单核销状态 0.待核销 1.核销完成',
|
||
`write_off_num` int DEFAULT NULL COMMENT '核销次数 -1.多次核销 0.无需核销 1.单次核销',
|
||
`write_off_multiple_count` int DEFAULT NULL COMMENT '核销次数 -1.无限次',
|
||
`write_off_start` datetime DEFAULT NULL COMMENT '核销开始时间',
|
||
`write_off_end` datetime DEFAULT NULL COMMENT '核销结束时间',
|
||
`write_off_count` int(0) NULL DEFAULT NULL COMMENT '订单被核销次数',
|
||
`main_order_id` bigint NOT NULL DEFAULT '0' COMMENT '主单号,在组合/套餐商品拆单时会存在',
|
||
PRIMARY KEY (`order_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_addr_order_id` (`order_addr_id`),
|
||
KEY `idx_finally_time` (`finally_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单信息';
|
||
|
||
DROP TABLE IF EXISTS `order_addr`;
|
||
CREATE TABLE `order_addr` (
|
||
`order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`post_code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮编',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
`lng` decimal(12,6) DEFAULT NULL COMMENT '经度',
|
||
`lat` decimal(12,6) DEFAULT NULL COMMENT '纬度',
|
||
PRIMARY KEY (`order_addr_id`),
|
||
KEY `idx_user_id` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户订单配送地址';
|
||
|
||
DROP TABLE IF EXISTS `order_invoice`;
|
||
CREATE TABLE `order_invoice` (
|
||
`order_invoice_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单发票ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单id',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺id',
|
||
`supplier_id` bigint DEFAULT NULL COMMENT '供应商id',
|
||
`invoice_type` tinyint NOT NULL DEFAULT '1' COMMENT '发票类型 1.电子普通发票',
|
||
`header_type` tinyint NOT NULL COMMENT '抬头类型 1.单位 2.个人',
|
||
`header_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '抬头名称',
|
||
`invoice_tax_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发票税号',
|
||
`invoice_context` tinyint NOT NULL DEFAULT '1' COMMENT '发票内容 1.商品明细',
|
||
`invoice_state` tinyint NOT NULL COMMENT '发票状态 1.申请中 2.已开票',
|
||
`file_id` bigint DEFAULT NULL COMMENT '文件id',
|
||
`application_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
||
`upload_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
|
||
PRIMARY KEY (`order_invoice_id`),
|
||
KEY `shop_idx` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||
|
||
DROP TABLE IF EXISTS `order_item`;
|
||
CREATE TABLE `order_item` (
|
||
`order_item_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单项ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺id',
|
||
`order_id` bigint NOT NULL COMMENT '订单id',
|
||
`category_id` bigint DEFAULT NULL COMMENT '分类id',
|
||
`spu_id` bigint unsigned NOT NULL COMMENT '产品ID',
|
||
`sku_id` bigint unsigned NOT NULL COMMENT '产品SkuID',
|
||
`user_id` bigint NOT NULL COMMENT '用户Id',
|
||
`final_refund_id` bigint DEFAULT NULL COMMENT '最终的退款id',
|
||
`distribution_user_id` bigint DEFAULT NULL COMMENT '推广员id',
|
||
`count` int DEFAULT '0' COMMENT '购物车产品个数',
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '产品名称',
|
||
`sku_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
`spu_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '产品主图片路径',
|
||
`is_comm` tinyint NOT NULL DEFAULT '0' COMMENT '是否以评价(0.未评价1.已评价)',
|
||
`comm_time` datetime DEFAULT NULL COMMENT '评论时间',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '单个orderItem的配送类型 1:快递 2:自提 3：无需快递 4:同城配送',
|
||
`shop_cart_time` datetime DEFAULT NULL COMMENT '加入购物车时间',
|
||
`price` bigint NOT NULL COMMENT '产品价格',
|
||
`spu_total_amount` bigint NOT NULL COMMENT '商品总金额',
|
||
`actual_total` bigint NOT NULL DEFAULT '0' COMMENT '商品实际金额 = 商品总金额 - 分摊的优惠金额',
|
||
`share_reduce` bigint NOT NULL DEFAULT '0' COMMENT '分摊的优惠金额(商家分摊 + 平台补贴)',
|
||
`platform_share_reduce` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠金额',
|
||
`distribution_amount` bigint NOT NULL DEFAULT '0' COMMENT '推广员佣金',
|
||
`distribution_parent_amount` bigint NOT NULL DEFAULT '0' COMMENT '上级推广员佣金',
|
||
`use_score` bigint NOT NULL DEFAULT '0' COMMENT '使用积分',
|
||
`gain_score` bigint NOT NULL DEFAULT '0' COMMENT '获得积分',
|
||
`rate` decimal(15,6) NOT NULL DEFAULT '0.000000' COMMENT '分账比例',
|
||
`platform_commission` bigint NOT NULL DEFAULT '0' COMMENT '平台佣金(商品实际金额 * 分账比例)',
|
||
`score_amount` bigint NOT NULL DEFAULT '0' COMMENT '积分抵扣金额',
|
||
`member_amount` bigint NOT NULL DEFAULT '0' COMMENT '会员折扣金额',
|
||
`platform_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠券优惠金额',
|
||
`shop_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家优惠券优惠金额',
|
||
`discount_amount` bigint NOT NULL DEFAULT '0' COMMENT '满减优惠金额',
|
||
`platform_free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台运费减免金额',
|
||
`free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家运费减免金额',
|
||
`shop_change_free_amount` bigint NOT NULL DEFAULT '0' COMMENT '店铺改价优惠金额',
|
||
`score_fee` bigint DEFAULT NULL COMMENT '积分价格（单价）',
|
||
`item_purchase_amount` bigint NOT NULL DEFAULT '0' COMMENT '采购价',
|
||
`purchase_platform_commission` bigint NOT NULL DEFAULT '0' COMMENT '供应商佣金',
|
||
`supplier_rate` decimal(15,6) NOT NULL DEFAULT '0.000000' COMMENT '供应商佣金比例',
|
||
`supplier_spu_id` bigint DEFAULT NULL COMMENT '供应商商品id',
|
||
`supplier_sku_id` bigint DEFAULT NULL COMMENT '供应商规格id',
|
||
`giveaway_amount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '赠品金额',
|
||
`main_giveaway` tinyint DEFAULT NULL COMMENT '主赠品',
|
||
`spu_mold` tinyint DEFAULT '0' COMMENT '商品类别 0.实物商品 1. 虚拟商品',
|
||
`is_refund` tinyint DEFAULT NULL COMMENT '是否可以退款 1.可以 0不可以',
|
||
`virtual_remark` json DEFAULT NULL COMMENT '虚拟商品的留言备注',
|
||
`combo_amount` bigint DEFAULT '0' COMMENT '套餐优惠金额',
|
||
`activity_id` bigint DEFAULT NULL COMMENT '活动id',
|
||
`activity_type` tinyint DEFAULT '0' COMMENT '活动类型 具体类型参考枚举类:OrderActivityType',
|
||
`supplier_pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '产品供应商主图片路径',
|
||
`write_off_num` int DEFAULT NULL COMMENT '核销次数 -1.多次核销 0.无需核销 1.单次核销',
|
||
`write_off_multiple_count` int DEFAULT NULL COMMENT '核销次数 -1.无限次',
|
||
`write_off_start` datetime DEFAULT NULL COMMENT '核销开始时间',
|
||
`write_off_end` datetime DEFAULT NULL COMMENT '核销结束时间',
|
||
`stock_point_id` bigint DEFAULT NULL COMMENT '库存点id',
|
||
PRIMARY KEY (`order_item_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_spu_id` (`spu_id`),
|
||
KEY `idx_sku_id` (`sku_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_final_refund_id` (`final_refund_id`),
|
||
KEY `idx_distribution_user_id` (`distribution_user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项';
|
||
|
||
DROP TABLE IF EXISTS `order_item_lang`;
|
||
CREATE TABLE `order_item_lang` (
|
||
`order_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
`supplier_spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '供应商商品名称',
|
||
`supplier_sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '供应商sku名称',
|
||
PRIMARY KEY (`order_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化';
|
||
|
||
DROP TABLE IF EXISTS `order_pre_sale_info`;
|
||
CREATE TABLE `order_pre_sale_info` (
|
||
`order_pre_sale_info_id` bigint NOT NULL AUTO_INCREMENT,
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`deposit_amount` bigint DEFAULT '0' COMMENT '定金金额',
|
||
`deposit_reduce_amount` bigint DEFAULT '0' COMMENT '定金优惠金额',
|
||
`deposit_expansion_amount` bigint DEFAULT '0' COMMENT '定金膨胀金额',
|
||
`balance_amount` bigint DEFAULT '0' COMMENT '尾款金额',
|
||
`pre_sale_type` tinyint DEFAULT '0' COMMENT '预售类型 0.全款预售类型 1.定金预售',
|
||
`pay_status` tinyint DEFAULT '0' COMMENT '定金预售支付状态 1.已支付定金 2.已支付尾款',
|
||
`balance_start_time` datetime DEFAULT NULL COMMENT '尾款支付开始时间',
|
||
`balance_end_time` datetime DEFAULT NULL COMMENT '尾款支付结束时间',
|
||
`balance_pay_time` datetime DEFAULT NULL COMMENT '尾款支付时间',
|
||
`pre_sale_delivery_type` tinyint DEFAULT NULL COMMENT '预售发货类型 -1.固定时间 x.（尾款）支付后x天发货',
|
||
`pre_sale_delivery_time` datetime DEFAULT NULL COMMENT '预售发货时间',
|
||
PRIMARY KEY (`order_pre_sale_info_id`),
|
||
KEY `idx_order_id` (`order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单预售信息';
|
||
|
||
DROP TABLE IF EXISTS `order_refund`;
|
||
CREATE TABLE `order_refund` (
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`shop_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家/供应商文件凭证(逗号隔开)',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息';
|
||
|
||
DROP TABLE IF EXISTS `order_refund_addr`;
|
||
CREATE TABLE `order_refund_addr` (
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址';
|
||
|
||
DROP TABLE IF EXISTS `order_refund_intervention`;
|
||
CREATE TABLE `order_refund_intervention` (
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录';
|
||
|
||
DROP TABLE IF EXISTS `order_refund_settlement`;
|
||
CREATE TABLE `order_refund_settlement` (
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据';
|
||
|
||
DROP TABLE IF EXISTS `order_self_station`;
|
||
CREATE TABLE `order_self_station` (
|
||
`order_self_station_id` bigint NOT NULL,
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`station_id` bigint DEFAULT NULL COMMENT '自提点id',
|
||
`station_user_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提人的手机',
|
||
`station_user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提人的名字',
|
||
`station_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提时间(用户下单时选择)',
|
||
`station_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提提货码',
|
||
`station_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '上门自提点的地址',
|
||
`station_phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '上门自提点的联系电话',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`order_self_station_id`),
|
||
KEY `STATION` (`station_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单自提信息';
|
||
|
||
DROP TABLE IF EXISTS `order_settlement`;
|
||
CREATE TABLE `order_settlement` (
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '支付结算单据ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单id',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '支付单号',
|
||
`pay_ids` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付单号集合，目前只有预售订单存在多条',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式',
|
||
`is_clearing` tinyint DEFAULT NULL COMMENT '是否清算 0:否 1:是',
|
||
`pay_score` bigint DEFAULT NULL COMMENT '支付积分',
|
||
`pay_amount` bigint DEFAULT NULL COMMENT '支付金额',
|
||
`clearing_time` datetime DEFAULT NULL COMMENT '清算时间',
|
||
`version` int DEFAULT NULL COMMENT '版本号',
|
||
`is_payed` tinyint DEFAULT NULL COMMENT '是否已支付，1.已支付0.未支付',
|
||
PRIMARY KEY (`settlement_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单结算表';
|
||
|
||
DROP TABLE IF EXISTS `order_virtual_info`;
|
||
CREATE TABLE `order_virtual_info` (
|
||
`order_virtual_info_id` bigint NOT NULL,
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`station_id` bigint DEFAULT NULL COMMENT '核销门店id',
|
||
`write_off_code` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '核销码',
|
||
`is_write_off` tinyint DEFAULT NULL COMMENT '是否核销 1.已核销 0.未核销',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`write_off_time` datetime DEFAULT NULL COMMENT '核销时间',
|
||
`write_off_multiple_count` int DEFAULT NULL COMMENT '剩余核销次数 -1.无限次',
|
||
PRIMARY KEY (`order_virtual_info_id`),
|
||
KEY `order_id` (`order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单虚拟商品信息';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order`;
|
||
CREATE TABLE `purchase_order` (
|
||
`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` varchar(1000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_addr`;
|
||
CREATE TABLE `purchase_order_addr` (
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_item`;
|
||
CREATE TABLE `purchase_order_item` (
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_item_lang`;
|
||
CREATE TABLE `purchase_order_item_lang` (
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_lang`;
|
||
CREATE TABLE `purchase_order_lang` (
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化';
|
||
|
||
USE tmerclub_payment;
|
||
DROP TABLE IF EXISTS `pay_info`;
|
||
CREATE TABLE `pay_info` (
|
||
`pay_id` bigint unsigned NOT NULL COMMENT '支付单号',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户id',
|
||
`order_ids` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '本次支付关联的多个订单号',
|
||
`biz_pay_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '外部订单流水号',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型 见SysTypeEnum',
|
||
`pay_entry` tinyint DEFAULT NULL COMMENT '支付入口 0下单 1余额充值',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式 1 微信支付 2 支付宝',
|
||
`pay_status` tinyint DEFAULT NULL COMMENT '支付状态',
|
||
`pay_score` bigint DEFAULT NULL COMMENT '支付积分',
|
||
`pay_amount` bigint DEFAULT NULL COMMENT '支付金额',
|
||
`version` int DEFAULT NULL COMMENT '版本号',
|
||
`callback_content` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回调内容',
|
||
`callback_time` datetime DEFAULT NULL COMMENT '回调时间',
|
||
`confirm_time` datetime DEFAULT NULL COMMENT '确认时间',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '本次支付关联的退款号',
|
||
`pay_sys_type` int DEFAULT NULL COMMENT '系统支付类型 0.默认支付系统 1.通联支付',
|
||
`to_user_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付给商城的用户id，通联支付独有',
|
||
`system_model` tinyint DEFAULT NULL COMMENT '支付的系统类型 1:pc 2:h5 3:小程序 4:安卓 5:ios',
|
||
`biz_user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '第三方系统userid',
|
||
PRIMARY KEY (`pay_id`),
|
||
KEY `idx_biz_pay_no` (`biz_pay_no`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_order_ids` (`order_ids`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单支付记录';
|
||
|
||
DROP TABLE IF EXISTS `refund_info`;
|
||
CREATE TABLE `refund_info` (
|
||
`refund_id` bigint unsigned NOT NULL COMMENT '退款单号',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_id` bigint DEFAULT NULL COMMENT '关联的支付订单id',
|
||
`pay_id` bigint NOT NULL DEFAULT '0' COMMENT '关联的支付单id',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户id',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`callback_content` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回调内容',
|
||
`callback_time` datetime DEFAULT NULL COMMENT '回调时间',
|
||
PRIMARY KEY (`refund_id`,`pay_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_userid` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款信息';
|
||
|
||
# ============================== 2.统计表数据到总表==============================
|
||
# 为方便查看，2中都只采用tmerclub_order_0库作为演示，实际需要更改tmerclub_order_0-7的8个库的分库分表!!!!!
|
||
# tmerclub_payment_0库作为演示，实际需要更改tmerclub_payment_0-7的8个库的分库分表!!!!!
|
||
USE tmerclub_order_0;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
# 汇总到总表
|
||
SET @create_allot_order_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`allot_order`
|
||
(SELECT * FROM allot_order_', i, ')'
|
||
);
|
||
PREPARE create_allot_order_table FROM @create_allot_order_table;
|
||
EXECUTE create_allot_order_table;
|
||
|
||
SET @create_allot_order_item_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`allot_order_item`
|
||
(SELECT * FROM allot_order_item_', i, ')'
|
||
);
|
||
PREPARE create_allot_order_item_table FROM @create_allot_order_item_table;
|
||
EXECUTE create_allot_order_item_table;
|
||
|
||
SET @create_delivery_order_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`delivery_order` (`create_time`,`update_time`,`order_id`,`user_id`,`delivery_company_id`,`delivery_no`,`consignee_name`,`consignee_mobile`,`status`,`all_count`,`delete_time`,`delivery_type`)
|
||
(SELECT `create_time`,`update_time`,`order_id`,`user_id`,`delivery_company_id`,`delivery_no`,`consignee_name`,`consignee_mobile`,`status`,`all_count`,`delete_time`,`delivery_type` FROM delivery_order_', i, ')'
|
||
);
|
||
PREPARE create_delivery_order_table FROM @create_delivery_order_table;
|
||
EXECUTE create_delivery_order_table;
|
||
|
||
SET @create_delivery_order_item_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`delivery_order_item` (`create_time`,`update_time`,`delivery_order_id`,`img_url`,`spu_name`,`count`,`supplier_img_url`,`supplier_spu_name`)
|
||
(SELECT `create_time`,`update_time`,`delivery_order_id`,`img_url`,`spu_name`,`count`,`supplier_img_url`,`supplier_spu_name` FROM delivery_order_item_', i, ')'
|
||
);
|
||
PREPARE create_delivery_order_item_table FROM @create_delivery_order_item_table;
|
||
EXECUTE create_delivery_order_item_table;
|
||
|
||
SET @create_order_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order`
|
||
(SELECT * FROM order_', i, ')'
|
||
);
|
||
PREPARE create_order_table FROM @create_order_table;
|
||
EXECUTE create_order_table;
|
||
|
||
SET @create_order_addr_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_addr`
|
||
(SELECT * FROM order_addr_', i, ')'
|
||
);
|
||
PREPARE create_order_addr_table FROM @create_order_addr_table;
|
||
EXECUTE create_order_addr_table;
|
||
|
||
SET @create_order_invoice_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_invoice`
|
||
(SELECT * FROM order_invoice_', i, ')'
|
||
);
|
||
PREPARE create_order_invoice_table FROM @create_order_invoice_table;
|
||
EXECUTE create_order_invoice_table;
|
||
|
||
SET @create_order_item_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_item`
|
||
(SELECT * FROM order_item_', i, ')'
|
||
);
|
||
PREPARE create_order_item_table FROM @create_order_item_table;
|
||
EXECUTE create_order_item_table;
|
||
|
||
SET @create_order_item_lang_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_item_lang`
|
||
(SELECT * FROM order_item_lang_', i, ')'
|
||
);
|
||
PREPARE create_order_item_lang_table FROM @create_order_item_lang_table;
|
||
EXECUTE create_order_item_lang_table;
|
||
|
||
SET @create_order_pre_sale_info_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_pre_sale_info`
|
||
(SELECT * FROM order_pre_sale_info_', i, ')'
|
||
);
|
||
PREPARE create_order_pre_sale_info_table FROM @create_order_pre_sale_info_table;
|
||
EXECUTE create_order_pre_sale_info_table;
|
||
|
||
SET @create_order_refund_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund`
|
||
(SELECT * FROM order_refund_', i, ')'
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund_addr` (`create_time`,`update_time`,`shop_id`,`refund_id`,`user_id`,`delivery_company_id`,`delivery_name`,`delivery_no`,`consignee_name`,`consignee_mobile`,`consignee_telephone`,`consignee_post_code`,`consignee_addr`,`sender_mobile`,`sender_remarks`,`imgs`)
|
||
(SELECT `create_time`,`update_time`,`shop_id`,`refund_id`,`user_id`,`delivery_company_id`,`delivery_name`,`delivery_no`,`consignee_name`,`consignee_mobile`,`consignee_telephone`,`consignee_post_code`,`consignee_addr`,`sender_mobile`,`sender_remarks`,`imgs` FROM order_refund_addr_', i, ')'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund_intervention` (`refund_id`,`create_time`,`update_time`,`biz_id`,`sys_type`,`voucher_desc`,`img_urls`)
|
||
(SELECT `refund_id`,`create_time`,`update_time`,`biz_id`,`sys_type`,`voucher_desc`,`img_urls` FROM order_refund_intervention_', i, ')'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund_settlement` (`create_time`,`update_time`,`pay_id`,`order_id`,`refund_id`,`user_id`,`biz_refund_no`,`pay_type`,`refund_status`,`refund_amount`,`order_total_amount`,`version`)
|
||
(SELECT `create_time`,`update_time`,`pay_id`,`order_id`,`refund_id`,`user_id`,`biz_refund_no`,`pay_type`,`refund_status`,`refund_amount`,`order_total_amount`,`version` FROM order_refund_settlement_', i, ')'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_order_self_station_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_self_station`
|
||
(SELECT * FROM order_self_station_', i, ')'
|
||
);
|
||
PREPARE create_order_self_station_table FROM @create_order_self_station_table;
|
||
EXECUTE create_order_self_station_table;
|
||
|
||
SET @create_order_settlement_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_settlement`
|
||
(SELECT * FROM order_settlement_', i, ')'
|
||
);
|
||
PREPARE create_order_settlement_table FROM @create_order_settlement_table;
|
||
EXECUTE create_order_settlement_table;
|
||
|
||
SET @create_order_virtual_info_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_virtual_info`
|
||
(SELECT * FROM order_virtual_info_', i, ')'
|
||
);
|
||
PREPARE create_order_virtual_info_table FROM @create_order_virtual_info_table;
|
||
EXECUTE create_order_virtual_info_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order`
|
||
(SELECT * FROM purchase_order_', i, ')'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_lang`
|
||
(SELECT * FROM purchase_order_lang_', i, ')'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_item`
|
||
(SELECT * FROM purchase_order_item_', i, ')'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_item_lang`
|
||
(SELECT * FROM purchase_order_item_lang_', i, ')'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_addr`
|
||
(SELECT * FROM purchase_order_addr_', i, ')'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_payment_0;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
# 汇总到总表
|
||
SET @create_pay_info_table = CONCAT(
|
||
'INSERT INTO tmerclub_payment.`pay_info`
|
||
(SELECT * FROM pay_info_', i, ')'
|
||
);
|
||
PREPARE create_pay_info_table FROM @create_pay_info_table;
|
||
EXECUTE create_pay_info_table;
|
||
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO tmerclub_payment.`refund_info`
|
||
(SELECT * FROM refund_info_', i, ')'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
# ============================== 3.删除多余分库分表 ==============================
|
||
# 请确保1-2成功执行，并且所有分表数据成功到总表中再执行
|
||
DROP DATABASE `tmerclub_order_0`;
|
||
DROP DATABASE `tmerclub_order_1`;
|
||
DROP DATABASE `tmerclub_order_2`;
|
||
DROP DATABASE `tmerclub_order_3`;
|
||
DROP DATABASE `tmerclub_order_4`;
|
||
DROP DATABASE `tmerclub_order_5`;
|
||
DROP DATABASE `tmerclub_order_6`;
|
||
DROP DATABASE `tmerclub_order_7`;
|
||
|
||
DROP DATABASE `tmerclub_payment_0`;
|
||
DROP DATABASE `tmerclub_payment_1`;
|
||
DROP DATABASE `tmerclub_payment_2`;
|
||
DROP DATABASE `tmerclub_payment_3`;
|
||
DROP DATABASE `tmerclub_payment_4`;
|
||
DROP DATABASE `tmerclub_payment_5`;
|
||
DROP DATABASE `tmerclub_payment_6`;
|
||
DROP DATABASE `tmerclub_payment_7`;
|
||
|
||
# ============================== 4.创建分表 ==============================
|
||
USE tmerclub_order;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
|
||
|
||
# ============================== 订单 ==============================
|
||
|
||
|
||
SET i = 0;
|
||
WHILE i<50 DO
|
||
|
||
SET @create_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_', i,
|
||
"
|
||
(
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`order_addr_id` bigint DEFAULT NULL COMMENT '用户订单地址Id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`shop_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '店铺名称',
|
||
`total` bigint NOT NULL COMMENT '总值',
|
||
`actual_total` bigint DEFAULT NULL COMMENT '实际总值',
|
||
`freight_amount` bigint DEFAULT NULL COMMENT '订单运费',
|
||
`order_score` bigint DEFAULT NULL COMMENT '订单使用积分',
|
||
`status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态 1:待付款 2:待发货(待自提) 3:待收货(已发货) 5:成功 6:失败 7:待成团 9.待付尾款',
|
||
`pay_type` tinyint unsigned DEFAULT NULL COMMENT '支付方式 请参考枚举PayType',
|
||
`order_type` tinyint DEFAULT NULL COMMENT '订单类型 1团购订单 2秒杀订单 3积分订单',
|
||
`all_count` int DEFAULT NULL COMMENT '订单商品总数',
|
||
`reduce_amount` bigint NOT NULL DEFAULT '0' COMMENT '优惠总额',
|
||
`score_amount` bigint NOT NULL DEFAULT '0' COMMENT '积分抵扣金额',
|
||
`member_amount` bigint NOT NULL DEFAULT '0' COMMENT '会员折扣金额',
|
||
`platform_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠券优惠金额',
|
||
`shop_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家优惠券优惠金额',
|
||
`discount_amount` bigint NOT NULL DEFAULT '0' COMMENT '满减优惠金额',
|
||
`shop_combo_amount` bigint NOT NULL DEFAULT '0' COMMENT '套餐优惠金额',
|
||
`platform_free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台运费减免金额',
|
||
`free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家运费减免金额',
|
||
`shop_change_free_amount` bigint NOT NULL DEFAULT '0' COMMENT '店铺改价优惠金额',
|
||
`distribution_amount` bigint NOT NULL DEFAULT '0' COMMENT '分销佣金',
|
||
`platform_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠金额',
|
||
`platform_commission` bigint NOT NULL DEFAULT '0' COMMENT '平台佣金',
|
||
`pay_time` datetime DEFAULT NULL COMMENT '付款时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`finally_time` datetime DEFAULT NULL COMMENT '完成时间',
|
||
`settled_time` datetime DEFAULT NULL COMMENT '结算时间',
|
||
`cancel_time` datetime DEFAULT NULL COMMENT '取消时间',
|
||
`book_time` datetime DEFAULT NULL COMMENT '预售发货时间',
|
||
`is_payed` tinyint(1) DEFAULT NULL COMMENT '是否已支付，1.已支付0.未支付',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '订单关闭原因 1-超时未支付 2-退款关闭 4-买家取消 15-已通过货到付款交易',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '订单退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`shop_remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`delete_status` tinyint DEFAULT '0' COMMENT '用户订单删除状态，0：没有删除， 1：回收站， 2：永久删除',
|
||
`version` int DEFAULT NULL COMMENT '订单版本号，每处理一次订单，版本号+1',
|
||
`is_settled` tinyint DEFAULT NULL COMMENT '是否已经进行结算',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`wait_purchase` tinyint NOT NULL DEFAULT '0' COMMENT '是否待采购 1.是 0.否 2.已处理',
|
||
`supplier_delivery_type` tinyint NOT NULL DEFAULT '0' COMMENT '供应商商品发货方式 1.供应商发货 2.仓库发货',
|
||
`purchase_spread_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家采购价差（需要商家从结算金额补上采购价的金额）',
|
||
`pre_sale_type` tinyint DEFAULT '-1' COMMENT '预售类型 0.全款预售类型 1.定金预售',
|
||
`order_mold` tinyint DEFAULT '0' COMMENT '是否为虚拟商品订单 1.是 0.否',
|
||
`write_off_status` tinyint(1) DEFAULT NULL COMMENT '订单核销状态 0.待核销 1.核销完成',
|
||
`write_off_num` int DEFAULT NULL COMMENT '核销次数 -1.多次核销 0.无需核销 1.单次核销',
|
||
`write_off_multiple_count` int DEFAULT NULL COMMENT '核销次数 -1.无限次',
|
||
`write_off_start` datetime DEFAULT NULL COMMENT '核销开始时间',
|
||
`write_off_end` datetime DEFAULT NULL COMMENT '核销结束时间',
|
||
`write_off_count` int(0) NULL DEFAULT NULL COMMENT '订单被核销次数',
|
||
main_order_id BIGINT NOT NULL DEFAULT '0' COMMENT '主单号,在组合/套餐商品拆单时会存在',
|
||
PRIMARY KEY (`order_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_addr_order_id` (`order_addr_id`),
|
||
KEY `idx_finally_time` (`finally_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单信息'
|
||
"
|
||
);
|
||
PREPARE sql_create_table FROM @create_order_table;
|
||
EXECUTE sql_create_table;
|
||
|
||
SET @create_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_addr_', i,
|
||
"
|
||
(
|
||
`order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`post_code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮编',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
`lng` decimal(12,6) DEFAULT NULL COMMENT '经度',
|
||
`lat` decimal(12,6) DEFAULT NULL COMMENT '纬度',
|
||
PRIMARY KEY (`order_addr_id`),
|
||
KEY `idx_user_id` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_order_addr_table FROM @create_order_addr_table;
|
||
EXECUTE create_order_addr_table;
|
||
|
||
SET @create_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_item_', i,
|
||
"
|
||
(
|
||
`order_item_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单项ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺id',
|
||
`order_id` bigint NOT NULL COMMENT '订单id',
|
||
`category_id` bigint DEFAULT NULL COMMENT '分类id',
|
||
`spu_id` bigint unsigned NOT NULL COMMENT '产品ID',
|
||
`sku_id` bigint unsigned NOT NULL COMMENT '产品SkuID',
|
||
`user_id` bigint NOT NULL COMMENT '用户Id',
|
||
`final_refund_id` bigint DEFAULT NULL COMMENT '最终的退款id',
|
||
`distribution_user_id` bigint DEFAULT NULL COMMENT '推广员id',
|
||
`count` int DEFAULT '0' COMMENT '购物车产品个数',
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '产品名称',
|
||
`sku_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
`spu_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '产品主图片路径',
|
||
`is_comm` tinyint NOT NULL DEFAULT '0' COMMENT '是否以评价(0.未评价1.已评价)',
|
||
`comm_time` datetime DEFAULT NULL COMMENT '评论时间',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '单个orderItem的配送类型 1:快递 2:自提 3：无需快递 4:同城配送',
|
||
`shop_cart_time` datetime DEFAULT NULL COMMENT '加入购物车时间',
|
||
`price` bigint NOT NULL COMMENT '产品价格',
|
||
`spu_total_amount` bigint NOT NULL COMMENT '商品总金额',
|
||
`actual_total` bigint NOT NULL DEFAULT '0' COMMENT '商品实际金额 = 商品总金额 - 分摊的优惠金额',
|
||
`share_reduce` bigint NOT NULL DEFAULT '0' COMMENT '分摊的优惠金额(商家分摊 + 平台补贴)',
|
||
`platform_share_reduce` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠金额',
|
||
`distribution_amount` bigint NOT NULL DEFAULT '0' COMMENT '推广员佣金',
|
||
`distribution_parent_amount` bigint NOT NULL DEFAULT '0' COMMENT '上级推广员佣金',
|
||
`use_score` bigint NOT NULL DEFAULT '0' COMMENT '使用积分',
|
||
`gain_score` bigint NOT NULL DEFAULT '0' COMMENT '获得积分',
|
||
`rate` decimal(15,6) NOT NULL DEFAULT '0.000000' COMMENT '分账比例',
|
||
`platform_commission` bigint NOT NULL DEFAULT '0' COMMENT '平台佣金(商品实际金额 * 分账比例)',
|
||
`score_amount` bigint NOT NULL DEFAULT '0' COMMENT '积分抵扣金额',
|
||
`member_amount` bigint NOT NULL DEFAULT '0' COMMENT '会员折扣金额',
|
||
`platform_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台优惠券优惠金额',
|
||
`shop_coupon_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家优惠券优惠金额',
|
||
`discount_amount` bigint NOT NULL DEFAULT '0' COMMENT '满减优惠金额',
|
||
`platform_free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '平台运费减免金额',
|
||
`free_freight_amount` bigint NOT NULL DEFAULT '0' COMMENT '商家运费减免金额',
|
||
`shop_change_free_amount` bigint NOT NULL DEFAULT '0' COMMENT '店铺改价优惠金额',
|
||
`score_fee` bigint DEFAULT NULL COMMENT '积分价格（单价）',
|
||
`item_purchase_amount` bigint NOT NULL DEFAULT '0' COMMENT '采购价',
|
||
`purchase_platform_commission` bigint NOT NULL DEFAULT '0' COMMENT '供应商佣金',
|
||
`supplier_rate` decimal(15,6) NOT NULL DEFAULT '0.000000' COMMENT '供应商佣金比例',
|
||
`supplier_spu_id` bigint DEFAULT NULL COMMENT '供应商商品id',
|
||
`supplier_sku_id` bigint DEFAULT NULL COMMENT '供应商规格id',
|
||
`giveaway_amount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '赠品金额',
|
||
`main_giveaway` tinyint DEFAULT NULL COMMENT '主赠品',
|
||
`spu_mold` tinyint DEFAULT '0' COMMENT '商品类别 0.实物商品 1. 虚拟商品',
|
||
`is_refund` tinyint DEFAULT NULL COMMENT '是否可以退款 1.可以 0不可以',
|
||
`virtual_remark` json DEFAULT NULL COMMENT '虚拟商品的留言备注',
|
||
`combo_amount` bigint DEFAULT '0' COMMENT '套餐优惠金额',
|
||
`activity_id` bigint DEFAULT NULL COMMENT '活动id',
|
||
`activity_type` tinyint DEFAULT '0' COMMENT '活动类型 具体类型参考枚举类:OrderActivityType',
|
||
`supplier_pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '产品供应商主图片路径',
|
||
`write_off_num` INT DEFAULT NULL COMMENT '核销次数 -1.多次核销 0.无需核销 1.单次核销',
|
||
`write_off_multiple_count` INT DEFAULT NULL COMMENT '核销次数 -1.无限次',
|
||
`write_off_start` DATETIME DEFAULT NULL COMMENT '核销开始时间',
|
||
`write_off_end` DATETIME DEFAULT NULL COMMENT '核销结束时间',
|
||
`stock_point_id` bigint DEFAULT NULL COMMENT '库存点id',
|
||
PRIMARY KEY (`order_item_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_spu_id` (`spu_id`),
|
||
KEY `idx_sku_id` (`sku_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_final_refund_id` (`final_refund_id`),
|
||
KEY `idx_distribution_user_id` (`distribution_user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项'
|
||
"
|
||
);
|
||
PREPARE create_order_item_table FROM @create_order_item_table;
|
||
EXECUTE create_order_item_table;
|
||
|
||
SET @create_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_item_lang_', i,
|
||
"
|
||
(
|
||
`order_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
`supplier_spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商商品名称',
|
||
`supplier_sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商sku名称',
|
||
PRIMARY KEY (`order_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_order_item_lang_table FROM @create_order_item_lang_table;
|
||
EXECUTE create_order_item_lang_table;
|
||
|
||
SET @create_order_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '支付结算单据ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单id',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '支付单号',
|
||
`pay_ids` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付单号集合，目前只有预售订单存在多条',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式',
|
||
`is_clearing` tinyint DEFAULT NULL COMMENT '是否清算 0:否 1:是',
|
||
`pay_score` bigint DEFAULT NULL COMMENT '支付积分',
|
||
`pay_amount` bigint DEFAULT NULL COMMENT '支付金额',
|
||
`clearing_time` datetime DEFAULT NULL COMMENT '清算时间',
|
||
`version` int DEFAULT NULL COMMENT '版本号',
|
||
`is_payed` tinyint DEFAULT NULL COMMENT '是否已支付，1.已支付0.未支付',
|
||
PRIMARY KEY (`settlement_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单结算表'
|
||
"
|
||
);
|
||
PREPARE create_order_settlement_table FROM @create_order_settlement_table;
|
||
EXECUTE create_order_settlement_table;
|
||
|
||
SET @create_order_pre_sale_info_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_pre_sale_info_', i,
|
||
"
|
||
(
|
||
`order_pre_sale_info_id` bigint NOT NULL AUTO_INCREMENT,
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`deposit_amount` bigint DEFAULT '0' COMMENT '定金金额',
|
||
`deposit_reduce_amount` bigint DEFAULT '0' COMMENT '定金优惠金额',
|
||
`deposit_expansion_amount` bigint DEFAULT '0' COMMENT '定金膨胀金额',
|
||
`balance_amount` bigint DEFAULT '0' COMMENT '尾款金额',
|
||
`pre_sale_type` tinyint DEFAULT '0' COMMENT '预售类型 0.全款预售类型 1.定金预售',
|
||
`pay_status` tinyint DEFAULT '0' COMMENT '定金预售支付状态 1.已支付定金 2.已支付尾款',
|
||
`balance_start_time` datetime DEFAULT NULL COMMENT '尾款支付开始时间',
|
||
`balance_end_time` datetime DEFAULT NULL COMMENT '尾款支付结束时间',
|
||
`balance_pay_time` datetime DEFAULT NULL COMMENT '尾款支付时间',
|
||
`pre_sale_delivery_type` tinyint DEFAULT NULL COMMENT '预售发货类型 -1.固定时间 x.（尾款）支付后x天发货',
|
||
`pre_sale_delivery_time` datetime DEFAULT NULL COMMENT '预售发货时间',
|
||
PRIMARY KEY (`order_pre_sale_info_id`),
|
||
KEY `idx_order_id` (`order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单预售信息'
|
||
"
|
||
);
|
||
PREPARE create_order_pre_sale_info_table FROM @create_order_pre_sale_info_table;
|
||
EXECUTE create_order_pre_sale_info_table;
|
||
|
||
SET @create_order_invoice_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_invoice_', i,
|
||
"
|
||
(
|
||
`order_invoice_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单发票ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单id',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺id',
|
||
`supplier_id` bigint NULL COMMENT '供应商id',
|
||
`invoice_type` tinyint NOT NULL DEFAULT '1' COMMENT '发票类型 1.电子普通发票',
|
||
`header_type` tinyint NOT NULL COMMENT '抬头类型 1.单位 2.个人',
|
||
`header_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '抬头名称',
|
||
`invoice_tax_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发票税号',
|
||
`invoice_context` tinyint NOT NULL DEFAULT '1' COMMENT '发票内容 1.商品明细',
|
||
`invoice_state` tinyint NOT NULL COMMENT '发票状态 1.申请中 2.已开票',
|
||
`file_id` bigint DEFAULT NULL COMMENT '文件id',
|
||
`application_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
||
`upload_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
|
||
PRIMARY KEY (`order_invoice_id`),
|
||
KEY `shop_idx`(`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
||
"
|
||
);
|
||
PREPARE create_order_invoice_table FROM @create_order_invoice_table;
|
||
EXECUTE create_order_invoice_table;
|
||
|
||
SET @create_order_self_station_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_self_station_', i,
|
||
"
|
||
(
|
||
`order_self_station_id` bigint NOT NULL,
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`station_id` bigint DEFAULT NULL COMMENT '自提点id',
|
||
`station_user_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提人的手机',
|
||
`station_user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提人的名字',
|
||
`station_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提时间(用户下单时选择)',
|
||
`station_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '自提提货码',
|
||
`station_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '上门自提点的地址',
|
||
`station_phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '上门自提点的联系电话',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`order_self_station_id`),
|
||
KEY `STATION` (`station_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单自提信息'
|
||
");
|
||
PREPARE create_order_self_station_table FROM @create_order_self_station_table;
|
||
EXECUTE create_order_self_station_table;
|
||
|
||
SET @create_order_virtual_info_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_virtual_info_', i,
|
||
"
|
||
(
|
||
`order_virtual_info_id` bigint NOT NULL ,
|
||
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`station_id` bigint DEFAULT NULL COMMENT '核销门店id',
|
||
`write_off_code` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '核销码',
|
||
`is_write_off` tinyint DEFAULT NULL COMMENT '是否核销 1.已核销 0.未核销',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`write_off_time` datetime DEFAULT NULL COMMENT '核销时间',
|
||
`write_off_multiple_count` int DEFAULT NULL COMMENT '剩余核销次数 -1.无限次',
|
||
PRIMARY KEY (`order_virtual_info_id`),
|
||
KEY `order_id` (`order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单虚拟商品信息'
|
||
");
|
||
PREPARE create_order_virtual_info_table FROM @create_order_virtual_info_table;
|
||
EXECUTE create_order_virtual_info_table;
|
||
|
||
SET @create_delivery_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS delivery_order_', i,
|
||
"
|
||
(
|
||
`delivery_order_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单物流包裹id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`user_id` bigint NOT NULL COMMENT '用户id',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '快递公司id',
|
||
`delivery_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '快递单号',
|
||
`consignee_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`status` tinyint DEFAULT NULL COMMENT '物流状态 1正常 -1删除',
|
||
`all_count` int DEFAULT NULL COMMENT '包裹商品总数',
|
||
`delete_time` datetime DEFAULT NULL COMMENT '删除时间',
|
||
`delivery_type` int DEFAULT NULL COMMENT '发货方式(1.快递 3.无需物流)',
|
||
PRIMARY KEY (`delivery_order_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=96 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单快递信息'
|
||
"
|
||
);
|
||
PREPARE create_delivery_order_table FROM @create_delivery_order_table;
|
||
EXECUTE create_delivery_order_table;
|
||
|
||
SET @create_delivery_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS delivery_order_item_', i,
|
||
"
|
||
(
|
||
`delivery_order_item_id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`delivery_order_id` bigint DEFAULT NULL COMMENT '订单物流包裹id',
|
||
`img_url` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`spu_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`count` int DEFAULT '0' COMMENT '商品数量',
|
||
`supplier_img_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商商品图片',
|
||
`supplier_spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商商品名称',
|
||
PRIMARY KEY (`delivery_order_item_id`),
|
||
KEY `idx_order_delivery_id` (`delivery_order_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='物流订单项信息'
|
||
"
|
||
);
|
||
PREPARE create_delivery_order_item_table FROM @create_delivery_order_item_table;
|
||
EXECUTE create_delivery_order_item_table;
|
||
|
||
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`shop_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家/供应商文件凭证(逗号隔开)',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint(0) NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint(0) NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int(1) NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET @add_allot_order = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS `allot_order_', i, "`
|
||
(
|
||
`allot_order_id` bigint(0) NOT NULL COMMENT '调拨订单id',
|
||
`out_warehouse_id` bigint(0) NULL DEFAULT NULL COMMENT '调出点仓库id',
|
||
`out_stock_point_type` int(0) NULL DEFAULT NULL COMMENT '调出点库存点类型(1:仓库, 2:门店)',
|
||
`in_warehouse_id` bigint(0) NULL DEFAULT NULL COMMENT '调入点仓库id',
|
||
`in_stock_point_type` int(0) NULL DEFAULT NULL COMMENT '调入点库存点类型(1:仓库, 2:门店)',
|
||
`dvy_company_id` bigint(0) NULL DEFAULT NULL COMMENT '物流公司id',
|
||
`dvy_type` int(0) NULL DEFAULT NULL COMMENT '物流方式(1:快递, 2:无需快递)',
|
||
`dvy_order_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '物流单号',
|
||
`status` int(0) NULL DEFAULT NULL COMMENT '状态(0:作废, 1:待入库, 2:部分入库, 3:已完成)',
|
||
`total_allot_count` int(0) NULL DEFAULT NULL COMMENT '总调拨数量',
|
||
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
||
`shop_id` bigint(0) NULL COMMENT '店铺id',
|
||
`sys_type` int(1) NULL COMMENT '系统类型',
|
||
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`allot_order_id`) USING BTREE
|
||
)
|
||
" );
|
||
|
||
PREPARE add_allot_order FROM @add_allot_order;
|
||
EXECUTE add_allot_order;
|
||
|
||
SET @add_allot_order_item = CONCAT(
|
||
'CREATE TABLE `allot_order_item_', i,
|
||
"` (
|
||
`allot_order_item_id` bigint NOT NULL COMMENT '调拨订单商品id',
|
||
`allot_order_id` bigint NULL COMMENT '调拨订单id',
|
||
`spu_id` bigint NULL COMMENT '商品spuId',
|
||
`sku_id` bigint NULL COMMENT '商品skuId',
|
||
`allot_count` int(255) NULL COMMENT '调拨数量',
|
||
`allot_in_count` int(255) NULL COMMENT '调拨入库数量',
|
||
`create_time` datetime NULL COMMENT '创建时间',
|
||
`update_time` datetime NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`allot_order_item_id`)
|
||
)
|
||
" );
|
||
PREPARE add_allot_order_item FROM @add_allot_order_item;
|
||
EXECUTE add_allot_order_item;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
|
||
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_payment;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<50 DO
|
||
SET @create_pay_info_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS pay_info_', i,
|
||
"
|
||
(
|
||
`pay_id` bigint unsigned NOT NULL COMMENT '支付单号',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户id',
|
||
`order_ids` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '本次支付关联的多个订单号',
|
||
`biz_pay_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '外部订单流水号',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型 见SysTypeEnum',
|
||
`pay_entry` tinyint DEFAULT NULL COMMENT '支付入口 0下单 1余额充值',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式 1 微信支付 2 支付宝',
|
||
`pay_status` tinyint DEFAULT NULL COMMENT '支付状态',
|
||
`pay_score` bigint DEFAULT NULL COMMENT '支付积分',
|
||
`pay_amount` bigint DEFAULT NULL COMMENT '支付金额',
|
||
`version` int DEFAULT NULL COMMENT '版本号',
|
||
`callback_content` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回调内容',
|
||
`callback_time` datetime DEFAULT NULL COMMENT '回调时间',
|
||
`confirm_time` datetime DEFAULT NULL COMMENT '确认时间',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '本次支付关联的退款号',
|
||
`pay_sys_type` int DEFAULT NULL COMMENT '系统支付类型 0.默认支付系统 1.通联支付',
|
||
`to_user_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付给商城的用户id，通联支付独有',
|
||
`system_model` tinyint DEFAULT NULL COMMENT '支付的系统类型 1:pc 2:h5 3:小程序 4:安卓 5:ios',
|
||
`biz_user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '第三方系统userid',
|
||
PRIMARY KEY (`pay_id`),
|
||
KEY `idx_biz_pay_no` (`biz_pay_no`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_order_ids` (`order_ids`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单支付记录'
|
||
"
|
||
);
|
||
PREPARE create_pay_info_table FROM @create_pay_info_table;
|
||
EXECUTE create_pay_info_table;
|
||
|
||
|
||
|
||
|
||
SET @create_refund_info_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS refund_info_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint unsigned NOT NULL COMMENT '退款单号',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`order_id` bigint DEFAULT NULL COMMENT '关联的支付订单id',
|
||
`pay_id` bigint NOT NULL DEFAULT '0' COMMENT '关联的支付单id',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户id',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`callback_content` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回调内容',
|
||
`callback_time` datetime DEFAULT NULL COMMENT '回调时间',
|
||
PRIMARY KEY (`refund_id`,`pay_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_userid` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款信息'
|
||
"
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
|
||
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
# ============================== 5.统计数据到各个分表 ==============================
|
||
USE tmerclub_order;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<50 DO
|
||
SET @create_allot_order_table = CONCAT(
|
||
'INSERT INTO allot_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`allot_order` WHERE (SUBSTRING(allot_order_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_allot_order_table FROM @create_allot_order_table;
|
||
EXECUTE create_allot_order_table;
|
||
|
||
SET @create_allot_order_item_table = CONCAT(
|
||
'INSERT INTO allot_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`allot_order_item` WHERE (SUBSTRING(allot_order_item_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_allot_order_item_table FROM @create_allot_order_item_table;
|
||
EXECUTE create_allot_order_item_table;
|
||
|
||
SET @create_delivery_order_table = CONCAT(
|
||
'INSERT INTO delivery_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`delivery_order` WHERE (SUBSTRING(delivery_order_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_delivery_order_table FROM @create_delivery_order_table;
|
||
EXECUTE create_delivery_order_table;
|
||
|
||
SET @create_delivery_order_item_table = CONCAT(
|
||
'INSERT INTO delivery_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`delivery_order_item` WHERE (SUBSTRING(delivery_order_item_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_delivery_order_item_table FROM @create_delivery_order_item_table;
|
||
EXECUTE create_delivery_order_item_table;
|
||
|
||
SET @create_order_table = CONCAT(
|
||
'INSERT INTO order_', i, '
|
||
(SELECT * FROM tmerclub_order.`order` WHERE (SUBSTRING(order_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_table FROM @create_order_table;
|
||
EXECUTE create_order_table;
|
||
|
||
SET @create_order_addr_table = CONCAT(
|
||
'INSERT INTO order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_addr` WHERE (SUBSTRING(order_addr_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_addr_table FROM @create_order_addr_table;
|
||
EXECUTE create_order_addr_table;
|
||
|
||
SET @create_order_invoice_table = CONCAT(
|
||
'INSERT INTO order_invoice_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_invoice` WHERE (SUBSTRING(order_invoice_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_invoice_table FROM @create_order_invoice_table;
|
||
EXECUTE create_order_invoice_table;
|
||
|
||
SET @create_order_item_table = CONCAT(
|
||
'INSERT INTO order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_item` WHERE (SUBSTRING(order_item_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_item_table FROM @create_order_item_table;
|
||
EXECUTE create_order_item_table;
|
||
|
||
SET @create_order_item_lang_table = CONCAT(
|
||
'INSERT INTO order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_item_lang` WHERE (SUBSTRING(order_item_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_item_lang_table FROM @create_order_item_lang_table;
|
||
EXECUTE create_order_item_lang_table;
|
||
|
||
SET @create_order_pre_sale_info_table = CONCAT(
|
||
'INSERT INTO order_pre_sale_info_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_pre_sale_info` WHERE (SUBSTRING(order_pre_sale_info_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_pre_sale_info_table FROM @create_order_pre_sale_info_table;
|
||
EXECUTE create_order_pre_sale_info_table;
|
||
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_order_self_station_table = CONCAT(
|
||
'INSERT INTO order_self_station_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_self_station` WHERE (SUBSTRING(order_self_station_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_self_station_table FROM @create_order_self_station_table;
|
||
EXECUTE create_order_self_station_table;
|
||
|
||
SET @create_order_settlement_table = CONCAT(
|
||
'INSERT INTO order_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_settlement` WHERE (SUBSTRING(settlement_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_settlement_table FROM @create_order_settlement_table;
|
||
EXECUTE create_order_settlement_table;
|
||
|
||
SET @create_order_virtual_info_table = CONCAT(
|
||
'INSERT INTO order_virtual_info_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_virtual_info` WHERE (SUBSTRING(order_virtual_info_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_virtual_info_table FROM @create_order_virtual_info_table;
|
||
EXECUTE create_order_virtual_info_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_payment;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<25 DO
|
||
SET @create_pay_info_table = CONCAT(
|
||
'INSERT INTO pay_info_', i, '
|
||
(SELECT * FROM tmerclub_payment.`pay_info` WHERE (SUBSTRING(pay_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_pay_info_table FROM @create_pay_info_table;
|
||
EXECUTE create_pay_info_table;
|
||
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO refund_info_', i, '
|
||
(SELECT * FROM tmerclub_payment.`refund_info` WHERE (SUBSTRING(refund_id, -3) + 0) % 50 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
# ============================== 6.修改shardingsphere、canal配置 ==============================
|
||
# 1.修改shardingsphere配置，具体参考tmerclub-doc文档/开发环境搭建/中间件docker-compse一键安装/shardingsphere/conf
|
||
|
||
# 2.修改canal配置
|
||
# 修改canal下/conf/example/instance.properties文件中的canal.instance.filter.regex，替换成下述内容
|
||
# canal.instance.filter.regex=tmerclub_product.spu:*,tmerclub_product.category:*,tmerclub_product.brand:*,tmerclub_product.spu_tag_reference:*,tmerclub_admin.shop_detail:*,tmerclub_product.spu_extension:*,tmerclub_product.sku:*,tmerclub_product.sku_stock:*,tmerclub_marketing.distribution_spu:*,tmerclub_order+.order_[0-9]+:*,tmerclub_order+.order_refund_[0-9]+:*,tmerclub_order+.purchase_order_[0-9]+:*,tmerclub_order+.order_invoice_[0-9]+:*,tmerclub_order+.allot_order_[0-9]+:*,\
|
||
# 修改后重启canal
|
||
|
||
# ============================== 7.删除总表中数据 ==============================
|
||
# 确保6.7.执行 成功 执行一次后，检查是否数据已经同步到各个分表中去，再执行下面的sql
|
||
USE tmerclub_order;
|
||
DROP TABLE IF EXISTS `allot_order`;
|
||
DROP TABLE IF EXISTS `allot_order_item`;
|
||
DROP TABLE IF EXISTS `delivery_order`;
|
||
DROP TABLE IF EXISTS `delivery_order_item`;
|
||
DROP TABLE IF EXISTS `order`;
|
||
DROP TABLE IF EXISTS `order_addr`;
|
||
DROP TABLE IF EXISTS `order_invoice`;
|
||
DROP TABLE IF EXISTS `order_item`;
|
||
DROP TABLE IF EXISTS `order_item_lang`;
|
||
DROP TABLE IF EXISTS `order_pre_sale_info`;
|
||
DROP TABLE IF EXISTS `order_refund`;
|
||
DROP TABLE IF EXISTS `order_refund_addr`;
|
||
DROP TABLE IF EXISTS `order_refund_intervention`;
|
||
DROP TABLE IF EXISTS `order_refund_settlement`;
|
||
DROP TABLE IF EXISTS `order_self_station`;
|
||
DROP TABLE IF EXISTS `order_settlement`;
|
||
DROP TABLE IF EXISTS `order_virtual_info`;
|
||
DROP TABLE IF EXISTS `purchase_order`;
|
||
DROP TABLE IF EXISTS `purchase_order_addr`;
|
||
DROP TABLE IF EXISTS `purchase_order_item`;
|
||
DROP TABLE IF EXISTS `purchase_order_item_lang`;
|
||
DROP TABLE IF EXISTS `purchase_order_lang`;
|
||
|
||
USE tmerclub_payment;
|
||
DROP TABLE IF EXISTS `pay_info`;
|
||
DROP TABLE IF EXISTS `refund_info`; |