SQL条件行插入 [英] SQL conditional row insert

问题描述

如果满足条件，是否可以插入新行?

Is it possible to insert a new row if a condition is meet?

例如，我的这张桌子既没有主键也没有唯一性

For example, i have this table with no primary key nor uniqueness

    +----------+--------+
    | image_id | tag_id |
    +----------+--------+
    |    39    |    8   |
    |    8     |    39  |
    |    5     |    11  |
    +----------+--------+

如果image_id和tag_id的组合不存在，我想插入一行 例如；

I would like to insert a row if a combination of image_id and tag_id doesn't exists for example;

INSERT ..... WHERE image_id!=39 AND tag_id!=8

推荐答案

我认为是在说:您需要避免在此表中重复行.

I think you're saying: you need to avoid duplicate rows in this table.

有很多处理方法.最简单的之一:

There are many ways of handling this. One of the simplest:

INSERT INTO theTable (image_id, tag_id) VALUES (39, 8)
WHERE NOT EXISTS 
    (SELECT * FROM theTable 
    WHERE image_id = 39 AND tag_id = 8)

正如@Henrik Opel指出的那样，您可以在合并的列上使用检查约束，但随后必须在其他位置使用try/catch块，这会增加不相关的复杂性.

As @Henrik Opel pointed out, you can use a check constraint on the combined columns, but then you have to have a try/catch block somewhere else, which adds irrelevant complexity.

编辑以解释该评论...

Edit to explain that comment...

我假设这是一个映射电影和标签之间的多对多关系的表.我意识到您可能正在使用php，但我希望下面的C#伪代码足够清晰.

I'm assuming this is a table mapping a many-to-many relationship between Movies and Tags. I realize you're probably using php, but I hope the C# pseudocode below is clear enough anyway.

如果我有一个Movie类，添加标签的最自然的方法是AddTag()方法:

If I have a Movie class, the most natural way to add a tag is an AddTag() method:

class Movie 
{
    public void AddTag(string tagname)
    {
        Tag mytag = new Tag(tagname); // creates new tag if needed

        JoinMovieToTag(this.id, mytag.id);
    }

    private void JoinMovieToTag(movie_id, tag_id)
    {
        /* database code to insert record into MovieTags goes here */

        /* db connection happens here */
        SqlCommand sqlCmd = new SqlCommand("INSERT INTO theTable... /* etc */");

        /* if you have a check constraint on Movie/Tag, this will 
           throw an exception if the row already exists */
        cmd.ExecuteNonQuery(); 
    }
}

在此过程的早期，没有实用的方法来检查重复项，因为另一个用户可能会随时标记影片，因此无法解决此问题.

There's no practical way to check for duplicates earlier in the process, because another user might Tag the Movie at any moment, so there's no way around this.

注意:如果尝试插入重复记录意味着存在错误，则抛出错误是适当的，但是如果不是这样，则您不希望错误处理程序具有额外的复杂性.

Note: If trying to insert a dupe record means there's a bug, then throwing an error is appropriate, but if not, you don't want extra complexity in your error handler.

这篇关于SQL条件行插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！