forest-mall-cloud/sql/更新sql.sql

#删除产品数据
DELETE FROM pms_product WHERE create_time >='2020-10-9 00:00:00';

#供应商
DELETE FROM ums_company_info WHERE create_time >='2020-10-9 00:00:00';
#供应商文件
DELETE FROM ums_company_file WHERE create_time >='2020-10-9 00:00:00';
#供应商商店
DELETE FROM ums_company_shop WHERE create_time >='2020-10-9 00:00:00';

#生产地
DELETE FROM ums_member_origin WHERE create_time >='2020-10-9 00:00:00';
#生产地产出物
DELETE FROM ums_member_origin_product WHERE create_time >='2020-10-9 00:00:00';

# 更新商品大类
UPDATE pms_product a
LEFT JOIN pms_product_category b ON a.product_big_category_name = b.`name`
SET a.product_big_category_id = b.id
WHERE
	b.parent_id = 0
AND a.create_time >= '2020-10-9 00:00:00';

# 更新商品小类
UPDATE pms_product a
LEFT JOIN pms_product_category c ON a.product_category_name = c.`name`
SET a.product_category_id = c.id
WHERE
	c.parent_id != 0
AND a.create_time >= '2020-10-9 00:00:00';

# 更新商品大类小类
UPDATE pms_product a
LEFT JOIN pms_product_category b ON a.product_big_category_name = b.`name`
LEFT JOIN pms_product_category c ON a.product_category_name = c.`name`

SET a.product_big_category_id = b.id,
 a.product_category_id = c.id
WHERE
	b.parent_id = 0
AND c.parent_id != 0
AND a.create_time >= '2020-10-9 00:00:00';

# 品牌表新增字段

ALTER TABLE pms_brand ADD COLUMN `create_id` BIGINT (20) DEFAULT NULL COMMENT '创建人' AFTER `brand_story`;

ALTER TABLE pms_brand ADD COLUMN `create_time` datetime DEFAULT NULL COMMENT '创建时间' AFTER `create_id`;

ALTER TABLE pms_brand ADD COLUMN `update_id` BIGINT (20) DEFAULT NULL COMMENT '更新人' AFTER `create_time`;

ALTER TABLE pms_brand ADD COLUMN `update_time` datetime DEFAULT NULL COMMENT '更新时间' AFTER `update_id`;


# 品牌表新增唯一索引
ALTER TABLE `pms_brand` ADD UNIQUE (`name`)


# 产品索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_product_id ( `product_id` );

# 产品购买人id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_member_id ( `member_id` );

# 产品供应商id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_comp_id ( `comp_id` );

# 产品发布人id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_push_id ( `push_id` );

# 生产地id索引
ALTER TABLE `oms_pre_item` ADD INDEX idx_origin_id ( `origin_id` );