Reputation: 1561
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
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
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
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