在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中的优化程序错误。