临时和永久设置MySQL sql_mode非容器方式和容器方式

文章目录

  • 临时和永久设置MySQL sql_mode
    • 前言
    • 查看sql_mode
    • 临时修改sql_mode
    • 永久修改sql_mode
    • 永久修改sql_mode(容器方式)
    • 不同MySQL版本默认sql_mode的区别
    • 参考文档

临时和永久设置MySQL sql_mode

前言

MySQL的sql_mode对SQL执行有重要影响,且不同版本的MySQL的默认sql_mode也不同。

如果你遇到原来可以执行的SQL,在数据库重启后不能执行了,或者换了一个数据库后就不能执行了。一般是由于sql_mode设置不同引起的。

查看sql_mode

查看全局sql_mode:

SELECT @@global.sql_mode;

查看会话sql_mode:

SELECT @@session.sql_mode;

临时修改sql_mode

修改ONLY_FULL_GROUP_BY兼容问题示例:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';

修改sql_mode后不需要重启数据库,但是客户端需要重新连接才会生效。

永久修改sql_mode

编辑/etc/mysql/my.cnf,在[mysqld]下添加:

sql_mode="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION"

修改后需要重启数据库。

永久修改sql_mode(容器方式)

如果MySQL数据库是以Docker容器方式安装(以mysql:8.0为例)的,则需要将MySQL配置文件/etc/mysql/my.cnf挂载到宿主机上,再在宿主机上修改my.cnf,再重启MySQL容器。

  1. 先正常启动MySQL容器,不挂载配置文件。

    如果挂载了配置文件,会启动不成功,报错Failed to access directory for --secure-file-priv

  2. 在宿主机上创建一个目录来存储my.cnf,比如/data/mysql_cnf

  3. 运行docker cp命令来将MySQL容器内的/etc/mysql/my.cnf复制到宿主机上:

    docker cp mysql:/etc/mysql/my.cnf /data/mysql_cnf
    
  4. 修改启动MySQL容器的脚本,增加挂载MySQL配置文件:

    -v /data/mysql_cnf/my.cnf:/etc/mysql/my.cnf
    
  5. 重启Docker容器
    如果重启Docker容器时报错name already exists,则需要:

# 检查mysql容器是否存在
docker ps -a | grep mysql

# 强制删除mysql容器
docker rm -f mysql

# 如果还不行,则需要再断开mysql的docker网络
docker network disconnect -f bridge mysql
  1. 运行docker inspect命令查找Mounts来检查容器挂载

  2. 然后就可以修改宿主机上的my.cnf,在[mysqld]下添加:

sql_mode="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION"
  1. 修改后重启MySQL容器

不同MySQL版本默认sql_mode的区别

MySQL版本默认sql_mode
MySQL 5.6NO_ENGINE_SUBSTITUTION
MySQL 5.7ONLY_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
MySQL 8.0ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION

总体来说,MySQL的默认sql_mode越来越严格。

Logo

权威|前沿|技术|干货|国内首个API全生命周期开发者社区

更多推荐