How Can I Set NOW() as the Default Value for a DateTime Column in MySQL?

Setting NOW() as Default Value for Datetime Datatype

When creating database tables, it can be beneficial to set default values for columns to ensure consistent data entry. For datetime data types, it is often desirable to use the current time as the default value.

Existing Table Modification Challenge

In MySQL, setting a default value of NOW() for an existing datetime column can be challenging. The error message encountered when attempting to set the default value to NOW() indicates that this is an invalid default value.

Solution for MySQL 5.6.5 and Above

Fortunately, MySQL 5.6.5 introduces the DATETIME data type, which allows for dynamic default values. To set the default value to NOW() while creating the table, use the following syntax:

CREATE TABLE users (
    registerDate DATETIME DEFAULT CURRENT_TIMESTAMP
)

Solution Prior to MySQL 5.6.5

For MySQL versions prior to 5.6.5, the TIMESTAMP data type must be used to automatically update the datetime value on record modification. However, it's worth noting that only one auto-updated TIMESTAMP field can exist per table.

CREATE TABLE users (
    registerDate TIMESTAMP
)

To prevent the timestamp value from being updated on UPDATE, add DEFAULT CURRENT_TIMESTAMP to the TIMESTAMP field definition:

CREATE TABLE users (
    registerDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

The above is the detailed content of How Can I Set NOW() as the Default Value for a DateTime Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!