MySQL函数查询目录树问题记录

合集 - 奇形怪状的问题合集(2)

1.SQL 复杂查询更新插入2024-05-14

2.MySQL函数查询目录树问题记录2024-05-27

收起

DELIMITER //  
  
CREATE FUNCTION `getChildXzqhList`(rootId BIGINT) RETURNS VARCHAR(4000)  
BEGIN  
    SET SESSION group_concat_max_len = 1000000;  -- 设置为1MB  设置 GROUP_CONCAT 函数输出的最大长度大小，太小的话整体会被截掉
      
    RETURN (  
        WITH RECURSIVE child_nodes AS (  
            SELECT id  
            FROM xzqh_info  
            WHERE id = rootId  
            UNION ALL  
            SELECT xz.id  
            FROM xzqh_info xz  
            JOIN child_nodes cn ON xz.pid = cn.id  
        )  
        SELECT GROUP_CONCAT(id ORDER BY id SEPARATOR ',') AS ids  
        FROM child_nodes  
        LIMIT 1  
    );  
END //  
  
DELIMITER ;

整体数据表的话是一张行政区域的索引表，整体呈现树状结构分布。通过定义的MySQL 函数的方式实现对复杂操作实现的封装。

CREATE TABLE `xzqh_info` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `pid` bigint DEFAULT NULL,
  `rank` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `short_name` varchar(255) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  `child` text,
  `create_at` datetime DEFAULT NULL,
  `update_at` datetime DEFAULT NULL,
  `delete_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_id_pid` (`id`,`pid`) USING BTREE,
  KEY `xzqh_btree` (`pid`,`id`,`name`,`rank`) COMMENT '行政区域表的索引'
) ENGINE=InnoDB AUTO_INCREMENT=999901 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='行政区划';