MySQL：SQL错误：1140，SQLState：42000 [英] MySQL : SQL Error: 1140, SQLState: 42000

问题描述

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

There are two databases are configured in my project, the one is running on my local machine and other is running on Remote server. I don't have any issue accessing the remote DB server but getting the following error while accessing the local MySQL DB.

 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代码和查询是正确的。

I saw the MySQL documentation but couldn't fully understand the root cause. what i think, as i can fetch the same data from remote DB then java code and query is correct.

推荐答案

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

The following query is invalid with ONLY_FULL_GROUP_BY enabled. The first is invalid because name in the select list is not named in the GROUP BY clause

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

$如果没有GROUP BY子句，没有GROUP列的b
$ b

是非法的

with no GROUP columns is illegal if there is no GROUP BY clause

你可以重写查询工作正常

You can re-write query to work fine as

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

或

关闭 ONLY_FULL_GROUP_BY ，错误应该消失。

Turn off the ONLY_FULL_GROUP_BY and the error should disappear.

链接可以帮助您

1. 你可以搜索MySQL错误此处


2. 逐个功能


3. stackoverflow.com上的类似帖子


4. < a href =https://forums.digitalpoint.com/threads/mysql-error-error-number-1140.2055781/ =nofollow noreferrer>另一个讨论类似问题的论坛


5. server-sql-mode

1. You can search MySQL errors here
2. group-by-functions
3. similar post on stackoverflow.com
4. another forum where similar problem discussed
5. server-sql-mode

更新

这是你的评论的答案。

This is answer to your comment.

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

服务器SQL模式

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

• The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. This capability enables each application to tailor the server's operating mode to its own requirements.

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

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. 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模式，请使用

To change the SQL mode at runtime, use a

SET [GLOBAL | SESSION] sql_mode ='modes'

语句设置sql_mode系统变量。设置 GLOBAL变量需要SUPER权限，并影响从那时起连接的所有客户端的操作。设置SESSION变量仅影响当前客户端。任何客户端都可以随时更改自己的会话sql_mode值。

SET [GLOBAL|SESSION] sql_mode='modes'
statement to set the sql_mode system variable. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

要确定当前的全局或会话sql_mode值，请使用以下语句：

To determine the current global or session sql_mode value, use the following statements:

SELECT @@ GLOBAL.sql_mode;

SELECT @@ SESSION.sql_mode;

您可以参考 sql_mode表

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

这是因为MySQL版本。什么是本地计算机的MySQL版本？

This happens because of MySQL version. What is MySQL version of your local computer?

如何检查MySQL版本？

How to check MySQL version?

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

为了测试sql_mode ONLY_FULL_GROUP_BY ，我用两列<$ c $创建了表 patient c> id，name 和插入的记录。记住sql_mode ONLY_FULL_GROUP_BY 不是默认设置，你需要设置。

For testing sql_mode ONLY_FULL_GROUP_BY, I created table patient with two columns id, name and inserted records. Remember sql_mode ONLY_FULL_GROUP_BY is not default set, you need to set if you want.

1）MySQL版本 5.0.45-community-nt

1)MySQL version 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子句中未命名的非聚合列。

It failed, there was no point in setting the sql_mode to ONLY_FULL_GROUP_BY as it won't allow nonaggregated columns that are not named in the GROUP BY clause.

2）MySQL版本 5.1.40-community

2)MySQL version 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

Then after setting 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

3)MySQL version 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

Then after setting 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（不确定）查询没有 GROUP BY 失败，无论sql_mode ONLY_FULL_GROUP_BY 是否设置。

As you can see query failed on version 5.0.45, and succeed on/after 5.1.40 and 5.5.28 Before MySQL version 5.1.10(not sure) query without GROUP BY fails irrespective of sql_mode ONLY_FULL_GROUP_BY set or not.

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

Some interesting bugs and sql_mode faq link

1. ONLY_FULL_GROUP_BY sql模式过于严格


2. sql-mode：只有模式不起作用的完整组


3. MySQL 5.0 FAQ：服务器SQL模式

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

这篇关于MySQL：SQL错误：1140，SQLState：42000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！