对于MySQL 5.7 / 8.0和MySQL 5.6中的每个重复选择，子查询的rand（）列都会重新评估

Question

我正在做一个子查询，其中有一个涉及随机数生成的计算列。在基本查询中，我两次选择此列。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 |
+---+-----+-----+

Answer 1

如MySQL 8.0.0 Milestone版本中所述，

在MySQL 5.6和更早的版本中，派生表总是实现的。在5.7中，在大多数情况下，派生表会合并到外部查询中，在某些情况下会具体化。

…

通过优化器提示启用合并派生表或视图（WL＃9307）-Guilhem Bichot的这项工作允许用户使用“合并”和“
o_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 |
+---+-----+-----+

正如 评论中 提到的 philipxy一样 ，无论是否应用任何优化，都必须严格定义查询表达式的结果。这意味着它是MySQL 5.7 /
8.0中的优化程序错误。