Flite Culture

Overriding MySQL Current_timestamp Default on Update

The MySQL TIMESTAMP data type supports automatic initialization and updating to the current date and time. This can be a convenient way to track when rows are created and modified, but it’s easy to forget about them and have them update data when you don’t want that. In those cases you need to explicitly set the column value to avoid the automatic behavior. On an update, that means setting the column to its current value. Quoting the manual:

If the column is auto-updated, it is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. The column remains unchanged if all other columns are set to their current values. To prevent the column from updating when other columns change, explicitly set it to its current value. To update the column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

For example, say you have a table with a column called mtime that you use to track the last time your application modified each row. If you do a bulk update to that table outside of the application, the mtime column will still be updated even if you don’t want that:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
create table ad (
  id int unsigned not null auto_increment,
  name varchar(255) not null,
  friendly_name varchar(255) not null,
  ctime timestamp not null default current_timestamp,
  mtime timestamp not null default current_timestamp on update current_timestamp,
  primary key (id)
);

mysql> insert into ad (name,friendly_name) values ('hello world','hello world');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ad;
+----+-------------+---------------+---------------------+---------------------+
| id | name        | friendly_name | ctime               | mtime               |
+----+-------------+---------------+---------------------+---------------------+
|  1 | hello world | hello world   | 2014-02-11 11:13:13 | 2014-02-11 11:13:13 |
+----+-------------+---------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> do sleep(5);
Query OK, 0 rows affected (5.01 sec)

mysql> update ad set friendly_name = replace(name,' ','_');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ad;
+----+-------------+---------------+---------------------+---------------------+
| id | name        | friendly_name | ctime               | mtime               |
+----+-------------+---------------+---------------------+---------------------+
|  1 | hello world | hello_world   | 2014-02-11 11:13:13 | 2014-02-11 11:13:18 |
+----+-------------+---------------+---------------------+---------------------+
1 row in set (0.00 sec)

In the above example the value of mtime changed from 2014-02-11 11:13:13 to 2014-02-11 11:13:18 when I did not want it to change.

The solution is to explicitly set the mtime column to its existing value:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> insert into ad (name,friendly_name) values ('hello world','hello world');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ad;
+----+-------------+---------------+---------------------+---------------------+
| id | name        | friendly_name | ctime               | mtime               |
+----+-------------+---------------+---------------------+---------------------+
|  1 | hello world | hello world   | 2014-02-11 11:18:03 | 2014-02-11 11:18:03 |
+----+-------------+---------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> do sleep(5);
Query OK, 0 rows affected (5.00 sec)

mysql> update ad set friendly_name = replace(name,' ','_'), 
    ->   mtime = mtime;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ad;
+----+-------------+---------------+---------------------+---------------------+
| id | name        | friendly_name | ctime               | mtime               |
+----+-------------+---------------+---------------------+---------------------+
|  1 | hello world | hello_world   | 2014-02-11 11:18:03 | 2014-02-11 11:18:03 |
+----+-------------+---------------+---------------------+---------------------+
1 row in set (0.00 sec)

In the above example the value of mtime did not change.

Comments

Comments