解决MySQL 5.7.9版本sql_mode=only_full_group_by问题

最新推荐文章于 2024-08-09 14:47:12 发布

原创最新推荐文章于 2024-08-09 14:47:12 发布 · 2.7k 阅读

1 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了解决MySQL中因sql_mode设置为only_full_group_by导致的查询错误的方法。通过调整sql_mode配置，可以避免在未使用GROUP BY的情况下包含非聚合列的查询错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.<init> 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>

If you want to affect on all client, please use “@@global”, for example:

SET @@global.sql_mode ="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";