作者：Guilhem Bichot 译：徐轶韬

在MySQL 8.0.14中，添加了LATERAL派生表的功能。在线手册https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html中描述了语法，并提供了如何使用该功能在表中查找最大值的示例。

在本文中，我将利用LATERAL解决另一个问题：假设我们有一堆节点，并希将每个节点与其他节点连接生成一个“随机图”。

我们从一个nodes表开始：

create table nodes(id int);

这个表将填充20个节点，使用递归通用表表达式Common Table Expression(MySQL 8.0.1的 另一个功能)：

insert into nodeswith recursive cte(n) as(select 1union allselect n+1 from cte where n<20)select * from cte;

现在，让我们在图上创建随机的边。它们是有方向的(具有“from”和“to”)。

create table edges (from_id int, to_id int);

对于每个原始节点，让我们选择两个随机目标节点，并将它们连接到原始节点。

如果我们使用普通派生表来存储两个目标节点：

insert into edges(from_id, to_id)select origin_nodes.id, target_nodes.idfrom nodes as origin_nodes,(select idfrom nodesorder by rand()limit 2) as target_nodes;

在执行INSERT查询时，此派生表仅计算(实体化)一次且仅有一次。在伪代码中，这样描述：

select two random target nodes, store into target_nodesfor each row R in origin_nodes:join R with target_nodesinsert the result into edges

因此，所有节点都只会连接到在开始时选择的相同的两个目标节点：

这不是理想的结果;所以删除掉：delete from edges;

在抛弃先前的SELECT查询之前，让我们注意它的EXPLAIN执行计划：

为了解决我们的问题，我们需要为每个原始节点重新计算目标节点，比如这个伪代码：

for each row R in origin_nodes:select two random target nodes, store into target_nodesjoin R with target_nodesinsert the result into edges

为了实现这一点，我们需要告诉MySQL两个随机目标节点的选择取决于当前的原始节点，每次都重复，我们使目标节点人为地依赖于原始节点：

insert into edges(from_id, to_id)select origin_nodes.id, target_nodes.idfrom nodes as origin_nodes,(select idfrom nodesorder by rand()+0*origin_nodes.idlimit 2) as target_nodes;

0 * origin_nodes.id不会更改ORDER BY子句的值，但是，它使这个子句以及由此派生的表target_nodes依赖于origin_nodes的当前行。

但是普通的派生表不允许依赖于FROM子句的先前表，所以当我们运行上面的查询时，我们会得到：ERROR 1054 (42S22): Unknown column 'origin_nodes.id' in 'order clause'

所以我们使它成为一个LATERAL派生表，根据定义它是：允许依赖于FROM子句的先前表的派生表。

insert into edges(from_id, to_id)select origin_nodes.id, target_nodes.idfrom nodes as origin_nodes,LATERAL (select idfrom nodesorder by rand()+0*origin_nodes.idlimit 2) as target_nodes;

现在它按照我们的预期工作，目标节点产生了变化：

并且EXPLAIN显示：

请注意新的指示内容：-DEPENDENT DERIVED：派生表依赖于另一个表;-origin_nodes上面Rematerialize：我们每次读取的行origin_nodes，MySQL从新实体化派生表Derived2(这是派生表target_nodes在MySQL的内部名称)。

让我们的问题变得更复杂一些：到目前为止，我们将每个原始节点连接到两个随机目标节点;如果我想要目标节点的数量也是随机的呢？比方说，将每个节点连接到0到4之间的多个随机节点？

查看我们之前成功的查询：

insert into edges(from_id, to_id)select origin_nodes.id, target_nodes.idfrom nodes as origin_nodes,lateral (select idfrom nodesorder by rand()+0*origin_nodes.idlimit 2) as target_nodes;

我们现在必须使LIMIT 2中的“2”成为0到4之间的随机值。由于LIMIT不允许变化，所以我们将放弃使用LIMIT并稍微变化。要构建0到4目标节点，我们将

选择所有节点作为目标，

按随机顺序编号(使用ROW_NUMBER窗口函数)，

将row_number与0到4之间的随机最大值进行比较，可以使用WHERE子句根据row_number对它们进行过滤。

delete from edges;insert into edges(from_id, to_id)select origin_nodes.id, target_nodes.idfrom nodes as origin_nodes,lateral (select id,row_number() over (order by rand()+0*origin_nodes.id) as rnfrom nodeslimit 4) as target_nodeswhere target_nodes.rn

如您所见，对于id为1的输入节点，我们有三个目标节点(id 2,4,8)，对于输入节点2，我们有一个(id 20)，对于3我们有一个，对于4我们有两个，...根据需要，每个原始节点具有不同数量的目标节点。

总结一下，在MySQL中我们现在有了LATERAL，所以每当你想到“每一行让我们这样做”时，你可以找到一个带有LATERAL派生表的解决方案。

我希望你会发现这个功能很有用。感谢您使用MySQL！