Can Multiple TIMESTAMP Columns Have CURRENT_TIMESTAMP in MySQL?

MySQL CURRENT_TIMESTAMP on Create and Update

When defining a MySQL table, it is desirable to include timestamps to track the creation and modification of records. However, an error may occur when attempting to define two TIMESTAMP fields with CURRENT_TIMESTAMP as the default or on update values.

Error Message

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Cause

MySQL versions prior to 5.6.25 only allowed a single TIMESTAMP column to be defined with CURRENT_TIMESTAMP as the default or on update value.

Solution

In MySQL versions 5.6.25 and above, it is possible to define multiple TIMESTAMP columns, each with its own CURRENT_TIMESTAMP setting. As of MySQL 8.0, this restriction has been completely removed. Therefore, for versions 5.6.25 and above, the following table definition should work correctly:

CREATE TABLE `msgs` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `msg` VARCHAR(256),
    `ts_create` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ts_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

This definition will create a table with two TIMESTAMP fields, ts_create and ts_update, which will be automatically updated with the current timestamp upon record creation and update, respectively.

The above is the detailed content of Can Multiple TIMESTAMP Columns Have CURRENT_TIMESTAMP in MySQL?. For more information, please follow other related articles on the PHP Chinese website!