CODEWITHSUNDEEP
mysqlsql
hogan
hogan

Reputation: 1561

MySQL changes 'changed' column on update by itself

create table test (id int, changed timestamp);

insert into test (id, changed) values (1, now());
insert into test (id, changed) values (2, now());
insert into test (id, changed) values (3, now());

select * from test;

+----+---------------------+
| id | changed             |
+----+---------------------+
| 1  | 2015-07-14 14:58:00 | <-- correct
| 2  | 2015-07-14 14:58:02 |
| 3  | 2015-07-14 14:58:04 |
+----+---------------------+


update test set id = 5 where id = 1;  -- nothing with 'changed' specified!

select * from test;

+----+---------------------+
| id | changed             |
+----+---------------------+
| 5  | 2015-07-14 15:00:00 | <-- wrong! why?
| 2  | 2015-07-14 14:58:02 |
| 3  | 2015-07-14 14:58:04 |
+----+---------------------+

See the column changed, i wanted to set id and not changed. First time that a database does this on me.

Why does MySQL do that?

How can I stop that behaviour?

Upvotes: 0

Views: 951

Answers (3)

Buddy
Buddy

Reputation: 11038

A timestamp column is automatically updated:

With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

See the docs.

Upvotes: 0

Cyclonecode
Cyclonecode

Reputation: 30131

By default a TIMESTAMP column will be set to CURRENT_TIMESTAMP and this will also be used whenever you updating the row.

When you create you table using:

CREATE TABLE test (
  id int,
  changed TIMESTAMP
);

It will be the same as this:

CREATE TABLE test (
  id int,
  changed TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

With a constant, the default is the given value. In this case, the column has no automatic properties at all:

CREATE TABLE test (
  id int,
  changed TIMESTAMP 0
);

Upvotes: 1

Mat Richardson
Mat Richardson

Reputation: 3606

It's because you used the timestamp data type for the 'changed' column, which updates every time the row is updated.

If you wanted this to work as expected I would change the 'changed' data type to datetime instead.

Upvotes: 0

Related Questions