Mysql nested insert for tagging

Question

I would like to add a tag to a blog post with a single sql statement.

let's say my tables would look like this:

tags
+-------+-----------+
| tagid | tag       |
+-------+-----------+
|     1 | news      | 
|     2 | top-story | 
+-------+-----------+

tag2post
+----+--------+-------+
| id | postid | tagid |     
+----+--------+-------+
|  0 |    322 |     1 |
+----+--------+-------+

the problem I would like to solve is to insert a new tag , get its id , and then insert this new identifier into the relationship table . > in one sql statement.

INSERT INTO tag2post (postid, tagid)
VALUES
(
    332, # the post
    IF (
        (SELECT tagid FROM tags WHERE tag = 'new_tag'),
        (SELECT tagid FROM tags WHERE tag = 'new_tag'),
         # here is where i'd like to insert 
         # the new_tag and return it id
        'i am lost here'
    )
)

Answer 1

You cannot do this as a single insertion, because the inserts are atomic, that is, the identifier is not defined until the instruction is complete.

Wrap both operators in a transaction, and you will get your identifier and atomicity.

Answer 2

GUID. GUID .

, 32- char .

, , MD5 .

Answer 3

, . - UNIQUE tag :

INSERT IGNORE INTO tags (tag) VALUE ('new_tag')

INSERT INTO tag2post (postid, tagid)
VALUES
(
    332, # the post
    (SELECT tagid FROM tags WHERE tag = 'new_tag')
)

,

INSERT IGNORE . , SELECT, , INSERT, , // SELECT . INSERT LAST_INSERT_ID() int, , SELECT..

, concurrency , . , UNIQUE tag, INSERT s.

Answer 4

, , , LAST_INSERT_ID() , , .

INSERT INTO tag2post (postid, tagid)
VALUES
(
    332, # the post
    IF (
        (SELECT tagid FROM tags WHERE tag = 'new_tag'),
        (SELECT tagid FROM tags WHERE tag = 'new_tag'),
        IF (
            (INSERT INTO tags (tag) VALUES ('new_tag')),
            LAST_INSERT_ID(),
            LAST_INSERT_ID()
        )
    )
)

Answer 5

If you use a field with non-automatic increment and perform your own growth calculation, you can determine what will appear before the identifier before inserting this field and manually specifying new identifiers.

Then you can build a line of statements for each tag2post entry using these identifiers separated by s, and you can send it to msyql for execution.