How to set default value for datetime type in MySQL

I encountered a problem when modifying the default datetime value through the navicat client.

The database table field type datetime originally defaults to NULL. When the default value is set to the current time through the interface, a prompt "1067-Invalid default value for 'CREATE_TM' " appears. However, this problem does not occur when creating a table. For example, the table creation statement:

CREATE TABLE `app_info1` ( 
 `id` bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID', 
 `app_id` varchar(30) DEFAULT NULL COMMENT 'Application code', 
 `app_name` varchar(30) DEFAULT NULL COMMENT 'Application name', 
 `package_name` varchar(100) DEFAULT NULL COMMENT 'Package name', 
 `version` int(11) DEFAULT NULL COMMENT 'Version number', 
 `version_name` varchar(20) DEFAULT NULL COMMENT 'Version name', 
 `icon_url` varchar(512) DEFAULT NULL COMMENT 'Icon address', 
 `download_url` varchar(512) DEFAULT NULL COMMENT 'Download URL', 
 `summary` varchar(512) DEFAULT NULL COMMENT 'Summary', 
 `desc` varchar(512) DEFAULT NULL COMMENT 'Description', 
 `app_status` int(4) DEFAULT '0' COMMENT 'Status 0: Available, 1: Deleted', 
 `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time', 
 `create_user` varchar(30) DEFAULT NULL COMMENT 'creator', 
 `modify_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Modification time', 
 `modify_user` varchar(30) DEFAULT NULL COMMENT 'Modifier', 
 PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Then I tried to modify the field directly through the script, which was also OK, as follows:

alter table ts_role 
MODIFY column create_tm datetime default CURRENT_TIMESTAMP COMMENT 'Creation time'

Of course, the MySQL I installed is 5.6 or above, and I haven't tried other versions.

The above method of setting the default value of the datetime type in MySQL is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:

Summary of the use of Datetime and Timestamp in MySQL
The difference and choice between datetime and timestamp in MySQL
A brief discussion on the datetime format when exporting table data from MySQL to Excel
The difference and usage of datetime and timestamp in MySQL
How to create a datetime type in a MySQL database

<<: Detailed explanation of how to find the location of the nginx configuration file

>>: Detailed explanation of how Node.js middleware works