MySQL UPDATE与INSERT和DELETE [英] 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)，但是仅使用单行选项是非常诱人的.删除基本上如下.这实际上是不好的编程习惯吗?除了约定外，还有什么理由使它永远不要这样做"！代码类型?
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扬起了一些眉毛 后来有人维护你 代码.
- 虽然听起来不像是 在你的情况下，你是 潜在影响参照 完整性，您需要这样做. 此外，您正在失去 相当有用的能力，可以轻松地 按创建顺序检索记录.
- 可能不是一个很重要的考虑因素 一个小的应用程序，但是你是 会以认真对待而告终 碎片化的数据库相当快 这会减慢数据检索的速度.
- Unless you bundle the INSERT/DELETE up into a single transaction, or better yet encapsulate the INSERT/DELETE up into a stored procedure you do run the theoretical risk of accumulating duplicates. If you use a SP or a transaction you're just effectively rewriting the UPDATE statement which is obviously inefficient and moreover will give rise to a few WTF raised eyebrows later by anyone maintaining your code.
- Although it doesn't sound like an issue in your case you are potentially impacting referential integrity should you need that. Furthermore you are loosing the rather useful ability to easily retrieve records in creation order.
- Probably not a great consideration on a small application, but you are going to end up with a seriously fragmented database fairly quickly which will slow data retrieval.
这篇关于MySQL UPDATE与INSERT和DELETE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
