sql模式设置引起的问题怎么解决
今天小编给大家分享一下sql模式设置引起的问题怎么解决的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。
1 报错类似如下
数据库错误: Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression 39 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.ss.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The error may exist in URL [jar:file:/usr/local/xxxxx.jar!/BOOT-INF/lib/summer-system-4.6.0.jar!/mapper/system/SuOrgProgramMapper.xml]
The error may involve com.ruoyi.system.mapper.SuOrgProgramMapper.selectSuOrgProgramList-Inline
The error occurred while setting parameters
SQL: select sop.*,so.org_name as orgName,sa.activity_name as activityName,sa.activity_name_en as activityNameEN, ss.student_id as matchedStudentId,ss.name as matchedStudentName, su.login_name as email, su.real_name AS firstName, su.nickname as nickname from su_org_program sop inner join su_activity sa on sop.activity_id=sa.activity_id inner join su_org so on so.org_id = sop.org_id left JOIN sys_user su ON sop.main_teacher_id = su.sub left join (select * from su_student_program where is_matched=1) ssp on ssp.org_program_id= sop.org_program_id left JOIN su_student ss on ssp.student_id=ss.student_id WHERE sop.org_id = ? GROUP BY sop.org_program_id
Cause: java.sql.SQLSyntaxErrorException: Expression 39 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.ss.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression 39 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.ss.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
2 解决办法
2.1 查看全局sql模式
使用如下m命令：
select @@global.sql_mode;
如下：
mysql> select @@global.sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@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 |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
2.2 设置全局sql模式
使用如下命令：
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
如下：
ysql> SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
2.3 查看当前sql模式
使用如下命令：
select @@sql_mode;
如下：
mysql> select @@sql_mode;+------------------------------------------------------------------------------------------------------------------------+| @@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 row in set (0.00 sec)mysql>
2.4 设置当前sql模式
使用如下命令：
set @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
如下：
mysql> set @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
3 永久生效的解决办法
3.1 找到my.cnf文件
可以使用如下命令查找，一般在 /etc/下或者 /etc/mysql/ 目录下
find /etc/ -name my.cnf
3.2 编辑my.cnf文件
vi /etc/mysql/my.cnf
然后增加以下内容：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
3.3 重启数据库服务
systemctl restart mysqld
以上就是"sql模式设置引起的问题怎么解决"这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注行业资讯频道。
