记录sql_mode中ONLY_FULL_GROUP_BY报错问题

项目场景：

Mysql数据库 8.0+版本，安装完成之后，项目代码运行报错 SELECT list is not in GROUP BY clause and contains nonaggregated column ‘solo.aa.oId’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

---

问题描述

当按照系统指引安装完成Mysql 8.0+版本之后，启动Flask项目, 发现 项目中的SQL语句报错了，导致500错误

---

原因分析：

查询了许多文档，原来，这个问题出现在MySQL5.7后版本上，默认的sql_mode值

---

解决方案：

解决方案挺多，分享Windows上一个比较实用的处理方案
1.首先查看Mysql安装目录下是否有my.ini文件
2.如果没有my.ini文件需要创建一个ini文件,首先找到Mysql安装目录 （目录默认地址C:\Program Files\MySQL\MySQL Server 8.0），创建一个txt文件，里面内容为

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = C:\Program Files\MySQL\MySQL Server 8.0
datadir = C:\Program Files\MySQL\MySQL Server 8.0\data
port = 3306
server_id = 1
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

注意：需要将文中 basedir datadir 改为自己的安装目录地址

打开任务管理器，查看Mysql服务名称

打开CMD，进入 mysql安装路径

删除当前服务，需要输入自己的名称

sc delete MySQLD80

初始化Mysql

mysqld --initialize-insecure --user=mysql

创建一个新的Mysql服务

# 创建mysql服务
mysqld --install "MySql" --defaults-file="C:\Program Files\MySQL\MySQL Server 8.0\my.ini"

完成之后再次打开任务管理器 就可以看到我们创建的Mysql服务项，如果原来的Mysql80还是存在 可以右击 进行停止服务 就会消失

重新启动Mysql服务

net start MySql

在到CMD内输入

mysql -u root -p

这个时候会提示你输密码，当前是没有密码的，直接回车就可以进入mysql

进入mysql之后重新修改密码为 root

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

输入 exit; 退出 在重新 进入 输入刚刚的密码 root 就可以了

到这里 MySQL 内的my.ini文件创建完毕 ，然后再到文档内
[mysqld] 下面输入

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

然后重新启动Mysql服务 就可以解决问题了