Database Reference
In-Depth Information
column attributes that enable you to track row-creation and -update times automati‐
cally:
• A
TIMESTAMP
or
DATETIME
column declared with the
DEFAULT
CURRENT_TIME
STAMP
attribute initializes automatically for new rows. Simply omit the column from
INSERT
statements and MySQL sets it to the row-creation time.
• A
TIMESTAMP
or
DATETIME
column declared with the
ON
UPDATE
CURRENT_TIME
STAMP
attribute automatically updates to the current date and time when you change
any other column in the row from its current value.
These special properties make the
TIMESTAMP
and
DATETIME
data types particularly
suited for applications that require recording the times at which rows are inserted or
updated. The following discussion shows how to take advantage of these properties
using
TIMESTAMP
columns. With some differences to be noted later, the discussion also
applies to
DATETIME
columns.
This section assumes that you have MySQL 5.6.5 or later. For older
versions, automatic initialization and update properties apply only to
TIMESTAMP
(not
DATETIME
), and to at most a single
TIMESTAMP
in a
table.
Our example table looks like this:
CREATE
TABLE
tsdemo
(
val
INT
,
ts_both
TIMESTAMP
DEFAULT
CURRENT_TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
,
ts_create
TIMESTAMP
DEFAULT
CURRENT_TIMESTAMP
,
ts_update
TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
);
The
TIMESTAMP
columns have these properties:
•
ts_both
auto-initializes and auto-updates. This is useful for tracking the time of
any change to a row, for both inserts and updates.
•
ts_create
auto-initializes only. This is useful when you want a column to be set to
the time at which a row is created, but remain constant thereafter.
•
ts_update
auto-updates only. It is set to the column default (or value you specify
explicitly) at row-creation time and it auto-updates for changes to the row there‐
after. The use cases for this are more limited—for example, to track row-creation
and last-modification times separately (using
ts_update
in conjunction with
ts_create
), rather than together in a single column like
ts_both
.
