MariaDB CTE使用rand()出现非确定性结果的技术咨询

DevOps 小满

2026-6-16

CTE引用随机函数出现重复执行的问题分析与解决

核心原因：不同SQL引擎的CTE实现逻辑差异

你遇到的问题本质是CTE的两种执行策略导致的：

PostgreSQL默认采用物化CTE：执行时会先把CTE的结果计算出来并暂存，后续所有对该CTE的引用都直接复用这个缓存结果，所以random()只执行一次，得到相同值。
你当前使用的引擎（比如MySQL）默认采用内联CTE：把CTE当作子查询的语法糖，每次引用CTE时都会重新执行对应的子查询逻辑，rand()自然会被调用两次，生成不同的随机值。

这种情况下，默认的CTE确实和直接复制粘贴子查询效果一致，因为没有做结果缓存。

实现确定性结果的几种方法

1. 强制物化CTE（引擎支持时优先用）

如果你的SQL引擎支持强制物化CTE的语法（比如MySQL 8.0.19+），可以直接给CTE加上MATERIALIZED关键字：

with test_a as materialized (
    select 
        rand()
), test_b as (
    select
        *
    from 
        test_a
)
select * from test_a
union all
select * from test_b;

这样test_a会被执行一次并保存结果，后续引用都复用这个值，就能得到相同的随机数。

2. 用临时表存储随机值

如果引擎不支持物化CTE，先把随机值存入临时表，再在后续查询中引用：

create temporary table test_a as select rand();

with test_b as (
    select * from test_a
)
select * from test_a
union all
select * from test_b;

3. 用变量存储随机值（适合单值场景）

对于只需要单个随机值的场景，可以用变量先存储结果，再在CTE中引用这个变量：

-- 以MySQL为例，不同引擎变量语法可能不同
set @rand_val = rand();

with test_a as (
    select @rand_val as rand_col
), test_b as (
    select * from test_a
)
select * from test_a
union all
select * from test_b;