SQL进销存设计方法详解，如何高效构建系统？

绕文岁

·

2026-05-17 16:35:06

阅读80分钟

已读16次

通过合理的 SQL 进销存设计，可以搭建出结构清晰、扩展性强、性能稳定的库存管理系统。核心思路是：基于“商品、仓库、订单、库存流水”四大对象进行规范化建模，用主外键与索引保证数据一致性与查询效率，并通过视图、存储过程或应用层服务实现业务逻辑计算（如实时库存、成本核算、销售分析）。在此基础上，再考虑多仓库、多单位、批次与序列号、预占库存等复杂场景，通过可扩展字段与关联表实现。合理的数据表设计 + 适配业务的 SQL 查询，是高效构建进销存系统的关键，后续可在此之上渐进式重构为更大规模的 ERP 或电商后台系统。

《SQL进销存设计方法详解，如何高效构建系统？》

---

SQL进销存设计方法详解，如何高效构建系统？

😀 一、进销存系统的核心业务与设计目标

在进行 SQL 进销存设计之前，需要先明确业务边界与系统目标。否则一开始就写表，很容易导致结构混乱，后期难以扩展。

1.1 进销存系统包含哪些核心业务？

典型进销存系统围绕“采购、销售、库存”三个主线展开，同时涉及基础资料与财务相关信息：

• 基础资料（Master Data）
• 商品（SKU）、商品分类、品牌
• 仓库、库位
• 客户、供应商
• 员工、部门、组织
• 采购业务（Purchase）
• 采购计划 / 采购申请（可选）
• 采购订单
• 采购入库（含赠品、退货）
• 销售业务（Sales）
• 销售订单
• 销售出库
• 销售退货
• 库存业务（Inventory）
• 期初库存
• 库存调整（盘点、报损、报溢）
• 调拨（仓库之间转移）
• 预占库存 / 可用库存
• 财务相关（不是本文重点，但需预留设计空间）
• 应付、应收
• 价格体系（采购价、销售价、促销价）
• 成本核算（移动加权、批次成本等）

这些业务最终都要落到数据库—特别是 SQL 关系型数据库—的建模上。

1.2 SQL 进销存设计的核心目标

以 SQL 为核心的进销存系统，一般追求以下几类目标：

1. 数据一致性

• 每一笔入库、出库、调拨都能准确影响库存量
• 避免数据冗余导致的库存不一致

2. 查询性能

• 常用报表（库存余额、销售明细、采购统计）查询要在可接受时间内完成
• 高并发下仍能快速写入单据

3. 扩展性与可维护性

• 能够从单仓扩展到多仓、多公司
• 能够在不大改结构的前提下支持批次、序列号、条码、属性扩展等

4. 审计与可追踪性

• 每笔库存变化都能追溯到来源单据
• 关键字段有创建人、修改人、时间等审计信息

将这些目标映射到 SQL 层面，核心就是：合理的数据表拆分、规范化设计 + 合理的索引与约束策略 + 适度利用视图与存储过程。

---

😎 二、总体架构：从 ER 模型到逻辑数据库设计

在具体建表前，先从整体架构划出一个 ER（实体关系）轮廓，有助于避免后期反复推翻重来。

2.1 典型 ER 模型框架

可以将进销存系统结构抽象为几大类实体：

1. 基础资料实体

• Product（商品）
• Warehouse（仓库）
• Customer（客户）
• Supplier（供应商）
• Unit（计量单位）
• Category（分类）等

2. 单据头实体（Header）

• PurchaseOrder（采购订单头）
• PurchaseReceipt / PurchaseIn（采购入库单头）
• SalesOrder（销售订单头）
• SalesDelivery / SalesOut（销售出库单头）
• InventoryAdjustment（库存调整单头）
• StockTransfer（调拨单头）

3. 单据明细实体（Line / Detail）

• PurchaseOrderLine
• PurchaseInLine
• SalesOrderLine
• SalesOutLine
• AdjustmentLine
• TransferLine

4. 库存变动实体

• StockTransaction / InventoryTransaction
• 用于记录商品在某仓、某批次、某单据下的增减记录

5. 库存余额实体

• StockBalance / InventoryBalance
• 存储某日/实时库存量，减少频繁汇总流水的压力

2.2 逻辑数据库层次图

从逻辑上可以分为几个层次：

• 基础数据层：商品、仓库、客户、供应商、计量单位等
• 业务单据层：采购、销售、调拨、盘点等单据头与明细
• 库存流水层：所有单据产生的库存增减记录
• 统计与报表层：库存余额、销售报表、采购报表等，可以是物化视图、聚合表或 BI 工具

这种分层，方便后续通过 SQL 视图或 ETL 把业务数据抽取到分析系统中。

---

📦 三、商品与基础资料表设计

商品（Product）相关表是整个 SQL 进销存设计的基础，设计合理与否直接影响后续的扩展性。

3.1 商品主表设计（Products）

核心原则：一个 SKU 对应一条记录。常见字段设计如下（示例为 MySQL 风格）：

