如何在VB.NET+MySQL 5.7中实现FIFO库存出库规则

一、数据库表设计 #

首先需要按采购批次存储库存数据，确保能按FIFO顺序扣减。创建inventory_batches表：

CREATE TABLE inventory_batches (
    batch_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    purchase_quantity DECIMAL(18,2) NOT NULL,
    remaining_quantity DECIMAL(18,2) NOT NULL,
    purchase_date DATETIME NOT NULL,
    INDEX idx_product_date (product_id, purchase_date)
);

• product_id：关联商品表的外键（需自行创建商品表）
• remaining_quantity：记录每个批次当前剩余库存，初始值等于purchase_quantity
• 索引idx_product_date用于提升按商品ID+采购日期排序的查询效率

二、MySQL存储过程实现FIFO扣减 #

编写存储过程实现原子性的批量扣减，避免并发场景下的库存异常：

DELIMITER //
CREATE PROCEDURE DeductInventoryFIFO(
    IN p_product_id INT,
    IN p_deduct_quantity DECIMAL(18,2),
    OUT p_result INT -- 0=成功，1=库存不足
)
BEGIN
    DECLARE v_remaining_deduct DECIMAL(18,2);
    DECLARE v_batch_remaining DECIMAL(18,2);
    DECLARE v_batch_id INT;
    DECLARE done INT DEFAULT FALSE;
    
    -- 声明游标，按采购日期升序获取待扣减的批次
    DECLARE batch_cursor CURSOR FOR 
        SELECT batch_id, remaining_quantity 
        FROM inventory_batches 
        WHERE product_id = p_product_id AND remaining_quantity > 0 
        ORDER BY purchase_date ASC;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET v_remaining_deduct = p_deduct_quantity;
    SET p_result = 0;
    
    -- 开启事务
    START TRANSACTION;
    
    OPEN batch_cursor;
    read_loop: LOOP
        FETCH batch_cursor INTO v_batch_id, v_batch_remaining;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        IF v_batch_remaining >= v_remaining_deduct THEN
            -- 当前批次足够扣减剩余量
            UPDATE inventory_batches 
            SET remaining_quantity = remaining_quantity - v_remaining_deduct 
            WHERE batch_id = v_batch_id;
            SET v_remaining_deduct = 0;
            LEAVE read_loop;
        ELSE
            -- 当前批次全部扣完，继续下一批
            UPDATE inventory_batches 
            SET remaining_quantity = 0 
            WHERE batch_id = v_batch_id;
            SET v_remaining_deduct = v_remaining_deduct - v_batch_remaining;
        END IF;
    END LOOP;
    
    IF v_remaining_deduct > 0 THEN
        -- 库存不足，回滚事务
        ROLLBACK;
        SET p_result = 1;
    ELSE
        -- 扣减成功，提交事务
        COMMIT;
    END IF;
    
    CLOSE batch_cursor;
END //
DELIMITER ;

• 游标按采购日期升序读取库存批次，确保先扣最早的库存
• 事务保证整个扣减过程的原子性，要么全部成功，要么全部回滚
• 输出参数p_result返回执行结果，方便VB.NET端判断

三、VB.NET端调用实现 #

使用MySqlConnection和MySqlCommand调用存储过程，处理库存扣减逻辑：

Imports MySql.Data.MySqlClient

Public Class InventoryManager
    Public Function DeductInventoryByFIFO(productId As Integer, deductQuantity As Decimal) As Boolean
        Dim connectionString As String = "server=你的服务器地址;user=用户名;password=密码;database=数据库名;SslMode=None;"
        Dim result As Integer = 0
        
        Using conn As New MySqlConnection(connectionString)
            Try
                conn.Open()
                
                Using cmd As New MySqlCommand("DeductInventoryFIFO", conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    
                    ' 添加输入参数
                    cmd.Parameters.AddWithValue("@p_product_id", productId)
                    cmd.Parameters.AddWithValue("@p_deduct_quantity", deductQuantity)
                    
                    ' 添加输出参数
                    Dim outputParam As New MySqlParameter("@p_result", MySqlDbType.Int32)
                    outputParam.Direction = ParameterDirection.Output
                    cmd.Parameters.Add(outputParam)
                    
                    cmd.ExecuteNonQuery()
                    
                    ' 获取执行结果
                    result = Convert.ToInt32(outputParam.Value)
                End Using
                
                conn.Close()
            Catch ex As Exception
                ' 处理异常，比如日志记录
                Console.WriteLine($"库存扣减失败：{ex.Message}")
                Return False
            End Try
        End Using
        
        ' 返回是否成功（0为成功）
        Return result = 0
    End Function
End Class

• 确保已引用MySql.Data NuGet包（对应MySQL 5.7的版本，建议使用8.0.x兼容版本）
• 异常处理可根据实际需求扩展，比如写入日志或返回更详细的错误信息
• 调用示例：Dim manager As New InventoryManager() Dim success As Boolean = manager.DeductInventoryByFIFO(1, 10000)

四、关键注意事项 #

• 并发控制：存储过程的事务和游标操作确保了单商品扣减的原子性，若高并发场景下需进一步优化，可考虑给inventory_batches表的product_id行加排他锁
• 数据精度：使用DECIMAL(18,2)而非INT存储数量，支持小数库存（如按重量计量的商品）
• 库存校验：在调用存储前，也可先查询商品总剩余库存是否足够，减少不必要的存储过程调用

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