CREATE TABLE seckill(
-> `seckill_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '商品库存ID',
-> `name` VARCHAR(120) NOT NULL COMMENT '商品名称',
-> `number` int NOT NULL COMMENT '库存数量',
-> `start_time` TIMESTAMP NOT NULL COMMENT '秒杀开始时间',
-> `end_time` TIMESTAMP NOT NULL COMMENT '秒杀结束时间',
-> `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-> 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='秒杀库存表';
运行时报 ERROR 1067 (42000): Invalid default value for 'end_time'
(对TIMESTAMP 类型的子段如果不设置缺省值或没有标志not null时候在创建表时会报这个错误)
这是因为sql_mode中的NO_ZEROR_DATE导制的,在strict mode中不允许'0000-00-00'作为合法日期
使用下面的命令查看sql_mode
mysql>show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
将上面的NO_ZERO_DATE改为下面的 ALLOW_INVALID_DATES
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
上面的设置是临时设置,在重新登陆后,该设置又恢复为NO_ZERO_DATE