How to Implement Multiple TIMESTAMP Fields with CURRENT_TIMESTAMP in MySQL?

MySQL: Supporting Multiple TIMESTAMP Fields with CURRENT_TIMESTAMP

The MySQL documentation states that a table can only have one TIMESTAMP column with the CURRENT_TIMESTAMP value in either the DEFAULT or ON UPDATE clause. This can be limiting when you want to track both the creation and update timestamps of a record.

Error Handling

When you try to create a table with more than one TIMESTAMP column using CURRENT_TIMESTAMP, you will encounter an error like the following:

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

Solution

Recent versions of MySQL (5.6.25 and later) have addressed this limitation. You can now define tables with multiple TIMESTAMP columns, each with its own CURRENT_TIMESTAMP behavior. Here is an example:

<code class="sql">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
);</code>

With this updated syntax, both the ts_create and ts_update columns will be automatically set to the current timestamp when a new record is inserted or an existing record is updated.

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