Shulou(Shulou.com)06/01 Report--

Version:mysql5.7

Execute sql:

CREATE TABLE seckill ( `seckill_id` BIGINT NOT NUll AUTO_INCREMENT COMMENT 'Item inventory ID',`name` VARCHAR(120) NOT NULL COMMENT ' Item name',`number` int NOT NULL COMMENT 'Inventory quantity',`start_time` TIMESTAMP NOT NULL COMMENT ' Spike start time',`end_time` TIMESTAMP NOT NULL COMMENT 'Spike end time',`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create 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 ='seconds kill library save table';

Execution Results:ERROR 1067 (42000): Invalid default value for 'end_time'

The reason is:mysql5.7 explicit_defaults_for_timestamp parameter is OFF by default, which conflicts with sql_mode NO_ZERO_DATE

View the explicit_defaults_for_timestamp and sql_mode variables by executing mysql>show variables;

explicit_defaults_for_timestamp = OFF

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

Analysis process:

1) By default, if the TIMESTAMP column does not have a null attribute displayed, then the column will be automatically added with a not null attribute (while other types of columns that do not have a null attribute displayed will allow null values). If a null value is inserted into this column, the value of the column will be automatically set to the current timestamp value.

2) The first TIMESTAMP column in the table, if no null attribute or default value is specified, and no ON UPDATE statement is specified. Then the column is automatically appended with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

3) Other TIMESTAMP columns after the first TIMESTAMP column, if no null attribute is specified and no default value is specified, then the column is automatically added with the DEFAULT '0000-00 -00 00:00:00' attribute. If no value is specified for the column in the insert statement,'0000-00-00 00:00:00' is inserted into the column without warning.

Solution:

mysql>set @@explicit_defaults_for_timestamp = 1;

1) At this time, if the TIMESTAMP column does not display the specified not null attribute, then the default column can be null. In this case, when inserting null value into the column, null will be recorded directly instead of current timestamp.

2) The DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes are not automatically added to the first TIMESTAMP column in the table unless you indicate so when creating the table.

3) If the TIMESTAMP column is appended with the not null attribute and no default value is specified. If a record is inserted into the table but no value is specified for the TIMESTAMP column, an error will be reported if strict sql_mode is specified. If strict sql_mode is not specified,'0000-00-00 00:00:00' is inserted into the column and a warning is generated.

NO_ZERO_DATE:

Time values in the format '0000-00- 00 00:00:00' are not allowed in this mode;

Solution:

mysql>set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Remove NO_ZERO_IN_DATE,NO_ZERO_DATE

Restart the server and execute the create statement normally.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.