1. Error message

When executing the following SQL statement, an error message appears: error 1067 (42000): invalid default value for ‘end’_ time’

CREATE TABLE seckill1(
    `seckill_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Commodity inventory id',
     `name` varchar(120) NOT NULL COMMENT'product name',
     `number` int NOT NULL COMMENT'Stock quantity',
     `start_time` timestamp NOT NULL COMMENT'second kill start time',
     `end_time` timestamp NOT NULL COMMENT'second kill end time',
    `create_time` timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    PRIMARY KEY(seckill_id),
    key idx_start_time(start_time),
    key idx_end_time(end_time),
    key idx_create_time(create_time)
)ENGINE = InnoDB AUTO_INCREMENT = 1000 DEFAULT CHARSET = utf8 COMMENT = 'Spike inventory table';

The error message is as follows:

2. Solution

(1) View SQL_mode :

show session variables like '%sql_mode%

(2) Modify sql_mode(remove NO_ZERO_IN_DATE,NO_ZERO_DATE) :

 set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

(3) Re-execute the SQL statement in 1:
the table creation success is displayed.
the screenshot of the three steps is as follows:

3. Reasons for error reporting

(1) Error explanation: the first timestamp column in the
table (that is, the start_time row in the SQL statement of 1) (if it is not declared null or the default or on update clause is displayed) will automatically assign the default current_timestamp and on update current_timestamp attributes.
the timestamp column after the first one (that is, the end_time line in the SQL statement of 1) if it is not declared null or the default clause is displayed), the default ‘0000-00-00 00:00’ (zero timestamp) will be automatically allocated, which does not meet the no_zero_date in sql_mode and an error will be reported.
(2) solution (three) Note: the solution in 2 is only for the created table
method 1: execute select @ @ sql_mode, copy the queried value, delete the no_zero_date, and then execute set sql_mode = 'modified value'. This method only takes effect in the current session
method 2: execute select @ @ global.sq first l_Mode , copy the queried value and put no in it_ZERO_Delete date and execute set global SQL_Mode = 'modified value', this method takes effect in the current service, and becomes invalid after re MySQL service
method 3: open my.In the MySQL installation directory Ini or my CNF file, add the following line,

 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，　　　

Then restart mysql. This method is permanent