How to Set a Default Value for a MySQL Datetime Column?

Creating a Default Value for a MySQL Datetime Column

In MySQL, setting a default value for a Datetime column can be achieved using the TIMESTAMP data type. While SQL Server uses getdate() for this purpose, MySQL does not have an equivalent function for DATETIME fields.

However, you can utilize the TIMESTAMP data type to create a default value that automatically populates the field with the current timestamp upon row insertion. Here's how:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

In this example, the ts column in the test table is defined as a TIMESTAMP field and given a default value of CURRENT_TIMESTAMP. When a new row is inserted into the table without explicitly specifying a value for ts, the default value will be automatically populated with the current system time.

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2023-03-08 15:23:45 | 
+------+---------------------+
1 row in set (0.00 sec)

As you can see, the ts column in the test table has been automatically populated with the current timestamp when the new row was inserted.

Important Note:

For default values in DATETIME columns, MySQL 5.6.5 and later versions now support the CURRENT_TIMESTAMP function. However, in earlier versions, this feature is only available for TIMESTAMP columns.

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