处理docker部署的mysql8在执行sql时报错[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause...

问题描述：

环境：本地docker部署的mysql8，系统Ubuntu 9.4.0-1ubuntu1~20.04.1
执行sql报错
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

---

解决方案：

查了一下mysql [Err] 1055 解决办法：
执行sql
SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,‘’));
可本次mysql运行解决，重启mysql失效。

在配置mysql.cnf的[mysqld]内新增sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 可长期有效
如果创建容器的时候docker -v挂载了位置，修改配置文件即可，悲剧的是当时我没有设置挂载位置。。
然后问题来了，得进docker内修改配置
发现docker内没有常用编辑工具vi、vim
安装apt install vim 提示没有apt 。。。
如下

bash-4.4# cat /proc/version
Linux version 5.15.0-46-generic (buildd@lcy02-amd64-007) (gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, GNU ld (GNU Binutils for Ubuntu) 2.34) #49~20.04.1-Ubuntu SMP Thu Aug 4 19:15:44 UTC 2022
bash-4.4# vim
bash: vim: command not found
bash-4.4# apt install vim
bash: apt: command not found
bash-4.4# apt-get install vim
bash: apt-get: command not found

bash-4.4# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

最终使用sed在第四行追加到位，重启mysql8容器解决问题

bash-4.4# sed -i '4a sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' /etc/my.cnf

sed -i ‘Na hello_world’ fileName
-i ：直接修改读取的文件内容，而不是输出到终端。
N是在N行后面追加一行"hello_world"
a表示追加