dha,y酷网,超前支架
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>
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
网友评论