基于条件从关联表随机选取parent_id并生成匹配的MySQL/PHP实现问询

逻辑说明 #

通过原子化的存储过程完成两次选取与更新操作，优先选取entry表中early_finish=1的关联记录，无符合条件时再选early_finish=0的；第二次选取需排除第一条记录的entry_group，同时通过行锁避免并发冲突。

存储过程代码 #

DELIMITER //

CREATE PROCEDURE GenerateMatch()
BEGIN
    DECLARE first_parent_id INT;
    DECLARE first_entry_group VARCHAR(255); -- 根据实际字段类型调整

    -- 选取第一个parent_id并锁定记录
    SELECT e.id INTO first_parent_id
    FROM entry e
    JOIN entries en ON e.id = en.parent_id
    WHERE e.fought = 0 -- 假设未匹配的fought值为0，匹配后设为1
    ORDER BY e.early_finish DESC, RAND()
    LIMIT 1 FOR UPDATE;

    -- 获取第一条记录的分组信息
    SELECT entry_group INTO first_entry_group FROM entry WHERE id = first_parent_id;

    -- 更新第一条记录的fought状态
    UPDATE entry SET fought = 1 WHERE id = first_parent_id;

    -- 选取第二个parent_id，排除第一条的分组并锁定
    SELECT e.id INTO @second_parent_id
    FROM entry e
    JOIN entries en ON e.id = en.parent_id
    WHERE e.fought = 0
      AND e.entry_group != first_entry_group
    ORDER BY e.early_finish DESC, RAND()
    LIMIT 1 FOR UPDATE;

    -- 更新第二条记录的fought状态
    UPDATE entry SET fought = 1 WHERE id = @second_parent_id;

    -- 返回匹配结果
    SELECT first_parent_id AS first_id, first_entry_group AS first_group, @second_parent_id AS second_id;
END //

DELIMITER ;

使用方式 #

直接调用存储过程即可生成一组匹配：

CALL GenerateMatch();

基于PDO实现事务化操作，保证两次选取与更新的原子性，逻辑与存储过程一致。

代码示例 #

<?php
$dsn = 'mysql:host=localhost;dbname=your_db;charset=utf8mb4';
$username = 'your_user';
$password = 'your_pwd';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction();

    // 选取第一条匹配记录
    $stmt = $pdo->prepare("
        SELECT e.id, e.entry_group
        FROM entry e
        JOIN entries en ON e.id = en.parent_id
        WHERE e.fought = 0
        ORDER BY e.early_finish DESC, RAND()
        LIMIT 1 FOR UPDATE
    ");
    $stmt->execute();
    $first = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$first) throw new Exception("无可用的第一条匹配记录");

    // 更新第一条记录状态
    $stmt = $pdo->prepare("UPDATE entry SET fought = 1 WHERE id = ?");
    $stmt->execute([$first['id']]);

    // 选取第二条匹配记录（排除第一条的分组）
    $stmt = $pdo->prepare("
        SELECT e.id
        FROM entry e
        JOIN entries en ON e.id = en.parent_id
        WHERE e.fought = 0
          AND e.entry_group != ?
        ORDER BY e.early_finish DESC, RAND()
        LIMIT 1 FOR UPDATE
    ");
    $stmt->execute([$first['entry_group']]);
    $second = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$second) throw new Exception("无符合条件的第二条匹配记录");

    // 更新第二条记录状态
    $stmt = $pdo->prepare("UPDATE entry SET fought = 1 WHERE id = ?");
    $stmt->execute([$second['id']]);

    $pdo->commit();
    echo "匹配生成成功：\n第一条ID：{$first['id']}，分组：{$first['entry_group']}\n第二条ID：{$second['id']}";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "生成失败：" . $e->getMessage();
}
?>

注意事项 #

• 确保entry表的fought字段初始值为0，匹配完成后设为1，避免重复选取。
• FOR UPDATE行锁用于防止多进程并发时的重复选取问题。
• 根据实际表结构调整字段类型（如entry_group为整数时，去掉字符串相关处理）。

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