Mysql: 禁用ONLY_FULL_GROUP_BY, 如何关闭ONLY_FULL_GROUP_BY, Error related to only_full_group_by when executing a query in MySql

错误描述：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方法，即使取消（disable） only_full_group_by

方法1： 在mysql命令行：

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

或者

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

方法2： 在php中

mysqli_query($this->db, "set session 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：

要保持当前的mysql设置并禁用ONLY_FULL_GROUP_BY我建议访问您的phpmyadmin或您正在使用的任何客户端并键入：

SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')

结果应该类似于：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

复制sql_mode值，删除ONLY_FULL_GROUP_BY，编辑my.cnf并将其余部分放在[mysqld]部分下的一行中：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

接下来将结果复制到您的my.ini文件中。

mint ： sudo nano /etc/mysql/my.cnf

Ubuntu 16以上 ： sudo nano /etc/mysql/my.cnf

ubuntu 14-16 ： /etc/mysql/mysql.conf.d/mysqld.cnf

然后重启mysql服务器。

如果您想永久禁用错误“ SELECT列表的表达式#N不在GROUP BY子句中，并且包含非聚集列’db.table.COL’，它在功能上不依赖于GROUP BY子句中的列;这与sql_mode = only_full_group_by不兼容 “做这些步骤：

1. sudo nano /etc/mysql/my.cnf
2. 将其添加到文件的末尾

   [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"

3. sudo service mysql restart重启MySQL

这将为所有用户禁用ONLY_FULL_GROUP_BY

本文：Mysql: 禁用ONLY_FULL_GROUP_BY, Error related to only_full_group_by when executing a query in MySql