MySQL 5.5.56临时表在触发器中使用时始终为空，但在手动运行查询时有效

Question

有人能帮我清理一下东西吗？我有一段SQL代码，需要在触发器上运行，但触发器无法工作。但在SQL客户端上手动运行代码时有效。

SET @sr_id = NEW.purchase_id; /* SET @sr_id = 123456 when run manually */
SET @ndi = (SELECT COUNT(a.id) FROM purchase_rewards a LEFT JOIN item b ON b.id = a.item_id WHERE a.unit_id IS NOT NULL AND COALESCE(b.is_privileged,0) = 0 AND a.purchase_id = @sr_id);
SET @res = @ndi - CEIL(@ndi/2);

DROP TEMPORARY TABLE IF EXISTS for_removal;
CREATE TEMPORARY TABLE for_removal
    SELECT ID FROM (
        SELECT a.id, @rownum := @rownum + 1 AS `rank` FROM purchase_rewards a LEFT JOIN item b ON b.id = a.item_id
            WHERE a.purchase_id = @sr_id AND COALESCE(b.is_privileged,0) = 0
        ) ft CROSS JOIN (SELECT @rownum := 0) r WHERE `rank` <= @res;
        
DELETE ta FROM purchase_rewards ta INNER JOIN for_removal tb ON ta.id = tb.id WHERE ta.purchase_id = @sr_id;

该代码查询购买的没有“特权”的商品，在每个商品上放置一个排名列，并删除其中的一半。你只能得到一半的奖励，这就是重点。这个软件是由其他没有源代码的人创建的，所以这是一个背负系统。

我把调试代码放在每一个中间，看看连接是否改变了，或者结果是空的，但除了最后一部分之外，一切都很好。在删除部分之前，我添加了调试代码：

SET @icount = (SELECT COUNT(ID) FROM for_removal);
INSERT INTO debug_log SET `log` = @icount;

结果就是表总是空的。我还尝试将代码转换为存储过程，但遇到了同样的问题。仅在代码有效的地方手动运行代码。

我目前使用的是游标和循环删除，但当有数百个项目时，速度会变慢。

示例数据：dbfiddle

谢谢!

Answer 1

根据上面的注释，答案是在查询之前设置@rownum变量。

SET @rownum = 0;
CREATE TEMPORARY TABLE ...

原因是您不能依赖于交叉连接中的表求值顺序。如果在@rownum初始化之前计算子查询，则@rownum将为NULL，任何使用@rownum := @rownum +1递增它的尝试也将生成NULL。因此，rank的每一行都将为NULL，并且所有行都不会满足WHERE子句。

至于为什么这在MySQL客户端有效，但在触发器中不起作用，我有一个理论：

如果您多次测试查询，会话变量@rownum将保留它的值。因此，如果您在会话中将其设置为某个非空值，然后随后在同一会话中测试排名查询，则它将递增。

但是，如果您将其作为触发器的一部分运行，则每次都可能是一个全新的会话，并且@rownum的值最初将为空。