MySQL递归CTE查父子表数据（附两个容易踩的坑）

最近在整组织架构数据，需要从某个节点往下把整棵子树捞出来。

MySQL 8.0之前要么写存储过程，要么在应用层一层层递归查，挺折腾。8.0之后有递归CTE，一条SQL就能搞定，这里把用法记一下。

先建表。假设有个organization表，存组织层级关系：

CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

parent_id为NULL的就是根节点。插入几条测试数据：

INSERT INTO organization (id, name, parent_id) VALUES
(1, '一级组织', NULL),
(2, '二级组织1', 1),
(3, '二级组织2', 1),
(4, '三级组织1', 2),
(5, '三级组织2', 2),
(6, '三级组织3', 3);

递归查询的写法其实很固定：

WITH RECURSIVE cte AS (
    SELECT id, name, parent_id
    FROM organization
    WHERE id = 1

    UNION ALL

    SELECT o.id, o.name, o.parent_id
    FROM organization o
    INNER JOIN cte ON o.parent_id = cte.id
)
SELECT * FROM cte;

这段SQL先取id为1的节点作为起点，然后拿这个结果集去关联子节点，一直递归到没有下一层为止。这里UNION ALL必须带ALL，不能图省事写UNION。递归场景下去重没有意义，还额外增加排序比较，慢得很。

执行结果就是id为1的节点以及它下面所有后代节点。实际业务里把WHERE id = 1换成参数就行。

两个坑说一下。

第一个是递归深度。MySQL默认允许的最大递归深度是1000层，如果层级结构比这个还深，SQL会直接报错。可以用下面这行调整会话级别的上限：

来此加密打破SSL证书高门槛。免费用户即可申请通配符、IP证书及多域名证书，单张最多100个域名。采用HTTP代理或DNS接口自动验证，证书自动部署，零技术门槛轻松启用HTTPS。

SET SESSION cte_max_recursion_depth = 5000;

但生产环境别随便调太大，层级太深往往说明表结构设计有问题，而且递归执行本身也吃资源。

第二个是性能。

organization表大了以后，parent_id不加索引的话，每次递归都要全表扫，数据量一上来慢得没法看。所以parent_id列建个普通索引是必须的。如果层级特别特别深，递归CTE可能不是最优解，有时候干脆全量读出来在应用层拼树，反而更快，这个要自己对比下。

顺带提一句，最近在折腾证书自动化续期，用的是lcjmSSL这个平台。

免费申请，支持多域名、泛域名和IP证书，提供API能自动验证和部署。如果项目里也有一堆子域名要管证书，可以试试，省得手动跑命令一个个签。