mysql [err] 1055错误解决

前言

在使用mysql是在navicat中查询一条简单语句使用了select * from table虽然查询成功了,但是报了
[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错误.

错误原因

在MySQL5.7之后，sql_mode中默认存在ONLY_FULL_GROUP_BY，SQL语句未通过ONLY_FULL_GROUP_BY语义检查所以报错。

ONLY_FULL_GROUP_BY模式要求select语句中查询出来的列必须是明确的（其他语句也是一样）。

以SQL语句select columes from table group by list为例：columns必须是聚集函数或者在group by后的表达式list中，并且list中必须包含主键，否则也会报错。

insert、update、delete语句都会报错（但不影响SQL语句的执行），因为这三种语句执行之前也会执行查询操作。

以主键为id的表为例：

#该SQL执行成功，因为count是聚集函数
SELECT count(1) FROM customer GROUP BY `name`;

#该SQL执行失败，因为*中包含主键id，而group by后的表达式中并没有包含id
SELECT * FROM customer GROUP BY `name`

#该SQL执行成功，因为name包含在group by后的表达式中
SELECT name FROM customer GROUP BY `name`

#该SQL执行失败，因为contact没有包含在group by后的表达式中
SELECT name, contact FROM customer GROUP BY `name`

查询mysql版本

mysql> select @@version;
+-----------------------------+
| @@version                   |
+-----------------------------+
| 5.7.28-0ubuntu0.18.04.4-log |
+-----------------------------+
1 row in set (0.00 sec)

查询sql_mode模式

mysql> show VARIABLES LIKE '%sql_mode%';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

解决方案

1. 临时解决

执行sql语句使sql_mode的模式去掉ONLY_FULL_GROUP_BY,但是重启服务会失效

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

2. 永久解决方案:修改mysql配置文件

注意: 在ubuntu 18中的mysql配置文件需要修改mysqld.cnf, 其他系统的其他mysql版本需要根据特定情况查找需要修改的配置文件,如:windows中一般是my.ini,linux中可能是my.cnf

/etc/mysql/mysql.conf.d/mysqld.cnf

在[mysqld]下面增加如下内容

[mysqld]
#取消错误: [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
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启服务

systemctl restart mysql

查看端口占用情况

netstat -lnp

root@VM-16-14-ubuntu ~# netstat -lnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...
tcp6       0      0 :::3306                 :::*                    LISTEN      775/mysqld
...

验证: 再次在navicat中select * from table查询,已没有错误.

最后编辑于：2020.01.23 02:10:37