临时和永久设置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容器。
-
先正常启动MySQL容器,不挂载配置文件。
如果挂载了配置文件,会启动不成功,报错Failed to access directory for --secure-file-priv
-
在宿主机上创建一个目录来存储
my.cnf
,比如/data/mysql_cnf
-
运行
docker cp
命令来将MySQL容器内的/etc/mysql/my.cnf
复制到宿主机上:docker cp mysql:/etc/mysql/my.cnf /data/mysql_cnf
-
修改启动MySQL容器的脚本,增加挂载MySQL配置文件:
-v /data/mysql_cnf/my.cnf:/etc/mysql/my.cnf
-
重启Docker容器
如果重启Docker容器时报错name already exists
,则需要:
# 检查mysql容器是否存在
docker ps -a | grep mysql
# 强制删除mysql容器
docker rm -f mysql
# 如果还不行,则需要再断开mysql的docker网络
docker network disconnect -f bridge mysql
-
运行
docker inspect
命令查找Mounts
来检查容器挂载 -
然后就可以修改宿主机上的
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"
- 修改后重启MySQL容器
不同MySQL版本默认sql_mode的区别
MySQL版本 | 默认sql_mode |
---|---|
MySQL 5.6 | NO_ENGINE_SUBSTITUTION |
MySQL 5.7 | 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 |
MySQL 8.0 | ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION |
总体来说,MySQL的默认sql_mode越来越严格。