目录： 

　　错误信息

　　原因分析

　　解决方案

　　操作示例

---

---

 

• 错误信息

  [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 中的SQL 语法并不是严格按照SQL 95 标准的。在聚合操作时，group by 允许不包含所有select的非聚合 列.

　　  参数sql_mode='ONLY_FULL_GROUP_BY' 可以使SQL 严格按照SQL 95标准编写，但是同时此参数是存在缺陷的。一些delete 等非聚合操作，从java 客户端发起时，有可能会引起错误：[err] 1055.

• 解决方案
  简单的解决方案就是取消此参数设置。参数的设置方法如下:

  # 临时调整set @@sql_mode='参数列表,以逗号分隔'或者set global sql_mode='参数列表,以逗号分隔'# 永久调整  修改参数配置文件my.cnfsql_mode = '参数列表，不同参数之间以逗号分隔'

• 示例
  • 临时修改示例

    mysql> 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';Query OK, 0 rows affected (0.00 sec)mysql> exitBye[root@test ~]# mysql -DReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1814164Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select @@sql_mode;+------------------------------------------------------------------------------------------------------------------------+| @@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.00 sec)

  • 永久修改示例

    # 修改之前sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'# 修改之后sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'