82 lines
4.1 KiB
SQL
82 lines
4.1 KiB
SQL
ALTER TABLE `shop_goods`
|
||
ADD COLUMN `belong_type` TINYINT NOT NULL DEFAULT 0 COMMENT '归属类型（0-借还柜 1-固资通）'
|
||
AFTER `category_id`;
|
||
|
||
ALTER TABLE `shop_goods`
|
||
ADD COLUMN `external_goods_id` BIGINT NULL COMMENT '外部归属类型的商品ID'
|
||
AFTER `category_id`;
|
||
|
||
-- 添加缺失字段
|
||
ALTER TABLE `return_approval`
|
||
ADD COLUMN `external_goods_id` bigint DEFAULT NULL COMMENT '外部归属类型的商品ID' AFTER `order_goods_id`,
|
||
ADD COLUMN `apply_quantity` int DEFAULT NULL COMMENT '申请数量' AFTER `external_goods_id`,
|
||
ADD COLUMN `approval_type` TINYINT NOT NULL DEFAULT 0 COMMENT '审批类型（0为借还柜 1为固资通）' AFTER `order_goods_id`,
|
||
ADD COLUMN `apply_remark` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '申请说明' AFTER `return_remark`;
|
||
|
||
-- 添加索引
|
||
ALTER TABLE `return_approval`
|
||
ADD INDEX `idx_external_goods_id` (`external_goods_id`),
|
||
ADD INDEX `idx_approval_type` (`approval_type`);
|
||
|
||
ALTER TABLE `shop_goods`
|
||
ADD COLUMN `corpid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '企业微信id'
|
||
AFTER `external_goods_id`;
|
||
|
||
ALTER TABLE `return_approval`
|
||
ADD COLUMN `corpid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '企业微信id'
|
||
AFTER `external_goods_id`;
|
||
|
||
|
||
ALTER TABLE `return_approval`
|
||
ADD COLUMN `external_approval_id` bigint DEFAULT NULL COMMENT '外部归属类型的审批ID'
|
||
AFTER `external_goods_id`;
|
||
ALTER TABLE `return_approval`
|
||
ADD INDEX `idx_external_approval_id` (`external_approval_id`);
|
||
|
||
CREATE TABLE `approval_goods` (
|
||
`approval_goods_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`approval_id` bigint NOT NULL COMMENT '审批ID',
|
||
`goods_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商品名称',
|
||
`goods_id` bigint NOT NULL COMMENT '关联商品ID',
|
||
`external_goods_id` bigint DEFAULT NULL COMMENT '外部归属类型的商品ID',
|
||
`corpid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '企业微信id',
|
||
`belong_type` tinyint NOT NULL DEFAULT '0' COMMENT '归属类型（0-借还柜 1-固资通）',
|
||
`price` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '销售价格',
|
||
`apply_quantity` int DEFAULT NULL COMMENT '申请数量',
|
||
`cover_img` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '封面图URL',
|
||
`creator_id` bigint NULL DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint NULL DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`approval_goods_id`),
|
||
KEY `idx_approval_id` (`approval_id`),
|
||
KEY `idx_goods_id` (`goods_id`),
|
||
KEY `idx_external_goods_id` (`external_goods_id`),
|
||
KEY `idx_corpid` (`corpid`),
|
||
KEY `idx_update_time` (`update_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='申请领用商品信息表';
|
||
|
||
ALTER TABLE `shop`
|
||
ADD COLUMN `corpid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '企业微信id'
|
||
AFTER `shop_name`;
|
||
|
||
ALTER TABLE `shop_order`
|
||
ADD COLUMN `corpid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '企业微信id';
|
||
|
||
ALTER TABLE `shop_order_goods`
|
||
ADD COLUMN `corpid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '企业微信id';
|
||
|
||
ALTER TABLE `shop_goods`
|
||
ADD COLUMN `monthly_purchase_limit` INT DEFAULT NULL COMMENT '每人每月限购数量'
|
||
AFTER `corpid`;
|
||
|
||
ALTER TABLE `shop_order_goods`
|
||
ADD COLUMN `approval_id` bigint NULL COMMENT '审批ID'
|
||
AFTER `order_id`;
|
||
|
||
UPDATE shop_goods SET corpid = 'wpZ1ZrEgAA2QTxIRcB4cMtY7hQbTcPAw';
|
||
UPDATE return_approval SET corpid = 'wpZ1ZrEgAA2QTxIRcB4cMtY7hQbTcPAw';
|
||
UPDATE shop SET corpid = 'wpZ1ZrEgAA2QTxIRcB4cMtY7hQbTcPAw';
|
||
UPDATE shop_order SET corpid = 'wpZ1ZrEgAA2QTxIRcB4cMtY7hQbTcPAw';
|
||
UPDATE shop_order_goods SET corpid = 'wpZ1ZrEgAA2QTxIRcB4cMtY7hQbTcPAw'; |