MySQL5.7.9版本sql_mode=only_full_group_by问题的解决办法

mysql 5.7.9版本sql_mode=only_full_group_by问题

21-jun-2018 18:45:58.056 info [http-nio-8080-exec-8] org.springframework.beans.factory.xml.xmlbeandefinitionreader.loadbeandefinitions loading xml bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
21-jun-2018 18:45:58.078 info [http-nio-8080-exec-8] org.springframework.jdbc.support.sqlerrorcodesfactory. sqlerrorcodes loaded: [db2, derby, h2, hsql, informix, ms-sql, mysql, oracle, postgresql, sybase]
org.springframework.jdbc.badsqlgrammarexception:
### error querying database.  cause: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: in aggregated query without group by, expression #1 of select list contains nonaggregated column 'fmedia_insurance.product.unique_code'; this is incompatible with sql_mode=only_full_group_by
### the error may exist in file [/data/project/insurance-server/web-inf/classes/com/jufengad/insurance/server/mapping/insuranceratemapper.xml]
### the error may involve com.jufengad.insurance.server.dao.insuranceratemapper.querypricefactorbyproductcode-inline
### the error occurred while setting parameters
### sql: select get_age(product.unique_code) as age_data, get_sex(product.unique_code) as sex_data, max(rate.social_security) as social_data,    get_pay_period(product.unique_code) as pay_period_data, get_safeguard_period(product.unique_code) as safeguard_period_data   from insurance_rate rate  left join insurance_product_main product on rate.product_id=product.id  where rate.product_code=?
### cause: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: in aggregated query without group by, expression #1 of select list contains nonaggregated column 'fmedia_insurance.product.unique_code'; this is incompatible with sql_mode=only_full_group_by
; bad sql grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: in aggregated query without group by, expression #1 of select list contains nonaggregated column 'fmedia_insurance.product.unique_code'; this is incompatible with sql_mode=only_full_group_by
        at org.springframework.jdbc.support.sqlexceptionsubclasstranslator.dotranslate(sqlexceptionsubclasstranslator.java:95)
        at org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:73)
        at org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:81)
        at org.mybatis.spring.mybatisexceptiontranslator.translateexceptionifpossible(mybatisexceptiontranslator.java:73)
        at org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke(sqlsessiontemplate.java:371)
        at com.sun.proxy.$proxy43.selectone(unknown source)

解决方法 ：

执行set global sql_mode = ''; 把sql_mode 改成非only_full_group_by模式。

验证是否生效 select @@global.sql_mode 或 select @@sql_mode

mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------
-----------------------------------------------+
| @@global.sql_mode
                                               |
+-------------------------------------------------------------------------------
-----------------------------------------------+
| only_full_group_by,strict_trans_tables,no_zero_in_date,error_for_division_by_z
ero,no_auto_create_user,no_engine_substitution |
+-------------------------------------------------------------------------------
-----------------------------------------------+
1 row in set (0.04 sec)

mysql>

mysql> set sql_mode = 'strict_trans_tables,no_zero_in_date,error_for_division_by
_zero,no_auto_create_user,no_engine_substitution';
query ok, 0 rows affected, 1 warning (0.04 sec)

mysql>