【WMS学习笔记系列】04-数据库设计

04 — 数据库设计4.1 ER 图核心实体关系4.2 DDL 建表语句4.2.1 仓库相关-- 仓库表 CREATE TABLE wms_warehouse ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, warehouse_code VARCHAR(32) NOT NULL COMMENT 仓库编码, warehouse_name VARCHAR(64) NOT NULL COMMENT 仓库名称, address VARCHAR(256) DEFAULT NULL COMMENT 仓库地址, contact_person VARCHAR(32) DEFAULT NULL COMMENT 联系人, contact_phone VARCHAR(20) DEFAULT NULL COMMENT 联系电话, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1-启用 0-禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_code (warehouse_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT仓库表; -- 库区表 CREATE TABLE wms_zone ( id BIGINT NOT NULL AUTO_INCREMENT, warehouse_id BIGINT NOT NULL COMMENT 所属仓库ID, zone_code VARCHAR(16) NOT NULL COMMENT 库区编码, zone_name VARCHAR(64) NOT NULL COMMENT 库区名称, zone_type VARCHAR(16) NOT NULL COMMENT 库区类型STORAGE/PICKING/STAGING/RETURN/DAMAGED, abc_class CHAR(1) DEFAULT NULL COMMENT ABC分类A/B/C, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_warehouse_zone (warehouse_id, zone_code), KEY idx_warehouse_id (warehouse_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT库区表; -- 库位表 CREATE TABLE wms_location ( id BIGINT NOT NULL AUTO_INCREMENT, warehouse_id BIGINT NOT NULL, zone_id BIGINT NOT NULL, location_code VARCHAR(32) NOT NULL COMMENT 库位编码, location_type VARCHAR(16) NOT NULL COMMENT 类型STORAGE/PICKING/STAGING, max_volume DECIMAL(10,3) DEFAULT NULL COMMENT 最大体积(m³), max_weight DECIMAL(10,3) DEFAULT NULL COMMENT 最大承重(kg), max_quantity INT DEFAULT NULL COMMENT 最大存放数量, status VARCHAR(16) NOT NULL DEFAULT IDLE COMMENT 状态IDLE/OCCUPIED/FROZEN/DISABLED, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_code (location_code), KEY idx_zone_id (zone_id), KEY idx_warehouse_zone (warehouse_id, zone_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT库位表;4.2.2 商品与货主-- 货主表 CREATE TABLE wms_owner ( id BIGINT NOT NULL AUTO_INCREMENT, owner_code VARCHAR(32) NOT NULL COMMENT 货主编码, owner_name VARCHAR(64) NOT NULL COMMENT 货主名称, owner_type VARCHAR(16) NOT NULL COMMENT 类型SUPPLIER/CUSTOMER/INTERNAL, contact_person VARCHAR(32) DEFAULT NULL, contact_phone VARCHAR(20) DEFAULT NULL, status TINYINT NOT NULL DEFAULT 1, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_code (owner_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT货主表; -- 商品表SKU CREATE TABLE wms_sku ( id BIGINT NOT NULL AUTO_INCREMENT, owner_id BIGINT NOT NULL COMMENT 所属货主ID, sku_code VARCHAR(32) NOT NULL COMMENT SKU编码, sku_name VARCHAR(128) NOT NULL COMMENT SKU名称, barcode VARCHAR(32) DEFAULT NULL COMMENT 条码, category VARCHAR(64) DEFAULT NULL COMMENT 商品分类, unit VARCHAR(8) NOT NULL DEFAULT PCS COMMENT 单位, specification VARCHAR(256) DEFAULT NULL COMMENT 规格描述, weight DECIMAL(10,3) DEFAULT NULL COMMENT 单件重量(kg), volume DECIMAL(10,3) DEFAULT NULL COMMENT 单件体积(m³), safety_stock INT NOT NULL DEFAULT 0 COMMENT 安全库存, max_stock INT DEFAULT NULL COMMENT 最大库存, abc_class CHAR(1) NOT NULL DEFAULT B COMMENT ABC分类, shelf_life_days INT DEFAULT NULL COMMENT 保质期天数, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1-启用 0-禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_sku_code (sku_code), KEY idx_owner_id (owner_id), KEY idx_category (category) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表;4.2.3 库存-- 库存表 CREATE TABLE wms_inventory ( id BIGINT NOT NULL AUTO_INCREMENT, warehouse_id BIGINT NOT NULL, sku_id BIGINT NOT NULL, location_id BIGINT NOT NULL, batch_id BIGINT DEFAULT NULL COMMENT 批次ID, owner_id BIGINT NOT NULL, quantity INT NOT NULL DEFAULT 0 COMMENT 数量, locked_quantity INT NOT NULL DEFAULT 0 COMMENT 锁定数量已被订单预占, status VARCHAR(16) NOT NULL DEFAULT AVAILABLE COMMENT AVAILABLE/LOCKED/FROZEN/DAMAGED/EXPIRED, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_sku_warehouse (sku_id, warehouse_id), KEY idx_location_id (location_id), KEY idx_batch_id (batch_id), KEY idx_owner_id (owner_id), UNIQUE KEY uk_inv (warehouse_id, sku_id, location_id, batch_id, owner_id, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT库存表; -- 库存流水表审计用 CREATE TABLE wms_inventory_log ( id BIGINT NOT NULL AUTO_INCREMENT, warehouse_id BIGINT NOT NULL, sku_id BIGINT NOT NULL, location_id BIGINT NOT NULL, batch_id BIGINT DEFAULT NULL, change_type VARCHAR(32) NOT NULL COMMENT 变动类型INBOUND/OUTBOUND/MOVE/ADJUST/COUNT, change_quantity INT NOT NULL COMMENT 变动数量正增加负减少, before_quantity INT NOT NULL COMMENT 变动前数量, after_quantity INT NOT NULL COMMENT 变动后数量, reference_no VARCHAR(64) DEFAULT NULL COMMENT 关联单号, operator_id BIGINT DEFAULT NULL COMMENT 操作人ID, remark VARCHAR(256) DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_sku_warehouse_time (sku_id, warehouse_id, create_time), KEY idx_reference_no (reference_no), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT库存流水表; -- 批次表 CREATE TABLE wms_batch ( id BIGINT NOT NULL AUTO_INCREMENT, sku_id BIGINT NOT NULL, batch_no VARCHAR(32) NOT NULL COMMENT 批次号, production_date DATE DEFAULT NULL COMMENT 生产日期, expiry_date DATE DEFAULT NULL COMMENT 过期日期, status VARCHAR(16) NOT NULL DEFAULT ACTIVE COMMENT ACTIVE/EXPIRED, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_batch (sku_id, batch_no), KEY idx_expiry_date (expiry_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT批次表;4.2.4 入库相关-- 入库单表 CREATE TABLE wms_inbound_order ( id BIGINT NOT NULL AUTO_INCREMENT, asn_no VARCHAR(32) NOT NULL COMMENT ASN单号, warehouse_id BIGINT NOT NULL, owner_id BIGINT NOT NULL COMMENT 货主ID供应商, order_type VARCHAR(16) NOT NULL COMMENT 类型PURCHASE/RETURN/TRANSFER/OTHER, status VARCHAR(16) NOT NULL COMMENT NOTIFIED/RECEIVING/RECEIVED/PUTTING/COMPLETED/CANCELLED, expected_arrive_time DATETIME DEFAULT NULL COMMENT 预计到货时间, actual_arrive_time DATETIME DEFAULT NULL COMMENT 实际到货时间, operator_id BIGINT DEFAULT NULL, remark VARCHAR(256) DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_asn_no (asn_no), KEY idx_warehouse_status (warehouse_id, status), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT入库单表; -- 入库明细表 CREATE TABLE wms_inbound_detail ( id BIGINT NOT NULL AUTO_INCREMENT, inbound_order_id BIGINT NOT NULL, sku_id BIGINT NOT NULL, expected_quantity INT NOT NULL COMMENT 预计数量, actual_quantity INT NOT NULL DEFAULT 0 COMMENT 实收数量, batch_id BIGINT DEFAULT NULL, location_id BIGINT DEFAULT NULL COMMENT 上架库位, status VARCHAR(16) NOT NULL DEFAULT PENDING COMMENT PENDING/RECEIVED/PUTAWAY/COMPLETED, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_inbound_order_id (inbound_order_id), KEY idx_sku_id (sku_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT入库明细表;4.2.5 出库相关-- 出库单表 CREATE TABLE wms_outbound_order ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 出库单号, source_order_no VARCHAR(64) DEFAULT NULL COMMENT 来源订单号OMS, warehouse_id BIGINT NOT NULL, owner_id BIGINT NOT NULL COMMENT 货主ID客户, wave_id BIGINT DEFAULT NULL COMMENT 波次ID, status VARCHAR(16) NOT NULL COMMENT RECEIVED/WAVING/ALLOCATED/PICKING/PICKED/CHECKING/CHECKED/SHIPPED/CANCELLED, carrier VARCHAR(64) DEFAULT NULL COMMENT 承运商, tracking_no VARCHAR(64) DEFAULT NULL COMMENT 快递单号, expected_ship_time DATETIME DEFAULT NULL COMMENT 期望发货时间, actual_ship_time DATETIME DEFAULT NULL COMMENT 实际发货时间, operator_id BIGINT DEFAULT NULL, remark VARCHAR(256) DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_warehouse_status (warehouse_id, status), KEY idx_wave_id (wave_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT出库单表; -- 出库明细表 CREATE TABLE wms_outbound_detail ( id BIGINT NOT NULL AUTO_INCREMENT, outbound_order_id BIGINT NOT NULL, sku_id BIGINT NOT NULL, ordered_quantity INT NOT NULL COMMENT 订单数量, allocated_quantity INT NOT NULL DEFAULT 0 COMMENT 已分配数量, picked_quantity INT NOT NULL DEFAULT 0 COMMENT 已拣数量, checked_quantity INT NOT NULL DEFAULT 0 COMMENT 复核通过数量, shipped_quantity INT NOT NULL DEFAULT 0 COMMENT 发货数量, batch_id BIGINT DEFAULT NULL, location_id BIGINT DEFAULT NULL, status VARCHAR(16) NOT NULL DEFAULT PENDING, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_outbound_order_id (outbound_order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT出库明细表; -- 波次表 CREATE TABLE wms_wave ( id BIGINT NOT NULL AUTO_INCREMENT, wave_no VARCHAR(32) NOT NULL COMMENT 波次号, warehouse_id BIGINT NOT NULL, wave_type VARCHAR(16) NOT NULL COMMENT 策略类型ORDER_COUNT/SKU_COUNT/INTERVAL/ZONE/CARRIER, status VARCHAR(16) NOT NULL COMMENT CREATED/PICKING/PICKED/COMPLETED, order_count INT NOT NULL DEFAULT 0 COMMENT 包含订单数, total_sku_count INT NOT NULL DEFAULT 0 COMMENT SKU总数, total_quantity INT NOT NULL DEFAULT 0 COMMENT 商品总数量, operator_id BIGINT DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_wave_no (wave_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT波次表;4.2.6 盘点相关-- 盘点计划表 CREATE TABLE wms_count_plan ( id BIGINT NOT NULL AUTO_INCREMENT, plan_no VARCHAR(32) NOT NULL COMMENT 盘点计划号, warehouse_id BIGINT NOT NULL, count_type VARCHAR(16) NOT NULL COMMENT DYNAMIC/CYCLE/FULL/RANDOM, count_mode VARCHAR(16) NOT NULL COMMENT OPEN(明盘)/BLIND(盲盘), status VARCHAR(16) NOT NULL COMMENT CREATED/COUNTING/DIFF_CHECK/COMPLETED, planned_start_time DATETIME DEFAULT NULL, planned_end_time DATETIME DEFAULT NULL, actual_start_time DATETIME DEFAULT NULL, actual_end_time DATETIME DEFAULT NULL, creator_id BIGINT DEFAULT NULL, remark VARCHAR(256) DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_plan_no (plan_no), KEY idx_warehouse_status (warehouse_id, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT盘点计划表; -- 盘点任务表 CREATE TABLE wms_count_task ( id BIGINT NOT NULL AUTO_INCREMENT, plan_id BIGINT NOT NULL, location_id BIGINT NOT NULL, assignee_id BIGINT DEFAULT NULL COMMENT 分配人ID, status VARCHAR(16) NOT NULL DEFAULT PENDING COMMENT PENDING/COUNTING/FIRST_DONE/RECHECKING/DONE, first_count_id BIGINT DEFAULT NULL, recheck_count_id BIGINT DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_plan_id (plan_id), KEY idx_assignee_status (assignee_id, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT盘点任务表; -- 盘点明细表 CREATE TABLE wms_count_detail ( id BIGINT NOT NULL AUTO_INCREMENT, task_id BIGINT NOT NULL, sku_id BIGINT NOT NULL, batch_id BIGINT DEFAULT NULL, system_quantity INT NOT NULL COMMENT 系统数量, count_quantity INT NOT NULL COMMENT 盘点数量, difference INT NOT NULL COMMENT 差异盘点-系统, round TINYINT NOT NULL DEFAULT 1 COMMENT 轮次1-初盘 2-复盘, operator_id BIGINT DEFAULT NULL, remark VARCHAR(256) DEFAULT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_task_id (task_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT盘点明细表;4.3 索引策略4.3.1 高频查询索引查询场景SQL 示例建议索引按仓库SKU查库存WHERE warehouse_id? AND sku_id?(warehouse_id, sku_id)按库位查库存WHERE location_id?(location_id)按单号查入库单WHERE asn_no?唯一索引 (asn_no)按状态仓库查出库单WHERE warehouse_id? AND status?(warehouse_id, status)按时间范围查流水WHERE create_time BETWEEN ? AND ?(create_time)按SKU仓库时间查流水WHERE sku_id? AND warehouse_id? AND create_time?(sku_id, warehouse_id, create_time)4.3.2 索引设计原则选择性高的列优先建索引如单号、SKU编码联合索引遵循最左前缀原则避免在索引列上使用函数如 DATE(create_time) 会导致索引失效定期分析慢查询日志优化索引4.4 数据归档策略库存流水表wms_inventory_log数据增长最快 建议按月分区或定期归档 方案保留最近 6 个月数据在主表更早数据迁移到归档表 ALTER TABLE wms_inventory_log PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202601 VALUES LESS THAN (TO_DAYS(2026-02-01)), PARTITION p202602 VALUES LESS THAN (TO_DAYS(2026-03-01)), -- ... 按月分区 );