sql - 列出所有祖先路径，LIKE vs rcte

Question

DROP TABLE IF EXISTS t;

CREATE TABLE t(
    mypath varchar(100),
    parent_path varchar(100)
);

INSERT INTO t VALUES ('a', NULL),('a/b', 'a'),('a/b/c', 'a/b');

-- 列出所有父路径

1) 使用 LIKE，不使用 parent_path 列：

SELECT a.mypath, b.mypath aS parent_path
FROM t a
JOIN t b ON a.mypath LIKE b.mypath + '%' AND a.mypath != b.mypath

2) 使用递归 cte，利用 parent_path 列

WITH cte AS (
    SELECT mypath, parent_path
    FROM t

    UNION ALL

    SELECT a.mypath, b.parent_path
    FROM cte a
    JOIN t b ON a.parent_path = b.mypath
)
SELECT * FROM cte WHERE parent_path IS NOT NULL;

在大型数据集上，每种方法的优缺点是什么，性能方面？我认为 rcte 方法应该更快吗？LIKE 是否应该能够使用索引，因为只有一个尾随通配符？

标签： sqlsql-serversql-likerecursive-query