MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE phrases (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, csv TEXT);
INSERT INTO phrases (csv) VALUES 
('a,b,c,d,e,f'),
('a,c,e,g,i'),
('b,c,d,e,f');
SELECT * FROM phrases;


id
csv


1
a,b,c,d,e,f

2
a,c,e,g,i

3
b,c,d,e,f


CREATE TABLE tags (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, tag VARCHAR(16) UNIQUE);
INSERT IGNORE INTO tags (tag)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(phrases.csv, ',', numbers1.num * 4 + numbers2.num + 1), ',', -1)
FROM phrases
CROSS JOIN ( SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) numbers1
CROSS JOIN ( SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) numbers2
SELECT * 
FROM tags
ORDER BY id;


id
tag


1
a

2
b

3
e

4
i

5
f

6
c

7
d

8
g


CREATE TABLE phrases_to_tag (p_id BIGINT UNSIGNED NOT NULL, 
                             FOREIGN KEY (p_id) REFERENCES phrases(id),
                             t_id BIGINT UNSIGNED NOT NULL, 
                             FOREIGN KEY (t_id) REFERENCES tags(id) );
INSERT INTO phrases_to_tag (p_id, t_id)
SELECT phrases.id, tags.id
FROM phrases
JOIN tags ON FIND_IN_SET(tags.tag, phrases.csv);
SELECT * 
FROM phrases_to_tag
ORDER BY p_id, t_id;


p_id
t_id


1
1

1
2

1
3

1
5

1
6

1
7

2
1

2
3

2
4

2
6

2
8

3
2

3
3

3
5

3
6

3
7