注意，请把这几行注释掉执行sql，写这行的目的是进行必要的说明： 1. 本次更新会将库存从数据库中移除，并使用redis进行存储。 2. 这里的redis是持久化的，所以需要先部署好mall4j-aof-redis。这里的reids与mall4d-bbc中的redis是不同的两个，所以需要重新配置，并且是需要挂载持久化的。 3. 把库存放在redis进行持久化存储，是为了减少数据库压力，提高性能。所以这个redis是持久化的，不能随便删除挂载的文件，否则会导致数据丢失。 库存初始化步骤： 1.根据`mall4d-bbc`项目中提供的部署模板，部署持久化redis（mall4j-aof-redis） 2.执行以下sql, 重新配置各个服务的redis并重启服务 3.到xxl-job的控制台执行一次`初始化商品库存`的定时任务，执行后即可删除该定时任务 # 修改菜单 INSERT INTO `tz_shop_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`) VALUES (742, 221, '', '', 0, '', 0, 0), (743, 742, 'stock/stowage/warehouse-manage/index', '', 1, '', 0, 0), (744, 742, 'stock/stowage/transfer-order/index', '', 1, '', 2, 0), (745, 742, 'stock/stowage/transfer-order-create/index', '', 1, '', 3, 1); INSERT INTO `tz_shop_menu_lang`(`menu_id`, `lang`, `name`) VALUES (742, 0, '仓库门店'), (743, 0, '仓库管理'), (744, 0, '调拨订单'), (745, 0, '新建调拨订单'); UPDATE `tz_shop_menu` SET `order_num` = 1 WHERE `menu_id` = 222; UPDATE `tz_shop_menu` SET `order_num` = 2 WHERE `menu_id` = 237; UPDATE `tz_shop_menu` SET `order_num` = 3 WHERE `menu_id` = 225; UPDATE `tz_shop_menu` SET `order_num` = 4 WHERE `menu_id` = 229; UPDATE `tz_shop_menu` SET `order_num` = 5 WHERE `menu_id` = 259; UPDATE `tz_shop_menu` SET `parent_id` = 742, `url` = 'stock/stowage/admin-station/index', `order_num` = 1 WHERE `menu_id` = 38; # 添加库存管理表 CREATE TABLE `tz_stock_point_sku` ( `stock_point_sku_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '库存商品关联skuid', `stock_point_id` bigint NOT NULL COMMENT '库存点id', `stock_point_type` tinyint NOT NULL COMMENT '库存点类型 1仓库 2门店', `prod_id` bigint DEFAULT NULL COMMENT 'prod_id', `sku_id` bigint DEFAULT NULL COMMENT 'skuid', `status` tinyint DEFAULT '0' COMMENT '状态 1:enable, 0:disable, -1:deleted', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `type` tinyint DEFAULT '1' COMMENT '仓库类型（0默认仓库，1区域仓库）', `stock_mode` tinyint DEFAULT NULL COMMENT '库存模式 1共享总部库存 2独立销售库存', PRIMARY KEY (`stock_point_sku_id`), KEY `idx_stock_point_id` (`stock_point_id`) USING BTREE, KEY `idx_prod_id` (`prod_id`) USING BTREE, KEY `idx_sku_id` (`sku_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; # 新增仓库表 CREATE TABLE `tz_warehouse` ( `warehouse_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 DEFAULT NULL COMMENT '店铺id', `warehouse_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '仓库名称', `type` tinyint DEFAULT NULL COMMENT '仓库类型（0默认仓库，1区域仓库）', `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '仓库地址', `manage` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '管理人', `phone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '管理电话', `sys_type` tinyint 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 '区', PRIMARY KEY (`warehouse_id`), KEY `shop_id` (`shop_id`) USING BTREE, KEY `idx_province_id` (`province_id`) USING BTREE, KEY `idx_city_id` (`city_id`) USING BTREE, KEY `idx_area_id` (`area_id`) USING BTREE, KEY `idx_warehouse_id` (`warehouse_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO `tz_warehouse` (`warehouse_id`,`shop_id`, `warehouse_name`, `type`, `address`, `manage`, `phone`, `sys_type`, `province_id`, `province`, `city_id`, `city`, `area_id`, `area`) VALUES (300000000000000,0,'默认仓库',0,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,NULL,NULL), (300000000001000,1,'默认仓库',0,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL); # 为旧门店数据创建门店仓库 INSERT INTO `tz_warehouse` ( `warehouse_id`, `shop_id`, `sys_type`, `type`, `warehouse_name`, `phone`, `province_id`, `province`, `city_id`, `city`, `area_id`, `area`, `address` ) ( SELECT station_id warehouse_id, station_id shop_id, '3' sys_type, '1' type, station_name warehouse_name, phone, province_id, province, city_id, city, area_id, area, addr address FROM `tz_station`); CREATE TABLE `tz_warehouse_area` ( `warehouse_area_id` bigint NOT NULL AUTO_INCREMENT, `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `warehouse_id` bigint DEFAULT NULL COMMENT '仓库id', `area_id` bigint DEFAULT NULL COMMENT '城市id', PRIMARY KEY (`warehouse_area_id`) USING BTREE, KEY `idx_warehouse_id` (`warehouse_id`) USING BTREE, KEY `idx_city_id` (`area_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='仓库项和供货城市关联信息'; INSERT INTO `tz_leaf_alloc`(`biz_tag`, `max_id`, `step`, `update_time`, `description`) VALUES ('mall4j-stock-point', 1000, 1000, '2023-12-15 17:28:15', '库存点id'), ('mall4j-allot-order', 1000, 1000, '2023-12-15 17:28:15', '调拨订单id'); # 所有门店添加库存模式，并统一为共享总部库存 ALTER TABLE `tz_station` ADD COLUMN `stock_mode` tinyint DEFAULT NULL COMMENT '库存模式 1共享总部库存 2独立销售库存'; update tz_station set stock_mode = 1; ALTER TABLE `tz_seckill` ADD COLUMN `replenish` int DEFAULT NULL COMMENT '补充库存 0不补充 1补充'; CREATE TABLE `tz_seckill_stock_point_sku` ( `seckill_stock_point_sku_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '库存商品关联skuid', `stock_point_id` bigint NOT NULL COMMENT '库存点id', `stock_point_type` tinyint NOT NULL COMMENT '库存点类型 1仓库 2门店', `sku_id` bigint DEFAULT NULL COMMENT 'sku_id', `seckill_sku_id` bigint DEFAULT NULL COMMENT '秒杀活动skuid', `status` tinyint DEFAULT '0' COMMENT '状态 1:enable, 0:disable, -1:deleted', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `type` tinyint DEFAULT NULL COMMENT '仓库类型（0默认仓库，1区域仓库）', `seckill_origin_stocks` int DEFAULT NULL COMMENT '秒杀库存点原始库存', `stock_mode` tinyint DEFAULT NULL COMMENT '库存模式 1共享总部库存 2独立销售库存', PRIMARY KEY (`seckill_stock_point_sku_id`), KEY `idx_stock_point_id` (`stock_point_id`) USING BTREE, KEY `idx_sku_id` (`sku_id`) USING BTREE, KEY `idx_seckill_sku_id` (`seckill_sku_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; # 商品组合商品相关字段 ALTER TABLE `tz_prod` MODIFY `mold` tinyint NOT NULL COMMENT '商品类别 0.实物商品 1.虚拟商品 2.组合商品', ADD COLUMN `is_all_entity` INT DEFAULT NULL COMMENT '是否为全部实物商品的组合商品 1.是 0.否'; ALTER TABLE `tz_sku` ADD COLUMN `stock_warning_status` tinyint NOT NULL DEFAULT '1' COMMENT '库存预警状态 2:库存充足 1:库存预警'; CREATE TABLE `tz_sku_combo` ( `sku_id` bigint NOT NULL COMMENT '规格id', `combo_sku_id` bigint NOT NULL COMMENT '组合的skuid', `combo_prod_id` bigint NOT NULL COMMENT '组合的spuid', `combo_count` int 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 '修改时间', PRIMARY KEY (`sku_id`,`combo_sku_id`), KEY `idx_spu_id` (`combo_prod_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='规格组合商品关联表'; ALTER TABLE `tz_order` ADD COLUMN main_order_number BIGINT NOT NULL DEFAULT '0' COMMENT '主单号,在组合/套餐商品拆单时会存在' AFTER `is_refund`, ADD KEY `idx_main_order_number` (`main_order_number`); ALTER TABLE `tz_order_item` ADD COLUMN `activity_id` bigint DEFAULT NULL COMMENT '活动id', ADD COLUMN `mold` tinyint NOT NULL COMMENT '商品类别 0.实物商品 1.虚拟商品 2.组合商品', ADD COLUMN `stock_point_id` bigint DEFAULT NULL COMMENT '库存点id', ADD COLUMN `activity_type` tinyint DEFAULT '0' COMMENT '活动类型 具体类型参考枚举类:OrderActivityType', ADD KEY `idx_activity_id` (`activity_id`); # 新增调拨订单 CREATE TABLE `tz_allot_order` ( `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:快递, 3:无需快递)', `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, KEY `idx_out_warehouse_id` (`out_warehouse_id`), KEY `idx_in_warehouse_id` (`in_warehouse_id`), KEY `idx_dvy_company_id` (`dvy_company_id`), KEY `idx_shop_id` (`shop_id`) ); # 新增调拨订单项 CREATE TABLE `tz_allot_order_item` ( `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` ), KEY `idx_allot_order_id` (`allot_order_id`), KEY `idx_spu` (`spu_id`, `sku_id`) ); # 库存流水字段补充 ALTER TABLE `tz_stock_bill_log` ADD COLUMN `stock_point_id` bigint(0) NULL COMMENT '库存点Id' AFTER `maker_mobile`, ADD COLUMN `stock_point_type` tinyint(1) NULL COMMENT '库存点类型(1:仓库， 2:门店)' AFTER `stock_point_id`, ADD KEY `idx_stock_point_id` (`stock_point_id`); # 采购订单字段补充 ALTER TABLE `tz_purchase_order` ADD COLUMN `warehouse_id` bigint(0) NULL COMMENT '仓库id' AFTER `employee_id`, ADD COLUMN `stock_point_type` tinyint(1) NULL COMMENT '库存点类型(1:仓库, 2:门店)' AFTER `warehouse_id`, ADD KEY `idx_warehouse_id` (`warehouse_id`); # 实物盘点字段补充 ALTER TABLE `tz_take_stock` ADD COLUMN `warehouse_id` bigint(0) NULL COMMENT '仓库id' AFTER `maker_mobile`, ADD COLUMN `stock_point_type` tinyint(1) NULL COMMENT '库存点类型(1:仓库, 2:门店)' AFTER `warehouse_id`, ADD KEY `idx_warehouse_id` (`warehouse_id`); # 移除多余的菜单 1046和1047是无用的的路径分析子菜单 delete from tz_sys_menu_lang where menu_id in (1046,1047); delete from tz_sys_menu where menu_id in (1046,1047); INSERT INTO `tz_leaf_alloc` (`biz_tag`, `max_id`, `step`, `description`) VALUES ("mall4j-purchases-order", 1, 100, "采购订单编号"), ("mall4j-ent-pay-order-no", 1, 100, "分销提现商家转账订单号"), ("mall4j-distribution-card-no", 1000000000000, 100, "分销员卡号"), ("mall4j-distribution-ticket", 1000000000000, 100, "分销商品二维码"), ("mall4j-seckill-order-path", 1000000000000, 100, "秒杀订单路径"); # 补全旧的赠品订单项数据 UPDATE `tz_order_item` SET activity_type = 5, activity_id = giveaway_order_item_id WHERE giveaway_order_item_id IS NOT NULL AND giveaway_order_item_id > 0; # 添加定时任务后只需要执行一次，将旧的商品数据转换 insert into `xxl_job`.`xxl_job_info`(`id`, `job_group`, `job_desc`, `add_time`, `update_time`, `author`, `alarm_email`, `schedule_type`, `schedule_conf`, `misfire_strategy`, `executor_route_strategy`, `executor_handler`, `executor_param`, `executor_block_strategy`, `executor_timeout`, `executor_fail_retry_count`, `glue_type`, `glue_source`, `glue_remark`, `glue_updatetime`, `child_jobid`, `trigger_status`, `trigger_last_time`, `trigger_next_time`) values (48,2, '初始化商品库存', '2024-03-06 10:07:30', '2024-03-06 10:07:30', 'admin', '', 'CRON', '0 0/30 * * * ?', 'DO_NOTHING', 'FIRST', 'intiStock', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2023-12-25 10:07:30', '', 0, 0, 0), (49,2, '退款订单库存处理', '2024-03-06 10:07:30', '2024-03-06 10:07:30', 'admin', '', 'CRON', '0 0/5 * * * ?', 'DO_NOTHING', 'FIRST', 'refundOrderStock', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2023-12-25 10:07:30', '', 1, 0, 0), (50,2, '恢复商品sku库存', '2024-03-06 10:07:30', '2024-03-06 10:07:30', 'admin', '', 'CRON', '0 0/30 * * * ?', 'DO_NOTHING', 'FIRST', 'restoreStock', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2023-12-25 10:07:30', '', 0, 0, 0) ; delete from `xxl_job`.`xxl_job_info` where executor_handler in ('hotStockPlacement', 'coldStockPlacement')