32

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Phiter
  • 14,570
  • 14
  • 50
  • 84
  • 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 Feb 12 '16 at 13:53
  • Related: http://stackoverflow.com/questions/13559583/will-mysql-write-same-value-to-the-disk-when-value-is-the-same-in-update (please answer that one, too, if you can) – Thilo Feb 12 '16 at 13:57

1 Answers1

47

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Phiter
  • 14,570
  • 14
  • 50
  • 84
  • 1
    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! – Phiter Feb 14 '16 at 10:33
  • 2
    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 ? – Fahim Uddin Sep 07 '18 at 08:40
  • 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 – Jairo Lozano Jul 29 '19 at 19:51
  • 1
    How would you find out which columns updated when some are the same value? – Maelish Aug 16 '19 at 13:49
  • The result value 0 in this case is the worst! It forces some strange workarounds in dev with pre-selects or pre-inserts. – Bitterblue Nov 21 '19 at 09:58