在mysql中嵌套插入以进行标记 [英] nested insert in mysql for tagging

问题描述

我想用一个sql语句在博客文章中添加标签.

i'd like to add a tag to a blogpost with a single sql statement.

说我的表格如下:

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

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

我要解决的问题是插入新标签，获取其ID ，然后将此新ID插入关系表 >在单个sql语句中.

the problem i'd like to solve is inserting a new tag, retrieve it's id and then inset this new id into the relation table in a single 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's id
        'i am lost here'
    )
)

推荐答案

您不能将其作为单个插入操作来完成，因为插入操作是原子性的，也就是说，直到语句完成才确定ID.

You can't do this as a single insert because inserts are atomic--that is, the ID isn't determined until the statement completes.

将两个语句包装在一个事务中，您将获得ID和原子性.

Wrap both statements in a transaction and you will get your ID, and atomicity.

这篇关于在mysql中嵌套插入以进行标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！