首先更新或删除，然后插入 [英] UPDATE or DELETE first then INSERT

问题描述

我再问一次，因为我无法从SO上的其他问题中得到直接答案.

I'm asking this again, because I can't get a straight answer from other questions on SO.

我的情况如下，两个表phones和features.

My situation is as follows, two tables, phones and features.

结构:

电话

• id
• brand_id
• 模型
• 图片
• 已发布

功能

• id
• phone_id
• 功能

现在，features包含很多值，这些值仅在1和2之间. 4将通过phone_id对应于某个电话.

Now, features contains a whole lot of values, which only between 1 & 4 will correspond to a certain phone via phone_id.

现在，当我更新功能时，您是否认为删除，然后再次插入新功能或更新现有功能将是最好的?

Now, when I update features, do you think deleting and then inserting new features again or updating existing features will be best?

//

特价

• id *
• phone_id
• contract_id
• start_publish
• finish_publish
• 已发布

合同

• id *
• 名称
• 好处
• 持续时间
• 价格等

品牌

• id
• 名称
• 徽标

因此，基本上，一个特殊功能将电话和合同链接在一起，并从两个表中获取信息以进行输出.然后，在此过程中，使用功能分别加载每部手机的功能.

So basically, a special links a phone and a contract together, and grabs info from both tables for output. Then during that process, each phone's features get loaded separately using a function.

//

哦，一次只能删除/更新一个手机的功能，一次只能删除1-4个功能.而且，功能始终可以或多或少，而不能是固定数量.

Oh, and only one phone's features will be deleted/updated at a time, and also, only between 1-4 features will be deleted at a time. Also, features can always be more or less, and never a set number.

推荐答案

如果每部电话的功能数量会发生变化，则必须删除并插入.要更新，您需要:

If the number of features per phone will ever change, then you must delete and insert. To update, you'd need to:

• 获取当前功能集
• 弄清楚有什么不同，添加了什么以及删除了什么
• 运行查询以更新，插入新数据和删除旧数据.

就好像您只是删除整个列表并重新插入它们一样，您可以避免一些维护上的麻烦.

Where as if you just delete the whole list and re-insert them, you save yourself some maintenance headaches.

如果数据一致性是一个问题或关注点(数据已删除但在插入之前发生错误导致脚本无法完成)，则将查询包装在事务中.

If consistency of your data is an issue or concern (the data is deleted but an error happens before insert causing the script to not complete) then wrap your queries in a transaction.

以这个例子为例:

我有一个包含Posts(id, title, body)和Categories(id, post_id, category)的博客.我的第一篇文章"Hello World"被归类为元，PHP和javascript".然后一年后，我修改了帖子，以获取有关mysql的一些信息(或添加mysql类别)并删除javascript.

I have a blog with Posts(id, title, body) and Categories(id, post_id, category). My first Post, "Hello World" is categorized under "meta, php, and javascript". Then a year later, I revise the post to have some information about mysql (or I add a mysql category) and take out javascript.

这是我需要做的(主要是伪代码):

Here's what I'd need to do (mostly psuedocode):

//old set
SELECT * FROM Categories WHERE post_id=1

foreach($categories as $category) {
    if(in_array($category['id'], $_POST['categories']) {
        //this category is in the new set and the old set, do nothing
    }
    elseif(!in_array($category['id'], $_POST['categories']) {
        //this category is in the old set, but not the new one, delete it
        $queries[] = "DELETE FROM Categories WHERE id=".$category['id'];
    }
    else {
        //new category, add it
        $queries[] = "INSERT INTO Categories()..."
    }
}

foreach($queries as $item) {
    //run query
}

相对于此:

DELETE FROM Categories WHERE post_id=$_POST['id']

foreach($_POST['categories'] as $item) {
    INSERT INTO Categories() ...
}

我个人认为选项2更好.

Personally, I think option #2 is better.

这篇关于首先更新或删除，然后插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！