/

MySQL 5.6 5.7 组内排序的区别

MySQL 5.7 对比 5.6 有很多的变化。一个常见的需求：按条件分组后，取出每组中某字段最大值的那条记录。其实就是组内排序的问题，我的做法是：子查询先进行倒序排序，外层查询分组。

示例

+----+----+-------+
| id | no | name |
+----+----+-------+
| 5 | 5 | Mike |
| 4 | 4 | Herry |
| 3 | 3 | wyett |
| 2 | 2 | John |
| 7 | 2 | John |
| 1 | 1 | Mike |
| 6 | 1 | John |
| 8 | 1 | Mike |
| 9 | 1 | Mike |
+----+----+-------+

要求：取出每人（按 name），最大 no 的记录。

select * from (
    select id,no,name from testorder order by no desc
)a group by a.name;
+----+----+-------+
| id | no | name |
+----+----+-------+
| 4 | 4 | Herry |
| 2 | 2 | John |
| 5 | 5 | Mike |
| 3 | 3 | wyett |
+----+----+-------+

但是在 5.7 中，首先需要关闭 ql_mode = ONLY_FULL_GROUP_BY；相同的 name 值，返回则是取了 最早写入的数据行 ，忽略了 order by no desc，按照数据的逻辑存储顺序来返回

+----+----+-------+
| id | no | name |
+----+----+-------+
| 4 | 4 | Herry |
| 2 | 2 | John |
| 1 | 1 | Mike |
| 3 | 3 | wyett |
+----+----+-------+

等价于

select id,no,name from testorder group by name

A query such as

SELECT field1, field2 FROM ( SELECT field1, field2 FROM table1 ORDER BY field2 ) alias

returns a result set that is not necessarily ordered by field2. This is not a bug.
A “table” (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows.
Rows in a table (or in a subquery in the FROM clause) do not come in any specific order.

可以总结为：

  • 在 FROM 后的 subquery 中的 ORDER BY 会被忽略
  • GROUP BY cloumn 返回的行是无序的

解决方案

select a.id,a.no,a.name
    from testorder a inner join (
        select max(no) no,name from testorder group by name
    ) b on a.no = b.no and a.name = b.name
group by name,no

其他方案：

  1. 对于不符合 ONLY_FULL_GROUP_BY 限制的字段，添加 unique 索引。
  2. 使用 ANY_VALUE()，让 MySQL 跳过 ONLY_FULL_GROUP_BY 检测。

小结

MySQL 5.6 Handling of GROUP BY

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

在标准 SQL 中，包含 GROUP BY 子句的查询 不能引用 select 列表中未在 GROUP BY 子句中命名的列。

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL.

MySQL 扩展了 GROUP BY 的标准 SQL 使用，以便选择列表可以引用 GROUP BY 子句中未命名的非集合列。这意味着前面的查询在 MySQL 中是合法的。

However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

但是，主要是在 GROUP BY 中 未命名的每个非分组列中的所有值对于每个组是相同的，这是有用的。服务器可以自由选择每个组中的任何值，因此除非它们相同，所选择的值是 不确定的。

Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

此外，通过添加 ORDER BY 子句不会影响来自每个组的值的选择。结果集排序发生在选择值后，ORDER BY 不影响 服务选择的每个组中的哪些值。

MySQL 5.7 Handling of GROUP BY

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.)

MySQL 5.7.5 及以上功能依赖检测功能。如果启用了 ONLY_FULL_GROUP_BY SQL 模式（默认情况下），MySQL 将拒绝对列表，HAVING 条件或 ORDER BY 列表的查询引用在 GROUP BY 子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5 之前，MySQL 没有检测到功能依赖关系，默认情况下不启用 ONLY_FULL_GROUP_BY）

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

你可以通过使用 ANY_VALUE() 使禁用了 ONLY_FULL_GROUP_BY 的 SQL，来实现相同的效果来引用非聚合列。

5.6 与 5.7 的区别

5.6 升级到 5.7 版本要注意：

  1. sql_mode 默认值的改变。
  2. optimizer_switch 值的改变。
  3. 备库升级影响主备复制。
SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

-- 5.6
-- NO_ENGINE_SUBSTITUTION

-- 5.7
-- 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
  • ONLY_FULL_GROUP_BY SELECT 查询的字段必须是 GROUP BY 中出现的或者使用聚合函数的或者是具有唯一索引的。
  • STRICT_TRANS_TABLES 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制。
  • NO_ZERO_IN_DATE 在严格模式，不接受月或日部分为 0 的日期。如果使用 IGNORE 选项，我们为类似的日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。
  • NO_ZERO_DATE 在严格模式，不要将 ‘0000-00-00’做为合法日期。你仍然可以用 IGNORE 选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。
  • ERROR_FOR_DIVISION_BY_ZERO 在严格模式，在 INSERT 或 UPDATE 过程中，如果被零除(或 MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时 MySQL 返回 NULL。如果用到 INSERT IGNORE 或 UPDATE IGNORE 中，MySQL 生成被零除警告，但操作结果为 NULL。
  • NO_AUTO_CREATE_USER 防止 GRANT 自动创建新用户，除非还指定了密码。
  • NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。

STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES 的区别

唯一的区别是：对于不支持事务的表，若开启 STRICT_TRANS_TABLES，MySQL 会尝试将一个不合法的字段值转换成一个值最近的合法值插入表中；而开启 STRICT_ALL_TABLES 后，则表现为不写入数据，且抛出错误。

因为现在绝大部分用的 InnoDB 引擎，是支持事务的，所以基本不用关心这种区别。

严格模式和非严格模式的区别

SET sql_mode = 'STRICT_TRANS_TABLES';

严格模式下不能在无符号整数字段插入负值。

  • 非严格模式下，会存储为 0。
  • 严格模式下，报错。

严格模式下，无默认值的 NOT NULL 字段在插入数据时必须指定值。

  • 非严格模式下，若不插入数据会存储字段类型的默认值。
  • 严格模式下，报错。

严格模式下，插入字符串不能超出定义长度

  • 非严格模式下，会成功插入数据，但是内容被截断。
  • 严格模式下，报错。

References

– EOF –