java - MySQL : SQL Error: 1140, SQLState: 42000

Question

我的项目中配置了两个数据库，一个在我的本地机器上运行，另一个在远程服务器上运行。我在访问远程数据库服务器时没有任何问题，但在访问本地 MySQL 数据库时出现以下错误。

 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1140, SQLState: 42000
 ERROR [org.hibernate.util.JDBCExceptionReporter] Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

我看过 MySQL 文档，但无法完全理解根本原因。我的想法是，因为我可以从远程数据库中获取相同的数据，所以 java 代码和查询 是正确的。

Answer 1

启用 ONLY_FULL_GROUP_BY 后，以下查询无效。第一个无效，因为选择列表中的名称未在 GROUP BY 子句中命名

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)

如果没有 GROUP BY 子句，则没有 GROUP 列是非法的

您可以重新编写查询以正常工作

mysql> SELECT name, MAX(age) FROM t GROUP BY name;

或

关闭 ONLY_FULL_GROUP_BY并且错误应该消失。

链接可能对您有帮助

1. 可以搜索MySQL错误here
2. group-by-functions
3. similar post on stackoverflow.com
4. another forum where similar problem discussed
5. server-sql-mode

更新

这是对您的评论的答复。

But i would like to know more about disabling the Group BY mode in mysql db.  

Server SQL Modes

• MySQL 服务器可以在不同的 SQL 模式下运行，并且可以根据 sql_mode 系统变量的值对不同的客户端应用不同的模式。此功能使每个应用程序都可以根据自己的要求定制服务器的操作模式。

• 要在服务器启动时设置 SQL 模式，请在命令行上使用 --sql-mode="modes" 选项，或 sql-mode="modes" 在选项文件中，例如 my.cnf(Unix 操作系统) 或 my.ini (Windows)。模式是由逗号分隔的不同模式的列表。要明确清除 SQL 模式，请在命令行中使用 --sql-mode="" 将其设置为空字符串，或在命令行中使用 sql-mode=""选项文件。

• 要在运行时更改 SQL 模式，请使用

  SET [GLOBAL|SESSION] sql_mode='modes'
  语句设置 sql_mode 系统变量。设置 GLOBAL 变量需要 SUPER 权限 并影响从那时起连接的所有客户端的操作。设置 SESSION 变量只影响当前客户端。任何客户端都可以随时更改自己的 session sql_mode 值。

• 要确定当前的全局或 session sql_mode 值，请使用以下语句:

  SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

• 可以引用sql_mode table

---

I followed the manuals, Added the ONLY_FULL_GROUP_BY in sql-mode
but no difference.  

发生这种情况是因为 MySQL 版本。您本地计算机的 MySQL 版本是多少？

如何查看MySQL版本？

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.5.28    |
+-----------+
1 row in set (0.00 sec)    

为了测试 sql_mode ONLY_FULL_GROUP_BY，我创建了包含两列 id, name 的表 patient 并插入了记录。请记住 sql_mode ONLY_FULL_GROUP_BY 不是默认设置，如果需要，您需要设置。

1)MySQL版本5.0.45-community-nt

SELECT name, MAX(id) FROM patient;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause  

它失败了，将 sql_mode 设置为 ONLY_FULL_GROUP_BY 没有意义，因为它不允许未在 GROUP BY 子句中命名的非聚合列。

2)MySQL版本5.1.40-community

mysql> SELECT name, MAX(id) from patient;
+----------+--------+
| MAX(id)  | name   |
+----------+--------+
|       33 | aniket |
+----------+--------+
1 row in set (0.03 sec)  

然后设置sql_mode ONLY_FULL_GROUP_BY

mysql> set sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, MAX(id) from patient;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause  

3)MySQL版本5.5.28

mysql> SELECT name, MAX(id) from patient;
+----------+--------+
| MAX(id)  | name   |
+----------+--------+
|       33 | aniket |
+----------+--------+
1 row in set (0.03 sec)  

然后设置sql_mode ONLY_FULL_GROUP_BY

mysql> set sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, MAX(id) from patient;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause  

结论

如您所见，查询在版本 5.0.45 上失败，并在 5.1.40 和 5.5.28 上/之后成功MySQL 版本之前 5.1.10(not sure)无论是否设置了 sql_mode ONLY_FULL_GROUP_BY，没有 GROUP BY 的查询都会失败。

一些有趣的错误和 sql_mode 常见问题解答链接

1. ONLY_FULL_GROUP_BY sql mode is overly restrictive
2. sql-mode: only full group by mode not working
3. MySQL 5.0 FAQ: Server SQL Mode