Working with Dates and Times - MySQL - page 193

To see how the table works, insert some rows into the table (a few seconds apart so the
timestamps differ), then select its contents:
mysql> INSERT INTO tsdemo (val) VALUES(5);
mysql> INSERT INTO tsdemo (val,ts_both,ts_create,ts_update)
-> VALUES(10,NULL,NULL,NULL);
mysql> SELECT * FROM tsdemo;
+------+---------------------+---------------------+---------------------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+---------------------+
| 5 | 2014-02-20 18:06:45 | 2014-02-20 18:06:45 | 0000-00-00 00:00:00 |
| 10 | 2014-02-20 18:06:50 | 2014-02-20 18:06:50 | 2014-02-20 18:06:50 |
+------+---------------------+---------------------+---------------------+
The first INSERT statement shows that you can set the auto-initialize columns to the
current date and time by omitting them from the INSERT statement entirely. The second
shows that you can set a TIMESTAMP column to the current date and time by setting it
explicitly to NULL , even one that does not auto-initialize. This NULL -assignment behavior
is not specific to INSERT statements; it works for UPDATE as well. You can disable this
special handling of NULL assignments, as we'll cover later in this section.
To see auto-updating in action, issue a statement that changes one row's val column
and check its effect on the table's contents. The result shows that the auto-update col‐
umns are updated (in the modified row only):
mysql> UPDATE tsdemo SET val = 11 WHERE val = 10;
mysql> SELECT * FROM tsdemo;
+------+---------------------+---------------------+---------------------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+---------------------+
| 5 | 2014-02-20 18:06:45 | 2014-02-20 18:06:45 | 0000-00-00 00:00:00 |
| 11 | 2014-02-20 18:06:55 | 2014-02-20 18:06:50 | 2014-02-20 18:06:55 |
+------+---------------------+---------------------+---------------------+
If you modify multiple rows, updates occur for the auto-update columns in each row:
mysql> UPDATE tsdemo SET val = val + 1;
mysql> SELECT * FROM tsdemo;
+------+---------------------+---------------------+---------------------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+---------------------+
| 6 | 2014-02-20 18:07:01 | 2014-02-20 18:06:45 | 2014-02-20 18:07:01 |
| 12 | 2014-02-20 18:07:01 | 2014-02-20 18:06:50 | 2014-02-20 18:07:01 |
+------+---------------------+---------------------+---------------------+
An UPDATE statement that doesn't actually change any value in a row doesn't modify
auto-update columns. To see this, set every row's val column to its current value, then
review the table contents to see that auto-update columns retain their values:
mysql> UPDATE tsdemo SET val = val;
mysql> SELECT * FROM tsdemo;