clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805426 fiddles created (40853 in the last week).

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
 hidden batch(es)


CREATE TABLE tags (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, tag VARCHAR(16) UNIQUE);
 hidden batch(es)


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
 hidden batch(es)


SELECT * FROM tags ORDER BY id;
id tag
1 a
2 b
3 e
4 i
5 f
6 c
7 d
8 g
 hidden batch(es)


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) );
 hidden batch(es)


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);
 hidden batch(es)


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
 hidden batch(es)