By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE posts(
post_id int unsigned NOT NULL AUTO_INCREMENT,
content varchar(200) NOT NULL,
PRIMARY KEY (post_id)
);
INSERT INTO posts VALUES
(1, 'Post 1'),
(2, 'Post 2');
CREATE TABLE hashtags(
hashtag_id int unsigned NOT NULL AUTO_INCREMENT,
hashtag varchar(40) NOT NULL,
PRIMARY KEY (hashtag_id)
);
INSERT INTO hashtags VALUES
(1, 'Hashtag 1'),
(2, 'Hashtag 2'),
(3, 'Hashtag 3'),
(4, 'Hashtag 4'),
(5, 'Hashtag 5'),
(6, 'Hashtag 6');
CREATE TABLE posts_hashtags_relation(
post_id int unsigned NOT NULL,
hashtag_id int unsigned NOT NULL,
PRIMARY KEY (post_id, hashtag_id)
);
INSERT INTO posts_hashtags_relation VALUES
(1,1), (1,2), (1,3),
(2,3), (2,4), (2,5);
SELECT * FROM posts;
SELECT * FROM hashtags;
SELECT * FROM posts_hashtags_relation;
Records: 2 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
| post_id | content |
|---|---|
| 1 | Post 1 |
| 2 | Post 2 |
| hashtag_id | hashtag |
|---|---|
| 1 | Hashtag 1 |
| 2 | Hashtag 2 |
| 3 | Hashtag 3 |
| 4 | Hashtag 4 |
| 5 | Hashtag 5 |
| 6 | Hashtag 6 |
| post_id | hashtag_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
CREATE PROCEDURE save_post_and_tags (post_content VARCHAR(200), hashtag_ids TEXT)
BEGIN
DECLARE post_id INT UNSIGNED;
DECLARE tag_id INT UNSIGNED;
INSERT INTO posts VALUES (DEFAULT, post_content);
SET post_id := LAST_INSERT_ID();
SET hashtag_ids := concat(hashtag_ids, ',');
REPEAT
SET tag_id := 0 + SUBSTRING_INDEX(hashtag_ids, ',', 1);
SET hashtag_ids := TRIM(SUBSTRING(hashtag_ids FROM 1 + LOCATE(',', hashtag_ids)));
INSERT INTO posts_hashtags_relation VALUES (post_id, tag_id);
UNTIL hashtag_ids = '' END REPEAT;
END
CALL save_post_and_tags ('Post No. 3', ' 1, 3 , 5 ');
SELECT * FROM posts;
SELECT * FROM hashtags;
SELECT * FROM posts_hashtags_relation;
| post_id | content |
|---|---|
| 1 | Post 1 |
| 2 | Post 2 |
| 3 | Post No. 3 |
| hashtag_id | hashtag |
|---|---|
| 1 | Hashtag 1 |
| 2 | Hashtag 2 |
| 3 | Hashtag 3 |
| 4 | Hashtag 4 |
| 5 | Hashtag 5 |
| 6 | Hashtag 6 |
| post_id | hashtag_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 3 | 1 |
| 3 | 3 |
| 3 | 5 |