MySQL数据库创建库存表代码 MySQL如何创建数据库库存表代码集锦

products

stock_movements

warehouses

warehouse_stock

products

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '唯一的产品ID',
    product_name VARCHAR(255) NOT NULL COMMENT '产品名称',
    sku VARCHAR(100) UNIQUE NOT NULL COMMENT '库存单位，唯一标识符',
    description TEXT COMMENT '产品详细描述',
    price DECIMAL(10, 2) NOT NULL COMMENT '产品销售单价',
    current_stock INT NOT NULL DEFAULT 0 COMMENT '当前可售库存数量',
    min_stock_level INT DEFAULT 0 COMMENT '最低库存预警线，低于此值需补货',
    max_stock_level INT DEFAULT 0 COMMENT '最高库存限制，避免过度采购',
    is_active BOOLEAN DEFAULT TRUE COMMENT '产品是否活跃或在售',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间'
) COMMENT '核心产品信息及总库存表';

stock_movements

CREATE TABLE stock_movements (
    movement_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '库存变动记录的唯一ID',
    product_id INT NOT NULL COMMENT '关联的产品ID',
    movement_type ENUM('inbound', 'outbound', 'adjustment') NOT NULL COMMENT '变动类型：入库、出库、调整',
    quantity INT NOT NULL COMMENT '变动的数量，入库为正，出库为负（或统一为正，通过类型区分）',
    reason VARCHAR(255) COMMENT '变动原因，如：客户订单、供应商入库、盘点调整',
    reference_id VARCHAR(255) COMMENT '关联的业务单据ID，如订单号、入库单号',
    movement_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '变动发生的时间',
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT ON UPDATE CASCADE
) COMMENT '详细库存变动日志表';

warehouses

warehouse_stock

CREATE TABLE warehouses (
    warehouse_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '仓库的唯一ID',
    warehouse_name VARCHAR(255) NOT NULL UNIQUE COMMENT '仓库名称',
    location TEXT COMMENT '仓库的物理地址或描述'
) COMMENT '仓库信息表';

CREATE TABLE warehouse_stock (
    warehouse_stock_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '仓库库存记录的唯一ID',
    product_id INT NOT NULL COMMENT '关联的产品ID',
    warehouse_id INT NOT NULL COMMENT '关联的仓库ID',
    quantity INT NOT NULL DEFAULT 0 COMMENT '该产品在该仓库的当前库存数量',
    UNIQUE (product_id, warehouse_id), -- 确保一个产品在一个仓库只有一条库存记录
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '按仓库划分的产品库存表';

UPDATE

UPDATE products SET current_stock = current_stock - 1 WHERE product_id = X;

START TRANSACTION; -- 开启事务

-- 假设要购买 product_id = 101 的商品，数量为 2
SET @product_id_to_buy = 101;
SET @quantity_to_deduct = 2;

-- 1. 查询并锁定待更新的库存行。
-- SELECT ... FOR UPDATE 会对查询到的行施加排他锁，其他事务无法修改或再次FOR UPDATE这些行，直到当前事务提交或回滚。
SELECT current_stock INTO @current_stock_available FROM products WHERE product_id = @product_id_to_buy FOR UPDATE;

-- 2. 检查库存是否充足
IF @current_stock_available < @quantity_to_deduct THEN
    ROLLBACK; -- 库存不足，回滚事务
    SELECT '库存不足，操作失败';
ELSE
    -- 3. 扣减库存
    UPDATE products
    SET current_stock = current_stock - @quantity_to_deduct
    WHERE product_id = @product_id_to_buy; -- 这里的WHERE条件再次确认，但主要保护作用由FOR UPDATE提供

    -- 4. 记录库存变动（非常重要，用于审计和追溯）
    INSERT INTO stock_movements (product_id, movement_type, quantity, reason, reference_id)
    VALUES (@product_id_to_buy, 'outbound', @quantity_to_deduct, '客户订单', 'ORDER_XYZ_123');

    -- 5. 执行其他相关的业务逻辑，比如创建订单项、更新订单状态等
    -- ...

    COMMIT; -- 所有操作成功，提交事务
    SELECT '库存扣减成功，订单处理完成';
END IF;

INT

DECIMAL

NOT NULL

DEFAULT

products

UPDATE

SELECT ... FOR UPDATE

stock_movements