Does MySQL overwrite a column of same value on update?

Question

When updating a table in MySQL, for example:

Table user

user_id | user_name

1         John
2         Joseph
3         Juan

If I run the query

UPDATE `user` SET user_name = 'John' WHERE user_id = 1

Will MySQL write the same value again or ignore it since it's the same content?

If this is about performance (i.e. there being a benefit to try being clever and avoiding this) it is a good thing to know. But I would not make application code depend on what gets returned for the "rows affected" count. — Thilo
– Thilo, Commented Feb 12, 2016 at 13:53
Related: stackoverflow.com/questions/13559583/… (please answer that one, too, if you can) — Thilo
– Thilo, Commented Feb 12, 2016 at 13:57

Peter Mortensen · Accepted Answer · 2020-07-28 17:35:08Z

50

As the MySQL manual for the UPDATE statement implies,

If you set a column to the value it currently has, MySQL notices this and does not update it.

So, if you run this query, MySQL will understand that the value you're trying to apply is the same as the current one for the specified column, and it won't write anything to the database.

edited Jul 28, 2020 at 17:35

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 12, 2016 at 13:47

Phiter

15k14 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Phiter Over a year ago

Tho I wonder if it's a good function, speaking of performance. If someone else desire to answer providing more details it would be great!

Fahim Uddin Over a year ago

does MySQL throws something back saying can't update because of same value or It says nothing, because when I am trying this, it returns 0 , 0 means what here? that can't update same data or nothing has done or if there is any server related issue then it will also return 0 right ?

Jairo Lozano Over a year ago

When executing statements MySQL will return two values, (last_inserted_id, rows_affected) that's what the 0,0 you're getting means, the statement was executed but it inserted nothing and updated nothing

Maelish Over a year ago

How would you find out which columns updated when some are the same value?

Bitterblue Over a year ago

The result value 0 in this case is the worst! It forces some strange workarounds in dev with pre-selects or pre-inserts.

Collectives™ on Stack Overflow

Does MySQL overwrite a column of same value on update?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related