MySQL 8.0

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

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  path VARCHAR(1024)
)
SELECT '1' path UNION ALL
SELECT '1.A' UNION ALL
SELECT '1.A.1' UNION ALL
SELECT '1.A.1.a' UNION ALL
SELECT '1.A.1.b' UNION ALL
SELECT '1.A.2' UNION ALL
SELECT '1.A.2.a' UNION ALL
SELECT '1.B.1' UNION ALL
SELECT '1.B.1.a' UNION ALL
SELECT '2' UNION ALL
SELECT '2.A';
SELECT * FROM test;

Records: 11  Duplicates: 0  Warnings: 0




id
path


1
1

2
1.A

3
1.A.1

4
1.A.1.a

5
1.A.1.b

6
1.A.2

7
1.A.2.a

8
1.B.1

9
1.B.1.a

10
2

11
2.A


ALTER TABLE test
ADD COLUMN parent_id INT,
ADD COLUMN node_name VARCHAR(128),
ADD FOREIGN KEY (parent_id) REFERENCES test (id);
SHOW CREATE TABLE test;

Records: 11  Duplicates: 0  Warnings: 0




Table
Create Table


test
CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `path` varchar(1024) DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  `node_name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `test_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `test` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


WITH cte AS (
  SELECT t1.id, MAX(t2.path) parent_path
  FROM test t1
  JOIN test t2 ON t1.path LIKE CONCAT(t2.path, '.%')
  GROUP BY t1.id
)
SELECT t1.id, t1.path,
       cte.parent_path,
       TRIM(LEADING '.' FROM SUBSTRING(t1.path FROM 1 + LENGTH(cte.parent_path))) node_name,
       t2.id parent_id
FROM test t1
JOIN cte ON t1.id = cte.id
JOIN test t2 ON cte.parent_path = t2.path;


id
path
parent_path
node_name
parent_id


8
1.B.1
1
B.1
1

2
1.A
1
A
1

6
1.A.2
1.A
2
2

3
1.A.1
1.A
1
2

5
1.A.1.b
1.A.1
b
3

4
1.A.1.a
1.A.1
a
3

7
1.A.2.a
1.A.2
a
6

9
1.B.1.a
1.B.1
a
8

11
2.A
2
A
10


WITH cte AS (
  SELECT t1.id, MAX(t2.path) parent_path
  FROM test t1
  JOIN test t2 ON t1.path LIKE CONCAT(t2.path, '.%')
  GROUP BY t1.id
)
UPDATE test t1
JOIN cte ON t1.id = cte.id
JOIN test t2 ON cte.parent_path = t2.path
SET t1.parent_id = t2.id,
    t1.node_name = TRIM(LEADING '.' FROM SUBSTRING(t1.path FROM 1 + LENGTH(cte.parent_path)));
SELECT * FROM test;

Rows matched: 9  Changed: 9  Warnings: 0




id
path
parent_id
node_name


1
1
null
null

2
1.A
1
A

3
1.A.1
2
1

4
1.A.1.a
3
a

5
1.A.1.b
3
b

6
1.A.2
2
2

7
1.A.2.a
6
a

8
1.B.1
1
B.1

9
1.B.1.a
8
a

10
2
null
null

11
2.A
10
A

id	path
1	1
2	1.A
3	1.A.1
4	1.A.1.a
5	1.A.1.b
6	1.A.2
7	1.A.2.a
8	1.B.1
9	1.B.1.a
10	2
11	2.A

id	path	parent_id	node_name
1	1	null	null
2	1.A	1	A
3	1.A.1	2	1
4	1.A.1.a	3	a
5	1.A.1.b	3	b
6	1.A.2	2	2
7	1.A.2.a	6	a
8	1.B.1	1	B.1
9	1.B.1.a	8	a
10	2	null	null
11	2.A	10	A