MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t(id INT, parent_id INT)
INSERT INTO t(id, parent_id) VALUES (1, NULL),(2,1),(3,2), (1,3)

Records: 4  Duplicates: 0  Warnings: 0


SELECT * FROM t


id
parent_id


1
null

2
1

3
2

1
3


WITH RECURSIVE cte AS (
  SELECT id, parent_id, 0 lvl 
  FROM t WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.parent_id, lvl + 1 AS lvl
  FROM cte c1
  JOIN t ON c1.id = t.parent_id
)
CYCLE id, parent_id RESTRICT 
SELECT * FROM cte ORDER BY lvl;


id
parent_id
lvl


1
null
0

2
1
1

3
2
2

1
3
3

id	parent_id
1	null
2	1
3	2
1	3