修改mysql的分组报错Expression #1 of SELECT list is not in GROUP

​

方法一：

1、通过下方SQL语句查询：

SELECT @@sql_mode;
2、查看是否存在 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
3、如果存在上方字段，使用下方SQL语句：

set @@global.sql_mode
=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

方法二：

在mysql的安装目录下创建图中两个文件

**

my.ini文件内容：

**

#此数据库没有my.ini文件，当前文件为后来增加的

[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.

#该参数指定了安装 MySQL 的安装路径，填写全路径可以解决相对路径所造成的问题。
basedir = D:/Program Files/mysql-5.7.23-winx64
#该参数指定了 MySQL 的数据库文件放在什么路径下。数据库文件即我们常说的 MySQL data 文件。
datadir = D:/Program Files/mysql-5.7.23-winx64/data

port = 3306

server_id = …

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语法，对数据的校验等等
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

max_allowed_packet=300M

​my-default.ini文件内容：

[mysqld]
character-set-server=utf8mb4
[mysql]
default-character-set=utf8mb4
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysql]

设置mysql客户端默认字符集

default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306

设置mysql的安装目录

basedir=D:\Program Files\mysql-5.7.23-winx64

允许最大连接数

max_connections=200

服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

创建新表时将使用的默认存储引擎

default-storage-engine=INNODB