SELECT列表不在GROUP BY子句中，包含未聚合的列....sql_mode=only_full_group_by不兼容

我使用MySQL 5.7.13在我的windows PC与WAMP服务器

这里我的问题是执行这个查询

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
`status` = 'Active'
GROUP BY `proof_type`

我总是得到这样的错误吗

SELECT列表中的表达式#1不在GROUP BY子句中，包含未聚合的列returntr_prod.tbl_customer_pod_uploads。id'，它不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容

你能告诉我最好的解决方案吗?

我需要这样的结果

+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name    | is_private | status | createdon           | updatedon           |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
|  1 |       1 | 78      | 1       | 21.1212  | 21.5454   |          1 |             1 | id_Card.docx |          0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
672422 次浏览
小开

当MySQL的only_full_group_by模式被打开时，这意味着在使用GROUP BY时将应用严格的ANSI SQL规则。对于你的查询，这意味着如果你proof_type列的GROUP BY，那么你只能选择两个东西:

  • proof_type列，或者
  • 任何其他列的聚合
< p > < br > 通过其他列的“聚合”，我的意思是对另一列使用诸如MIN()、MAX()或AVG()之类的聚合函数。因此，在您的情况下，以下查询是有效的:

SELECT proof_type,
MAX(id) AS max_id,
MAX(some_col),
MIN(some_other_col)
FROM tbl_customer_pod_uploads
WHERE load_id = '78' AND
status = 'Active'
GROUP BY proof_type

我在SO上看到的绝大多数MySQL GROUP BY问题都关闭了严格模式，所以查询正在运行，但结果不正确。在您的情况下，查询根本不会运行，迫使您考虑真正想要做什么。

注意:ANSI SQL扩展了在GROUP BY中允许被选择的内容，也包括了被选择列上的功能上的依赖列。函数依赖的一个例子是按表中的主键列分组。由于保证主键对于每条记录都是唯一的，因此任何其他列的值也将确定。MySQL是允许这样做的数据库之一(SQL Server和Oracle不AFAIK)。

小开
最佳答案

这

SELECT列表中的表达式#1不在GROUP BY子句中，包含未聚合的列returntr_prod.tbl_customer_pod_uploads。id'，它不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容

将通过这个命令改变MySQL中的sql模式来简单地解决，

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

这也适用于我.. 我使用了这个，因为在我的项目中有很多这样的查询，所以我只是把这个sql模式改为只有_full_group_by

或者简单地包括SELECT语句指定的GROUP BY子句中的所有列。sql_mode可以保持启用状态。

谢谢你！: -)

小开

可以禁用 sql_mode = only_full_group_by 通过一些命令可以尝试通过终端或MySql IDE

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';
小开

打开WAMP面板，打开MySQL配置文件。在其中，如果找到“sql_mode”，则搜索它，将其设置为“”，否则，如果没有找到它，则在文件中添加sql_mode=“”。

重新启动MySQL服务器，你就可以开始了…

快乐的编码。

小开

要使查询在SQL92中合法，必须从选择列表中省略name列或在GROUP BY子句中指定名称。

如果可选特性T301在功能上依赖于GROUP BY列，SQL99及后续版本允许这些非聚合:如果name和custid之间存在这样的关系，则查询是合法的。例如，如果custid是客户的主键，就会出现这种情况。

MySQL 5.7.5及更高版本实现了函数依赖性检测。如果启用了ONLY_FULL_GROUP_BY SQL模式(默认是这样)，MySQL将拒绝select列表、HAVING条件或ORDER by列表引用既不在GROUP by子句中命名也在功能上依赖于它们的非聚合列的查询。

通过MySQL:: MySQL 5.7参考手册::12.19.3 MySQL处理GROUP BY

你可以用下面的命令改变sql模式来解决这个问题:

