mysql5.7分组查询异常处理 this is incompatible with sql_mode=only_full_group_by

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'lenovo_face_check_module.a.create_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in file [G:\workspace\lenovo-face-module\src\main\webapp\WEB-INF\classes\mappings\modules\sys\FaceHistoryDao.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT DATE_FORMAT(create_date, '%Y-%m-%d') as date, count(distinct emp_id) as count         FROM tf_project_face_history a LEFT JOIN tf_project_emp o ON o.id = a.emp_id LEFT JOIN tf_project_device d ON d.id = a.location_id          WHERE a.location_id = ? AND a.create_date >= ? AND a.create_date <= ? AND a.face_id != ? AND d.id = ? AND d.device_name LIKE ? AND d.address = ? AND d.device_enable = ? AND d.password = ? AND d.expire_time = ? AND d.device_type = ? AND d.mac_address = ? and emp_id is not null          GROUP BY date ORDER BY a.create_date DESC
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'lenovo_face_check_module.a.create_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'lenovo_face_check_module.a.create_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)

产生原因: 管理平台dashboard展示聚合查询出现异常,linux下mysql5.7(非docker安装)

解决:  命令行/图形化工具运行 : select @@global.sql_mode   查看全局配置

  ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

原因明确, mysql5.7默认配置

 运行 :  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';

问题完美解决,.  

使用docker安装的小伙伴,

在 [mysqld]和[mysql]下添加

 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的配置文件挂载到docker容器里面:

services:
  data:
    image: mysql:5.7
    volumes:
      - "./datadir:/var/lib/mysql"
      - "./mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf"
      - "./data-update.sql:/data-update.sql"
    environment:
      - MYSQL_ROOT_PASSWORD=******
    ports:
      - "3306:3306"
    restart: always
    command: --default-authentication-plugin=mysql_native_password

即可解决,楼主是在linux下宿主机mysql遇到的问题,docker需要各位老哥动动手