mysql 自动更新时间

自动获取创建时间：timestamp not null default CURRENT_TIMESTAMP

自动获取更新时间：timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

mysql  datetime

MySQL导入数据库时报错：ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘return_create_time’ at row 1

导入数据中时间字段的格式为 ‘0000-00-00 00:00:00’，报错ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘return_create_time’ at row 1

原因中数据库的sql_mode包含 no_zero_date 模式，不支持‘0000-00-00 00:00:00’，可以使用以下方式进行修改

 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 |
±--------------±----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

临时解决方案：

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

永久解决方案：

添加到my.cnf里边 mysql 5.7版本的配置文件为：/etc/mysql/mysql.conf.d/mysqld.cnf

sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';