MySQL 8.0

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

select version();


version()


8.0.27


CREATE TABLE test 
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  next_id INTEGER NOT NULL
);
INSERT INTO test (id,   next_id) VALUES
(1, 3),
(2, 3),
(3, 6),
(4, 5),
(5, 6),
(6, 8),
(7, 10),
(8, 9);
WITH RECURSIVE cte (id_, next_id_) AS
(
  SELECT id, next_id FROM test WHERE id = (SELECT MIN(id) FROM test)
  UNION ALL
  SELECT 
    c.next_id_, t.next_id
  FROM
    cte c
  JOIN test t
  ON c.next_id_ = t.id
)
SELECT * FROM cte;


id_
next_id_


1
3

3
6

6
8

8
9


SHOW VARIABLES LIKE 'cte_max_recursion_depth';


Variable_name
Value


cte_max_recursion_depth
1000


SET cte_max_recursion_depth = 1200;
SHOW VARIABLES LIKE 'cte_max_recursion_depth';


Variable_name
Value


cte_max_recursion_depth
1200