报错如下:
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