停用ONLY_FULL_GROUP_BY

本文翻译自：Disable ONLY_FULL_GROUP_BY

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? 如何禁用它？

---

#1楼

参考：https://stackoom.com/question/1cMyT/停用ONLY-FULL-GROUP-BY

---

#2楼

Give this a try: 试试看：

SET sql_mode = ''

---

Community Note: As pointed out in the answers below, this actually clears all the SQL modes currently enabled. 社区说明：正如下面的答案所指出的，这实际上清除了当前启用的所有 SQL模式。 That may not necessarily be what you want. 那不一定是您想要的。

---

#3楼

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_BY option. 首先，获取以逗号分隔的启用模式列表，然后将其设置为不带ONLY_FULL_GROUP_BY选项的列表。

---

#4楼

The MySQL documentation also specifies the following methods: MySQL文档还指定了以下方法：

• Set sql-mode="<modes>" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). 在选项文件（例如my.cnf（Unix操作系统）或my.ini（Windows））中设置sql-mode="<modes>" 。
• To set the SQL mode at server startup via the command line, use the --sql-mode="<modes>" option. 要通过命令行在服务器启动时设置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.cnf and it worked. 我在/etc/my.cnf添加了sql-mode=""选项，它可以正常工作。

This SO solution discusses 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。

---

#5楼

    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;

---

#6楼

Solution 1: Remove ONLY_FULL_GROUP_BY from 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_BY from phpmyadmin 解决方案2：从phpmyadmin中删除ONLY_FULL_GROUP_BY

• Open phpmyadmin & select localhost 打开phpmyadmin并选择localhost
• Click on menu Variables & scroll down for sql mode 单击菜单变量并向下滚动至sql模式
• Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save. 单击编辑按钮以更改值并删除ONLY_FULL_GROUP_BY ，然后单击保存。