sql模式设置引起的问题怎么解决

发布时间:2022-05-16 09:48:46 作者:iii
来源:亿速云 阅读:260

今天小编给大家分享一下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模式设置引起的问题怎么解决”这篇文章的所有内容,感谢各位的阅读!相信大家阅读完这篇文章都有很大的收获,小编每天都会为大家更新不同的知识,如果还想学习更多的知识,请关注亿速云行业资讯频道。

推荐阅读:
  1. 解决一个因Bitmap引起的OOM问题
  2. 如何解决python升级引起pip执行错误的问题

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

sql

上一篇:JavaScript的Symbol类型、隐藏属性、全局注册表实例分析

下一篇:自动生成代码controller tool怎么使用

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》