SQL:2023新特性详解：JSON_TABLE、GREATEST/LEAST、QUALIFY 到底怎么用？

这个DBA有点耶
78 阅读4分钟

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

上个月 code review，看到一个同事写了一段 30 行的 SQL，嵌套了三层子查询，就为了从 JSON 字段里取几个值。

我问他：“你知不知道 SQL:2023 有个东西叫 JSON_TABLE？”

他一脸懵：“SQL 还有版本？”

——有。而且 2023 版已经发布三年了。

一、SQL 标准演进简述

SQL 是有国际标准（ISO/IEC 9075）的，隔几年更新一版：

版本发布时间核心变化
SQL:19921992 年基础标准，大部分数据库支持
SQL:19991999 年引入正则表达式、递归查询
SQL:20032003 年引入窗口函数、XML 相关
SQL:20082008 年引入 TRUNCATE、INSTEAD OF 触发器
SQL:20112011 年引入时序数据、增强窗口函数
SQL:20162016 年引入 JSON 支持
SQL:20232023 年JSON_TABLE、GREATEST/LEAST、QUALIFY、图查询

SQL:2023 是第九版，也是迄今为止功能最丰富的一版。下面三个特性是最容易上手、最常用到的。

二、新特性一：JSON_TABLE

背景：此前操作 JSON 字段主要依赖 JSON_EXTRACT 等函数提取单个值，当 JSON 内部存在数组结构、需要将数组元素展开为多行时，不得不在应用层做二次处理。JSON_TABLE 的作用：将 JSON 数据直接映射为关系型表格，数组元素可展开为多行记录。

旧写法（假设 orders 表 order_info 字段存了如下 JSON）：

{"order_id": "ORD-001", "items": [{"name": "手机", "price": 5999}, {"name": "充电器", "price": 99}]}

SELECT 
    JSON_EXTRACT(order_info, '$.order_id') AS order_id,
    JSON_EXTRACT(items, '$[*].name') AS item_names,
    JSON_EXTRACT(items, '$[*].price') AS item_prices
FROM orders;

多个 JSON_EXTRACT 嵌套调用，结果以数组形式返回，需在应用层拆解。

SQL:2023 新写法：

SELECT t.order_id, jt.name, jt.price
FROM orders o,
JSON_TABLE(
    o.order_info,
    '$.items[*]' COLUMNS(
        name VARCHAR(100) PATH '$.name',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt;

JSON_TABLE 位于 FROM 子句，将 JSON 数组的每个元素映射为一行，数据在 SQL 层完成结构化展开，应用层直接拿到规整结果集。

性能对比：某日志解析场景，100 万行 JSON 数据，旧写法约 4.2 秒，新写法约 2.8 秒，提升约 33%。

三、新特性二：GREATEST / LEAST

背景：MySQL 很早就支持了 GREATEST 和 LEAST，但直到 SQL:2023 才被正式纳入国际标准。在此之前，取多列极值只能使用 CASE WHEN 或 UNION 方式实现。

旧写法：

SELECT 
    CASE 
        WHEN col1 >= col2 AND col1 >= col3 THEN col1
        WHEN col2 >= col1 AND col2 >= col3 THEN col2
        ELSE col3
    END AS max_value
FROM table;

SQL:2023 新写法：

SELECT GREATEST(col1, col2, col3) AS max_value FROM table;
SELECT LEAST(col1, col2, col3) AS min_value FROM table;

一行完成，支持任意多个参数。任一参数为 NULL 则返回 NULL（与 MySQL 行为一致）。

四、新特性三：QUALIFY

背景：窗口函数的计算结果无法直接在 WHERE 子句中使用，必须通过子查询或 CTE 包装一层。QUALIFY 解决了这个痛点。

旧写法（查每个分类销售额排名前 3 的产品）：

WITH ranked AS (
    SELECT 
        category,
        product,
        sales,
        ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
)
SELECT category, product, sales
FROM ranked
WHERE rn <= 3;

SQL:2023 新写法：

sql

SELECT 
    category,
    product,
    sales,
    ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales DESC) AS rn
FROM products
QUALIFY rn <= 3;

QUALIFY 直接过滤窗口函数结果，无需额外包装层。

五、补充：SQL:2023 其他实用特性

  • 十六进制/八进制字面量：0xFF、0o755
  • 数字下划线分组：1_000_000 替代 1000000
  • ANY_VALUE()  ：解决 ONLY_FULL_GROUP_BY 模式下的限制
  • SQL/PGQ（图查询）  ：SQL 标准中首次纳入图查询能力

六、主流数据库兼容性一览

数据库GREATEST/LEASTJSON_TABLEQUALIFY
MySQL 8.0✅❌❌
PostgreSQL 16+✅⚠️ 部分❌
Oracle 23c✅✅❌
SQL Server 2022❌❌❌
Snowflake/BigQuery✅✅✅

升级建议：GREATEST/LEAST 兼容性最好，可优先推广。JSON_TABLE 需确认数据库版本。QUALIFY 当前主要在云数仓场景可用。

七、小结

SQL:2023 新增的特性远不止这三个，但它们是最贴近日常开发、改造门槛最低的一组。

下次写 SQL 的时候，试试这些新写法——代码量减半，可读性翻倍。

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~

avatar
文章
阅读
粉丝