本文主要解决了使用docker安装mysql8.0/5.7版本。在执行sql语句提示报错sql_mode=only_full_group_by
异常信息
Cause: java.sql.SQLSyntaxErrorException: expression #2 of SELECt list is not in GROUP BY clause and contains nonaggregated column ‘wechatworkx.u.user_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘wechatworkx.u.user_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
安装，启动见这里
https://www.runoob.com/docker/docker-install-mysql.html
MySql从5.7版本开始默认开启only_full_group_by规则，
规则核心原则如下，没有遵循原则的sql会被认为是不合法的sql
不允许select后面的非聚合列不出现在group by中
不允许name的存在
示例sql
SELECT
id,
name
FROM
table_name
GROUP BY
id
解决方案
根据实际情况选择方案解决
第3中方案比较简单
参考教程网址，这个教程应该是5.7版本的
https://zhuanlan.zhihu.com/p/103283746
下面可以作为mysql8版本教程，主要区别是sql_mode中没有NO_AUTO_CREATE_USER
查看当前状态
SELECt @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
查看结果，主要是ONLY_FULL_GROUP_BY影响，需要将这个删除，
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
执行sql修改
set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';方案2.通过将本地mysql配置文件，挂载到docker服务中，重启服务还是生效，需要删除容器重新运行，mysql数据会丢失，注意备份数据
参考教程
https://blog.csdn.net/qq_31659985/article/details/80701769
https://blog.csdn.net/zhuyu19911016520/article/details/104707595
- 查看mysql服务名
docker ps -a
2. 进入到docker，mysql服务中，mysql-test为服务名，需要根据实际情况替换
docker exec -it mysql-test bash
- 查看msyql的配置文件，获取读取配置文件夹位置
cat /etc/mysql/my.cnf
4. 退出docker，
exit
- 在liunx服务器上创建mysqld.cnf配置文件
注意这里如果是在win电脑上编辑，然后上传到liunx服务器，见下面的mysqld.cnf无权限，读取失败，注意点进行修改文件权限，
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql #log-error = /var/log/mysql/error.log # By default we only accept connections from localhost #bind-address = 127.0.0.1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #如果要更改docker mysql的端口，请先修改mysql.cnf文件中的端口 port=3306 #从 show variables like '%sql_mode'; 里面复制出来，并去掉了 only_full_group_by sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
- 删除mysql容器
mysql所有数据都会丢失，注意备份数据
docker stop mysql-test docker rm mysql-test
- 启动命令增加将liunx配置文件映射到docker，mysql文件夹中。
-v 挂载文件
/home/docker/mysql/mysqld.cnf liunx服务器文件路径
:/etc/mysql/conf.d/mysqld.cnf 挂载到docker，mysql中文件路径
docker run -itd -p 3306:3306 --name mysql-test -v /home/docker/mysql/mysqld.cnf:/etc/mysql/conf.d/mysqld.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql
- 执行sql查看当前状态，没有ONLY_FULL_GROUP_BY表示成功
SELECT @@GLOBAL.sql_mode;方案3.直接进入进入到mysql容器中，新建mysqld.cnf配置文件，好处是直接重启容易生效，不用备份数据，但是删除容器之后需要再次操作
执行方案2中第1,2,3,5步
需要在docker中的mysql执行上传或编辑，需要安装相关依赖
上传文件提示rz、sz命令找不到或不成功： 执行命令：apt-get update && apt-get install lrzsz vi命令找不到 执行命令：apt-get update && apt-get install vim
然后将win电脑编辑的文件上传到docker中mysql文件夹中
修改文件权限
chmod 644 mysqld.cnf
停止mysql之后在启动mysql
docker stop mysql-test docker start mysql-test
执行方案2中第9步
异常错误发生错误，具体看日志，查看日志中error和自己操作步骤相关的关键字，然后网上进行搜索相关教程
使用命令查看日志
docker logs -f mysql-testmysqld.cnf无权限，读取失败
查考教程
https://blog.csdn.net/exeron/article/details/120982051
mysqld: File ‘/etc/mysql/conf.d/mysqld.cnf’ not found (OS errno 13 - Permission denied)
mysqld: [ERROR] Stopped processing the ‘includedir’ directive in file /etc/mysql/my.cnf at line 29.
原因是，在win编辑了mysqld.cnf然后上传到liunx服务器中，然后在mysql启动命令中进行了挂载，这个时候liunx服务器是什么文件权限，在mysql中也是什么权限，
linux中
容器中msyql，前面有命令进入到docker容器中查看
不要设置权限为777，具体见下面
chmod 644 mysqld.cnfmysql日志中忽略文件
mysqld: [Warning] World-writable config file ‘/etc/mysql/conf.d/mysqld.cnf’ is ignored.
原因是mysqld.cnf，权限被设置为777，对太高权限的文件进行忽略
