mysql8.0 sql_mode操作

前置条件:

• mysql版本: 8.0
• 安装方法: docker安装
• 使用状态: PC本地可以远程连接
• docker 安装mysql:8.0 , docker 安装mysql:5.7

问题及其解决方案:

• 1 date数据类型 不能置为0000-00-00 00:00:00
  • 已尝试方案:mysql5.7的sql_mode操作, 未能解决问题
  • 通过各种查询, 了解到 8.0的sql_mode以上已经取消了NO_AUTO_CREATE_USER这个关键字，尝试在mysql5.7的sql_mode操作, 将这个关键字删除, 以下为实际操作

[root@ecs-s6-medium-2-linux-20191113090041 conf]# cat mysql.conf 
[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
# default: sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# modeified: 
sql_mode= STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/

[root@ecs-s6-medium-2-linux-20191113090041 conf]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
7141cbf9d051        mysql:8.0           "docker-entrypoint..."   27 minutes ago      Up 21 minutes       33060/tcp, 0.0.0.0:3307->3306/tcp   mysql8.0
251e5b8eaced        mysql:5.7           "docker-entrypoint..."   4 hours ago         Up 21 minutes       33060/tcp, 0.0.0.0:3309->3306/tcp   mysql5.7

# 重启docker服务(在docker run 时 带有参数 --restart=always, 所以重启docker服务, mysql也会重启)
[root@ecs-s6-medium-2-linux-20191113090041 conf]# service docker restart
Redirecting to /bin/systemctl restart docker.service

# 检查是否已经启动
[root@ecs-s6-medium-2-linux-20191113090041 conf]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
7141cbf9d051        mysql:8.0           "docker-entrypoint..."   27 minutes ago      Up 5 seconds        33060/tcp, 0.0.0.0:3307->3306/tcp   mysql8.0
251e5b8eaced        mysql:5.7           "docker-entrypoint..."   4 hours ago         Up 4 seconds        33060/tcp, 0.0.0.0:3309->3306/tcp   mysql5.7

[root@ecs-s6-medium-2-linux-20191113090041 conf]#