MySQL 禁用 ONLY_FULL_GROUP_BY

Disable ONLY_FULL_GROUP_BY

提问by ZviBar

I accidentally enabled ONLY_FULL_GROUP_BY mode like this:

我不小心启用了 ONLY_FULL_GROUP_BY 模式，如下所示：

SET sql_mode = 'ONLY_FULL_GROUP_BY';

How do I disable it?

如何禁用它？

回答by Eyo Okon Eyo

Solution 1:Remove ONLY_FULL_GROUP_BYfrom mysql console

解决方案 1：从 mysql 控制台中 删除ONLY_FULL_GROUP_BY

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

you can read more here

你可以在这里阅读更多

Solution 2:Remove ONLY_FULL_GROUP_BYfrom phpmyadmin

解决方案 2：从 phpmyadmin 中删除ONLY_FULL_GROUP_BY

• Open phpmyadmin & select localhost
• Click on menu Variables & scroll down for sql mode
• Click on edit button to change the values & remove ONLY_FULL_GROUP_BY& click on save.

• 打开 phpmyadmin 并选择本地主机
• 单击菜单变量并向下滚动以获取 sql 模式
• 单击编辑按钮更改值并删除ONLY_FULL_GROUP_BY并单击保存。

回答by breq

Update:

更新：

To keep your current mysql settings and disable ONLY_FULL_GROUP_BYI suggest to visit your phpmyadmin or whatever client you are using and type:

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

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

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

next copy result to your my.inifile.

下一个复制结果到您的my.ini文件。

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

薄荷：sudo nano /etc/mysql/my.cnf

ubuntu 16 and up: sudo nano /etc/mysql/my.cnf

ubuntu 16 及更高版本：sudo nano /etc/mysql/my.cnf

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

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

Caution! copy_meresult can contain a long text which might be trimmed by default. Make sure you copy whole text!

警告！copy_me结果可以包含默认情况下可能会被修剪的长文本。确保您复制整个文本！

---

---

old answer:

旧答案：

If you want to disable permanentlyerror "Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.table.COL' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" do those steps:

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

1. sudo nano /etc/mysql/my.cnf

2. Add this to the end of the file

   [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 restartto restart MySQL

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

This will disable ONLY_FULL_GROUP_BYfor ALLusers

这将禁用ONLY_FULL_GROUP_BY对所有用户

回答by Taran

Be careful using

小心使用

SET sql_mode = '' 

This actually clears all the modes currently enabled.If you don't want to mess with other settings, you'll want to do a

这实际上清除了当前启用的所有模式。如果你不想弄乱其他设置，你会想要做一个

SELECT @@sql_mode 

first, to get a comma-separated list of the modes enabled, then SET it to this list without the ONLY_FULL_GROUP_BYoption.

首先，获取以逗号分隔的已启用模式列表，然后将其设置为该列表而不带ONLY_FULL_GROUP_BY选项。

回答by Tripp Kinetics

Give this a try:

试试这个：

SET sql_mode = ''

---

---

Community Note: As pointed out in the answers below, this actually clears allthe SQL modes currently enabled. That may not necessarily be what you want.

社区注意：正如下面的答案所指出的，这实际上清除了当前启用的所有SQL 模式。那可能不一定是您想要的。

回答by WeiYuan

    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> exit;

回答by Janaka R Rajapaksha

Adding only one mode to sql_mode without removing existing ones:

只向 sql_mode 添加一种模式而不删除现有模式：

SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));

Removing only a specific mode from sql_mode without removing others:

仅从 sql_mode 中删除特定模式而不删除其他模式：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));

In your case, if you want to remove only ONLY_FULL_GROUP_BYmode, then use below command:

在您的情况下，如果您只想删除ONLY_FULL_GROUP_BY模式，请使用以下命令：

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

Reference: http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html

参考：http: //johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html

回答by Arvid

Thanks to @cwhisperer. I had the same issue with Doctrine in a Symfony app. I just added the option to my config.yml:

感谢@cwhisperer。我在 Symfony 应用程序中遇到了与 Doctrine 相同的问题。我刚刚在 config.yml 中添加了该选项：

doctrine:
    dbal:
        driver:   pdo_mysql
        options:
            # PDO::MYSQL_ATTR_INIT_COMMAND
            1002: "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"

This worked fine for me.

这对我来说很好。

回答by Jadeye

On:

在：

• Ubuntu 14.04
• mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

• Ubuntu 14.04
• mysql Ver 14.14 Distrib 5.7.16，用于 Linux (x86_64) 使用 EditLine 包装器

Do:

做：

$ sudo nano /etc/mysql/conf.d/mysql.cnf

Copy and paste:

复制和粘贴：

[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

To the bottom of the file

到文件底部

$ sudo service mysql restart

回答by Waqleh

I have noticed that @Eyo Okon Eyo solution works as long as MySQL server is not restarted, then defaults settings are restored. Here is a permanent solution that worked for me:

我注意到 @Eyo Okon Eyo 解决方案只要不重启 MySQL 服务器就可以工作，然后恢复默认设置。这是一个对我有用的永久解决方案：

To remove particular SQL mode (in this case ONLY_FULL_GROUP_BY), find the current SQL mode:

要删除特定的 SQL 模式（在本例中为ONLY_FULL_GROUP_BY），请找到当前的 SQL 模式：

SELECT @@GLOBAL.sql_mode;

copy the result and remove from it what you don't need (ONLY_FULL_GROUP_BY)

复制结果并从中删除您不需要的内容（ONLY_FULL_GROUP_BY）

e.g.:

例如：

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

to

到

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

create and open this file:

创建并打开此文件：

/etc/mysql/conf.d/disable_strict_mode.cnf

and write and past into it your new SQL mode:

并将您的新 SQL 模式写入并粘贴到其中：

[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

restart MySQL:

重启 MySQL：

sudo service mysql restart

Or you can use ANY_VALUE()to suppress ONLY_FULL_GROUP_BY value rejection, you can read more about it here

或者您可以使用ANY_VALUE()抑制 ONLY_FULL_GROUP_BY 值拒绝，您可以在此处阅读更多相关信息

回答by br3nt

The MySQL documentationalso specifies the following methods:

在MySQL文档还规定了以下几种方法：

• Set sql-mode="<modes>"in an option file such as my.cnf (Unix operating systems) or my.ini (Windows).
• To set the SQL mode at server startup via the command line, use the --sql-mode="<modes>"option.

• sql-mode="<modes>"在选项文件中设置，例如 my.cnf（Unix 操作系统）或 my.ini（Windows）。
• 要通过命令行在服务器启动时设置 SQL 模式，请使用该--sql-mode="<modes>"选项。

*Where <modes>is a list of different modes separated by commas.

*<modes>以逗号分隔的不同模式列表在哪里。

To clear the SQL mode explicitly, set it to an empty string using --sql-mode=""on the command line, or sql-mode=""in an option file.

要明确清除 SQL 模式，请--sql-mode=""在命令行或sql-mode=""选项文件中使用将其设置为空字符串。

I added the sql-mode=""option to /etc/my.cnfand it worked.

我添加了该sql-mode=""选项/etc/my.cnf并且它起作用了。

This SO solutiondiscusses ways to find out which my.cnf file is being used by MySQL.

这个 SO 解决方案讨论了找出 MySQL 正在使用哪个 my.cnf 文件的方法。

Don't forget to restart MySQL after making changes.

更改后不要忘记重新启动 MySQL。