MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
id INT,
subOperation VARCHAR(50));

INSERT INTO mytable VALUES
(1,'test1'),
(2,'test2'),
(3,'test3'),
(4,'test1,test2'),
(5,'test1,test3');
SET @sql := NULL;
  
SET @sql :=  (SELECT CONCAT('SELECT ROW_NUMBER() OVER (ORDER BY id_suboperation, id_element) AS id, id_suboperation, id_element FROM (',GROUP_CONCAT(qv SEPARATOR ' union all '),' ) sq GROUP BY id_suboperation, id_element ORDER BY id_suboperation, id_element;')
FROM
(SELECT CONCAT("SELECT id AS id_suboperation, 
      SUBSTRING_INDEX(
          SUBSTRING_INDEX(
              REPLACE(subOperation,'test',''),',',",idx,"),',',-1) AS id_element
FROM mytable") qv FROM mytable a CROSS JOIN
(WITH RECURSIVE wordCount AS (
SELECT 1 IDX, 
       (LENGTH(subOperation)-LENGTH(REPLACE(subOperation,',','')))+1 AS MAXCOUNT 
  FROM mytable UNION ALL
SELECT IDX+1, MAXCOUNT FROM wordCount WHERE IDX+1 <= MAXCOUNT)
SELECT IDX FROM wordCount
GROUP BY IDX) b
GROUP BY qv) c);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


id
id_suboperation
id_element


1
1
1

2
2
2

3
3
3

4
4
1

5
4
2

6
5
1

7
5
3


INSERT INTO mytable VALUES
(6,'test2,test4,test5'),
(7,'test3,test5'),
(8,'test3');
SET @sql := NULL;
  
SET @sql :=  (SELECT CONCAT('SELECT ROW_NUMBER() OVER (ORDER BY id_suboperation, id_element) AS id, id_suboperation, id_element FROM (',GROUP_CONCAT(qv SEPARATOR ' union all '),' ) sq GROUP BY id_suboperation, id_element ORDER BY id_suboperation, id_element;')
FROM
(SELECT CONCAT("SELECT id AS id_suboperation, 
      SUBSTRING_INDEX(
          SUBSTRING_INDEX(
              REPLACE(subOperation,'test',''),',',",idx,"),',',-1) AS id_element
FROM mytable") qv FROM mytable a CROSS JOIN
(WITH RECURSIVE wordCount AS (
SELECT 1 IDX, 
       (LENGTH(subOperation)-LENGTH(REPLACE(subOperation,',','')))+1 AS MAXCOUNT 
  FROM mytable UNION ALL
SELECT IDX+1, MAXCOUNT FROM wordCount WHERE IDX+1 <= MAXCOUNT)
SELECT IDX FROM wordCount
GROUP BY IDX) b
GROUP BY qv) c);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


id
id_suboperation
id_element


1
1
1

2
2
2

3
3
3

4
4
1

5
4
2

6
5
1

7
5
3

8
6
2

9
6
4

10
6
5

11
7
3

12
7
5

13
8
3