CREATE TABLE products (
product_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
product_code      VARCHAR(64) NOT NULL UNIQUE, -- 商品编码
product_name      VARCHAR(255) NOT NULL,       -- 名称
sku               VARCHAR(128) NULL,           -- SKU 编码（如有）
bar_code          VARCHAR(64) NULL,            -- 条形码（可多个，另建表时再拆）
category_id       BIGINT NULL,                 -- 分类
brand_id          BIGINT NULL,                 -- 品牌
unit_id           BIGINT NOT NULL,             -- 主计量单位
is_active         TINYINT(1) NOT NULL DEFAULT 1,
spec              VARCHAR(255) NULL,           -- 规格型号
length_mm         DECIMAL(18,3) NULL,
width_mm          DECIMAL(18,3) NULL,
height_mm         DECIMAL(18,3) NULL,
weight_kg         DECIMAL(18,3) NULL,
purchase_price    DECIMAL(18,6) NULL,          -- 参考采购价
sale_price        DECIMAL(18,6) NULL,          -- 参考销售价
created_at        DATETIME NOT NULL,
created_by        BIGINT NOT NULL,
updated_at        DATETIME NULL,
updated_by        BIGINT NULL,
CONSTRAINT fk_products_category
FOREIGN KEY (category_id) REFERENCES product_categories(category_id),
CONSTRAINT fk_products_brand
FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
CONSTRAINT fk_products_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

设计要点：

• 尽量使用 product_code 作为对外展示编码，主键使用自增 ID 或 UUID
• 价格字段多用作 “默认价格” 或 “参考价”
• 含多单位、多条码场景时，使用关联表，不要直接塞多个字段

3.2 商品分类与品牌表

CREATE TABLE product_categories (
category_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
parent_id     BIGINT NULL,
category_name VARCHAR(255) NOT NULL,
category_code VARCHAR(64) NOT NULL,
level         INT NOT NULL DEFAULT 1,
is_active     TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE brands (
brand_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
brand_name   VARCHAR(255) NOT NULL,
brand_code   VARCHAR(64) NOT NULL,
is_active    TINYINT(1) NOT NULL DEFAULT 1
);

支持多级分类时：

• parent_id 自关联
• level 表示层级，有利于统计与查询

3.3 计量单位与多单位换算设计

多单位管理是进销存的常见需求，例如“箱/瓶”、“包/片”等。

单位表：

CREATE TABLE units (
unit_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
unit_name   VARCHAR(64) NOT NULL,
unit_code   VARCHAR(32) NOT NULL,
is_active   TINYINT(1) NOT NULL DEFAULT 1
);

商品多单位换算表（product_units）：

CREATE TABLE product_units (
id             BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id     BIGINT NOT NULL,
unit_id        BIGINT NOT NULL,
rate_to_main   DECIMAL(18,6) NOT NULL, -- 与主单位换算比例
is_main_unit   TINYINT(1) NOT NULL DEFAULT 0,
bar_code       VARCHAR(64) NULL,       -- 对应包装条码
CONSTRAINT fk_product_units_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_product_units_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

关键点：

• 每个商品至少有一个 is_main_unit = 1 的记录，作为库存计量的 “基准单位”
• 所有库存量、成本计算统一基于主单位存储
• 录单时可以选择任意单位，通过 rate_to_main 转换

3.4 仓库表设计（Warehouses）

CREATE TABLE warehouses (
warehouse_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
warehouse_code VARCHAR(64) NOT NULL UNIQUE,
warehouse_name VARCHAR(255) NOT NULL,
address        VARCHAR(255) NULL,
city           VARCHAR(64) NULL,
country        VARCHAR(64) NULL,
is_active      TINYINT(1) NOT NULL DEFAULT 1,
created_at     DATETIME NOT NULL,
created_by     BIGINT NOT NULL
);

可扩展支持：

• 多公司（增加 company_id）
• 多库位（另建 locations / bin 表，与 warehouse 关联）

3.5 客户与供应商表

可以分表，也可采用统一“业务伙伴”表再用类型区分。这里以分表为例：

CREATE TABLE suppliers (
supplier_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
supplier_code  VARCHAR(64) NOT NULL UNIQUE,
supplier_name  VARCHAR(255) NOT NULL,
contact_name   VARCHAR(128) NULL,
phone          VARCHAR(64) NULL,
email          VARCHAR(128) NULL,
address        VARCHAR(255) NULL,
tax_number     VARCHAR(64) NULL,
is_active      TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE customers (
customer_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
customer_code  VARCHAR(64) NOT NULL UNIQUE,
customer_name  VARCHAR(255) NOT NULL,
contact_name   VARCHAR(128) NULL,
phone          VARCHAR(64) NULL,
email          VARCHAR(128) NULL,
address        VARCHAR(255) NULL,
tax_number     VARCHAR(64) NULL,
is_active      TINYINT(1) NOT NULL DEFAULT 1
);

---

🧾 四、采购模块 SQL 设计（采购订单与入库）

采购模块关乎库存增加与应付账款，一般分为：采购订单 → 采购入库 → 采购退货 / 红冲。

4.1 采购订单（Purchase Orders）

采购订单头：

CREATE TABLE purchase_orders (
po_id           BIGINT PRIMARY KEY AUTO_INCREMENT,
po_number       VARCHAR(64) NOT NULL UNIQUE,      -- PO编号
supplier_id     BIGINT NOT NULL,
order_date      DATE NOT NULL,
expected_date   DATE NULL,
status          VARCHAR(32) NOT NULL,             -- DRAFT, APPROVED, CLOSED, CANCELED
currency        VARCHAR(16) NOT NULL DEFAULT 'USD',
total_amount    DECIMAL(18,6) NULL,
remark          VARCHAR(512) NULL,
created_at      DATETIME NOT NULL,
created_by      BIGINT NOT NULL,
approved_at     DATETIME NULL,
approved_by     BIGINT NULL,
CONSTRAINT fk_po_supplier
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

采购订单明细：

CREATE TABLE purchase_order_lines (
line_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
po_id          BIGINT NOT NULL,
line_no        INT NOT NULL,           -- 行号
product_id     BIGINT NOT NULL,
warehouse_id   BIGINT NOT NULL,        -- 预期入哪个仓
unit_id        BIGINT NOT NULL,        -- 下单单位
quantity       DECIMAL(18,6) NOT NULL,
price          DECIMAL(18,6) NOT NULL,
tax_rate       DECIMAL(5,2) NULL,     -- 税率
discount       DECIMAL(18,6) NULL,    -- 折扣金额或折扣率（视规则而定）
remark         VARCHAR(512) NULL,
CONSTRAINT fk_pol_po
FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id),
CONSTRAINT fk_pol_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_pol_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_pol_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

设计说明：

• 采购订单本身不直接影响库存，仅产生“预采购量”
• 可以通过状态字段控制是否允许生成入库单

4.2 采购入库单（Purchase Receipts / Purchase In）

采购入库头：

CREATE TABLE purchase_receipts (
receipt_id       BIGINT PRIMARY KEY AUTO_INCREMENT,
receipt_number   VARCHAR(64) NOT NULL UNIQUE,
po_id            BIGINT NULL,          -- 可关联采购订单
supplier_id      BIGINT NOT NULL,
receipt_date     DATE NOT NULL,
status           VARCHAR(32) NOT NULL, -- DRAFT, POSTED, CANCELED
warehouse_id     BIGINT NOT NULL,      -- 收货仓库（如按行分仓可挪到明细）
total_amount     DECIMAL(18,6) NULL,
remark           VARCHAR(512) NULL,
created_at       DATETIME NOT NULL,
created_by       BIGINT NOT NULL,
posted_at        DATETIME NULL,
posted_by        BIGINT NULL,
CONSTRAINT fk_pr_supplier
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
CONSTRAINT fk_pr_po
FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id),
CONSTRAINT fk_pr_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);

采购入库明细：

CREATE TABLE purchase_receipt_lines (
line_id         BIGINT PRIMARY KEY AUTO_INCREMENT,
receipt_id      BIGINT NOT NULL,
line_no         INT NOT NULL,
product_id      BIGINT NOT NULL,
warehouse_id    BIGINT NOT NULL,        -- 如头已固定，可冗余或与头保持一致
unit_id         BIGINT NOT NULL,
quantity        DECIMAL(18,6) NOT NULL,
price           DECIMAL(18,6) NOT NULL,
tax_rate        DECIMAL(5,2) NULL,
amount          DECIMAL(18,6) NULL,     -- 含税金额等
po_line_id      BIGINT NULL,            -- 关联采购订单行
batch_no        VARCHAR(64) NULL,       -- 如启用批次
production_date DATE NULL,
expiry_date     DATE NULL,
remark          VARCHAR(512) NULL,
CONSTRAINT fk_prl_receipt
FOREIGN KEY (receipt_id) REFERENCES purchase_receipts(receipt_id),
CONSTRAINT fk_prl_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_prl_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_prl_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

业务逻辑要点：

• 状态为 POSTED 时，才真正生成库存流水（入库）
• 如果启用批次管理，需要保证相同批次逻辑的一致性
• 可支持采购退货：字段中增加 is_return 或专门的退货单（结构类似但数量为负）

---

💸 五、销售模块 SQL 设计（销售订单与出库）

销售模块是库存减少与收入统计的核心。

5.1 销售订单（Sales Orders）

销售订单头：

CREATE TABLE sales_orders (
so_id           BIGINT PRIMARY KEY AUTO_INCREMENT,
so_number       VARCHAR(64) NOT NULL UNIQUE,
customer_id     BIGINT NOT NULL,
order_date      DATE NOT NULL,
delivery_date   DATE NULL,
status          VARCHAR(32) NOT NULL,  -- DRAFT, APPROVED, PART_DELIVERED, CLOSED, CANCELED
currency        VARCHAR(16) NOT NULL DEFAULT 'USD',
total_amount    DECIMAL(18,6) NULL,
remark          VARCHAR(512) NULL,
created_at      DATETIME NOT NULL,
created_by      BIGINT NOT NULL,
approved_at     DATETIME NULL,
approved_by     BIGINT NULL,
CONSTRAINT fk_so_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

销售订单明细：

CREATE TABLE sales_order_lines (
line_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
so_id          BIGINT NOT NULL,
line_no        INT NOT NULL,
product_id     BIGINT NOT NULL,
warehouse_id   BIGINT NOT NULL,        -- 计划发货仓
unit_id        BIGINT NOT NULL,
quantity       DECIMAL(18,6) NOT NULL,
price          DECIMAL(18,6) NOT NULL,
tax_rate       DECIMAL(5,2) NULL,
discount       DECIMAL(18,6) NULL,
remark         VARCHAR(512) NULL,
CONSTRAINT fk_sol_so
FOREIGN KEY (so_id) REFERENCES sales_orders(so_id),
CONSTRAINT fk_sol_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_sol_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_sol_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

销售订单通常会产生：

• 预占库存（预留给某客户的可用量减少）
• 后续生成销售出库单（Shipment/Delivery）

5.2 销售出库单（Sales Delivery / Sales Out）

出库单头：

CREATE TABLE sales_deliveries (
delivery_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
delivery_number  VARCHAR(64) NOT NULL UNIQUE,
so_id            BIGINT NULL,
customer_id      BIGINT NOT NULL,
warehouse_id     BIGINT NOT NULL,
delivery_date    DATE NOT NULL,
status           VARCHAR(32) NOT NULL, -- DRAFT, POSTED, CANCELED
remark           VARCHAR(512) NULL,
created_at       DATETIME NOT NULL,
created_by       BIGINT NOT NULL,
posted_at        DATETIME NULL,
posted_by        BIGINT NULL,
CONSTRAINT fk_sd_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CONSTRAINT fk_sd_so
FOREIGN KEY (so_id) REFERENCES sales_orders(so_id),
CONSTRAINT fk_sd_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);

出库单明细：

CREATE TABLE sales_delivery_lines (
line_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
delivery_id    BIGINT NOT NULL,
line_no        INT NOT NULL,
product_id     BIGINT NOT NULL,
warehouse_id   BIGINT NOT NULL,
unit_id        BIGINT NOT NULL,
quantity       DECIMAL(18,6) NOT NULL,
price          DECIMAL(18,6) NOT NULL,
tax_rate       DECIMAL(5,2) NULL,
amount         DECIMAL(18,6) NULL,
so_line_id     BIGINT NULL,
batch_no       VARCHAR(64) NULL,
remark         VARCHAR(512) NULL,
CONSTRAINT fk_sdl_delivery
FOREIGN KEY (delivery_id) REFERENCES sales_deliveries(delivery_id),
CONSTRAINT fk_sdl_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_sdl_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_sdl_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

业务逻辑要点：

• 出库单 POSTED 时，生成库存减记录
• 可能存在超卖控制，需要在提交之前检查库存或可用量
• 销售退货可采用结构类似的退货单，数量为正，类型为入库

---

🧮 六、库存核心：库存流水与库存余额的 SQL 设计

库存模块是整个进销存系统的“心脏”。设计不当会导致性能问题或数据不一致。

6.1 设计思路：流水 vs 余额

常见做法是双层结构：

1. 库存流水表（Stock Transactions）

• 每一笔入库、出库、调拨、盘点都写入一条或多条流水
• 字段包含：商品、仓库、批次、数量变化、来源单据等

2. 库存余额表（Stock Balance）

• 存储当前或某个时点的库存数量
• 从业务角度频繁查询这个表，减少对流水的大量汇总

6.2 库存流水表设计（Stock Transactions）

CREATE TABLE stock_transactions (
transaction_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id        BIGINT NOT NULL,
warehouse_id      BIGINT NOT NULL,
batch_no          VARCHAR(64) NULL,
unit_id           BIGINT NOT NULL,        -- 一般使用主单位
quantity_change   DECIMAL(18,6) NOT NULL, -- 入库为正，出库为负
transaction_type  VARCHAR(32) NOT NULL,   -- PURCHASE_IN, SALES_OUT, TRANSFER_OUT, TRANSFER_IN, ADJUSTMENT, OPENING_BALANCE etc.
source_doc_type   VARCHAR(32) NOT NULL,   -- PO, PR, SO, SD, ADJ, ST等
source_doc_id     BIGINT NOT NULL,
source_line_id    BIGINT NULL,
transaction_date  DATETIME NOT NULL,
created_at        DATETIME NOT NULL,
created_by        BIGINT NOT NULL,
remark            VARCHAR(512) NULL,
CONSTRAINT fk_st_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_st_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_st_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id),
INDEX idx_st_product_warehouse_batch (product_id, warehouse_id, batch_no),
INDEX idx_st_date (transaction_date)
);

设计关键点：

• quantity_change 正负区分入库/出库
• transaction_type 和 source_doc_type 用于对账与追踪
• 尽量统一使用主计量单位，避免换算误差

6.3 库存余额表设计（Stock Balance）

CREATE TABLE stock_balances (
id             BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id     BIGINT NOT NULL,
warehouse_id   BIGINT NOT NULL,
batch_no       VARCHAR(64) NULL,
unit_id        BIGINT NOT NULL,          -- 主单位
quantity       DECIMAL(18,6) NOT NULL DEFAULT 0,
last_update_at DATETIME NOT NULL,
CONSTRAINT uq_stock_balances UNIQUE (product_id, warehouse_id, batch_no),
CONSTRAINT fk_sb_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_sb_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_sb_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id),
INDEX idx_sb_product_warehouse (product_id, warehouse_id)
);

更新方式：

• 每写一条 stock_transactions 时，同时更新对应 stock_balances 中 quantity
• 使用事务保证两张表的原子性
• 并发情况下可以：
• 使用行锁（InnoDB 默认）
• 或采用乐观锁（版本号）+ 重试机制

6.4 实时库存查询 SQL 示例

查询某商品在所有仓库的库存：

SELECT
p.product_code,
p.product_name,
w.warehouse_name,
sb.batch_no,
sb.quantity
FROM stock_balances sb
JOIN products p   ON sb.product_id = p.product_id
JOIN warehouses w ON sb.warehouse_id = w.warehouse_id
WHERE p.product_code = 'SKU-001';

按商品汇总所有仓库库存：

SELECT
product_id,
SUM(quantity) AS total_qty
FROM stock_balances
GROUP BY product_id;

如需按库存流水动态计算某天历史库存，可以：

SELECT
product_id,
warehouse_id,
SUM(quantity_change) AS qty
FROM stock_transactions
WHERE transaction_date <= '2024-12-31 23:59:59'
GROUP BY product_id, warehouse_id;

为避免历史查询太慢，可引入“日结库存表”或按月预聚合。

---

🔁 七、调拨、盘点与库存调整的 SQL 设计

除了采购入库 / 销售出库，日常还有调拨和盘点等操作。

7.1 调拨单设计（Stock Transfer）

调拨涉及两个仓库：出库仓 与 入库仓。可以采用“单据 + 两条流水”的方式。

调拨单头：

CREATE TABLE stock_transfers (
transfer_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
transfer_number  VARCHAR(64) NOT NULL UNIQUE,
from_warehouse_id BIGINT NOT NULL,
to_warehouse_id   BIGINT NOT NULL,
transfer_date    DATE NOT NULL,
status           VARCHAR(32) NOT NULL, -- DRAFT, POSTED, CANCELED
remark           VARCHAR(512) NULL,
created_at       DATETIME NOT NULL,
created_by       BIGINT NOT NULL,
posted_at        DATETIME NULL,
posted_by        BIGINT NULL,
CONSTRAINT fk_stf_from_wh
FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_stf_to_wh
FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(warehouse_id)
);

调拨单明细：

CREATE TABLE stock_transfer_lines (
line_id         BIGINT PRIMARY KEY AUTO_INCREMENT,
transfer_id     BIGINT NOT NULL,
line_no         INT NOT NULL,
product_id      BIGINT NOT NULL,
unit_id         BIGINT NOT NULL,
quantity        DECIMAL(18,6) NOT NULL,
batch_no        VARCHAR(64) NULL,
remark          VARCHAR(512) NULL,
CONSTRAINT fk_stfl_transfer
FOREIGN KEY (transfer_id) REFERENCES stock_transfers(transfer_id),
CONSTRAINT fk_stfl_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_stfl_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

库存流水生成逻辑：

• 对每条调拨明细生成两条 stock_transactions：
• FROM 仓库：quantity_change = -quantity，transaction_type = ‘TRANSFER_OUT’
• TO 仓库：quantity_change = +quantity，transaction_type = ‘TRANSFER_IN’

7.2 盘点与库存调整单（Inventory Adjustment）

盘点的本质是：系统账面数 vs 实际数 的差异调整。

盘点/调整单头：

CREATE TABLE inventory_adjustments (
adjustment_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
adjustment_number VARCHAR(64) NOT NULL UNIQUE,
warehouse_id     BIGINT NOT NULL,
adjustment_date  DATE NOT NULL,
status           VARCHAR(32) NOT NULL, -- DRAFT, POSTED, CANCELED
reason           VARCHAR(128) NULL,    -- 盘点、报损、报溢等
remark           VARCHAR(512) NULL,
created_at       DATETIME NOT NULL,
created_by       BIGINT NOT NULL,
posted_at        DATETIME NULL,
posted_by        BIGINT NULL,
CONSTRAINT fk_adj_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);

调整单明细：

CREATE TABLE inventory_adjustment_lines (
line_id         BIGINT PRIMARY KEY AUTO_INCREMENT,
adjustment_id   BIGINT NOT NULL,
line_no         INT NOT NULL,
product_id      BIGINT NOT NULL,
unit_id         BIGINT NOT NULL,
batch_no        VARCHAR(64) NULL,
qty_book        DECIMAL(18,6) NOT NULL, -- 账面数量
qty_actual      DECIMAL(18,6) NOT NULL, -- 实盘数量
qty_diff        DECIMAL(18,6) NOT NULL, -- 差异 = actual - book
remark          VARCHAR(512) NULL,
CONSTRAINT fk_adjl_adjustment
FOREIGN KEY (adjustment_id) REFERENCES inventory_adjustments(adjustment_id),
CONSTRAINT fk_adjl_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_adjl_unit
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);

库存流水：

• qty_diff > 0 → 入库（溢）
• qty_diff < 0 → 出库（损）

---

📊 八、预占库存、可用库存与多仓场景设计

电商 / 分销场景中，有“订单下单但未发货”的情况，需要用 SQL 设计预占逻辑。

8.1 核心概念

• 实际库存（On-hand）：仓库内实物数量
• 在途库存（In-transit）：已经发货或在调拨途中，未入目标仓库
• 预占库存（Reserved）：已经被订单锁定但尚未发货
• 可用库存（Available）：可用于新订单的数量

常用计算方式：

可用库存 = 实际库存 + 在途库存 - 预占库存

8.2 预占库存实现方式

实现预占有多种方案：

方案	描述	优点	缺点
A	不建表，仅根据未出库的销售订单动态计算预占	结构简单	高并发下查询慢
B	建 stock_reservations 表，订单确认时写入	查询快，可精细控制	增加写入逻辑和一致性维护
C	在 stock_balances 中加 reserved_qty 字段	查询方便	更新逻辑复杂，易出现竞争

对于高并发电商 / B2B 场景，推荐方案 B 或 B+C 组合。

预占库存表设计示例：

CREATE TABLE stock_reservations (
reservation_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id       BIGINT NOT NULL,
warehouse_id     BIGINT NOT NULL,
so_id            BIGINT NOT NULL,
so_line_id       BIGINT NOT NULL,
reserved_qty     DECIMAL(18,6) NOT NULL,
created_at       DATETIME NOT NULL,
created_by       BIGINT NOT NULL,
status           VARCHAR(32) NOT NULL DEFAULT 'ACTIVE', -- ACTIVE, RELEASED
CONSTRAINT fk_sr_product
FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_sr_warehouse
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
CONSTRAINT fk_sr_so
FOREIGN KEY (so_id) REFERENCES sales_orders(so_id),
CONSTRAINT fk_sr_so_line
FOREIGN KEY (so_line_id) REFERENCES sales_order_lines(line_id),
INDEX idx_sr_product_wh (product_id, warehouse_id)
);

可用库存查询示例：

SELECT
sb.product_id,
sb.warehouse_id,
sb.quantity AS on_hand_qty,
IFNULL(SUM(sr.reserved_qty), 0) AS reserved_qty,
sb.quantity - IFNULL(SUM(sr.reserved_qty), 0) AS available_qty
FROM stock_balances sb
LEFT JOIN stock_reservations sr
ON sb.product_id = sr.product_id
AND sb.warehouse_id = sr.warehouse_id
AND sr.status = 'ACTIVE'
WHERE sb.product_id = :product_id
GROUP BY sb.product_id, sb.warehouse_id, sb.quantity;

8.3 多仓多组织（Multi-warehouse & Multi-entity）扩展

如果系统需要支持：

• 多公司/多法人
• 跨地区仓库
• 跨组织调拨、结算

可以在关键表中增加 company_id / org_id 字段，并加入组织架构表：

CREATE TABLE companies (
company_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
company_code VARCHAR(64) NOT NULL,
company_name VARCHAR(255) NOT NULL
);

在 warehouses, stock_balances, stock_transactions, 各单据头中加入 company_id 字段，并建立外键和索引，从而实现按公司隔离数据。

---

🧱 九、规范化建模 vs 性能优化：如何权衡？

SQL 进销存设计中，既要保证规范化（Normal Form），又要兼顾性能，常常需要折中。

9.1 何时规范化，何时反规范化？

规范化（Normal Form）优点：

• 避免数据冗余和更新异常
• 结构清晰、可维护
• 对变化和扩展更友好

反规范化（Denormalization）场景：

• 高频查询需要跨多表 join，导致性能瓶颈
• 明确冗余数据的来源，容易通过程序控制同步
• 实时报表、BI 需要快速聚合

在进销存系统中常见的适度反规范化：

• 在单据明细中冗余商品名称、仓库名称，避免大量 join
• 在 stock_balances 中存储最新成本单价（如移动加权价）
• 销售订单头冗余客户名称、销售员名称等

9.2 索引设计原则

典型查询模式包括：

• 按商品+仓库查询库存
• 按单据编号查询单据
• 按客户 / 供应商 + 时间段查询订单
• 按日期范围查询库存流水

因此：

• stock_balances：(product_id, warehouse_id, batch_no) 组合索引
• stock_transactions：(product_id, warehouse_id, transaction_date) 索引
• 订单头表：(so_number) 唯一索引，(customer_id, order_date) 组合索引
• 订单明细：(so_id, line_no) 索引

同时注意：

• 避免在高频写表上创建过多索引
• 针对报表需求，可单独建汇总表或物化视图

9.3 分库分表与数据量控制

当数据量增长到千万/亿级，单表性能可能成为瓶颈，可以逐步采用：

• 按时间分表（如 stock_transactions_2024, stock_transactions_2025）
• 按公司/组织分库
• 将历史数据归档到冷数据仓库

这一层面已经接近系统架构设计，需要 DBA 和后端团队协同规划。

---

🧮 十、成本核算与价格管理的 SQL 实现思路

成本核算不是进销存的最初目标，但很多企业希望在库存系统上实现简单成本计算。

10.1 常见成本核算方法

• 移动加权平均成本（Moving Weighted Average）
• 每次入库后重新计算平均成本
• 批次成本（Batch Cost）
• 成本随批次不同而不同
• 先进先出（FIFO）/ 后进先出（LIFO）
• 更多用于会计核算，SQL 实现相对复杂

在 SQL 进销存系统中，常用的方法是：

• 以商品+仓库为维度维护移动平均成本
• 对于需要精细核算的场景，可按批次管理成本

10.2 移动平均成本字段设计

可以在 stock_balances 中增加 avg_cost 字段：

ALTER TABLE stock_balances
ADD COLUMN avg_cost DECIMAL(18,6) NULL;  -- 当前移动平均成本

计算逻辑（简化示例）：

• 初始状态：库存数量 Q0，平均成本 C0
• 新入库：数量 Q1，单价 P1
• 新平均成本：

C_new = (Q0 * C0 + Q1 * P1) / (Q0 + Q1)

在 SQL 或应用层中：

1. 从 stock_balances 取得当前数量与成本
2. 根据入库明细计算新的平均成本
3. 更新 stock_balances 的 quantity 与 avg_cost

出库时：

• 通常采用当前 avg_cost 作为出库成本，写入销售出库明细或成本表

10.3 成本流水表（可选）

若需要更完整的成本审计，可专门建立 cost_transactions 表，用于记录每一次成本变动：

CREATE TABLE cost_transactions (
cost_txn_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id       BIGINT NOT NULL,
warehouse_id     BIGINT NOT NULL,
transaction_date DATETIME NOT NULL,
old_quantity     DECIMAL(18,6) NOT NULL,
old_cost         DECIMAL(18,6) NOT NULL,
new_quantity     DECIMAL(18,6) NOT NULL,
new_cost         DECIMAL(18,6) NOT NULL,
amount           DECIMAL(18,6) NOT NULL, -- 本次入库金额
source_doc_type  VARCHAR(32) NOT NULL,
source_doc_id    BIGINT NOT NULL,
source_line_id   BIGINT NULL,
created_at       DATETIME NOT NULL
);

---

🧩 十一、SQL 实现中的事务控制与并发问题

进销存系统中，事务和并发控制尤为关键，尤其是“下单-扣库存”链路。

11.1 核心并发场景

• 多个用户同时对同一商品同一仓库进行出库操作
• 多个销售订单同时预占同一库存
• 采购入库、库存调整与销售出库同时发生

若不加控制，可能出现：

• 超卖（库存被减成负数）
• 两个并发操作覆盖了对方的库存更新结果

11.2 常见解决策略对比

策略	描述	优点	缺点
行锁 + 事务	SELECT ... FOR UPDATE 锁定库存行	简单直接，数据库保障一致性	高并发下锁竞争激烈，吞吐下降
乐观锁（版本号）	更新时带 version 条件	减少锁等待，可重试	代码逻辑复杂，需要重试机制
队列/消息	将扣库存异步串行化处理	可缓冲高并发	不是纯 SQL 层面，需要中间件

在大多数中小规模进销存系统中，行锁 + 事务 已足够：

• 使用 InnoDB 默认的行级锁
• 在更新 stock_balances 前，先 SELECT ... FOR UPDATE 把库存行锁住
• 在事务内写入 stock_transactions 和更新 stock_balances

11.3 事务示例：销售出库扣减库存

以 MySQL 为例（伪代码）：

START TRANSACTION;

-- 1. 锁定库存余额行
SELECT quantity
FROM stock_balances
WHERE product_id = :pid
AND warehouse_id = :wid
FOR UPDATE;

-- 2. 检查是否足够
IF quantity < :out_qty THEN
ROLLBACK;
-- 返回库存不足错误
END IF;

-- 3. 写入库存流水
INSERT INTO stock_transactions (
product_id, warehouse_id, unit_id,
quantity_change, transaction_type,
source_doc_type, source_doc_id, source_line_id,
transaction_date, created_at, created_by
) VALUES (
:pid, :wid, :uid,
-:out_qty, 'SALES_OUT',
'SD', :delivery_id, :line_id,
NOW(), NOW(), :user_id
);

-- 4. 更新库存余额
UPDATE stock_balances
SET quantity = quantity - :out_qty,
last_update_at = NOW()
WHERE product_id = :pid
AND warehouse_id = :wid;

COMMIT;

在应用层进一步封装，可以减少 SQL 逻辑重复。

---

🧰 十二、结合低代码/现成系统：落地进销存设计

并不是所有团队都需要从零开始编码。合理利用成熟系统或低代码平台，可以在 SQL 设计思路之上快速落地。

12.1 自建 vs 采用已有进销存系统的对比

方式	优点	缺点	适用场景
完全自建	结构可控，贴合业务，技术积累	成本高，周期长，维护压力大	有强技术团队，业务高度定制
开源系统二开	基础功能齐全，可修改	需要理解原有架构，二次开发难度不定	预算有限，能接受开源生态
SaaS / 低代码平台搭建	快速上线，可配置程度高，运维成本低	底层结构与逻辑不可控部分；复杂定制有限	中小企业、快速试点、MVP 验证

在基于 SQL 的进销存架构之上，如果希望以较低成本快速搭建并可后续扩展，可以考虑用支持数据建模、表单和流程的工具来实现。例如在灵活支持商品、仓库、订单、库存流水的场景里，像 <简道云进销存>（https://s.fanruan.com/8bn69） 这类低代码模板，可以把“商品、仓库、订单、库存记录”等模型直接做成可视化表单，并通过逻辑校验、自动计算、权限控制来落地上文提到的 SQL 设计思路。实际项目中，这种方式可以作为“业务需求验证 + 快速试运营”的一站式方案，再视情况逐步演进成自建系统。

12.2 如何将本文的 SQL 设计映射到低代码/模板系统？

对应关系大致是：

概念	SQL 中的对象	在低代码 / 模板中的表现
商品	products 表	“商品档案”数据表/表单
仓库	warehouses 表	“仓库信息”数据表
采购订单	purchase_orders + purchase_order_lines	“采购订单”主从表单
采购入库	purchase_receipts + purchase_receipt_lines	“采购入库单”主从表单
销售订单	sales_orders + sales_order_lines	“销售订单”主从表单
销售出库	sales_deliveries + sales_delivery_lines	“销售出库单”主从表单
库存流水	stock_transactions	“库存流水/库存变动记录”表
库存余额	stock_balances	“实时库存汇总”视图/统计表

通过字段映射、计算公式、触发器/流程配置，就能在不写或少写 SQL 的前提下实现：自动扣减库存、校验库存是否足够、生成报表等逻辑，开发与维护成本相对较低。

---

🧭 十三、典型查询与报表 SQL 示例

在进销存系统投产后，报表需求会迅速增长。良好的 SQL 设计要能支持常见报表。

13.1 商品库存汇总报表

按商品维度汇总各仓当前库存：

SELECT
p.product_code,
p.product_name,
SUM(sb.quantity) AS total_qty
FROM stock_balances sb
JOIN products p ON sb.product_id = p.product_id
GROUP BY p.product_code, p.product_name
ORDER BY p.product_code;

按商品 + 仓库维度：

SELECT
p.product_code,
p.product_name,
w.warehouse_name,
sb.quantity
FROM stock_balances sb
JOIN products p   ON sb.product_id = p.product_id
JOIN warehouses w ON sb.warehouse_id = w.warehouse_id
ORDER BY p.product_code, w.warehouse_name;

13.2 销售明细与销售汇总报表

某一时间段内的销售明细：

SELECT
so.so_number,
so.order_date,
c.customer_name,
p.product_code,
p.product_name,
sol.quantity,
sol.price,
(sol.quantity * sol.price) AS line_amount
FROM sales_orders so
JOIN sales_order_lines sol ON so.so_id = sol.so_id
JOIN customers c           ON so.customer_id = c.customer_id
JOIN products p            ON sol.product_id = p.product_id
WHERE so.order_date BETWEEN :start_date AND :end_date
AND so.status IN ('APPROVED', 'CLOSED');

按商品汇总销售数量与金额：

SELECT
p.product_code,
p.product_name,
SUM(sol.quantity) AS total_qty,
SUM(sol.quantity * sol.price) AS total_amount
FROM sales_orders so
JOIN sales_order_lines sol ON so.so_id = sol.so_id
JOIN products p            ON sol.product_id = p.product_id
WHERE so.order_date BETWEEN :start_date AND :end_date
AND so.status IN ('APPROVED', 'CLOSED')
GROUP BY p.product_code, p.product_name
ORDER BY total_amount DESC;

13.3 采购统计报表

按供应商汇总采购金额：

SELECT
s.supplier_name,
SUM(prl.quantity * prl.price) AS total_purchase
FROM purchase_receipts pr
JOIN purchase_receipt_lines prl ON pr.receipt_id = prl.receipt_id
JOIN suppliers s                ON pr.supplier_id = s.supplier_id
WHERE pr.receipt_date BETWEEN :start_date AND :end_date
AND pr.status = 'POSTED'
GROUP BY s.supplier_name
ORDER BY total_purchase DESC;

---

🚀 十四、总结与未来趋势展望

14.1 本文设计要点回顾

围绕“SQL 进销存设计方法详解，如何高效构建系统”这一问题，整个设计思路可以概括为：

1. 从业务抽象出关键实体与关系

• 商品、仓库、客户、供应商等基础数据
• 采购、销售、调拨、盘点等单据头和明细
• 统一的库存流水表和库存余额表

2. 在 SQL 层面实现规范化 + 可扩展的建模

• 通过主外键、索引、主从表结构组织数据
• 使用批次、多单位、预占库存等扩展表满足复杂业务

3. 通过事务和锁机制保障库存数据一致性

• 销售出库、采购入库等操作必须封装为原子事务
• 适度使用行锁和乐观锁，平衡并发与性能

4. 利用视图、汇总表和报表 SQL 支撑分析需求

• 基于库存余额做实时库存查询
• 基于订单与流水做销售、采购统计

5. 结合低代码/现成模板加速落地

• 用 SQL 模型指导结构，用可视化工具实现表单与流程
• 类似 <简道云进销存>（https://s.fanruan.com/8bn69） 这样的模板，可以把上述设计快速映射为可用系统，后续再根据业务迭代优化字段与流程

14.2 未来趋势与演进方向

随着业务规模与场景复杂度提升，SQL 进销存系统通常会向以下方向演进：

1. 从单体数据库走向分布式架构

• 引入读写分离、分库分表
• 库存核心仍然保持强一致，其余报表可以采用最终一致

2. 更紧密地与电商平台、WMS、财务系统集成

• 通过 API/消息队列同步订单、发货、结算数据
• 在 SQL 层抽象出更清晰的“接口表”或“中间表”

3. 与 BI / 数据仓库结合，强化分析能力

• ETL 抽取业务数据进入数据仓库
• 通过维度建模构建销售、库存、采购主题，支持多维分析

4. 低代码 + PaaS 平台支持深度定制

• 越来越多企业会用低代码平台搭建进销存 + CRM + 财务一体化应用
• 通过配置化方式快速上线，并保留 SQL 级别的扩展能力

在实战中，一个务实的路线是：先用清晰的 SQL 进销存模型搭建最小可用系统，再围绕业务需求迭代扩展。无论是自研代码，还是基于低代码/SaaS 平台，良好的数据库设计永远是系统稳定运行和可持续演进的基础。

最后，如果你希望直接在现成模板上实践文中的思想，可以参考我们在内部实际使用的一套进销存系统模板： 分享一个我们公司在用的进销存系统模板，需要的可以自取，可直接使用，也可以自定义编辑修改： https://s.fanruan.com/8bn69

精品问答:

---

SQL进销存设计方法中，如何优化数据库结构以提升系统性能？

在设计SQL进销存系统时，我总是不确定怎样优化数据库结构才能提升整体性能。有没有具体的方法或技巧，能帮助我构建一个既高效又稳定的数据库？

优化SQL进销存系统的数据库结构，关键在于合理设计表结构和索引。常用方法包括：

1. 范式设计：确保数据库满足第三范式（3NF），减少数据冗余，提高数据一致性。
2. 索引优化：为常用查询字段建立合适的索引，如主键索引、联合索引，提升查询速度。
3. 分区表设计：针对大数据量的库存表，采用分区技术，分散IO压力。
4. 使用视图和存储过程：封装复杂业务逻辑，减少客户端负担。

例如，某电商平台通过为订单表建立联合索引，将查询响应时间从2秒降至500毫秒，性能提升了75%。

SQL进销存系统设计中，如何实现数据一致性和事务管理？

我在学习进销存系统的设计时，比较担心数据一致性问题，尤其是在多用户同时操作库存时。如何利用SQL的事务机制来保证数据的准确和可靠？

保证数据一致性和事务管理是进销存系统设计的核心，主要方法包括：

1. 使用ACID事务特性：确保操作的原子性、一致性、隔离性和持久性。
2. 合理设置事务隔离级别：如读已提交（Read Committed）或可重复读（Repeatable Read），防止脏读和幻读。
3. 悲观锁和乐观锁结合使用：悲观锁适合高并发场景，防止写冲突；乐观锁适合低冲突环境，提高并发性能。

举例来说，某仓储系统通过设置可重复读隔离级别，避免了库存数量在多用户操作下的错误更新，库存数据准确率提升至99.9%。

在SQL进销存设计中，如何通过报表和数据分析支持业务决策？

我想让我的SQL进销存系统不仅能管理库存和订单，还能生成有价值的报表和数据分析，支持公司的经营决策。有哪些设计思路和技术可以实现这一目标？

实现报表和数据分析功能，设计时应考虑以下技术和思路：

1. 数据仓库与ETL设计：定期将业务数据抽取、转换、加载到数据仓库，便于多维度分析。
2. 多维数据模型（OLAP）：设计星型或雪花型模式，支持快速聚合查询。
3. 使用SQL聚合函数与窗口函数：如SUM(), AVG(), RANK()等，进行销售趋势、库存周转率等指标计算。
4. 报表自动化工具集成：结合BI工具（如Power BI、Tableau）实现可视化分析。

例如，通过实现库存周转率报表，某零售企业提升了库存资金利用率15%，大幅降低了库存积压风险。

如何在SQL进销存系统中设计灵活的权限管理机制？

我担心在SQL进销存系统中，用户权限管理不当会导致数据泄露或误操作。怎样设计一个既灵活又安全的权限管理机制？

设计灵活的权限管理机制，主要包括：

1. 基于角色的访问控制（RBAC）：定义角色和权限，用户根据角色获得相应权限，简化权限管理。
2. 细粒度权限控制：支持到表、字段甚至行级别的权限设置，保障数据安全。
3. 审计日志机制：记录用户操作历史，便于追踪和审计。
4. 结合SQL安全特性：如视图权限、存储过程权限，避免直接操作基础表。

案例中，某制造企业通过RBAC机制，将不同部门的用户权限严格区分，降低了人为错误率35%，提升系统安全性。

147

×

微信分享

扫描二维码分享到微信