如何编写允许270-279范围mID重复、其余ID去重的随机SQL查询

适用范围：MySQL 8.0及以上版本（依赖递归CTE特性）

核心逻辑 #

针对每个分类的查询，拆分两类数据池处理：

• 非270~279区间的mID条目：最多只取1次，优先全部加入结果集
• 270~279区间的mID条目：可重复随机选取，用于补充差额，直到达到LIMIT指定的条数
  注意将原语句的UNION改为UNION ALL，避免全局去重导致重复的270~279区间条目被删除。

完整修改后代码 #

-- 如果需要返回的单分类条数超过1000，先执行以下语句调整递归深度
-- SET SESSION cte_max_recursion_depth = 10000;

$query = 
WITH RECURSIVE 
-- 第一个分类：mRar='1'，目标返回10条
rar1_pool AS (
    SELECT mID, mName, mType, mRar FROM mcitems WHERE mRar = '1' AND mType='13'
),
rar1_non_repeat AS (
    SELECT * FROM rar1_pool WHERE mID NOT BETWEEN 270 AND 279 ORDER BY RAND()
),
rar1_repeat_pool AS (
    SELECT * FROM rar1_pool WHERE mID BETWEEN 270 AND 279
),
rar1_result AS (
    -- 先取所有不可重复的条目
    SELECT *, 1 AS level FROM rar1_non_repeat
    UNION ALL
    -- 递归补充可重复的条目，直到达到10条
    SELECT r.*, rr.level + 1 
    FROM rar1_repeat_pool r
    JOIN rar1_result rr ON rr.level < 10
    ORDER BY RAND() LIMIT 1
)
SELECT mID, mName, mType, mRar FROM rar1_result LIMIT 10

UNION ALL

-- 第二个分类：mRar='3'，目标返回3条
WITH RECURSIVE
rar3_pool AS (
    SELECT mID, mName, mType, mRar FROM mcitems WHERE mRar = '3' AND mType='13'
),
rar3_non_repeat AS (
    SELECT * FROM rar3_pool WHERE mID NOT BETWEEN 270 AND 279 ORDER BY RAND()
),
rar3_repeat_pool AS (
    SELECT * FROM rar3_pool WHERE mID BETWEEN 270 AND 279
),
rar3_result AS (
    SELECT *, 1 AS level FROM rar3_non_repeat
    UNION ALL
    SELECT r.*, rr.level + 1 
    FROM rar3_repeat_pool r
    JOIN rar3_result rr ON rr.level < 3
    ORDER BY RAND() LIMIT 1
)
SELECT mID, mName, mType, mRar FROM rar3_result LIMIT 3;

低版本MySQL适配方案 #

如果使用的是MySQL 5.x不支持CTE，可在业务代码层实现逻辑：

1. 先查询对应分类下所有非270~279区间的条目，存入结果数组
2. 查询对应分类下所有270~279区间的条目，存入重复池数组
3. 随机从重复池数组中取值加入结果数组，直到数组长度达到目标LIMIT值
4. 最后随机打乱整个结果数组即可

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