MySQL 的 LATERAL 派生表

--- ### **一、MySQL 的 LATERAL 派生表（横向关联）** 1. **功能支持** • **版本要求**：从 **MySQL 8.0.14** 开始支持 `LATERAL` 关键字。 • **核心作用**：在 `FROM` 子句中的派生表（子查询）中，允许直接引用外层表的字段，实现类似“逐行关联”的效果。例如： ```sql SELECT t1.id, t2.* FROM t1, LATERAL (SELECT * FROM t2 WHERE t2.col = t1.col) AS t2; ``` 此查询会为 `t1` 的每一行执行一次子查询 `t2`，并将结果关联。 2. **与 Hive LATERAL VIEW 的区别** • **应用场景差异**： ◦ **Hive**：主要用于展开复杂数据类型（如数组、Map），配合 `EXPLODE` 函数生成多行结果。 ◦ **MySQL**：侧重通过横向关联优化多表查询逻辑，例如逐行计算或动态筛选。 • **语法形式**： ◦ Hive 的 `LATERAL VIEW EXPLODE()` 是专门用于数组展开的语法，而 MySQL 的 `LATERAL` 是通用横向关联机制。 --- ### **二、典型使用场景** 1. **动态关联子查询** 当需要根据外层表字段动态生成子查询结果时，例如为每个用户生成最近 3 条订单记录： ```sql SELECT u.user_id, o.order_date FROM users u LATERAL ( SELECT order_date FROM orders WHERE user_id = u.user_id ORDER BY order_date DESC LIMIT 3 ) AS o; ``` 2. **替代复杂的 JOIN 操作** 在需要逐行处理逻辑的场景中（如计算行级统计值），`LATERAL` 可简化多层嵌套查询。例如统计每个部门的最高工资员工： ```sql SELECT d.dept_name, e.emp_name, e.salary FROM departments d LATERAL ( SELECT emp_name, salary FROM employees WHERE dept_id = d.dept_id ORDER BY salary DESC LIMIT 1 ) AS e; ``` --- ### **三、限制与注意事项** 1. **语法限制** • **位置约束**：`LATERAL` 派生表只能出现在 `FROM` 子句中，且需符合特定的 JOIN 类型（如 `INNER JOIN`、`LEFT JOIN`）。 • **聚合函数限制**：若子查询中包含聚合函数（如 `SUM()`），则外层查询不能是聚合操作的目标。 2. **性能影响** • **潜在风险**：若外层表数据量较大，逐行执行子查询可能导致性能下降。需通过执行计划（`EXPLAIN`）分析是否生成临时表（如 `MATERIALIZED` 标记）。 • **优化建议**：合理使用索引（如子查询中关联字段的索引）以减少重复计算。 --- ### **四、替代方案** 若需处理类似 Hive 中数组展开的场景（如 JSON 数组解析），可使用 MySQL 的 **JSON_TABLE() 函数**： ```sql SELECT t.id, j.item FROM my_table t, JSON_TABLE(t.items, '$[*]' COLUMNS (item VARCHAR(20) PATH '$')) AS j; ``` 此函数将 JSON 数组展开为多行，实现类似 `LATERAL VIEW EXPLODE` 的效果。 --- ### **总结** • **支持性**：MySQL 通过 `LATERAL` 关键字支持横向关联，但语法和场景与 Hive 的 `LATERAL VIEW` 不同。 • **适用性**：适合需要逐行动态关联子查询的场景，而非直接展开复杂数据类型。 • **建议**：在涉及动态行级关联时优先使用 `LATERAL`，处理 JSON/数组展开时结合 `JSON_TABLE()`。