ProblemYou want a row's creation time or last modification time to be recorded automatically. SolutionUse the TIMESTAMP data type, which has auto-initialization and auto-update properties. DiscussionMySQL supports a TIMESTAMP data type that stores date-and-time values. Earlier sections covered the range of values for TIMESTAMP (Section 6.1) and the conversion of TIMESTAMP values to and from UTC when they are stored and retrieved (Section 6.3). This section focuses on how TIMESTAMP columns enable you to track row creation and update times automatically:
The special properties that relate to row creation and modification make the TIMESTAMP data type particularly suited for certain kinds of problems, such as automatically recording the times at which table rows are inserted or updated. The following discussion shows how to take advantage of these properties. The syntax for defining TIMESTAMP columns is covered in full in the MySQL Reference Manual. Here we cover only some simple cases. By default, if you specify no special handling for the first TIMESTAMP column in a table, it is equivalent to specifying that it should have both the auto-initialize and auto-update properties explicitly. You can see this as follows, where the SHOW CREATE TABLE statement displays the full TIMESTAMP definition that results from the CREATE TABLE statement: mysql> CREATE TABLE t (ts TIMESTAMP); mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 If you explicitly specify only the DEFAULT or ON UPDATE attribute for the TIMESTAMP column, it has only that attribute and not the other one. The NOT NULL shown in the column definition might seem a curious thing, given that you can insert NULL into the column, and no error occurs. What this means is that, although you can specify NULL as the value to be inserted, you cannot store NULL because MySQL stores the current date and time instead. To create a table in which each row contains a value that indicates when the row was created or most recently updated, include a TIMESTAMP column. MySQL will set the column to the current date and time when you create a new row, and update the column whenever you update the value of another column in the row. Suppose that you create a table tsdemo1 with a TIMESTAMP as one of its columns: CREATE TABLE tsdemo1 ( ts TIMESTAMP, val INT ); In this case, the ts column has both the auto-initialize and auto-update properties, for reasons just discussed. Insert a couple of rows into the table, and then select its contents. (Issue the INSERT statements a few seconds apart so that the timestamps differ.) The first INSERT statement shows that you can set ts to the current date and time by omitting it from the INSERT statement entirely; the second shows that you can do so by setting ts explicitly to NULL: mysql> INSERT INTO tsdemo1 (val) VALUES(5); mysql> INSERT INTO tsdemo1 (ts,val) VALUES(NULL,10); mysql> SELECT * FROM tsdemo1; +---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:21:26 | 5 | | 2006-06-03 08:21:31 | 10 | +---------------------+------+ Now issue a statement that changes one row'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; +---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:21:52 | 6 | | 2006-06-03 08:21:31 | 10 | +---------------------+------+ The result shows that the TIMESTAMP column of the modified row was updated. If you modify multiple rows, the TIMESTAMP value in each of them is updated: mysql> UPDATE tsdemo1 SET val = val + 1; mysql> SELECT * FROM tsdemo1; +---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:22:00 | 7 | | 2006-06-03 08:22:00 | 11 | +---------------------+------+ An UPDATE statement that doesn't actually change the value in the val column doesn't update the TIMESTAMP value. To see this, set every row's val column to its current value, and then review the contents of the table: mysql> UPDATE tsdemo1 SET val = val; mysql> SELECT * FROM tsdemo1; +---------------------+------+ | ts | val | +---------------------+------+ | 2006-06-03 08:22:00 | 7 | | 2006-06-03 08:22:00 | 11 | +---------------------+------+ If you want the TIMESTAMP column to be set initially to the time at which a row is created, but to remain constant thereafter, define it to auto-initialize but not auto-update. To do this, the TIMESTAMP definition can be given as shown in the following table: CREATE TABLE tsdemo2 ( t_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP, val INT ); Create the table, and then insert records into it with the TIMESTAMP column not specified (or specified as NULL) to initialize it to the current date and time: mysql> INSERT INTO tsdemo2 (val) VALUES(5); mysql> INSERT INTO tsdemo2 (t_create,val) VALUES(NULL,10); mysql> SELECT * FROM tsdemo2; +---------------------+------+ | t_create | val | +---------------------+------+ | 2006-06-03 08:26:00 | 5 | | 2006-06-03 08:26:05 | 10 | +---------------------+------+ After inserting the records, change the val column, and then verify that the update leaves the t_create column unchanged (that is, set to the record-creation time): mysql> UPDATE tsdemo2 SET val = val + 1; mysql> SELECT * FROM tsdemo2; +---------------------+------+ | t_create | val | +---------------------+------+ | 2006-06-03 08:26:00 | 6 | | 2006-06-03 08:26:05 | 11 | +---------------------+------+ See AlsoIf you want to simulate the TIMESTAMP auto-initialization and auto-update properties for other temporal types, you can use triggers (Section 16.5). |