MySQL 更新与插入和删除 [英] MySQL UPDATE vs INSERT and DELETE

问题描述

我正在开发一个 Web 应用程序项目，并且有一个相当大的 html 表单需要将其数据存储在一个表中.表单和插入已经完成，但我的客户希望能够将保存的数据加载回 HTML 表单并能够更改它，再次，这没问题，但是我在进行更新时遇到了一个问题，如果是编辑，只保留插入查询然后删除旧行是否合适?

I am working on a web app project and there is a rather large html form that needs to have its data stored in a table. The form and insert are already done but my client wants to be able to load the saved data back into the HTML form and be able to change it, again, this is no problem, but I came across a question when going to do the update, would it be appropriate to just keep the insert query and then delete the old row if it was an edit?

基本上，当提交表单时，已经发生的事情是使用 INSERT 将所有数据放入表中，如果数据用于正在更新的现有字段，我还有一个名为 edit 的标志，其中包含主键 ID.我可以通过两种方式处理更新功能:

Basically, what already happens is when the form is submitted all of the data is put into a table using INSERT, I also have a flag called edit that contains the primary key ID if the data is for an existing field being updated. I can handle the update function two ways:

a) 创建一个包含所有字段/数据集的实际更新查询，并使用 if/else 来决定是运行更新还是插入查询.

a) Create an actual update query with all the fields/data set and use an if/else to decide whether to run the update or insert query.

b) 每次都进行插入，但插入成功后在 DELETE WHERE row=editID 中添加一行.

b) Do the insert every time but add a single line to DELETE WHERE row=editID after the insert is successful.

由于只有在 INSERT 成功的情况下才会删除，所以我不会冒在不插入的情况下删除数据的风险，从而丢失数据，但是由于 INSERT/DELETE 是两个查询，因此它的效率是否会低于仅使用一个 if/else 来决定是运行插入还是更新?

Since the Delete would only happen if the INSERT was successful I don't run the risk of deleting the data without inserting, thus losing the data, but since INSERT/DELETE is two queries, would it be less efficient than just using an if/else to decide whether to run an insert or update?

还有第二个表使用自增id作为外键，但是每次提交表单时都必须更新这个表，所以如果我删除表A中的行，我也会删除表 b 中的关联行.这似乎是一种不好的编程习惯，所以无论如何我倾向于选项 a)，但仅使用单行选项非常诱人.DELETE 基本上如下.这实际上是不好的编程习惯吗?除了约定之外，还有什么理由可以说明这是永远不要那样做！"代码类型?

There is a second table that uses the auto-increment id as a foreign key, but this table has to be updated every time the form is submitted, so if I delete the row in table A, I will also be deleting the associated rows from table b. This seems like it would be bad programming practice, so I am leaning towards option a) anyway, but it is very tempting just to use the single line option. The DELETE would basically be as follows. Would this in fact be bad programming practice? Aside from conventions, are there any reasons why this is a "never do that!" type of code?

    if ($insertFormResults) {
        $formId = mysql_insert_id();
        echo "Your form was saved successfully.";
        if(isset($_POST['edit'])){
            $query = "DELETE FROM registerForm WHERE id='$_POST[edit]'";
            $result = mysql_query($query);
        }
    }

推荐答案

虽然 INSERT/DELETE 选项可以很好地工作，但我建议不要这样做:

Whilst the INSERT/DELETE option would work perfectly well I'd recommend against it as:

• 除非你捆绑了 INSERT/DELETE最多一次交易，或更好的是封装插入/删除到存储的你运行理论的程序累积重复的风险.如果您使用 SP 或您的交易只是有效地重写 UPDATE显然是声明效率低下，而且会给上升到几个 WTF 挑眉稍后由维护您的任何人代码.
• 虽然听起来不像在你的情况下你是问题潜在影响参考如果你需要的话，完整性.此外，您正在失去相当有用的能力很容易按创建顺序检索记录.
• 可能不是一个很好的考虑因素一个小应用程序，但你是最终会认真碎片化数据库相当快这会减慢数据检索速度.

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