SET GLOBAL sql_mode=(SELECT (@@sql_mode，'ONLY_FULL_GROUP_BY'， ")); < /代码> < / p >

和…记得重新连接数据库!!

小开

从它的外观来看，我认为按多个列/字段分组不会损害你的结果。你为什么不试试这样加入这个群呢:

GROUP BY `proof_type`, `id`

这将首先按proof_type分组，然后按id分组。我希望这不会改变结果。在某些/大多数情况下，按多列分组会给出错误的结果。

小开

在MySql引擎中有一个系统变量ONLY_FULL_GROUP_BY。

Mysql 5.7.5版本: ONLY_FULL_GROUP_BY SQL模式默认开启

5.7.5版本之前: ONLY_FULL_GROUP_BY默认未启用。

如果启用了ONLY_FULL_GROUP_BY SQL模式(从5.7.5版本开始默认启用)，MySQL将拒绝select列表、HAVING条件或ORDER BY列表引用的非聚合列的查询，这些列既不在GROUP BY子句中命名，也在功能上依赖于它们。

为了解决问题，可以使用以下3种解决方案中的任意一种

(1) PHPMyAdmin

禁用: ONLY_FULL_GROUP_BY模式

如果你正在使用phpMyAdmin，然后更改下面截图中提到的sql_mode设置。 enter image description here < / p >

编辑sql mode变量并从值中删除ONLY_FULL_GROUP_BY文本

或

(2) SQL/命令提示

禁用: ONLY_FULL_GROUP_BY模式。

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

或

(3)不要使用SELECT *

不要禁用: ONLY_FULL_GROUP_BY模式

在SELECT查询中使用相关列。相关的表示列，这些列要么来自group by子句，要么来自具有聚合函数的列(MAX， MIN， SUM， COUNT等)

重要提示

使用point(1) OR point(2)所做的更改不会将其设置为permanent，并且它将在每次重新启动后恢复。

所以你应该在你的配置文件中设置这个(例如在[mysqld]部分中的/etc/mysql/my.cnf)，这样在MySQL重启后更改仍然有效:

配置文件: /etc/mysql/my.cnf

__abc2: __abc0 __abc3 __abc1

从值中删除单词ONLY_FULL_GROUP_BY并保存文件。

请注意:如果你在配置文件中没有找到sql_mode变量，请在文件末尾插入以下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
小开

在你的my.ini中，这样写:

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

取决于你的版本。或者:

[mysqld]
sql_mode = ""

或者简单地删除这个:ONLY_FULL_GROUP_BY

小开
  1. 登录到phpMyAdmin
  2. 导航到:Server: localhost:3306然后执行 不选择任何数据库
  3. 点击顶部菜单中的变量
  4. 搜索“sql mode”并将相应的值编辑为:NO_ENGINE_SUBSTITUTION

这是所有。

我在我的Ec2中这样做了，它很有魅力。

小开

下面的方法解决了我的问题:

在ubuntu中

类型:sudo vi /etc/mysql/my.cnf

输入A进入插入模式

在最后一行粘贴下面两行代码:

[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

按esc退出输入模式

Type :wq to save and close vim.

输入sudo service mysql restart重新启动MySQL。

小开

MySQL 8.0升级

你的sql-mode将没有NO_AUTO_CREATE_USER，因为它已经被删除了——how-to-set-sql-mode-in-my-cnf-in-mysql-8

[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

同样，如果某人没有my.cnf文件，那么他们可以在/etc/mysql/my.cnf中创建一个新的文件，然后添加上面的行。

小开

在Ubuntu中

步骤1:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

第二步:转到最后一行并添加以下内容

sql_mode = ""

第三步:保存

步骤4:重启mysql服务器。

小开

Hi，而不是获取所有列，只需使用ANY_VALUE(column_name)获取所需的列。它工作得很完美。只是检查。

例如:

SELECT proof_type,any_value("customer_name") as customer_name
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND `status` = 'Active' GROUP BY `proof_type`
小开

如果你正在使用PhpMyAdmin，搜索“SQL模式”，并删除值:ONLY_FULL_GROUP_BY，刚刚完成，它是好的。

小开

转到phpmyadmin并打开控制台并执行此请求

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
小开 
> sudo nano /etc/mysql/my.cnf

输入下面的

[mysqld]
sql_mode = ""

Ctrl + O => Y = Ctrl + X

> sudo service mysql restart
小开

这里有一个非常快速和简单的方法来永久设置它

注意:运行SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));是临时的，在服务器重新启动时，你仍然会得到错误。 要永久修复此问题，请执行以下

  1. 以根用户身份登录到服务器
  2. 在您的终端运行 李wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh < / >
  3. 通过运行chmod +x disable-strict-mode.sh使脚本可执行
  4. 通过运行./disable-strict-mode.sh来运行脚本

完成后，mysql将被修改，并将重新启动

小开

我有类似的问题，我的部分数据库崩溃。我所做的是通过PHPStorm数据库控制台更改DB中的参数，如下所示:

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

现在工作很有魅力

小开

Only_full_group_by = on告诉MySQL引擎:当你怀疑要显示什么结果并抛出错误时，不要应用GROUP BY。如果Group By明确告诉你要做什么，就应用只有。即当Group By是满的且完全的!

Only_full_group_by = off告诉MySQL引擎:总是应用GROUP BY，如果你对选择什么结果有疑问，就随机选择一个!

如果你正确使用GROUP BY，你不必关闭它!

例子:

表:用户

 id   |  name
----------------
1      ali
2      john
3      ali

当你对name列使用GROUP BY时:

SELECT * FROM users GROUP BY name;

有两种可能的结果:

  1      ali
2      john

或

  2      john
3      ali

MYSQL不知道选择什么结果!因为有不同的__abc0，但都有name=ali。

Solution1:

只选择name字段:

SELECT name FROM users GROUP BY name;

结果:

  ali
john
这是一个完美的解决方案。删除使GROUP BY混淆的列。这意味着你知道你在做什么。通常不需要
这些列，但如果你需要它们，去环境!< / p >

Solution2:

关闭only_full_group_by。MYSQL将从两个可能的结果中选择一个随机!!(如果你真的不关心id将选择什么，这是可以的，但请记住在你的查询后立即打开它，以防止在未来的groupby中出现意外行为)

环境

使用Aggregate函数，如MIN()， MAX()来帮助MYSQL决定它必须选择什么。

例如:

SELECT MAX(id), name FROM users GROUP BY name;

它将选择具有最大id的ali行:

  2      john
3      ali
小开

我正面临这个问题和下面的查询还不够

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

因为我正在使用存储过程。执行此查询后，我必须删除过程并重新创建。

小开

在运行查询之前运行下面的查询

SET SQL_MODE = '';
小开

适用于CentOS/RHEL (Linux服务器)和XAMPP本地操作系统

你好，我有一个非常不同的解决方案，我希望这能帮助到一些人。(可能看起来有点过分，但这对我来说真的很有用，不像其他解决方案)

我正在运行CentOS 7，我所有的代码都在本地工作，但当我将其上传到我的服务器时，我开始得到这个问题解决的错误。

几个小时后，我尝试从不同的角度查看这个问题，我记得我的本地设置使用XAMPP，我以为我使用MySQL 8 (MySQL 8安装在我的服务器上)。

但是从XAMPP登录到MySql后，我得到了下面的输出:

mysql -u root -p //Login




Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.4.18-MariaDB mariadb.org binary distribution


Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

是的，你可能已经注意到同样的，XAMPP使用MariaDB，所以解决方案是卸载mysql和安装MariaDB

当我运行Centos 7时，这样做的步骤如下:

  1. 从你的服务器删除mysql
# yum remove mysql mysql-server
  1. 更新你的系统(这是常见的每次我们安装新东西)
# sudo yum  -y update
  1. 将MariaDB添加到存储库
# sudo tee /etc/yum.repos.d/MariaDB.repo<<EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
  1. 更新缓存
# sudo yum makecache fast
  1. 安装MariaDB
# sudo yum -y install MariaDB-server MariaDB-client
  1. 添加MariaDB到启动(每次系统重新启动时启动MariaDB)
#sudo systemctl enable --now mariadb

然后你可以通过运行下面的命令来保护你的安装:

# sudo mysql_secure_installation

最后一个命令将开始一个过程，您可以在其中设置密码和其他选项。

之后不要忘记添加非root用户，并授予它必要的特权，这是因为你不能在你的应用程序中使用root用户(是的，我必须弄清楚这一点)

首次使用root帐号登录:

# mysql -u root -p

然后添加您的用户并授予权限:

CREATE USER 'YourUserName'@localhost IDENTIFIED BY 'YourPassword';
GRANT ALL PRIVILEGES ON *.* TO 'YourUserName'@localhost IDENTIFIED BY 'YourPassword' WITH GRANT OPTION;

最后，你必须创建你的数据库，导入你的表/数据/触发器/过程。

现在您将能够毫无问题地运行代码，就像您的本地设置一样。(也许你将不得不安装mysql扩展，以防你不得不在本地做)。

小开

也可以简单地将GROUP_CONCAT ()添加到非聚合列中。

像GROUP_CONCAT(your_column)

小开

登录phpMyAdmin 导航到:服务器:http://localhost/phpmyadmin，不要选择任何数据库 点击SQL从顶部菜单和虫害下面的代码运行SQL查询/查询服务器 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 比照链接 < / p >

小开

两种解决方案:

)使用

SET GLOBAL sql_mode=(SELECT (@@sql_mode，'ONLY_FULL_GROUP_BY'， "));

b)可以在不禁用ONLY_FULL_GROUP_BY的情况下使用ANY_VALUE()引用非聚合列来达到同样的效果。

ANY_VALUE(值)

小开

如果你在Mac上使用MAMP，当你重新启动MAMP时，对MySQL变量的任何更改都将被擦除。要确保更改是永久性的，请执行以下操作:

停止正在运行的MAMP服务器。

使用文本编辑器创建一个名为my.cnf的文件，并将其保存到/Applications/MAMP/conf文件夹中。将文件放在文件夹的根目录下(这看起来有点奇怪，因为它包含了大量的apache文件夹，但这是可以的)。在文件中添加如下代码行:

[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

保存文件并关闭编辑器。

.启动MAMP服务器

这将使改变成为永久性的。

小开

在我的情况下，这是我的旧sql_mode

sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

你可以检查sql_mode

show variables like "sql_mode";

然后我把它设置成这样

SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
小开

步骤1。检查sql模式:

SELECT @@GLOBAL.sql_mode;

步骤2。在/etc/mysql/conf目录下创建一个新的配置文件。d /目录:

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

在编辑器中输入以下文本:

[mysqld]


sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

步骤3。重新启动MySQL:

sudo service mysql restart

步骤4。确认更改:

SELECT @@GLOBAL.sql_mode;
小开

如果你不想禁用ONLY_FULL_GROUP_BY模式，你只需要在GROUP BY子句中指定你想选择的每一列。

不要使用通配符(*)，并确保您的列与您的需求相关


提交答案