MariaDB自定义random_category函数关联查询结果异常求助

问题根源 #

在WHERE子句中调用random_category()时，MariaDB会为category表的每一行单独执行一次该函数，而非仅执行一次后用结果匹配所有行。这就导致每行判断时用到的随机ID都不相同：

• 若某次生成的ID不存在于表中，返回空结果；
• 若多轮函数执行生成的不同ID恰好对应表中不同行，会返回多行结果；
• 仅当某行的ID刚好等于当前行执行函数生成的ID时，才会返回单行结果。

另外，原函数返回类型为int，但表中id字段是bigint，类型不匹配可能引发隐性转换问题，建议统一为bigint。

解决方案 #

方案1：用子查询固定随机ID（最稳妥）

通过子查询预先获取唯一的随机ID，再用该值匹配表数据，子查询仅执行一次，结果稳定：

SELECT * FROM category c 
WHERE id = (SELECT id FROM category ORDER BY RAND() LIMIT 1);

方案2：利用会话变量让函数仅生成一次随机ID

修改函数，借助会话变量存储随机ID，同一会话内首次调用生成值，后续复用，避免重复执行随机逻辑：

DELIMITER //
CREATE OR REPLACE FUNCTION random_category() RETURNS BIGINT
BEGIN
    IF @_rand_category_id IS NULL THEN
        SET @_rand_category_id = (SELECT id FROM category ORDER BY RAND() LIMIT 1);
    END IF;
    RETURN @_rand_category_id;
END //
DELIMITER ;

注意：若需要每次查询都重新生成随机ID，需先重置会话变量：

SET @_rand_category_id = NULL;
SELECT * FROM category c WHERE id = random_category();

方案3：使用变量存储随机ID后查询

先单独获取随机ID存入变量，再用变量匹配数据：

SET @rand_id = (SELECT id FROM category ORDER BY RAND() LIMIT 1);
SELECT * FROM category WHERE id = @rand_id;

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