如何将多组好友状态查询合并为单次MySQL查询或存储过程？

现有一个可正常运行的函数，用于通过用户ID检查两位用户的好友状态：

function checkFriendShipBetweenUsers(user1Id, user2Id) {
    var checkFriendShipBetweenUsersQuery = "SELECT status FROM friends WHERE (user1Id=? AND user2Id =?) OR (user1Id=? AND user2Id =?)"
    var queryParameterList = [user1Id, user2Id, user2Id, user1Id]
}

现在需要实现一个新函数，通过单次数据库调用完成一位用户与另外三位用户的好友状态检查，返回对应三位用户的好友状态（3个整数）。待实现函数框架如下：

function checkFriendShipBetweenUsers(user1Id, userIdList) {
    var checkFriendShipBetweenUsersQuery = ""
    var queryParameterList = []
}

给定参数示例：

• user1Id: 'a8t57h6p8n2efden'
• userIdList: ['typ3vg6xb1vt7nw2', 'cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl', '0bw87kprb97pes1crom8ceodi07r2kd0']

解决方案 #

方案1：单条SQL查询（无存储过程依赖） #

通过构造临时目标用户列表，结合左连接和条件聚合，一次性返回三位用户的好友状态。

函数实现

function checkFriendShipBetweenUsers(user1Id, userIdList) {
    // 自定义无好友关系时的默认状态值（示例为0，可根据业务调整）
    const defaultStatus = 0;

    var checkFriendShipBetweenUsersQuery = `
        SELECT
            MAX(CASE WHEN idx = 1 THEN COALESCE(f.status, ${defaultStatus}) ELSE ${defaultStatus} END) AS status1,
            MAX(CASE WHEN idx = 2 THEN COALESCE(f.status, ${defaultStatus}) ELSE ${defaultStatus} END) AS status2,
            MAX(CASE WHEN idx = 3 THEN COALESCE(f.status, ${defaultStatus}) ELSE ${defaultStatus} END) AS status3
        FROM (
            SELECT 1 AS idx, ? AS target_id UNION ALL
            SELECT 2 AS idx, ? UNION ALL
            SELECT 3 AS idx, ?
        ) AS targets
        LEFT JOIN friends f 
            ON (f.user1Id = ? AND f.user2Id = targets.target_id) 
            OR (f.user1Id = targets.target_id AND f.user2Id = ?);
    `;

    var queryParameterList = [
        userIdList[0],
        userIdList[1],
        userIdList[2],
        user1Id,
        user1Id
    ];

    // 此处添加执行查询并返回结果的逻辑（根据你的数据库驱动调整）
    // 例如：return db.execute(checkFriendShipBetweenUsersQuery, queryParameterList);
}

逻辑说明

1. 用UNION ALL构造包含目标用户ID和顺序索引的临时表，保证返回结果的顺序与userIdList一致；
2. 通过LEFT JOIN匹配双向好友关系（user1Id与目标用户的两种组合）；
3. 用COALESCE处理无好友关系的场景，返回默认状态值；
4. 用MAX(CASE...)将每个目标用户的状态提取为单独的列，最终返回一行包含三个状态的结果。

方案2：MySQL存储过程 #

若有权限创建存储过程，可封装逻辑为存储过程，通过调用存储过程获取结果。

创建存储过程

DELIMITER //
CREATE PROCEDURE CheckMultiFriendShip(
    IN p_user1Id VARCHAR(50),
    IN p_targetId1 VARCHAR(50),
    IN p_targetId2 VARCHAR(50),
    IN p_targetId3 VARCHAR(50),
    OUT p_status1 INT,
    OUT p_status2 INT,
    OUT p_status3 INT
)
BEGIN
    -- 定义默认状态值
    SET @default_status = 0;

    -- 查询第一个目标用户的好友状态
    SELECT COALESCE(status, @default_status) INTO p_status1
    FROM friends
    WHERE (user1Id = p_user1Id AND user2Id = p_targetId1) 
       OR (user1Id = p_targetId1 AND user2Id = p_user1Id)
    LIMIT 1;

    -- 查询第二个目标用户的好友状态
    SELECT COALESCE(status, @default_status) INTO p_status2
    FROM friends
    WHERE (user1Id = p_user1Id AND user2Id = p_targetId2) 
       OR (user1Id = p_targetId2 AND user2Id = p_user1Id)
    LIMIT 1;

    -- 查询第三个目标用户的好友状态
    SELECT COALESCE(status, @default_status) INTO p_status3
    FROM friends
    WHERE (user1Id = p_user1Id AND user2Id = p_targetId3) 
       OR (user1Id = p_targetId3 AND user2Id = p_user1Id)
    LIMIT 1;
END //
DELIMITER ;

函数调用实现

function checkFriendShipBetweenUsers(user1Id, userIdList) {
    var checkFriendShipBetweenUsersQuery = `
        CALL CheckMultiFriendShip(?, ?, ?, ?, @status1, @status2, @status3);
        SELECT @status1 AS status1, @status2 AS status2, @status3 AS status3;
    `;

    var queryParameterList = [
        user1Id,
        userIdList[0],
        userIdList[1],
        userIdList[2]
    ];

    // 此处添加执行查询并返回结果的逻辑（根据你的数据库驱动调整）
    // 例如：return db.execute(checkFriendShipBetweenUsersQuery, queryParameterList);
}

逻辑说明

存储过程内部分别查询三位目标用户的好友状态，通过输出参数返回结果；函数调用时先执行存储过程，再查询输出参数的值，实现单次调用获取所有状态。

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