5.33 Recording a Row's Last Modification Time
5.33.1 Problem
You want to automatically record the time
when a record was last updated.
5.33.2 Solution
Include a TIMESTAMP column in your table.
5.33.3 Discussion
To create a table where each row contains a value that indicates when
the record was most recently updated, include a
TIMESTAMP column. The column will be set to the
current date and time when you create a new row, and updated whenever
you update the value of another column in the row. Suppose you create
a table tsdemo1 with a
TIMESTAMP column that looks like this:
CREATE TABLE tsdemo1
(
t TIMESTAMP,
val INT
);
Insert a couple of records into the table and then select its
contents. (Issue the INSERT queries a few seconds
apart so that you can see how the timestamps differ.) The first
INSERT statement shows that you can set
t to the current date and time by setting it
explicitly to NULL; the second shows that you set
t by omitting it from the
INSERT statement entirely:
mysql> INSERT INTO tsdemo1 (t,val) VALUES(NULL,5);
mysql> INSERT INTO tsdemo1 (val) VALUES(10);
mysql> SELECT * FROM tsdemo1;
+----------------+------+
| t | val |
+----------------+------+
| 20020715115825 | 5 |
| 20020715115831 | 10 |
+----------------+------+
Now issue a query that changes one record's
val column and check its effect on the
table's contents:
mysql> UPDATE tsdemo1 SET val = 6 WHERE val = 5;
mysql> SELECT * FROM tsdemo1;
+----------------+------+
| t | val |
+----------------+------+
| 20020715115915 | 6 |
| 20020715115831 | 10 |
+----------------+------+
The result shows that the TIMESTAMP has been
updated only for the modified record.
If you modify multiple records, the TIMESTAMP
values in all of them will be updated:
mysql> UPDATE tsdemo1 SET val = val + 1;
mysql> SELECT * FROM tsdemo1;
+----------------+------+
| t | val |
+----------------+------+
| 20020715115926 | 7 |
| 20020715115926 | 11 |
+----------------+------+
Issuing an UPDATE statement that
doesn't actually change the values in the
val column doesn't update the
TIMESTAMP values. To see this, set every
record's val column to its
current value, then review the contents of the table:
mysql> UPDATE tsdemo1 SET val = val + 0;
mysql> SELECT * FROM tsdemo1;
+----------------+------+
| t | val |
+----------------+------+
| 20020715115926 | 7 |
| 20020715115926 | 11 |
+----------------+------+
An alternative to using a
TIMESTAMP is to use
a DATETIME column and
set it to NOW( ) explicitly when you create a
record and whenever you update a record. However, in this case, all
applications that use the table must implement the same strategy,
which fails if even one application neglects to do so.
|
