关于mysql数据库迁移，datetime遇到的坑

工作中经常会遇到迁移数据库。
如果用phpmyadmin导入，数据库小还能解决，如果太大就麻烦了，不是超时，就是文件太大，不允许导入。
所以我就选择使用Navicat For Mysql进行数据库的复制

在这个过程中，我经常会遇到一个问题：

ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

经过查询资料，发现原因是在MySQL中，timestamp类型的合法区间是1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC，而在存储是，会先将你插入的数据转换为UTC时间，然后存储起来，读取的时候，再转换为你的本地时间。由于我的时区为东八区，因此转换后就变为了1970-01-01 00:00:00 UTC，成为了非法时间。（摘自MySQL时间类型和模式）

MySql严格模式

mysql> 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.00 sec)

其中NO_ZERO_IN_DATE,NO_ZERO_DATE两个模式，限制datetime的时间格式必须合法，所以

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTOO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like "%sql_mode%";                                                             
+---------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                        |
+---------------+--------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

重新设置模式，将NO_ZERO_IN_DATE,NO_ZERO_DATE去掉

此时应特别注意，用Navicat For Mysql复制数据库之前，必须先断开当前服务器的连接，并重新连接（不能重启Mysql,因为重启过后sql mode会被my.ini重置,恢复成原来模式）。
因为Mysql连接的操作权限和限制条件在连接成功后，就已经确定并存储起来，所以需要断开连接并重新获取操作权限和限制条件才可以复制成功，不然依然会报错