-- 工艺知识库独立数据结构;不修改既有路线、参数和工序数据。 -- 使用业务 ID 关联现有主数据,不跨模块设置物理外键,便于项目间迁移。 CREATE TABLE IF NOT EXISTS mes_pd_knowledge ( id BIGINT NOT NULL AUTO_INCREMENT, code VARCHAR(64) NOT NULL COMMENT '知识编码', name VARCHAR(200) NOT NULL COMMENT '知识名称', version INT NOT NULL DEFAULT 1 COMMENT '知识版本', product_item_id BIGINT DEFAULT NULL COMMENT '适用成品物料', bom_id BIGINT DEFAULT NULL COMMENT '适用 BOM 子物料编号', voltage_min DECIMAL(16,4) DEFAULT NULL COMMENT '最低电压,单位 kV', voltage_max DECIMAL(16,4) DEFAULT NULL COMMENT '最高电压,单位 kV', market VARCHAR(16) NOT NULL DEFAULT 'ALL' COMMENT 'ALL、DOMESTIC 或 EXPORT', route_id BIGINT DEFAULT NULL COMMENT '参考工艺路线', template_id BIGINT DEFAULT NULL COMMENT '参考参数模板', description TEXT COMMENT '适用说明', status TINYINT NOT NULL DEFAULT 0 COMMENT '0 草稿、1 已发布、2 停用', creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (id), UNIQUE KEY uq_knowledge_code_version (code, version), KEY idx_knowledge_match (status, market, product_item_id, deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识版本'; -- 同步为先前已创建的表补建唯一索引;如已有重复数据则应先人工处理,不能静默跳过。 SET @knowledge_unique_sql := IF( (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'mes_pd_knowledge' AND index_name = 'uq_knowledge_code_version') = 0, 'ALTER TABLE mes_pd_knowledge ADD UNIQUE KEY uq_knowledge_code_version (code, version)', 'SELECT 1'); PREPARE knowledge_unique_stmt FROM @knowledge_unique_sql; EXECUTE knowledge_unique_stmt; DEALLOCATE PREPARE knowledge_unique_stmt; CREATE TABLE IF NOT EXISTS mes_pd_knowledge_standard ( id BIGINT NOT NULL AUTO_INCREMENT, knowledge_id BIGINT NOT NULL, standard_code VARCHAR(128) NOT NULL COMMENT '适用标准编码', standard_name VARCHAR(200) DEFAULT NULL COMMENT '适用标准名称', creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (id), KEY idx_knowledge_standard_parent (knowledge_id, deleted), KEY idx_knowledge_standard_code (standard_code, deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识适用标准'; CREATE TABLE IF NOT EXISTS mes_pd_knowledge_resource ( id BIGINT NOT NULL AUTO_INCREMENT, knowledge_id BIGINT NOT NULL, process_id BIGINT DEFAULT NULL COMMENT '现有工序', resource_type VARCHAR(32) NOT NULL COMMENT '资源类型', resource_id BIGINT DEFAULT NULL COMMENT '现有资源编号', capability_description VARCHAR(500) DEFAULT NULL COMMENT '能力及约束描述', creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (id), KEY idx_knowledge_resource_parent (knowledge_id, deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识资源能力'; CREATE TABLE IF NOT EXISTS mes_pd_knowledge_reference ( id BIGINT NOT NULL AUTO_INCREMENT, knowledge_id BIGINT NOT NULL, knowledge_version INT NOT NULL COMMENT '引用时版本', target_type VARCHAR(32) NOT NULL COMMENT '引用目标类型', target_id BIGINT NOT NULL COMMENT '引用目标编号', creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (id), UNIQUE KEY uq_knowledge_reference (knowledge_id, target_type, target_id), KEY idx_knowledge_reference_source (knowledge_id, knowledge_version, deleted), KEY idx_knowledge_reference_target (target_type, target_id, deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识引用记录'; SET @knowledge_reference_sql := IF( (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'mes_pd_knowledge_reference' AND index_name = 'uq_knowledge_reference') = 0, 'ALTER TABLE mes_pd_knowledge_reference ADD UNIQUE KEY uq_knowledge_reference (knowledge_id, target_type, target_id)', 'SELECT 1'); PREPARE knowledge_reference_stmt FROM @knowledge_reference_sql; EXECUTE knowledge_reference_stmt; DEALLOCATE PREPARE knowledge_reference_stmt;