对 MySQL 5.7/8.0 与 MySQL 5.6 中的每个重复选择重新评估子查询的 rand() 列

本教程将介绍对 MySQL 5.7/8.0 与 MySQL 5.6 中的每个重复选择重新评估子查询的 rand() 列的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

对 MySQL 5.7/8.0 与 MySQL 5.6 中的每个重复选择重新评估子查询的 rand() 列 教程 第1张

问题描述

我正在做一个子查询,其中我有一个涉及随机数生成的计算列.在基本查询中,我选择了此列两次.MySQL 5.6 按我的预期工作,计算值被调用一次并修复.5.7+/8.0+ 执行似乎为每个选择单独重新评估子查询的列值.这是正确的行为吗?我可以做些什么来强制它在较新版本的 MySQL 中按预期工作?

CREATE TABLE t (
  `id` BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=InnoDB;

insert into t values();
insert into t values();
insert into t values();
insert into t values();
insert into t values();

SELECT  q.i,
  q.r,
  q.r
FROM (
  SELECT id AS i,
 (FLOOR(RAND(100) * 4)) AS r
  FROM t
  ) q;

MySQL 5.6 产生(值相同):

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |0 |0 |
| 2 |2 |2 |
| 3 |3 |3 |
| 4 |2 |2 |
| 5 |1 |1 |
+---+-----+-----+

而 5.7 的产量(值不同):

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |0 |2 |
| 2 |3 |2 |
| 3 |1 |1 |
| 4 |2 |1 |
| 5 |2 |0 |
+---+-----+-----+

推荐答案

如 MySQL 8.0.0 Milestone Release 可用,

在 MySQL 5.6 及更早版本中,派生表始终是具体化的.在5.7,派生表在大多数情况下合并到外部查询中,并在某些情况下具体化.

...

通过优化器提示启用合并派生表或视图 (WL#9307) — Guilhem Bichot 的这项工作允许用户使用merge"和no_merge"提示控制是合并还是实现派生表或视图.

我想这是我在较新版本的 MySQL 中观察到的行为的原因.提到的提示可以与 MySQL 8.0 一起使用来强制 RAND() 只被调用一次:

SELECT  /* NO_MERGE(q) */
  q.i,
  q.r,
  q.r
FROM (
  SELECT 
 id AS i,
 (FLOOR(RAND(100) * 4)) AS r
  FROM t
  ) AS q;

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |0 |0 |
| 2 |2 |2 |
| 3 |3 |3 |
| 4 |2 |2 |
| 5 |1 |1 |
+---+-----+-----+

然而,这在 5.7 中不可用.要在 5.7 中实现所需的行为,请将 LIMIT <a very high number> 添加到派生表定义中(我在下面使用带符号的 LONG_MAX).感谢 Roy Lyseng 提供的解决方法.

SELECT
  q.i,
  q.r,
  q.r
FROM (
  SELECT 
 id AS i,
 (FLOOR(RAND(100) * 4)) AS r
  FROM t LIMIT 9223372036854775807
  ) AS q;

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |0 |0 |
| 2 |2 |2 |
| 3 |3 |3 |
| 4 |2 |2 |
| 5 |1 |1 |
+---+-----+-----+

正如评论中提到的,无论是否应用了任何优化,都必须严格定义查询表达式的结果.这意味着它是 MySQL 5.7/8.0 中的优化器错误.

好了关于对 MySQL 5.7/8.0 与 MySQL 5.6 中的每个重复选择重新评估子查询的 rand() 列的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。