sql模式設置引起的問題解決辦法

Posted on by

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模式設置引起的問題解決辦法的文章就介紹到這瞭,更多相關sql模式設置內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：