MySQL Update When Data Doesn't Change

PHP to update data if nothing changes

[boxfan]

Hi,

I have a look-up form with 10 fields. The user can edit any of the 10 fields. The user decides to only edit 1 field and the form is submitted.

The query in PHP is something like

UPDATE table SET field1 = '$field1', field2 = 'field2', etc

Does MySQL update all the fields even if the data is exactly the same? Or does MySQL skip the update on the field if the data is the same?

So, MySQL would only actually update the one field that was changed and not spend the resources updating the ones that are the same.

I thought I had read that MySQL only updates what has changed.

Thanks,

John

[vincevincevince]

It will need to check everything you specify. Don't specify a field-value pair and it will ignore it.

If you haven't changed field 2 then instead of:
UPDATE table SET field1 = '$field1', field2 = 'field2', etc
Write:
UPDATE table SET field1 = '$field1', etc

The unreferenced fields will be fully ignored.

and not spend the resources updating the ones that are the same.

To be honest I suspect that in most cases just overwriting the value is faster than doing a comparison for equality for the field before deciding whether to overwrite it.

If I tell you:
a = 7, and then
b = 10, then finally
a = 3

Would you bother checking to see if a is already 3 before setting it to 3?

[boxfan]

So, MySQL would only actually update the one field that was changed and not spend the resources updating the ones that are the same.

Yeah, after I read that again I realized how stupid it sounded.

Thanks for the reply.

John