How Can I Set Default Values for Datetime Columns in MySQL?

How to Assign Default Values to MySQL Datetime Columns

In SQL Server, the getdate() function sets default values for Datetime columns. MySQL offers an alternative approach for MySQL 5.x.

TIMESTAMP Data Type

MySQL previous to version 5.6.5 does not support default values for DATETIME columns. However, you can utilize the TIMESTAMP data type:

CREATE TABLE test (
  str VARCHAR(32),
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This creates a table with a ts column that automatically sets the default value to the current timestamp upon row insertion.

CURRENT_TIMESTAMP Keyword

In MySQL versions 5.6.5 and above, the CURRENT_TIMESTAMP keyword can be used to assign default values to DATETIME columns:

CREATE TABLE test (
  str VARCHAR(32),
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

Considerations

Updates: When using CURRENT_TIMESTAMP ON for a column, it must always be included in the UPDATE statement. Otherwise, the column value will be automatically updated with the current timestamp.

Caveat: In MariaDB 5.5.56, specifying CURRENT_TIMESTAMP without a value when updating a record will also reset the column value to the current timestamp.

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