2. 批次主数据模型与数据库表结构设计

批次主数据模型设计思路

食品批次追溯的核心是“一个批次号能串起产品、工单、原料、质检与流向”。在数据库层面，需要把批次主数据独立成表，而不是把批次号冗余散落在各业务表中。本例设计三张核心表：产品表 product（定义产品代码与规格）、生产工单表 work_order（记录一次生产任务的计划与实际产出）、批次主数据表 batch（承载批次号、生产日期、产线、班次等追溯关键属性）。批次表通过外键关联产品与工单，形成“产品—工单—批次”的层级关系。

主键选择上，批次表使用自增 batch_id 作为代理主键，同时为业务批次号 batch_no 建立唯一索引，兼顾关联效率与业务唯一性。外键 product_id、work_order_id 分别指向产品表与工单表，并建立普通索引以加速关联查询。批次号本身包含生产日期、产线、班次信息，但为了查询性能，仍将生产日期、产线、班次拆为独立列并建立联合索引，避免每次查询都解析字符串。

建表 SQL 与约束定义

以下 SQL 以 MySQL 8.0 语法编写，使用 InnoDB 引擎以支持外键约束。三张表按依赖顺序创建：先产品表，再工单表，最后批次表。批次表的 batch_no 唯一索引保证同一批次号不可重复录入；product_id 与 work_order_id 外键保证批次不会挂到不存在的产品或工单上。

-- 1. 产品表
CREATE TABLE product (
    product_id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_code    VARCHAR(32)  NOT NULL,
    product_name    VARCHAR(128) NOT NULL,
    spec            VARCHAR(64)  DEFAULT NULL,
    shelf_life_days INT UNSIGNED DEFAULT NULL,
    category        VARCHAR(32)  DEFAULT NULL,
    UNIQUE KEY uk_product_code (product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 生产工单表
CREATE TABLE work_order (
    work_order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no      VARCHAR(32)  NOT NULL,
    product_id    BIGINT UNSIGNED NOT NULL,
    plan_qty      DECIMAL(12,2) NOT NULL DEFAULT 0,
    actual_qty    DECIMAL(12,2) DEFAULT NULL,
    plan_date     DATE NOT NULL,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_wo_product (product_id),
    CONSTRAINT fk_wo_product FOREIGN KEY (product_id)
        REFERENCES product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 批次主数据表
CREATE TABLE batch (
    batch_id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    batch_no      VARCHAR(48)  NOT NULL,
    product_id    BIGINT UNSIGNED NOT NULL,
    work_order_id BIGINT UNSIGNED NOT NULL,
    produce_date  DATE NOT NULL,
    line_code     VARCHAR(16) NOT NULL,
    shift_code    VARCHAR(8)  NOT NULL,
    status        TINYINT NOT NULL DEFAULT 0 COMMENT '0=在制,1=完工,2=冻结',
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_batch_no (batch_no),
    KEY idx_batch_prod_date (product_id, produce_date),
    KEY idx_batch_wo (work_order_id),
    CONSTRAINT fk_batch_product FOREIGN KEY (product_id)
        REFERENCES product (product_id),
    CONSTRAINT fk_batch_wo FOREIGN KEY (work_order_id)
        REFERENCES work_order (work_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例数据与关联查询验证

插入一组教学示例数据：一个产品、一张工单、三个批次。随后执行两类典型追溯查询，并用 EXPLAIN 观察索引命中情况。以下数据与执行计划均为示例，实际结果取决于数据量与统计信息。

INSERT INTO product (product_code, product_name, spec, shelf_life_days, category)
VALUES ('YP-250ML', '巴氏鲜牛奶', '250ml/盒', 7, '乳制品');

INSERT INTO work_order (order_no, product_id, plan_qty, actual_qty, plan_date)
VALUES ('WO-20240601-01', 1, 5000, 4980, '2024-06-01');

INSERT INTO batch (batch_no, product_id, work_order_id, produce_date, line_code, shift_code, status)
VALUES
('YP-250ML-20240601-L1-A-001', 1, 1, '2024-06-01', 'L1', 'A', 1),
('YP-250ML-20240601-L1-B-002', 1, 1, '2024-06-01', 'L1', 'B', 1),
('YP-250ML-20240602-L2-A-003', 1, 1, '2024-06-02', 'L2', 'A', 0);

-- 查询1：按产品+日期范围查批次（应命中 idx_batch_prod_date）
EXPLAIN SELECT b.batch_no, b.line_code, b.shift_code, p.product_name
FROM batch b
JOIN product p ON b.product_id = p.product_id
WHERE b.product_id = 1
  AND b.produce_date BETWEEN '2024-06-01' AND '2024-06-02';

-- 查询2：按工单反查所有批次（应命中 idx_batch_wo）
EXPLAIN SELECT b.batch_no, b.produce_date, w.order_no
FROM batch b
JOIN work_order w ON b.work_order_id = w.work_order_id
WHERE b.work_order_id = 1;

设计要点与常见问题

• 代理主键 vs 业务主键：batch_id 用自增 BIGINT 作为代理主键，batch_no 单独建唯一索引。这样外键关联用整型更高效，业务批次号变更也不影响关联关系。
• 联合索引列顺序：idx_batch_prod_date (product_id, produce_date) 遵循“等值列在前、范围列在后”原则，WHERE 中 product_id 等值匹配、produce_date 范围匹配时可走 range 扫描。
• 外键与性能：外键保证数据一致性，但每次插入/更新会触发父表检查。高并发写入场景可评估是否改为应用层校验，本例为教学完整性保留外键。
• 状态字段：status 用 TINYINT 表示在制/完工/冻结，便于后续按状态过滤批次，避免用字符串比较。