Can MySQL tables have more than one CURRENT_TIMESTAMP column?

Overcoming the Limitation of Multiple CURRENT_TIMESTAMP Columns in MySQL

It is often desirable to track both the creation and update timestamps of data in database tables. In MySQL, the CURRENT_TIMESTAMP keyword can be used to automatically populate a timestamp column with the current date and time. However, MySQL has a restriction that only one CURRENT_TIMESTAMP column can be specified in a table's CREATE statement.

Addressing the Error

The error generated when attempting to create a table with multiple CURRENT_TIMESTAMP columns is:

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

This error indicates that MySQL is unable to process the table definition because it violates the restriction on multiple CURRENT_TIMESTAMP columns.

Solution

To overcome this limitation, a newer version of MySQL (5.6.25 or later) can be used. In MySQL 5.6.25 and above, the restriction on multiple CURRENT_TIMESTAMP columns has been removed. This allows you to define a table with multiple TIMESTAMP columns that are automatically updated with the current date and time.

For example, the following table definition is valid in MySQL 5.6.25 and later:

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
);

In this table definition, both the ts_create and ts_update columns are TIMESTAMP columns with CURRENT_TIMESTAMP set as the default value for insertion and update operations, respectively. This ensures that the timestamps are automatically updated with the current date and time when records are created or updated.

The above is the detailed content of Can MySQL tables have more than one CURRENT_TIMESTAMP column?. For more information, please follow other related articles on the PHP Chinese website!