MySQL 8.0

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

CREATE TABLE tab
AS
SELECT 1 AS ID, NULL AS Parent
UNION ALL SELECT 2,1
UNION ALL SELECT 3,1
UNION ALL SELECT 4   ,  3                                
UNION ALL SELECT 5  ,   4                               
UNION ALL SELECT 6  ,   4                              
UNION ALL SELECT 7  ,   6 

Records: 7  Duplicates: 0  Warnings: 0


SELECT * FROM tab;


ID
Parent


1
null

2
1

3
1

4
3

5
4

6
4

7
6


WITH RECURSIVE cte AS (
   SELECT 1 AS lvl, Parent, id
   FROM tab
   WHERE Parent IS NULL
   UNION ALL
   SELECT lvl + 1, tab.Parent, tab.id
   FROM tab
   JOIN cte
     ON tab.Parent = cte.Id
)
SELECT *
FROM cte


lvl
Parent
id


1
null
1

2
1
2

2
1
3

3
3
4

4
4
5

4
4
6

5
6
7


WITH RECURSIVE cte AS (
   SELECT 1 AS lvl, Parent, id
   FROM tab
   WHERE Parent IS NULL
   UNION ALL
   SELECT lvl + 1, tab.Parent, tab.id
   FROM tab
   JOIN cte
     ON tab.Parent = cte.Id
)
SELECT MAX(lvl) AS max_depth_of_tree
FROM cte


 


max_depth_of_tree


5

ID	Parent
1	null
2	1
3	1
4	3
5	4
6	4
7	6