MariaDB 10.6

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

-- Step 1: Create the table and insert data
CREATE TABLE your_table (
    id INT PRIMARY KEY,
    tags VARCHAR(255)
);

INSERT INTO your_table (id, tags) VALUES
(1, '1,2,3,-52,apple tree,banana fruit'),
(2, '4,5,pear,grape'),
(3, '6,7,8,9,77,88,9878,788,apple,kiwi'),
(4, '10,11,12,orange');

-- Create the view to split the comma-separated tags
CREATE VIEW separated_tags_view AS
WITH RECURSIVE SplitTags AS (
    SELECT
        id,
        SUBSTRING_INDEX(REPLACE(tags, ',', '|'), '|', 1) AS separated_tag,
        SUBSTRING(tags, LENGTH(SUBSTRING_INDEX(tags, ',', 1)) + 2) AS remaining_tags
    FROM
        your_table
    WHERE
        CHAR_LENGTH(tags) > 0
    UNION ALL
    SELECT
        id,
        SUBSTRING_INDEX(REPLACE(remaining_tags, ',', '|'), '|', 1) AS separated_tag,
        SUBSTRING(remaining_tags, LENGTH(SUBSTRING_INDEX(remaining_tags, ',', 1)) + 2) AS remaining_tags
    FROM
        SplitTags
    WHERE
        CHAR_LENGTH(remaining_tags) > 0
)
SELECT id, separated_tag
FROM SplitTags;

SELECT * FROM your_table;

-- Query the view
SELECT * FROM separated_tags_view ORDER BY id, separated_tag;

Records: 4  Duplicates: 0  Warnings: 0

id	tags
1	1,2,3,-52,apple tree,banana fruit
2	4,5,pear,grape
3	6,7,8,9,77,88,9878,788,apple,kiwi
4	10,11,12,orange

id	separated_tag
1	-52
1	1
1	2
1	3
1	apple tree
1	banana fruit
2	4
2	5
2	grape
2	pear
3	6
3	7
3	77
3	788
3	8
3	88
3	9
3	9878
3	apple
3	kiwi
4	10
4	11
4	12
4	orange