mysql频繁insert和delete,MySQL UPDATE与INSERT和DELETE

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

}

}

解决方案

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

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.