Solve the problem of MySQL reporting Invalid default value for ''operate_time'' error

Execute the create table statement in the database

CREATE TABLE `sys_acl` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Authorization id',
 `code` varchar(20) NOT NULL DEFAULT '' COMMENT 'Authorization code',
 `name` varchar(20) NOT NULL DEFAULT '' COMMENT 'Authorization name',
 `acl_module_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Authorization module id where the permission is located',
 `url` varchar(100) NOT NULL DEFAULT '' COMMENT 'Requested url, can be filled with regular expression',
 `type` int(11) NOT NULL DEFAULT '3' COMMENT 'Type, 1: menu, 2: button, 3: other',
 `status` int(11) NOT NULL DEFAULT '1' COMMENT 'Status, 1: normal, 0: frozen',
 `seq` int(11) NOT NULL DEFAULT '0' COMMENT 'The order of permissions in the current module, from small to large',
 `remark` varchar(200) DEFAULT '' COMMENT 'Remarks',
 `operator` varchar(20) NOT NULL DEFAULT '' COMMENT 'Operator',
 `operate_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Last update time',
 `operate_ip` varchar(20) NOT NULL DEFAULT '' COMMENT 'The last updater's IP address',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

Report Invalid default value for 'operate_time' error

After checking the information, I found that MySQL 5.6 and later supported the datetime type, so I changed datetime to timestamp and successfully solved it.

The difference between datetime and timestamp is not particularly large. The main differences are as follows:

1. The storage methods of the two are different

For TIMESTAMP, it converts the time inserted by the client from the current time zone to UTC (Coordinated Universal Time) for storage. When querying, it is converted into the client's current time zone and returned. For DATETIME, no changes are made and it is basically input and output as is.

2. The time range that can be stored by the two is different

The time range that timestamp can store is: '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

The time range that datetime can store is: '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.

Summarize:

There is not much difference between TIMESTAMP and DATETIME except for the storage range and storage method.

Of course, TIMESTAMP is more appropriate for cross-time zone business.

Reference link: MYSQL-Difference between datatime and timestamp

Supplement: Solution to the "1067 - Invalid default value for 'UPDATE_TIME'" error message in MySQL

Since the field UPDATE_TIME of tmp_wrh_1 is of type timestamp, the default value is: '0000-00-00 00:00:00'

Right now:

`UPDATE_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Update time';

When operating on this table,

like:

alter table tmp_wrh_1 MODIFY column BUSINESS_TYPE varchar(5) comment 'hhr-service fee withdrawal'; -- Execution failed

An error message will appear: 1067 - Invalid default value for 'UPDATE_TIME'

Problem Analysis:

Because the timestamp type value range is: 1970-01-01 00:00:00 to 2037-12-31 23:59:59,

Therefore, the default value of the UPDATE_TIME field must be changed to a value between 1970-01-01 00:00:00 and 2037-12-31 23:59:59.

It is found that the default value can only be modified successfully after 1970-01-01 10:00:00. I don't know why!

Problem Solved:

alter table tmp_wrh_1 alter column update_time drop default;
alter table tmp_wrh_1 alter column UPDATE_TIME set default '1970-01-01 10:00:00';
alter table tmp_wrh_1 MODIFY column BUSINESS_TYPE varchar(5) comment 'hhr-service fee withdrawal'; --Execution successful

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.