121 lines
9.5 KiB
SQL
121 lines
9.5 KiB
SQL
-- ================================================================
|
||
-- 产品中心三模块架构升级迁移脚本
|
||
-- 执行前请备份数据库！
|
||
-- ================================================================
|
||
|
||
-- 1. f_product_category 增加 module_type 字段
|
||
ALTER TABLE f_product_category ADD COLUMN module_type VARCHAR(20) NOT NULL DEFAULT 'product_line' COMMENT '模块类型 product_line/equipment/spare_part' AFTER name_en;
|
||
|
||
-- 2. 更新现有分类的 module_type
|
||
UPDATE f_product_category SET module_type = 'product_line' WHERE product_category_id IN (1, 11, 12, 13, 21, 22, 23, 31, 32, 41);
|
||
UPDATE f_product_category SET module_type = 'equipment' WHERE product_category_id = 5;
|
||
UPDATE f_product_category SET module_type = 'spare_part' WHERE product_category_id = 6;
|
||
|
||
-- 3. 清空旧分类数据，按PPT重新插入三模块分类
|
||
DELETE FROM f_product_category;
|
||
|
||
-- 整线机组分类（parent_id = 1）
|
||
INSERT INTO f_product_category (product_category_id, name_zh, name_en, module_type, parent_id, sort_order) VALUES
|
||
(1, '整线机组', 'Complete Production Line', 'product_line', NULL, 10),
|
||
(11, '热镀锌/镀铝锌机组', 'Hot-dip Galvanizing/Aluminum-zinc Coating Line', 'product_line', 1, 11),
|
||
(12, '锌铝镁机组', 'Zinc-aluminum-magnesium Coating Line', 'product_line', 1, 12),
|
||
(13, '普碳钢连续退火线', 'Carbon Steel Continuous Annealing Line', 'product_line', 1, 13),
|
||
(14, '冷轧板退火镀锌两用机组', 'Cold-rolled Sheet Annealing and Galvanizing Dual-purpose Line', 'product_line', 1, 14),
|
||
(15, '彩涂机组', 'Color Coating Line', 'product_line', 1, 15),
|
||
(16, '高效电工钢（硅钢）连续处理机组', 'High-efficiency Electrical Steel (Silicon Steel) Continuous Processing Line', 'product_line', 1, 16),
|
||
(17, '碳钢酸洗机组', 'Carbon Steel Pickling Line', 'product_line', 1, 17),
|
||
(18, '不锈钢连续退火酸洗机组', 'Stainless Steel Continuous Annealing and Pickling Line', 'product_line', 1, 18),
|
||
(19, '拉矫线、脱脂线、重卷线', 'Tension Leveling Line, Degreasing Line, Recoiling Line', 'product_line', 1, 19);
|
||
|
||
-- 核心单体设备分类（parent_id = 2）
|
||
INSERT INTO f_product_category (product_category_id, name_zh, name_en, module_type, parent_id, sort_order) VALUES
|
||
(2, '核心单体设备', 'Core Single Equipment', 'equipment', NULL, 20),
|
||
(51, '红外辐射炉', 'Infrared Radiation Furnace', 'equipment', 2, 51),
|
||
(52, '硅钢轧前感应加热', 'Induction Heating for Silicon Steel Before Rolling', 'equipment', 2, 52),
|
||
(53, '气雾冷却', 'Aerosol Cooling', 'equipment', 2, 53),
|
||
(54, '焊缝退火感应加热', 'Induction Heating for Weld Seam Annealing', 'equipment', 2, 54),
|
||
(55, '氢气回收装置', 'Hydrogen Recovery Device', 'equipment', 2, 55),
|
||
(56, '镀后冷却', 'Post-coating Cooling', 'equipment', 2, 56),
|
||
(57, '涂层干燥感应加热', 'Induction Heating for Coating Drying', 'equipment', 2, 57),
|
||
(58, '合金化炉', 'Alloying Furnace', 'equipment', 2, 58),
|
||
(59, '镀锡软熔感应加热', 'Induction Heating for Tin Plating Reflow', 'equipment', 2, 59),
|
||
(60, '电磁驱渣器', 'Electromagnetic Dross Remover', 'equipment', 2, 60),
|
||
(61, '直燃无焰加热', 'Direct-fired Flameless Heating', 'equipment', 2, 61),
|
||
(62, '圆盘剪、拉弯矫直、涂机、开收卷机', 'Circular Shear, Stretch-bend Straightener, Coater, Uncoiler/Coiler', 'equipment', 2, 62);
|
||
|
||
-- 备品备件分类（parent_id = 3）：五大分组，f_spare_part 多条单品共用 category_id
|
||
INSERT INTO f_product_category (product_category_id, name_zh, name_en, module_type, parent_id, sort_order) VALUES
|
||
(3, '备品备件', 'Spare Parts', 'spare_part', NULL, 30),
|
||
(71, 'W型/I型/U型辐射管', 'W-type/I-type/U-type Radiant Tubes', 'spare_part', 3, 71),
|
||
(72, '翅片管换热器', 'Finned Tube Heat Exchanger', 'spare_part', 3, 72),
|
||
(73, '炉辊、张力辊、镀铬辊、PU辊', 'Furnace Rolls, Tension Rolls, Chrome-plated Rolls, PU Rolls', 'spare_part', 3, 73),
|
||
(74, '水淬辊、汲料辊、托辊', 'Water Quenching Rolls, Dross Skimming Rolls, Support Rolls', 'spare_part', 3, 74),
|
||
(75, '轴套、衬套、滑块、支臂', 'Sleeves, Bushings, Sliders, Support Arms', 'spare_part', 3, 75);
|
||
|
||
ALTER TABLE f_product_category AUTO_INCREMENT = 100;
|
||
|
||
-- ================================================================
|
||
-- 4. f_product_line 增加整线机组专属字段
|
||
-- ================================================================
|
||
ALTER TABLE f_product_line ADD COLUMN cover_image VARCHAR(255) COMMENT '主封面图路径' AFTER achievements;
|
||
ALTER TABLE f_product_line ADD COLUMN applicable_materials_zh TEXT COMMENT '适用材料(中文)' AFTER cover_image;
|
||
ALTER TABLE f_product_line ADD COLUMN applicable_materials_en TEXT COMMENT '适用材料(英文)' AFTER applicable_materials_zh;
|
||
ALTER TABLE f_product_line ADD COLUMN thickness_range VARCHAR(200) COMMENT '厚度范围' AFTER applicable_materials_en;
|
||
ALTER TABLE f_product_line ADD COLUMN width_range VARCHAR(200) COMMENT '宽度范围' AFTER thickness_range;
|
||
ALTER TABLE f_product_line ADD COLUMN running_speed VARCHAR(200) COMMENT '运行速度' AFTER width_range;
|
||
ALTER TABLE f_product_line ADD COLUMN equipment_composition_zh TEXT COMMENT '设备组成(中文)' AFTER running_speed;
|
||
ALTER TABLE f_product_line ADD COLUMN equipment_composition_en TEXT COMMENT '设备组成(英文)' AFTER equipment_composition_zh;
|
||
ALTER TABLE f_product_line ADD COLUMN technical_highlights_zh TEXT COMMENT '技术亮点(中文)' AFTER equipment_composition_en;
|
||
ALTER TABLE f_product_line ADD COLUMN technical_highlights_en TEXT COMMENT '技术亮点(英文)' AFTER technical_highlights_zh;
|
||
ALTER TABLE f_product_line ADD COLUMN application_cases_zh TEXT COMMENT '应用案例(中文)' AFTER technical_highlights_en;
|
||
ALTER TABLE f_product_line ADD COLUMN application_cases_en TEXT COMMENT '应用案例(英文)' AFTER application_cases_zh;
|
||
|
||
-- ================================================================
|
||
-- 5. f_single_equipment 增加核心单体设备专属字段
|
||
-- ================================================================
|
||
ALTER TABLE f_single_equipment ADD COLUMN cover_image VARCHAR(255) COMMENT '设备实拍照片' AFTER technical_highlights_en;
|
||
ALTER TABLE f_single_equipment ADD COLUMN function_description_zh TEXT COMMENT '功能说明(中文)' AFTER cover_image;
|
||
ALTER TABLE f_single_equipment ADD COLUMN function_description_en TEXT COMMENT '功能说明(英文)' AFTER function_description_zh;
|
||
ALTER TABLE f_single_equipment ADD COLUMN product_advantages_zh TEXT COMMENT '产品优势(中文)' AFTER specifications_en;
|
||
ALTER TABLE f_single_equipment ADD COLUMN product_advantages_en TEXT COMMENT '产品优势(英文)' AFTER product_advantages_zh;
|
||
ALTER TABLE f_single_equipment ADD COLUMN applicable_lines_zh TEXT COMMENT '适配产线(中文)' AFTER product_advantages_en;
|
||
ALTER TABLE f_single_equipment ADD COLUMN applicable_lines_en TEXT COMMENT '适配产线(英文)' AFTER applicable_lines_zh;
|
||
ALTER TABLE f_single_equipment ADD COLUMN application_cases_zh TEXT COMMENT '应用案例(中文)' AFTER applicable_lines_en;
|
||
ALTER TABLE f_single_equipment ADD COLUMN application_cases_en TEXT COMMENT '应用案例(英文)' AFTER application_cases_zh;
|
||
|
||
-- ================================================================
|
||
-- 6. f_spare_part 增加备品备件专属字段
|
||
-- ================================================================
|
||
ALTER TABLE f_spare_part ADD COLUMN cover_image VARCHAR(255) COMMENT '产品实物图' AFTER applications;
|
||
ALTER TABLE f_spare_part ADD COLUMN model_spec VARCHAR(200) COMMENT '型号规格' AFTER cover_image;
|
||
ALTER TABLE f_spare_part ADD COLUMN applicable_line_model VARCHAR(200) COMMENT '适配机组型号' AFTER model_spec;
|
||
ALTER TABLE f_spare_part ADD COLUMN material_zh VARCHAR(200) COMMENT '材质(中文)' AFTER applicable_line_model;
|
||
ALTER TABLE f_spare_part ADD COLUMN material_en VARCHAR(200) COMMENT '材质(英文)' AFTER material_zh;
|
||
ALTER TABLE f_spare_part ADD COLUMN lifespan VARCHAR(200) COMMENT '使用寿命' AFTER material_en;
|
||
ALTER TABLE f_spare_part ADD COLUMN product_advantages_zh TEXT COMMENT '产品优势(中文)' AFTER lifespan;
|
||
ALTER TABLE f_spare_part ADD COLUMN product_advantages_en TEXT COMMENT '产品优势(英文)' AFTER product_advantages_zh;
|
||
|
||
-- ================================================================
|
||
-- 7. 新增整线机组与单体设备关联表
|
||
-- ================================================================
|
||
DROP TABLE IF EXISTS f_product_line_equipment;
|
||
CREATE TABLE f_product_line_equipment (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
product_line_id BIGINT NOT NULL COMMENT '整线机组ID',
|
||
equipment_id BIGINT NOT NULL COMMENT '单体设备ID',
|
||
sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_line_equipment (product_line_id, equipment_id),
|
||
KEY idx_ple_equipment (equipment_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='整线机组与单体设备关联';
|
||
|
||
-- ================================================================
|
||
-- 8. 扩展 f_product_media 增加 product_type 字段以支持三模块
|
||
-- ================================================================
|
||
ALTER TABLE f_product_media ADD COLUMN product_type VARCHAR(20) NOT NULL DEFAULT 'product_line' COMMENT '产品类型 product_line/equipment/spare_part' AFTER product_id;
|
||
|
||
-- 删除旧的唯一索引并重建
|
||
ALTER TABLE f_product_media DROP INDEX uk_f_product_media_pair;
|
||
CREATE UNIQUE INDEX uk_f_product_media_pair ON f_product_media (product_id, product_type, media_id);
|