How to Set Default Values for DATETIME Columns in MySQL?

Setting Default Values for Datetime Columns in MySQL

In MySQL, setting a default value for a Datetime column differs from SQL Server's getdate() function.

MySQL versions prior to 5.6.5 do not allow default values for DATETIME fields. However, you can use TIMESTAMP instead:

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

Inserting a row without specifying a timestamp will automatically populate it with the current time:

INSERT INTO test (str) VALUES ('demo');

Caution: Using CURRENT_TIMESTAMP as a default can lead to unexpected behavior during updates. To maintain the original timestamp, you must explicitly set the column to its current value:

UPDATE test SET ts = ts;

MySQL 5.6.5 and Later

MySQL 5.6.5 introduces support for default values for DATETIME fields:

CREATE TABLE test2 (
  str VARCHAR(32),
  dt DATETIME DEFAULT '2000-01-01 00:00:00'
);

This method is preferred over using TIMESTAMP for setting default values for datetime columns.

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