首页 \ 问答 \ MySQL UPDATE vs INSERT和DELETE(MySQL UPDATE vs INSERT and DELETE)

MySQL UPDATE vs INSERT和DELETE(MySQL UPDATE vs INSERT and DELETE)

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

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

a)使用所有字段/数据集创建实际更新查询,并使用if / else来决定是运行更新还是插入查询。

b)每次插入,但在插入成功后向DELETE WHERE row = editID添加一行。

由于只有在INSERT成功时才会发生删除,因此我不会冒没有插入而删除数据的风险,从而丢失数据,但由于INSERT / DELETE是两个查询,它的效率会低于仅使用if /否则决定是否运行插入或更新?

有一个第二个表使用自动增量id作为外键,但每次提交表单时都必须更新此表,所以如果我删除表A中的行,我也将删除相关的行表b。 这似乎是糟糕的编程习惯,所以无论如何我倾向于选项a)但是使用单行选项非常诱人。 DELETE基本上如下。 这实际上是不好的编程习惯吗? 除了惯例之外,还有任何理由说明这是“从不这样做!” 代码类型?

    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);
        }
    }

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?

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) 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) Do the insert every time but add a single line to DELETE WHERE row=editID after the insert is successful.

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?

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);
        }
    }

原文:https://stackoverflow.com/questions/4854863
更新时间:2020-05-09 07:48

相关文章

更多

最新问答

更多
  • jsPlumb draggable element javascript函数(jsPlumb draggable element javascript function)
  • MVC4:ViewModel(带有radiobuttonlist)在HttpPost之后为空(MVC4: ViewModel (with radiobuttonlist) is empty after HttpPost)
  • 如何在同一帐户上设置“Dev repo”(在prod和团队之间)(How to set up a “Dev repo” (between the prod and the team) on the same account)
  • 如何在tcl中将eth0配置为发送方udp端口(how to configure eth0 as a sender udp port in tcl)
  • 如何在datarow []中的列中找到最大值?(How to find max value in a column in a datarow[] ?)
  • 如何使用预定义文本替换来自数据库的部分结果(How do I replace part of result coming from Database with predefined text)
  • Selenium Java注入了新的Javascript函数(Selenium Java inject new Javascript function)
  • 使用.on的多个下拉菜单选择文本仅适用于第一个下拉列表(Multiple Dropdowns Menu Selection text using .on works only on first dropdown)
  • 快速将黄土曲线添加到大型数据集图中的方法(Quick way to add loess curve to large data set graph)
  • FilteringSelect in mvc(FilteringSelect in mvc)
  • 在Delphi XE2中开发Mac或iOS应用程序需要哪些硬件/软件?(What hardware/software is necessary to develop Mac or iOS apps in Delphi XE2?)
  • 在原型的构造函数中初始化属性时获取“未定义”(Getting 'undefined' when a property is initialized in the constructor of a prototype)
  • 通过越狱加载的应用程序的Documents文件夹位置(Location of Documents folder for an app loaded via jailbreak)
  • 在OpenGL中使用可编程和固定管道功能(Using both programmable and fixed pipeline functionality in OpenGL)
  • 将任何用户输入重定向到单独的底层程序(redirect any user input to a separate underlying program)
  • 编辑文本不能正常工作android(Edit texts not working properly android)
  • “user_denied”Facebook应用页面上的Facebook用户区域设置(Facebook user locale on “user_denied” facebook app page)
  • 在大图像中找到小的部分透明图像的坐标(find coordinates of small partially-transparent image within a large image)
  • 我如何在cakephp 3.1中获得完整的相对路径?(How i can get full relative path of image in cakephp 3.1?)
  • 如何保存拖动标记的新本地化?(How to save new localization of dragged marker?)
  • MySQL UPDATE vs INSERT和DELETE(MySQL UPDATE vs INSERT and DELETE)
  • 在执行查询之前,在SQLAlchemy模型中将datetime转换为unix时间戳?(Convert datetime to unix timestamp in SQLAlchemy model before executing query?)
  • OpenCL与OpenGL互操作的优势(Advantage of OpenCL interoperability with OpenGL)
  • 如何解析用点和等分隔的数据然后添加到listview(How to parsing data from delimited with dot and equal then add to listview)
  • 带调试输出的X3解析器段错误(BOOST_SPIRIT_X3_DEBUG)(X3 parser segfaults with debug output (BOOST_SPIRIT_X3_DEBUG))
  • 将文件夹名称添加到fgrep结果(Add folder name to fgrep result)
  • 在MySQL中加载一个表是非常慢的(Loading one table in MySQL is ridiculously slow)
  • 如何将JSON放入PHP变量?(How do I put JSON into a PHP Variable?)
  • 如何绕过Microsoft.Speech.Recognition中的不流畅?(How to bypass disfluencies in Microsoft.Speech.Recognition?)
  • 原点的最后一行是什么?(What is the last row of an origin for?)