mysql insert if not exists on joined tables

My tables look like this:

Table "posts"

posts.id = PK, auto-increment
posts.text = varchar
posts.tag_id 1 = int, FK to tags.id
posts.tag_id 2 = int, FK to tags.id
posts.tag_id 3 = int, FK to tags.id

Table "tags"

tags.id = PK, auto-increment
tags.tag = varchar

Now I want to insert the following data:

text: 'lorem ipsum'
tag1: 'music'
tag2: 'pop'
tag3: 'singer'

So I need a query that checks if tag1/tag2/tag3 already exist in "tags", inserts them otherwise and then insert those as foreign-keys + the "text" into a new row in "posts".

I had a look into mysql INSERT IF NOT EXISTS but I'm just stuck and don't know where to start. I know I could handle it with multiple queries but there has to be another, lighter way to achieve the same result.

Has anyone any experience with this?

Update

A friend of mine proposed something like this:

CREATE FUNCTION getTagID(tag VARCHAR('100')) RETURNS int

INSERT INTO posts (text,tag1,tag2,tag3) 
VALUES ('lorem ipsum', getTagID('music'), getTagID('pop'), getTagID('singer'));

Of course the implementation of getTagId is still missing, but does that make sense? getTagID should select the id with the given tag, if it doesn't exist, insert it and return this. Any help is appreciated.

Solution

I created a custom function in MySql:

DELIMITER ;;

CREATE FUNCTION getTagID(tag VARCHAR(100)) RETURNS BIGINT
BEGIN
  DECLARE tagID BIGINT;
  SET tagID = (SELECT id FROM tags WHERE text = tag);
  IF tagID IS NULL
  THEN
    INSERT INTO tags (text) VALUES (tag);
    SET tagID = (SELECT LAST_INSERT_ID());
  END IF;
  RETURN tagID;
END;;

DELIMITER ;

and now I can just insert into posts like that:

INSERT INTO posts (text,tag1,tag2,tag3) 
VALUES ('lorem ipsum', getTagID('music'), getTagID('pop'), getTagID('singer'));

with that function, which inserts into "tags" only if the tag does not yet exist and gives back the ID of the existing or newly created tag. Yipeee :)

You have to insert to posts, then insert to tags. There is no multi-table insert solution in SQL.

You can't even insert the post text to posts from a trigger on tags, because the insert to tags can only carry columns that belong to tags.

You can use INSERT IGNORE or REPLACE or INSERT ... ON DUPLICATE KEY UPDATE if you need to avoid duplicates in the tags table. See my answer to "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" for more details on that.

Re your comment.

You can get the generated PK only if you let the auto-increment mechanism generate a new id. If you let auto-increment happen, you're guaranteed not to result in a duplicate PK.

If you specify an id and bypass auto-increment, you can't get the id anyway. But you don't need to query for the value if you specified it in your INSERT -- you should have it already.

The only other case is if you have a secondary unique key that's not auto-increment.

CREATE TABLE foo (id int auto_increment primary key, name varchar(10), unique key (name));
INSERT INTO foo (name) VALUES ('bill');
SELECT LAST_INSERT_ID(); -- returns 1
INSERT INTO foo (name) VALUES ('bill');
SELECT LAST_INSERT_ID(); -- returns 1

This is slightly confusing because the last insert id comes from the last INSERT that succeeded and generated an id.

Re your update with INSERT INTO posts (text,tag1,tag2,tag3). That's called repeating groups. What if you need four tags? What if a post has only two tags? How do you make an indexed search for posts with a given tag?