How Do I Set Default Values for DATETIME Columns in MySQL?

Setting Default Values for Datetime Columns

In the given table, two columns, registerDate and lastVisitDate, need their default values adjusted. The objective is to set registerDate to the current time and lastVisitDate to a specific value instead of the default NULL.

Using Modify Table

The provided ALTER TABLE statements, while attempting to modify the default values, encounter an error. This is because prior to MySQL 5.6.5, setting default values for datetime columns was not possible using the DEFAULT keyword.

Solution for Pre-5.6.5

For versions of MySQL prior to 5.6.5, consider using the TIMESTAMP data type, which automatically updates its value on record modification. However, only one auto-updated TIMESTAMP field is permitted per table.

Solution for MySQL 5.6.5 and Later

Starting with MySQL 5.6.5, the DATETIME data type supports dynamic default values. This allows you to set registerDate to the current time using:

CREATE TABLE users (
    registerDate DATETIME DEFAULT CURRENT_TIMESTAMP
)

Setting Default Values for lastVisitDate

To set the default value of lastVisitDate to a specific value (e.g., '0000-00-00 00:00:00'), you can use the following statement:

ALTER TABLE users ALTER COLUMN lastVisitDate DATETIME DEFAULT '0000-00-00 00:00:00'

By utilizing these approaches, you can configure the default values for the datetime columns in the users table to meet your desired requirements.

The above is the detailed content of How Do I Set Default Values for DATETIME Columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!