执行sql报错 ERROR 1055 (42000): xxx; this is incompatible with sql_mode=only_full_group_by

执行sql 

mysql> SELECT b.pluginId, b.version FROM ( SELECT t.plugin_id AS pluginId, t.version FROM table1 as  t WHERE t.is_deploy = 1 AND t.is_delete = 0 ) AS b GROUP BY b.pluginId ;

报错信息如下
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'b.version' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这句错误的意思是查询列表 select 的字段中有字段没有出现在gruop by子句中，不符合 数据库sql_mode设置的only_full_group_by规则

这是一个sql规则，可以选择遵从也可以选择不遵从。

我这里的sql有这个需求，所以最终选择不遵从这个规则

解决办法：修改sql_model

在linux 环境下修改  /etc/my.cnf文件  

找到sql_model 标签删除 only_full_group_by

如果没有sql_model则需要在[mysqld]标签下边添加一个

添加的内容可以先用  select @@sql_mode; 语句查看下包括哪些规则，然后拷贝出来查询内容 去除掉only_full_group_by 复制在 /etc/my.cnf文件里然后保存

例如：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=mysql-bin
server-id=101014

接下来使用 service mysqld restart  重启mysql即可生效

在windows环境下修改安装目录下的my.ini 文件即可，和上边相同，然后重启mysql即可

转载于:https://www.cnblogs.com/imfx/p/11510538.html