MySQL5.7+Codeigniter3查询指定sponsor_hash下全层级子用户实现方案

基于Codeigniter 3开发，受限于MySQL 5.7版本不支持高版本递归查询特性，需要实现查询指定sponsor_hash下所有无限层级子user_hash，并按层级分组返回数组：array[0]存1级子用户、array[1]存2级子用户，以此类推。

表包含Id、user_hash、sponsor_hash三个字段，示例数据如下：

示例返回结果（根sponsor_hash为v90349dlksdfgbjdfjksdjhsdzadisxb34237sda时）：

array[0] = array(
   'sdf320380sdjnsd0wq0sdfuwe038we08d8sca',
   'sjhsad78w3s78sdag3e97scjhww8a7sd8df0d'
);
array[1] = array(
   'jsdfhsdsdfh42333489dsdhsd9wehsd80shsd',
   'vusdfjsdiflsdjldfudf90df0h76sd79w34ns4334'
);

1. MySQL变量查询：仅能返回第一层子用户，代码如下：

select  id,
        user_hash,
        sponsor_hash
from    (select * from table
         order by sponsor_hash, id) table_sorted,
        (select @pv := 'v90349dlksdfgbjdfjksdjhsdzadisxb34237sda') initialisation
where   find_in_set(sponsor_hash, @pv)
and     length(@pv := concat(@pv, ',', user_hash))

2. PHP递归循环查库：可返回全量层级数据，但多次查询数据库效率低，且返回嵌套sub结构不符合按层级分组要求，代码如下：

$generations = $this->db->select('user_hash')->where('sponsor_hash', $user_hash)->get('generations')->result();

foreach ($generations as $k=>$v) {
            $generations[$k]->sub = $this->generations($v->user_hash);
        }

function generations($ids) {
    $generations = $this->db->select('u.ids')->where('sponsor_hash', $ids)->get('generations')->result();

    foreach ($generations as $k=>$v) {
        $generations[$k]->sub = $this->generations($v->user_hash);
    }
    return $generations;
}

方案1：PHP单次查库+内存分层（推荐） #

仅需要1次数据库查询，全部逻辑在内存中完成，性能最高，适配无限层级：

function getLayeredUsers($root_sponsor_hash) {
    // 一次性查询全表的关联关系，数据量过大时可加条件过滤不需要的数据
    $all_users = $this->db->select('user_hash, sponsor_hash')
        ->get('generations')
        ->result_array();
    
    // 先把所有用户按sponsor_hash分组，方便后续快速查找
    $sponsor_map = [];
    foreach ($all_users as $user) {
        $sponsor_map[$user['sponsor_hash']][] = $user['user_hash'];
    }

    $result = [];
    $current_level_hashes = [$root_sponsor_hash];
    $level = 0;

    while (!empty($current_level_hashes)) {
        $next_level_hashes = [];
        foreach ($current_level_hashes as $hash) {
            if (isset($sponsor_map[$hash])) {
                $next_level_hashes = array_merge($next_level_hashes, $sponsor_map[$hash]);
            }
        }
        if (!empty($next_level_hashes)) {
            $result[$level] = $next_level_hashes;
            $level++;
        }
        $current_level_hashes = $next_level_hashes;
    }

    return $result;
}

调用方式：直接传入根sponsor_hash即可返回符合要求的分层数组。

方案2：MySQL存储过程 #

适合希望逻辑下沉到数据库层的场景：

DELIMITER //
CREATE PROCEDURE GetLayeredChildren(IN root_sponsor VARCHAR(255))
BEGIN
    DECLARE current_level INT DEFAULT 0;
    DROP TEMPORARY TABLE IF EXISTS level_users;
    DROP TEMPORARY TABLE IF EXISTS result;
    CREATE TEMPORARY TABLE level_users(user_hash VARCHAR(255), level INT);
    CREATE TEMPORARY TABLE result(user_hash VARCHAR(255), level INT);
    
    -- 插入第一级子用户
    INSERT INTO level_users SELECT user_hash, current_level FROM generations WHERE sponsor_hash = root_sponsor;
    INSERT INTO result SELECT * FROM level_users;
    
    WHILE ROW_COUNT() > 0 DO
        SET current_level = current_level + 1;
        -- 查下一级子用户
        DELETE FROM level_users;
        INSERT INTO level_users 
            SELECT g.user_hash, current_level 
            FROM generations g
            INNER JOIN result r ON g.sponsor_hash = r.user_hash
            WHERE r.level = current_level - 1;
        INSERT INTO result SELECT * FROM level_users;
    END WHILE;
    
    -- 返回分层结果，PHP端可按level字段分组即可
    SELECT * FROM result;
END //
DELIMITER ;

调用方式：执行CALL GetLayeredChildren('根sponsor_hash')，拿到结果后在PHP中按level字段分组即可得到要求的数组结构。

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