搜索
简帛阁>技术文章>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
目录1报错类似如下2解决办法21查看全局sql模式22设置全局sql模式23查看当前sql模式24设置当前sql模式3永久生效解决办法31找到mycnf文件32编辑mycnf文件33重启数据库服务1
背景执行mvncleantest命令提示部分包不存在,但通过eclipseclean操作后可以执行mvntest命令解决方法mvnclean操作为清空编译class文件,test的话,会去编译te
解决session阻塞问题办法:在session操作完成后调用session_write_close()即可避免此问题;案例一:使用session过程中,在开启session后,同一浏览器,执行同
数据库安装包中,我们通过osqlexe这个工具来对相关数据库脚本进行更新,昨天突然发现安装包报错了,说脚本错误,但我们将脚本拿到数据库查询分析器中执行,一切OK。问题出在哪里呢?通过使用osql单
案例环境:操作系统版本:WindowsServer2008R2StandardSP1数据库版本:MicrosoftSQLServer2012(SP1)11030000(X64)案例介绍:由于不能将生产
更新:这个问题是SystemDataSqlClient一个bug引起,详见坑暗花明:又遇NETCore中SystemDataSqlClient查询缓慢问题最近遇到一个非常奇特问题,在一个ASP
之前写过一篇博客“SQLSERVER中关于OR会导致索引扫描或全表扫描浅析”,里面介绍了OR可能会引起全表扫描或索引扫描各种案例,以及如何优化查询条件中含有ORSQL语句几种方法,其实还有一些
编码规则是utf8,如网页头中的:<metahttpequiv"ContentType"content"text/html;charsetUTF8"/>那么js文件中如果有中文输出就会出现
冲突1使用maven管理项目时可能会遇到包冲突情况比如:log4joverslf4jjar和slf4jlog4j12jar这两个包同时一起运行时就会有问题。2这种冲突可能是显式依赖导致,也可能是隐
在发送短信息验证码时候要用到js设置时间倒序问题:有时候这种常规写法会导致js失效,试了很多方法才找到问题所在,可能是因为js版本过低导致。setTimeout(showT(t1),5000)解决