How to Set Default Values for MySQL Datetime Columns?

Setting Default Values for MySQL Datetime Columns

In SQL Server, the getdate() function is used to define a default value for a Datetime column. But in MySQL, the situation is different.

Prior to MySQL 5.6.5, it was not possible to set a default value for a Datetime column. However, you could achieve this functionality using a Timestamp column instead. In MySQL 5.x, the following syntax can be used:

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

This syntax ensures that the ts column will have a default value of the current timestamp upon record insertion.

Important Note:

In MySQL versions prior to 5.6.5, using CURRENT_TIMESTAMP as the default value for a Datetime column was not allowed. It is important to note that the default value will automatically reset to the current timestamp during record updates if you specify a column with CURRENT_TIMESTAMP ON as the default. This means that you must always include [your column name] = [your column name] (or another value) in your UPDATE statement to prevent the value from resetting to the current timestamp.

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