基于MySQL的零件编号（PN）生成方案技术咨询

核心思路 #

你的需求核心是持久化存储固定不变的两种PN，同时实现新增化合物时自动批量生成对应所有尺寸的PN条目。视图无法满足固定PN的存储需求（动态计算无持久化），触发器+物理表是最适合的方案，以下是具体实现步骤：

1. 表结构设计 #

先确认现有基础表结构（假设，可根据实际调整）

-- 化合物表
CREATE TABLE compounds (
    compound_id INT PRIMARY KEY AUTO_INCREMENT,
    compound_code VARCHAR(20) UNIQUE NOT NULL -- 示例：A22、B75
);

-- 尺寸表
CREATE TABLE sizes (
    size_id INT PRIMARY KEY AUTO_INCREMENT,
    size_code VARCHAR(20) UNIQUE NOT NULL -- 示例：-2X5、-2X10
);

零件编号表设计

CREATE TABLE part_numbers (
    part_id INT PRIMARY KEY AUTO_INCREMENT,
    computer_pn VARCHAR(20) UNIQUE NOT NULL, -- 内部增量PN：AS1000034
    actual_pn VARCHAR(50) UNIQUE NOT NULL, -- 客户PN：A22-2X5
    compound_id INT NOT NULL,
    size_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 关联基础表
    FOREIGN KEY (compound_id) REFERENCES compounds(compound_id),
    FOREIGN KEY (size_id) REFERENCES sizes(size_id),
    -- 确保同一化合物+尺寸只生成一条记录
    UNIQUE KEY (compound_id, size_id)
);

-- 设置内部PN起始编号（匹配示例格式AS1000034）
ALTER TABLE part_numbers AUTO_INCREMENT = 1000000;

2. 自动生成PN的触发器实现 #

新增化合物时，自动批量生成对应所有尺寸的PN记录：

DELIMITER //
CREATE TRIGGER generate_part_numbers_after_compound_insert
AFTER INSERT ON compounds
FOR EACH ROW
BEGIN
    -- 批量插入当前化合物对应所有尺寸的PN记录（先填充客户PN）
    INSERT INTO part_numbers (actual_pn, compound_id, size_id)
    SELECT 
        CONCAT(NEW.compound_code, s.size_code),
        NEW.compound_id,
        s.size_id
    FROM sizes s;
    
    -- 自动生成并填充内部增量PN
    UPDATE part_numbers
    SET computer_pn = CONCAT('AS', part_id)
    WHERE compound_id = NEW.compound_id;
END //
DELIMITER ;

3. 确保PN固定不变的约束 #

添加触发器阻止修改已生成的PN：

DELIMITER //
CREATE TRIGGER prevent_part_number_modification
BEFORE UPDATE ON part_numbers
FOR EACH ROW
BEGIN
    IF NEW.computer_pn != OLD.computer_pn OR NEW.actual_pn != OLD.actual_pn THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '内部PN和客户PN不允许修改';
    END IF;
END //
DELIMITER ;

4. 初始数据批量导入 #

针对现有130种化合物，批量生成所有对应尺寸的PN：

-- 批量插入所有化合物+尺寸组合的PN记录
INSERT INTO part_numbers (actual_pn, compound_id, size_id)
SELECT 
    CONCAT(c.compound_code, s.size_code),
    c.compound_id,
    s.size_id
FROM compounds c
CROSS JOIN sizes s;

-- 统一生成内部PN
UPDATE part_numbers
SET computer_pn = CONCAT('AS', part_id);

为什么不选视图？ #

视图是动态计算的虚拟表，存在两个核心问题：

1. 无法存储固定的内部增量PN：每次查询都会重新计算，无法保证PN的唯一性和持久性；
2. 性能问题：32.5万条记录的笛卡尔积查询，每次视图调用都会重复计算，远不如物理表的直接查询高效。

内容的提问来源于stack exchange，提问作者Ian Lance