MySQL的NO_ZERO_DATE

报错

        
        MYSQL
      
mysql> alter table wp_comments add column comment_qq_id varchar(20) after user_id;
ERROR 1067 (42000): Invalid default value for 'comment_date'
mysql> alter table wp_comments add column comment_qq_id varchar(20) after user_id;
ERROR 1067 (42000): Invalid default value for 'comment_date'

查看’comment_date’怎么回事

        
        MYSQL
      
mysql> show columns from wp_comments;
mysql> show columns from wp_comments;

Field	Type	Null	Key	Default	Extra
comment_date	datetime	NO		0000-00-00 00:00:00	-

对timestamp/datetime类型的字段如果不设置缺省值或没有标志not null时候在创建表时会报这个错误这是因为sql_mode中的NO_ZEROR_DATE导制的，在strict mode中不允许'0000-00-00’作为合法日期

解决方法

1. 查看sql_mode

        
        MYSQL
      
mysql>  show variables like '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_ENGINE_SUBSTITUTION

2. 更改slq_mode

把 NO_ZERO_DATE 改为 ALLOW_INVALID_DATES

        
        MYSQL
      
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION