MySQL函式查詢目錄樹問題記錄

信念是基石發表於2024-05-27
MySql函式
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='行政區劃';

相關文章