MySQL系列(8)： group by 坑和 update 强制条件

MySQL 5.7.5 版本，默认开启了only_full_group_by模式，select字段只能是group by的字段，否则会报错，可以关闭该模式，或使用 any_value(column) 来抑制该异常。

防止在生产中使用 update 语句漏带条件导致全表更新事故，开启安全更新模式，强制带条件更新，否则报错。　

5.7.5 group by

SQL-92 和更早版本不允许 SELECT的列，HAVING 条件或ORDER BY列未在GROUP BY子句中指定的非聚合列的查询。

例如，此查询在标准SQL-92中是非法的，因为选择的非聚合name列未出现在GROUP BY中：

SELECT o.custid, c.NAME, MAX( o.payment ) 
FROM orders AS o, customers AS c 
WHERE o.custid = c.custid 
GROUP BY o.custid;

为了满足查询在 SQL-92 中合法，必须从选择列中去掉name列或在GROUP BY子句中指定。

MySQL 5.7.5 及更高版本实现对功能依赖性的检测。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），则MySQL 拒绝查询 SELECT 的列 ，HAVING 条件 或ORDER BY列未在GROUP BY子句中指定且在功能上不依赖于它们的非聚合列。

在5.7.5之前，MySQL不会检测到功能依赖性，并且默认情况下未启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明，请参见《 MySQL 5.6参考手册》。

查询非 group by 字段异常

示例：查询的 value 不是 group by 字段，报 only_full_group_by 错误。

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

在实际的业务场景中，经常有分组统计需要带出额外的字段，可以关闭 only_full_group_by 模式，或者使用 ANY_VALUE(column)抑制非 group by 字段。

使用ANY_VALUE()

启用ONLY_FULL_GROUP_BY SQL模式时，此函数对GROUP BY查询很有用，对于 MySQL，因无法确定的原因而拒绝已知的有效查询的情况。

函数的返回值和类型与参数的返回值和类型相同，但是对于ONLY_FULL_GROUP_BY SQL模式，不会检查函数结果。

-- 无效SQL
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

-- 使用 ANY_VALUE(column)
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

关闭only_full_group_by

MySQL 5.7 是遵循 SQL

1. 查看sql_model

   select @@global.sql_mode;
   
   ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2. 去掉ONLY_FULL_GROUP_BY，重新设置值。

   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';
   
   -- 该设置是改变全局`sql_mode`,对后续新建的数据库有效，对已存在数据库，需修改当前库的**sql_mode**。
   set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

   update 强制条件

   严禁在生产环境中使用 UPDATE 语句更新表数据，忘记带 WHERE 条件的情况总有发生。可开启 MySQL 自带的参数来限制 update 语句强制带索引列条件。　　

常年在生产系统上裸操作的正确的操作习惯是先写 select，查处数据核查是要修改的后再把前面改写成 update xxx set xxx什么的。
sql_safe_updates 该参数支持在线变更，当该参数开启的情况下，必须要在 UPDATE 语句后携带 WHERE 条件，条件列必须是索引列，否则就会报出ERROR。
sql_safe_updates=0,即未开启，=1表示开启。

-- 查看状态：ON-表示开启，OFF表示关闭
SHOW VARIABLES LIKE 'sql_safe_updates';  

-- 开启
SET sql_safe_updates = 1;

-- 关闭
SET sql_safe_updates = 0;

-- 示例
UPDATE student SET age = 25 WHERE id = 1;

-- error:如果WHERE条件列不带索引，则报如下错误
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

参考

1. MySQL Handling of GROUP BY
2. ANY_VALUE(arg)
3. mysql 新版本出现group by 语句不兼容问题