mysql - 如何在 MySQL v8 中模拟 LATERAL JOIN 来为每一行执行子查询或连接？

Question

我有两个表:

film  with primary key film_id
actor with primary key actor_id

我现在想填充一个表film_actor(film_id, actor_id)，它将每部电影连接到 250 个随机 Actor 。所以每部电影应该有 250 个不同的 Actor 。

在 PostgreSQL 中，我会这样做:

insert into film_actor(film_id, actor_id)
select film_id, actor_id
from   film
cross join lateral
(
    select actor_id
    from   actor
    where  film_id is not null -- to force lateral behavior
    order  by random()
    limit  250
) as actor;

PostgreSQL fiddle 可以在这里找到:https://dbfiddle.uk/?rdbms=postgres_10&fiddle=6dc21a3ce3404aaf3f4453e2ee4f863b .如您所见，每部电影都有不同的 Actor 。

我在 MySQL v8 中找不到对 LATERAL JOIN 的支持。如何在 MySQL v8 中执行此类构造？

可以在这里找到一个不工作的 MySQL fiddle :https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6c1fb7df00cf8c73cbcca77752c9ef0d如您所见，每部电影都有相同的 Actor 。

Answer 1

LATERAL 已在 8.0.14 版中添加到 MySQL。

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-14.html#mysqld-8-0-14-sql-syntax https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html

A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.