Can MySQL 4.0 Tables Have Multiple TIMESTAMP Columns with Automatic Updates?

Overcoming MySQL 4.0 Restrictions on Multiple TIMESTAMP Columns

Background:

When creating MySQL 4.0 tables, users often encounter an error while attempting to define both a Created and LastUpdated timestamp column with default values of CURRENT_TIMESTAMP. This is because the default settings for timestamp columns in MySQL 4.0 restrict the presence of only one such column.

Question:

Can users have both a Created and LastUpdated timestamp column in MySQL 4.0, or must the LastUpdated field be manually updated during each transaction?

Answer:

Early Versions:

Prior to MySQL 5.5, the restriction was in place. Only one TIMESTAMP column could be defined with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP clause. Therefore, users had to manually update the LastUpdated field during every transaction.

MySQL 5.5:

The documentation for MySQL 5.5 clarified that it was still not possible to have one timestamp column as the default value and another as the auto-update value.

MySQL 5.6.5 and Beyond:

However, significant changes were introduced in MySQL 5.6.5. The restriction on having multiple TIMESTAMP columns with automatic initialization or updates was lifted. As such, in MySQL versions 5.6.5 and above, users can define both a Created and LastUpdated timestamp column with the desired default values. This removes the need for manual updates during transactions.

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