How Can I Define Multiple TIMESTAMP Columns with CURRENT_TIMESTAMP in MySQL?

Managing CURRENT_TIMESTAMP in MySQL for Creation and Update Operations

In relational database management systems like MySQL, maintaining temporal information, such as when records are created or updated, is crucial for data integrity and tracking changes. MySQL provides the CURRENT_TIMESTAMP clause to automatically populate TIMESTAMP columns with the current system time.

However, when attempting to define a table with multiple TIMESTAMP columns, each with CURRENT_TIMESTAMP as the default or on update value, you might encounter the following error:

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

This error prevents you from creating the table with the desired behavior of separate creation and update timestamp columns.

Resolution

Recent versions of MySQL, such as MySQL 5.6.25, have resolved this limitation. In these versions, it is possible to define multiple TIMESTAMP columns with CURRENT_TIMESTAMP as the default or on update value.

For example, to create a table with two TIMESTAMP columns, ts_create and ts_update, with the desired behavior:

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 statement will successfully create the table with both TIMESTAMP columns, and their values will be automatically updated with the current system time upon record creation and update, respectively.

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