欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-09 22:18:57
mysql 5.7.9版本sql_mode=only_full_group_by问题 21-jun-2018 18:45:58.056 info [http-nio-8080-exec-8] o...

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>