如何编写支持多章节筛选、按分值单独限制随机抽题的MySQL查询

核心思路 #

• 先过滤公共章节：所有题目必须命中用户选中的章节列表，用IN语法实现
• 按分值分组抽题：对每个指定分值的题目单独随机排序，取对应配置的抽题上限数
• 全量使用预处理绑定参数，避免SQL注入风险

方案1：MySQL 8.0+ 窗口函数实现（推荐，性能更优） #

用ROW_NUMBER()窗口函数对每个分值的题目按随机数分组排序，筛选行号小于等于对应抽题上限的记录即可。

完整PHP实现代码 #

<?php
// 示例用户输入，可根据实际表单提交规则调整
$selectedChapters = [2,5,8]; // 用户选中的公共章节数组
$markConfig = [
    1 => 3, // 键为分值，值为对应抽题数量
    2 => 5,
    6 => 10
];
$rkey = generateRandomString(32);

// 前置参数合法性校验
foreach($selectedChapters as $chap) {
    if(!in_array($chap, range(1,9))) die('章节参数非法');
}
foreach($markConfig as $mark => $limit) {
    if(!in_array($mark, range(1,6)) || $limit < 1) die('分值或抽题数参数非法');
}

// 动态拼接SQL和参数
$chapterPlaceholders = implode(',', array_fill(0, count($selectedChapters), '?'));
$markList = implode(',', array_keys($markConfig));
$casePart = '';
$bindParams = [$rkey]; // 第一个参数为全局rkey
$bindTypes = 's'; // 第一个参数类型为字符串

// 拼接章节绑定参数
foreach($selectedChapters as $chap) {
    $bindParams[] = $chap;
    $bindTypes .= 'i';
}

// 拼接分值抽题数判断逻辑
foreach($markConfig as $mark => $limit) {
    $casePart .= " WHEN {$mark} THEN {$limit} ";
}

$query = <<<SQL
INSERT INTO tbltemp (qchapter, qtype, qmarks, qsession, qlanguage, qclass, question, id, rkey)
SELECT qchapter, qtype, qmarks, qsession, qlanguage, qclass, question, id, ?
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY qmarks ORDER BY RAND()) AS rn
    FROM questions
    WHERE qchapter IN ($chapterPlaceholders)
      AND qmarks IN ($markList)
) t
WHERE rn <= CASE qmarks $casePart END
SQL;

// 执行查询
$stmt = $mysqli->prepare($query);
$stmt->bind_param($bindTypes, ...$bindParams);
$stmt->execute();
if ($mysqli->errno) {
    printf("执行失败: %s<br />", $mysqli->error);
}
$mysqli->close();
?>

方案2：兼容MySQL 5.x 版本（UNION ALL 拼接实现） #

如果你的MySQL版本不支持窗口函数，可以把每个分值的查询单独写，用UNION ALL拼接，每个子查询单独加LIMIT即可。

核心代码片段 #

<?php
// 前面的参数定义、合法性校验和方案1完全一致
$selectArr = [];
$bindParams = [];
$bindTypes = '';

foreach($markConfig as $mark => $limit) {
    $chapterPlaceholders = implode(',', array_fill(0, count($selectedChapters), '?'));
    // 每个分值单独生成查询子句
    $selectArr[] = "(SELECT qchapter, qtype, qmarks, qsession, qlanguage, qclass, question, id, ? 
                     FROM questions 
                     WHERE qchapter IN ($chapterPlaceholders) AND qmarks = ? 
                     ORDER BY RAND() LIMIT $limit)";
    // 绑定当前子查询的参数：rkey + 所有章节 + 当前分值
    $bindParams[] = $rkey;
    $bindTypes .= 's';
    foreach($selectedChapters as $chap) {
        $bindParams[] = $chap;
        $bindTypes .= 'i';
    }
    $bindParams[] = $mark;
    $bindTypes .= 'i';
}

// 拼接最终插入SQL
$query = "INSERT INTO tbltemp (qchapter, qtype, qmarks, qsession, qlanguage, qclass, question, id, rkey) " . implode(' UNION ALL ', $selectArr);
// 后续执行逻辑和方案1完全一致
$stmt = $mysqli->prepare($query);
$stmt->bind_param($bindTypes, ...$bindParams);
$stmt->execute();
?>

注意事项 #

• ORDER BY RAND()在题目量超过10万条时性能会有明显损耗，如果数据量较大建议优化随机抽题逻辑
• 可以提前校验对应分值+章节组合的题库总量是否大于等于要抽取的数量，不足时给用户友好提示

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