记一次group by报this is incompatible with sql_mode=only_full_group_by

小鱼儿 2022-01-23 10:27 293阅读 0赞

group by查询语句：

select fprojectid,fteamid,fprojectname,fdevmanage,ftestmanage,fcreateuser,fcreatetime,fupdatetime  
from t\_project group by fprojectid;

报错：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70][]

然后我用的是Navicat premium 可视化工具。

查询mysql版本号：SELECT VERSION();

查询结果是5.7.20

查询SQL模式： SELECT @@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

在可是化工具里面设置sql\_mode：set @@sql\_mode='NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES'

查询SELECT @@sql\_mode; 看到结果修改成功了。

运行group by语句就没有报错。

但是我再重启Navicat，查看sql\_mode看到我的sql\_mode还是之前的，并没有修改成功。说明用命令修改参数，可能当前会话生效，可以全局生效；但是数据库重启之后都会失效。

于是想到去修改my.ini文件，在文件中加上sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70 1][]

重启Navicat查询还是没有会报错。

于是尝试Windows DOS命令下使用root权限修改mysql的配置：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70 2][]

全局的这么设置：

set @@global.sql\_mode='NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES'

重启mysql，发现sql\_mode是否修改成功！

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70 3][]

你再去Navicat 运行group by语句，终于没有报错了。

sql\_mode的参数很多，有些是针对特殊场景的。

可以了解一下这些参数代表的意义。

ONLY\_FULL\_GROUP\_BY：

如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的。

只能select column1 from tb1 group by column1，只能展示group by的字段，其他字段都报1055错。

STRICT\_TRANS\_TABLES ：

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制。

NO\_ENGINE\_SUBSTITUTION：

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70]: /images/20220123/d9c16c23467442ab9ad5ba15a4e2b115.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70 1]: /images/20220123/e614188e83704be2af539da718b92270.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70 2]: /images/20220123/be763e98110141a0951a1729425f20e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzcyNTE4_size_16_color_FFFFFF_t_70 3]: /images/20220123/d755a78f4b1d46eb8bf54f87f31160a8.png