MySQL 5.7下高效识别可用Product的方案咨询（排除库外逻辑）

除了触发器，针对你的需求，在MySQL 5.7中还有以下几种纯数据库内的实现方案：

1. 实时关联子查询 #

直接编写SQL查询语句，实时计算每个产品的可用状态，无需额外维护对象。核心逻辑是先获取每个产品的最新撤回记录，再判断是否符合可用条件：

SELECT 
    p.id AS product_id,
    CASE
        WHEN w.id IS NULL THEN '可用'
        WHEN w.state = 'FINISHED' THEN '可用'
        ELSE '不可用'
    END AS product_status
FROM Product p
LEFT JOIN (
    SELECT 
        product_id,
        state,
        id,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date DESC) AS rn
    FROM Withdraw
) w ON p.id = w.product_id AND w.rn = 1
WHERE 
    w.id IS NULL OR w.state = 'FINISHED';

说明 #

• 子查询通过ROW_NUMBER()窗口函数按产品分组，取每条产品最新的撤回记录（按date降序排序，rn=1即为最新记录）
• 左连接Product表后，过滤出“无撤回记录”或“最新撤回状态为FINISHED”的产品，同时输出对应状态
• 优点：无需额外维护，逻辑直观；缺点：若Withdraw表数据量极大，每次查询性能可能受影响

2. 创建查询视图 #

将上述查询逻辑封装为视图，客户端直接查询视图即可，逻辑完全封装在数据库内：

CREATE VIEW AvailableProducts AS
SELECT 
    p.id AS product_id,
    CASE
        WHEN w.id IS NULL THEN '可用'
        WHEN w.state = 'FINISHED' THEN '可用'
        ELSE '不可用'
    END AS product_status
FROM Product p
LEFT JOIN (
    SELECT 
        product_id,
        state,
        id,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date DESC) AS rn
    FROM Withdraw
) w ON p.id = w.product_id AND w.rn = 1
WHERE 
    w.id IS NULL OR w.state = 'FINISHED';

说明 #

• 客户端只需执行SELECT * FROM AvailableProducts;就能获取目标结果
• 优点：复用性强，客户端无需关心底层逻辑；缺点：本质还是实时计算，性能表现和子查询一致

3. 模拟物化视图（定时刷新表） #

如果每日查询频率极高，对实时性要求不是极端严格，可以用MySQL事件调度器定时刷新一张专门存储可用产品的表，客户端直接查询这张表即可：

步骤1：创建结果缓存表 #

CREATE TABLE AvailableProductsCache (
    product_id INT PRIMARY KEY,
    product_status VARCHAR(20),
    last_refreshed DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

步骤2：创建刷新数据的存储过程 #

DELIMITER //
CREATE PROCEDURE RefreshAvailableProducts()
BEGIN
    TRUNCATE TABLE AvailableProductsCache;
    INSERT INTO AvailableProductsCache (product_id, product_status)
    SELECT 
        p.id AS product_id,
        CASE
            WHEN w.id IS NULL THEN '可用'
            WHEN w.state = 'FINISHED' THEN '可用'
            ELSE '不可用'
        END AS product_status
    FROM Product p
    LEFT JOIN (
        SELECT 
            product_id,
            state,
            id,
            ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date DESC) AS rn
        FROM Withdraw
    ) w ON p.id = w.product_id AND w.rn = 1
    WHERE 
        w.id IS NULL OR w.state = 'FINISHED';
END //
DELIMITER ;

步骤3：创建定时刷新事件（示例为每5分钟刷新一次） #

SET GLOBAL event_scheduler = ON;

CREATE EVENT RefreshAvailableProductsEvent
ON SCHEDULE EVERY 5 MINUTE
STARTS CURRENT_TIMESTAMP
DO CALL RefreshAvailableProducts();

说明 #

• 客户端直接查询AvailableProductsCache表，性能极高
• 优点：查询速度快，适合高并发场景；缺点：数据存在延迟，延迟时长由刷新间隔决定

4. 存储过程封装查询 #

把查询逻辑封装成存储过程，客户端调用存储过程即可获取结果集：

DELIMITER //
CREATE PROCEDURE GetAvailableProducts()
BEGIN
    SELECT 
        p.id AS product_id,
        CASE
            WHEN w.id IS NULL THEN '可用'
            WHEN w.state = 'FINISHED' THEN '可用'
            ELSE '不可用'
        END AS product_status
    FROM Product p
    LEFT JOIN (
        SELECT 
            product_id,
            state,
            id,
            ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date DESC) AS rn
        FROM Withdraw
    ) w ON p.id = w.product_id AND w.rn = 1
    WHERE 
        w.id IS NULL OR w.state = 'FINISHED';
END //
DELIMITER ;

说明 #

• 客户端执行CALL GetAvailableProducts();即可获取目标结果
• 优点：可灵活扩展（比如后续添加过滤参数），逻辑封装在库内；缺点：性能表现和实时查询一致

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