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

 

其他方法 2: 从phpmyadmin中移除  Remove ONLY_FULL_GROUP_BY from phpmyadmin

  • 打开phpmyadmin,点击顶部server:localhost (Open phpmyadmin & select localhost )
  • 点击上面 variables菜单,然后找到 sql mode 选项    (Click on menu Variables & scroll down for sql mode)
  • 点击编辑按钮,移除ONLY_FULL_GROUP_BY 然后保存(Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.)

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

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

Loading

Add a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.