Mysql err 1055

目录： 

　　错误信息

　　原因分析

　　解决方案

　　操作示例

---

---

 

• 错误信息

  [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.cnf
  sql_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> exit
    Bye
    [root@test ~]# mysql -D
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1814164
    Server 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 its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    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'