如何在mysql中设计产品库存管理功能

CREATE TABLE product_inventory (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL UNIQUE,
    stock_quantity INT DEFAULT 0,
    reserved_quantity INT DEFAULT 0,
    available_quantity INT AS (stock_quantity - reserved_quantity) STORED,
    min_stock_level INT DEFAULT 10,
    max_stock_level INT DEFAULT 100,
    status ENUM('in_stock', 'out_of_stock', 'low_stock') DEFAULT 'in_stock',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE inventory_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    inventory_id BIGINT NOT NULL,
    change_type ENUM('in', 'out', 'adjust', 'sale', 'return') NOT NULL,
    quantity_change INT NOT NULL,
    reference_type VARCHAR(50),
    reference_id BIGINT,
    operator_id INT,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inventory_id) REFERENCES product_inventory(id)
);

START TRANSACTION;
<p>UPDATE product_inventory 
SET 
reserved_quantity = reserved_quantity + 1,
updated_at = NOW()
WHERE product_id = ? 
AND (stock_quantity - reserved_quantity) >= 1;</p><p>-- 检查是否影响一行
IF ROW_COUNT() = 0 THEN
ROLLBACK;
-- 返回库存不足错误
ELSE
INSERT INTO inventory_log (inventory_id, change_type, quantity_change, reference_type, reference_id)
VALUES (?, 'sale', 1, 'order', ?);
COMMIT;
END IF;</p>

SELECT product_id, stock_quantity, min_stock_level 
FROM product_inventory 
WHERE stock_quantity <= min_stock_level;