CODEWITHSUNDEEP
mysqlsqldatabasesql-update
dai
dai

Reputation: 1075

Do you need to update the data when it doesn't change

I want to update a row in the table，before updating, do I need to check if there is any change in each column?

Upvotes: 2

Views: 4003

Answers (3)

Curtis Yallop
Curtis Yallop

Reputation: 7329

mysql appears to not update the actual row if no data changed.

Demonstration:

create temporary table test (id int not null, value int default null, PRIMARY KEY (`id`));
insert into test (id, value) values (1,1),(2,2);

-- no-change update
update test set value = 2 where id = 2;
select row_count();
> "mysql -vvv" rows_affected output: Query OK, 0 rows affected (0.01 sec)
> mysql output: Rows matched: 1  Changed: 0  Warnings: 0
> row_count: 0

-- has-change update
update test set value = 3 where id = 2;
select row_count();
> "mysql -vvv" rows_affected output: Query OK, 1 row affected (0.01 sec)
> mysql output: Rows matched: 1  Changed: 1  Warnings: 0
> row_count: 1

I believe: row_count = rows_affected = "Changed"

The mysql_rows_affected link is helpful but does not explicitly state that the row will not be updated if there is no data change. It only states that the row will not be updated if it does not match the UPDATE's WHERE-clause.

https://dev.mysql.com/doc/c-api/5.7/en/mysql-affected-rows.html

Note that I don't see any documented guarantee of this behaviour (that mysql will never write rows if the data has not changed).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270683

In MySQL, you do not need to check the value. MySQL does not update the record if there is no change. That means that MySQL does not incur the overhead of logging or writing the data. There is a slight cost of checking if any values changed, and I think it does run the triggers.

Other databases behave differently.

This is in an arcane location in the documentation, where mysql_affected_rows() is described:

For UPDATE statements, the affected-rows value by default is the number of rows actually changed.

. . .

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records were updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed.

Upvotes: 6

t..
t..

Reputation: 1101

That would be totally up to you to check if anything is in there before hand. You can do updates based on a single row, multiple, or all of them.

An example where you update a specific row is like this:

UPDATE your_table
SET Username='TestName123'
WHERE UserID='12486';

where you would be changing the username where the userid is 12486

OR you can update all of the rows with data you want like

UPDATE Customers
SET Country='USA'

This would update every record to have the Country column be filled with USA.

Upvotes: 0

Related Questions