docker 下修改 mysql sql_mode和配置文件解决创建表或导入sql时date和timestamp类型字段无法将默认值设置为0000-00-00或0000-00-00 00:00:00_docker mysql timestamp

docker 下修改 mysql sql_mode和配置文件，解决在虚拟机中从docker拉取mysql-5.7镜像（或最新版本），而本地mysql是5.6，在创建表或者导入sql文件时出现date和timestamp类型字段无法将默认值设置为0000-00-00或0000-00-00 00:00:00

[参考博客]https://blog.csdn.net/qq_31659985/article/details/80701769

问题描述

从docker上下载了mysql-5.7的镜像，创建并后台启动mysql-5.7容器(文中的命令均在root下执行的)，然后向数据库（mysql-5.6）导入SQL文件，报错如下：
1067 - Invalid default value for 'childdate’


后面发现，在创建表或者导入SQL时，date和timestamp类型字段无法将默认值设置为0000-00-00或0000-00-00 00:00:00。

分析

通过查询，发现这种报错多是mysql升级到5.7而引起的默认值不兼容的问题。时间字段，类型是date或datetime。可能是类型的默认值被限制了，查看 sql_mode。

解决办法

修改mysql容器配置文件中的sql_mode，此处我们使用docker安装mysql-5.7，挂载外部配置和数据，并修改虚拟机本地配置文件（映射容器中mysql的配置文件）中的sql_mode。

1. 创建并启动mysql容器（指定5.7版本）

docker run --name mysql-5.7 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
1

参数说明：
	-e: 为当前容器添加一个环境变量
	-p: 将容器中mysql端口映射到宿主机指定的端口中
	-d: 让MySQL容器在后台启动，不去占用前台的进程
1
2
3
4

2. 创建成功后查看当前运行的容器

docker ps
1

3. 进入容器，查看mysql-5.7容器的配置文件

docker exec -it mysql-5.7 /bin/bash

cat /etc/mysql/my.cnf

1
2
3
4

4. 可以看到此处包含两个目录下的文件，查看两个目录中是否有我们需要的配置文件，发现在mysql.conf.d下有该文件

cat /etc/mysql/mysql.conf.d/mysqld.cnf
1

复制该配置文件内容备用，以作为虚拟机本地配置文件。

5. 可以看到此处并没有sql_mode的配置，说明mysql使用了默认的配置，通过进入mysql查看

mysql -u root -p
1

回车输入密码，进入mysql控制台

6. 查看默认sql_mode配置，如下：
   输入如下命令查看

 show variables like '%sql_mode';
1

7. exit退出mysql控制台，将之前容器中mysql配置文件mysqld.cnf放入虚拟机本地建立的文件目录下，然后在 [mysqld]下面添加 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，并保存
   
   mysqld.cnf配置文件
   

8. 然后切换到命令行，输入exit退出容器，停止并删除mysql-5.7容器

docker stop mysql-5.7
docker rm mysql-5.7
1
2

9. 挂载外部配置和数据安装（在虚拟机上）
   （1）创建目录和配置文件
   命令方式 ：

mkdir /docker
mkdir /docker/mysql
mkdir /docker/mysql/conf
mkdir /docker/mysql/data

1
2
3
4
5
6

创建mysql.cnf配置文件

touch /docker/mysql/conf/mysqld.cnf

1
2
3

进入mysqld.cnf配置文件进行修改

辅助工具方式 ：如Xftp6+Notepad++（FTP插件）等工具，此处不做过多赘述。

（2）创建并启动容器

docker run --name mysql-5.7 --privileged=true -p 3307:3306 -v /docker/mysql/conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

1
2
3

参数说明：
	--privileged=true：容器内的root拥有真正root权限，否则容器内root只是外部普通用户权限
	
	-v /docker/mysql/conf/mysql.cnf:mysql.conf.d/mysqld.cnf：映射配置文件
	
	-v /docker/mysql/data:/var/lib/mysql：映射存储数据目录
1
2
3
4
5
6

10. 按照之前的步骤，重新进入当前运行的mysql-5.7容器

docker exec -it mysql-5.7 /bin/bash
1

11. 进入mysql

mysql -u root -p 123456
1

12. 查看此时的sql_mode参数是否通过本地配置文件被修改

show variables like '%sql_mode';
1

可以看到修改成功

13. 可以看到修改成功后，再将准备好的sql导入数据库，没有报错，成功导入。

提醒

如果我们直接拉取mysql-5.6版本镜像，并创建容器，此时不会出现该问题。

因为mysql-5.6的配置文件中sql_mode没有进行设置：

5.6可以正常运行：