如何让Aurora MySQL 8.x中CTE仅调用一次expensive函数？

在你的场景里，尽管CTE逻辑上只返回一行数据，但因为后续和children表做关联，MySQL优化器会把CTE的执行和关联操作合并，导致expensive()函数被每一条子记录触发一次调用。哪怕给函数加上DETERMINISTIC属性也没用——因为这个函数带有插入calls表的副作用，优化器不会缓存这类有副作用的函数结果。

下面是两种能确保函数只执行一次的调整方案：

方案1：用用户变量存储函数结果 #

先单独调用一次函数，把结果存到变量里，后续查询直接用这个变量。这种方式最直观，能彻底保证函数只执行一次。

set @pParentId = 2;
-- 提前调用函数，将结果存入变量
set @func_result = sandpit.expensive(@pParentId);

SELECT p.*, c.*, @func_result as result
FROM sandpit.parents p
LEFT OUTER JOIN sandpit.children c ON c.parentId = p.id
WHERE p.id = @pParentId;

方案2：用独立子查询强制函数单次执行 #

把函数调用放在一个完全独立的子查询里，用from dual和limit 1确保子查询只执行一次，再通过CROSS JOIN把结果关联到主查询中：

set @pParentId = 2;

SELECT p.*, c.*, func_result.result
FROM sandpit.parents p
LEFT OUTER JOIN sandpit.children c ON c.parentId = p.id
-- 独立子查询获取函数结果，确保只执行一次
CROSS JOIN (
    select sandpit.expensive(@pParentId) as result
    from dual
    limit 1
) as func_result
WHERE p.id = @pParentId;

这两种方案都能让expensive()函数只执行一次，同时把结果返回给所有同parentId的子行。

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