MySQL 5.7 报错：1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

发表于 | 分类于 ， | 评论数： | 热度： ℃ | 热度： ℃

  通过 Docker 安装的 MySQL 5.7，发现使用GROUP BY语句时或报错，记错下处理过程：

  MySQL 5.7 的安装可参考 前端学习 Docker 之旅（四）—— 安装 MySQL 并启动、连接，修改时区。

1、进入容器

1
docker exec -it mysql-5.7 /bin/bash

2、进入 MySQL 的配置文件夹

  通过 docker 安装的 MySQL 5.7 配置文件为/etc/mysql/mysql.conf.d/mysqld.cnf

1
2
cd  /etc/mysql/mysql.conf.d
cat mysqld.cnf

  可以看看有没有vi、vim，没有的话可以通过echo编辑mysqld.cnf，命令格式为echo '' > fileName，引号内为文件内容。
  将上一步 cat 得到的原有配置文件拷贝到编辑器，在配置文件中的[mysqld]下添加一行：

1
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

  在上述编辑后的内容前后加上命令的内容，并拷贝到 docker 镜像内的终端执行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
echo '# 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, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# 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, version 2.0, 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]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
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' > mysqld.cnf

3、重启 docker 镜像

  完成上述步骤后可以cat mysqld.cnf看下是否已保存成功，重启 docker 镜像后就可以使用GROUP BY语句啦。

1
docker restart mysql-5.7

参考资料：

mysql5.7 报错1055:Expression #1 of SELECT list is not in GROUP BY clause and contains non

以上