sql_mode=only_full_group_by问题

 报错如下：

 SQL: SELECT   t.dispatch_order_id,   t.dispatch_userid,   t.is_receive,   t.firstsec,   t.secondsec,   t.receive_userid,   t.dispatch_type,   t.work_order_id,   t.firstsec,   t.secondsec,   rr.app_username as receiveUsername,   dr.app_username as dispatchUsername,   w.user_name as customerUsername   FROM   (   SELECT   dispatch_order_id,   dispatch_userid,   is_receive,   receive_userid,   work_order_id,   dispatch_type,   ( UNIX_TIMESTAMP( first_waring_time ) - UNIX_TIMESTAMP( now( ) ) ) AS firstsec,   ( UNIX_TIMESTAMP( second_waring_time ) - UNIX_TIMESTAMP( now( ) ) ) AS secondsec   FROM   wyy_dispatch_order   ) t  LEFT JOIN wyy_role rr ON rr.app_userid = t.receive_userid   LEFT JOIN wyy_role dr ON dr.app_userid = t.dispatch_userid   LEFT JOIN wyy_work_order w ON w.work_order_id = t.work_order_id   WHERE   t.is_receive = 0     AND ((  t.firstsec < 30 AND t.firstsec>=0) or (t.secondsec <30 and t.secondsec>=0))     GROUP BY t.dispatch_order_id
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dev.rr.app_username' 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 #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dev.rr.app_username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：当sql_mode=only_full_group_by时，这主要在分组时，只能对该分组的字段进行查询，如果是查询其他字段要用联合 GROUP_CONCAT（字段名）进行。一般都是5.7版本的固有配置，更改配置即可。

 方案一：

1.查看sql_mode

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

2.去掉ONLY_FULL_GROUP_BY，重新设置值。

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';

方案二：修改配置

1.先可查找资源

find / -name my.cnf

效果如下：

2.修改配置

vi /etc/my.cnf

 添加这行配置sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.重启mysql

在任何目录下，都可使用该命令。

systemctl restart mysqld