I can't build recursive queries in MySQL 5+

Question

I'm using MySQL 5.7 and have a few problems formulating a recursive query. This is my table:

> show columns from test;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| belong | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

> select * from test;

+----+--------+--------+
| id | name   | belong |
+----+--------+--------+
|  1 | value1 | 0      |
|  2 | value2 | 1      |
|  3 | value3 | 2      |
|  4 | value4 | 0      |
|  5 | value5 | 1,4    |
+----+--------+--------+

I would now like to select all records in whose belong field the ID 1 occurs and of course all records that are children of 1. In the example above, all records except the first.

As I have read, recursive queries are only possible from MySQL 8 onwards. So that's no use to me. However, I have also read that you can "simulate" recursion with subqueries and inline variables. But I have to admit that I can't do it and need a little help.

Your version does not support recursive **queries**. But you may use recursive stored procedures. — Akina, Sep 07 '20 at 10:24
your first problem starts with comma seperated column data, that is not good see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad next you can also check http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — nbk, Sep 07 '20 at 10:40
record with id=4 belongs to `0`, why do you want to include that? (same question for record with id=5) — Luuk, Sep 07 '20 at 10:47
MySQL 5.x does not support recursive queries. You need to upgrade to MySQL 8.x to use recursive CTEs. — The Impaler, Sep 07 '20 at 12:11

marcothesane · Answer 1 · 2020-09-07T12:31:51.163

If record with id = 4, as in your example, has a belong of 0, then it should be all records except the first and the fourth.

I improved the design to reflect atomic values instead of a comma separated list of values.

So id 5 occurs once with a belong of 1, and once with a belong of 4. The database performance will thank you for that.

Had forgotten that MySQL in older versions does not even support the plain WITH clause. Then it gets slightly more complicated. And with many levels you had better write yourself a generator , in perl or python for example ...

CREATE TABLE test ( id, nm, belong) AS (
          SELECT 1 ,'value1', 0
UNION ALL SELECT 2 ,'value2', 1
UNION ALL SELECT 3 ,'value3', 2
UNION ALL SELECT 4 ,'value4', 0
UNION ALL SELECT 5 ,'value5', 1
UNION ALL SELECT 5 ,'value5', 4
)
;

One way is to expand all CTE expressions wherever you'd otherwise use them:

SELECT * FROM (
  SELECT
    0 AS lvl
  , *
  FROM test
  WHERE belong=1
) AS lvl0
UNION ALL SELECT * FROM (
  SELECT
    1 AS lvl
  , test.*
  FROM test
  JOIN (
    SELECT
      0 AS lvl
    , *
    FROM test
    WHERE belong=1
  ) AS lvl0 ON test.belong=lvl0.id
) AS lvl1
UNION ALL SELECT * FROM (
  SELECT
    2 AS lvl
  , test.*
  FROM test
  JOIN (
    SELECT
      1 AS lvl
    , test.*
    FROM test
    JOIN (
      SELECT
        0 AS lvl
      , *
      FROM test
      WHERE belong=1
    ) AS lvl0 ON test.belong=lvl0.id
  ) AS lvl1 ON test.belong=lvl1.id
) AS lvl2
ORDER BY 1,2
;
-- out Time: First fetch (0 rows): 28.938 ms. All rows formatted: 28.946 ms
-- out Time: First fetch (0 rows): 27.098 ms. All rows formatted: 27.100 ms
-- out  lvl | id |   nm   | belong 
-- out -----+----+--------+--------
-- out    0 |  2 | value2 |      1
-- out    0 |  5 | value5 |      1
-- out    1 |  3 | value3 |      2

Another way is to UNION SELECT join-to-join-to-join , etc several times..

SELECT
    0 AS lvl
  , *
  FROM test
  WHERE belong=1
UNION ALL SELECT
    1 AS lvl
  , lvl1.*
  FROM test AS lvl1 
  JOIN test AS lvl0 ON lvl1.belong=lvl0.id AND lvl0.belong=1
UNION ALL SELECT
    2 AS lvl
  , lvl2.*
  FROM test AS lvl2
  JOIN test AS lvl1 ON lvl2.belong=lvl1.id 
  JOIN test AS lvl0 ON lvl1.belong=lvl0.id AND lvl0.belong=1
ORDER BY 1,2
;
-- out  lvl | id |   nm   | belong 
-- out -----+----+--------+--------
-- out    0 |  2 | value2 |      1
-- out    0 |  5 | value5 |      1
-- out    1 |  3 | value3 |      2

http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/ — Luuk, Sep 07 '20 at 10:53

I can't build recursive queries in MySQL 5+

1 Answers1