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

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《关于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连接的操作权限和限制条件在连接成功后，就已经确定并存储起来，所以需要断开连接并重新获取操作权限和限制条件才可以复制成功，不然依然会报错

本篇关于《关于mysql数据库迁移，datetime遇到的坑》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！