MySQL查询聚合操作
问题描述
在MySQL查询时,使用分组或者相加等函数时,会出现一种现象,会发现相同的SQL,在不同服务器的MySQL服务上执行,一组正常,一组执行失败,比如【mysql错误1140】,错误代码1140表示“Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause”。这意味着你的SQL语句中包含了聚合函数(如MIN()、MAX()、COUNT()等),但在没有使用GROUP BY子句的情况下,又使用了其他非聚合的列,比如
SELECT
a.parking,
a.message
from park_info a
WHERE
a.id IN (SELECT MAX(b.id) FROM park_info b WHERE b.delete_flag = 0 AND b.park_code = 'CODE' GROUP BY parking)
AND a.delete_flag = 0
AND a.park_code = 'CODE'
GROUP BY a.parking
出现这种情况,是由于MySQL在处理这种类型的查询时会根据严格模式(STRICT MODE)的设置有所不同,因此在某些情况下可能会导致执行报错;
在MySQL的严格模式下,默认情况下,如果查询中的列在GROUP BY子句中没有明确指定,MySQL会拒绝执行这种查询,因为它违反了SQL标准。但是,MySQL在非严格模式下允许这样的查询,并且会根据GROUP BY子句的列进行分组,但这可能会导致结果不确定性。
可以使用以下命令来查看当前严格模式的设置
SELECT @@sql_mode;
一般默认的会是
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
如果是严格模式一般会是
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
要将MySQL的严格模式设置为非严格模式,可以通过SQL语句和配置文件两种方式,
SQL方式:
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
这条语句将全局sql_mode设置为您提供的新值。请注意,这会立即更改MySQL实例中所有连接的sql_mode配置。如果您只想为当前会话更改sql_mode,可以使用以下语句:
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
这样会话中的sql_mode会被设置为新值,但不会影响全局设置。
修改配置文件的方式:
可以找到MySQL的配置文件(通常是my.cnf或my.ini),在其中找到sql_mode的配置项,然后将其修改为所需的值[没有则自行创建]:
[mysqld]
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
保存文件后,重启MySQL服务以使更改生效。
注:在MySQL中,有一些sql_mode选项是不允许动态修改的,包括NO_AUTO_CREATE_USER。因此,无法直接将NO_AUTO_CREATE_USER添加到sql_mode中,两种方式都不可以。