MySQL 8.0 的通用表达式(WITH)用法

原创一名数据库小白的成长笔记 2025-04-13

229

MySQL 8.0 的通用表达式WITH用法

通过临时命名结果集简化复杂查询。以下是其深入用法解析，结合典型场景和示例说明：

一、基础概念与语法

通用表达式（CTE）是一个命名的临时结果集，作用域为当前语句，支持递归查询和多次引用。基本语法：

WITH [RECURSIVE] cte_last_name (column_list) AS (
    -- 初始查询（种子查询）
    SELECT ...
    UNION [ALL]
    -- 递归部分（仅递归 CTE 需要）
    SELECT ...
)
SELECT * FROM cte_last_name; -- 主查询引用 CTE

with tmp(a) as (select 1 union all select 2) select * from tmp;

其实 WITH 表达式除了和 SELECT 一起用， 还可以有下面的组合：

insert with 、with update、with delete、with  with、with recursive(可以模拟数字、日期等序列)、WITH 可以定义多张表

二、高级用法场景

1. 用 WITH 表达式来造数据

利用 CTE 快速生成序列或随机数据，常用于模拟测试场景：给表 test 添加100条记录，日期字段要随机。

create table test (id serial primary key, r1 int,log_date date);
Query OK, 0 rows affected (0.09 sec)

INSERT test (r1,log_date)
WITH recursive tmp (a, b) AS
(SELECT
  1,
  '2021-04-20'
UNION
ALL
SELECT
  ROUND(RAND() * 10),
  b - INTERVAL ROUND(RAND() * 1000) DAY
FROM
  tmp
LIMIT 100) TABLE tmp;

Query OK, 100 rows affected (0.03 sec)
Records: 100  Duplicates: 0  Warnings: 0

table test limit 10;
+----+------+------------+
| id | r1   | log_date   |
+----+------+------------+
|  1 |    1 | 2021-04-20 |
|  2 |    8 | 2020-04-02 |
|  3 |    5 | 2019-05-26 |
|  4 |    1 | 2018-01-21 |
|  5 |    2 | 2016-09-08 |
|  6 |    9 | 2016-06-14 |
|  7 |    7 | 2016-02-06 |
|  8 |    6 | 2014-03-18 |
|  9 |    6 | 2011-08-25 |
| 10 |    9 | 2010-02-02 |
+----+------+------------+
10 rows in set (0.00 sec)

2. 用 WITH 表达式来更新表数据

WITH 表达式可以与 UPDATE 语句一起，来执行要更新的表记录：

WITH recursive tmp (a, b, c) AS
(SELECT
  1,
  1,
  '2021-04-20'
UNION ALL
SELECT
  a + 2,
  100,
  DATE_SUB(
    CURRENT_DATE(),
    INTERVAL ROUND(RAND() * 1000, 0) DAY
  )
FROM
  tmp
WHERE a < 100)
UPDATE
  tmp AS a,
  test AS b
SET
  b.r1 = a.b
WHERE a.a = b.id;

Query OK, 49 rows affected (0.02 sec)
Rows matched: 50  Changed: 49  Warnings: 0

table test limit 10;
+----+------+------------+
| id | r1   | log_date   |
+----+------+------------+
|  1 |    1 | 2021-04-20 |
|  2 |    8 | 2019-12-26 |
|  3 |  100 | 2018-06-12 |
|  4 |    8 | 2017-07-11 |
|  5 |  100 | 2016-08-10 |
|  6 |    9 | 2015-09-14 |
|  7 |  100 | 2014-12-19 |
|  8 |    2 | 2014-08-13 |
|  9 |  100 | 2014-08-05 |
| 10 |    8 | 2011-11-12 |
+----+------+------------+
10 rows in set (0.00 sec)

3. 用 WITH 表达式来删除表数据

比如删除 ID 为奇数的行，可以用 WITH DELETE 形式的删除语句：

WITH recursive tmp (a) AS
(SELECT
  1
UNION
ALL
SELECT
  a + 2
FROM
  tmp
WHERE a < 100)
DELETE FROM test WHERE id IN (TABLE tmp);

Query OK, 50 rows affected (0.02 sec)

table test limit 10;
+----+------+------------+
| id | r1   | log_date   |
+----+------+------------+
|  2 |    6 | 2019-05-16 |
|  4 |    8 | 2015-12-07 |
|  6 |    2 | 2014-05-14 |
|  8 |    7 | 2010-05-07 |
| 10 |    3 | 2007-03-27 |
| 12 |    6 | 2006-12-14 |
| 14 |    3 | 2004-04-22 |
| 16 |    7 | 2001-09-16 |
| 18 |    7 | 2001-01-04 |
| 20 |    7 | 2000-02-12 |
+----+------+------------+
10 rows in set (0.00 sec)

与 DELETE 一起使用，要注意一点：WITH 表达式本身数据为只读，所以多表 DELETE 中不能包含 WITH 表达式。比如把上面的语句改成多表删除形式会直接报 WITH 表达式不可更新的错误。

WITH recursive tmp (a) AS
  (SELECT
    1
  UNION
  ALL
  SELECT
    a + 2
  FROM
    tmp
  WHERE a < 100)
  delete a,b from test a join tmp b where a.id = b.a;
ERROR 1288 (HY000): The target table b of the DELETE is not updatable

4. WITH 和 WITH 一起用

前提条件：WITH 表达式不能在同一个层级，一个层级只允许一个 WITH 表达式

SELECT * FROM  
(
  WITH tmp1 (a, b, c) AS 
  (
    VALUES
      ROW (1, 2, 3),
      ROW (3, 4, 5),
      ROW (6, 7, 8)
  ) SELECT  * FROM
      (
        WITH tmp2 (d, e, f) AS (
          VALUES
            ROW (100, 200, 300),
            ROW (400, 500, 600)
          ) TABLE tmp2
      ) X
        JOIN tmp1 Y
) Z ORDER BY a;
+-----+-----+-----+---+---+---+
| d   | e   | f   | a | b | c |
+-----+-----+-----+---+---+---+
| 400 | 500 | 600 | 1 | 2 | 3 |
| 100 | 200 | 300 | 1 | 2 | 3 |
| 400 | 500 | 600 | 3 | 4 | 5 |
| 100 | 200 | 300 | 3 | 4 | 5 |
| 400 | 500 | 600 | 6 | 7 | 8 |
| 100 | 200 | 300 | 6 | 7 | 8 |
+-----+-----+-----+---+---+---+
6 rows in set (0.01 sec)

5. WITH 多个表达式来 JOIN

用上面的例子，改写多个 WITH 为一个 WITH：

WITH
tmp1 (a, b, c) AS 
(
VALUES
ROW (1, 2, 3),
ROW (3, 4, 5),
ROW (6, 7, 8)
),
tmp2 (d, e, f) AS (
    VALUES
      ROW (100, 200, 300),
      ROW (400, 500, 600)
)

SELECT * FROM  tmp2,tmp1 ORDER BY a;
+-----+-----+-----+---+---+---+
| d   | e   | f   | a | b | c |
+-----+-----+-----+---+---+---+
| 400 | 500 | 600 | 1 | 2 | 3 |
| 100 | 200 | 300 | 1 | 2 | 3 |
| 400 | 500 | 600 | 3 | 4 | 5 |
| 100 | 200 | 300 | 3 | 4 | 5 |
| 400 | 500 | 600 | 6 | 7 | 8 |
| 100 | 200 | 300 | 6 | 7 | 8 |
+-----+-----+-----+---+---+---+
6 rows in set (0.00 sec)

6. with 生成日期序列

用 WITH 表达式生成日期序列，类似于 POSTGRESQL 的 generate_series 表函数，比如，从 ‘2020-01-01’ 开始，生成一个月的日期序列：

WITH recursive seq_date (log_date) AS
      (SELECT
        '2020-01-01'
      UNION
      ALL
      SELECT
        log_date + INTERVAL 1 DAY
      FROM
        seq_date
      WHERE log_date + INTERVAL 1 DAY < '2020-02-01')
      SELECT
        log_date
      FROM
        seq_date;
+------------+
| log_date   |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
| 2020-01-04 |
| 2020-01-05 |
| 2020-01-06 |
| 2020-01-07 |
| 2020-01-08 |
| 2020-01-09 |
| 2020-01-10 |
| 2020-01-11 |
| 2020-01-12 |
| 2020-01-13 |
| 2020-01-14 |
| 2020-01-15 |
| 2020-01-16 |
| 2020-01-17 |
| 2020-01-18 |
| 2020-01-19 |
| 2020-01-20 |
| 2020-01-21 |
| 2020-01-22 |
| 2020-01-23 |
| 2020-01-24 |
| 2020-01-25 |
| 2020-01-26 |
| 2020-01-27 |
| 2020-01-28 |
| 2020-01-29 |
| 2020-01-30 |
| 2020-01-31 |
+------------+
31 rows in set (0.00 sec)

7. with 表达式做派生表

使用刚才那个日期列表，

SELECT
      *
    FROM
      (
        WITH recursive seq_date (log_date) AS
        (SELECT
          '2020-01-01'
        UNION
        ALL
        SELECT
          log_date + INTERVAL 1 DAY
        FROM
          seq_date
        WHERE log_date+ interval 1 day  < '2020-02-01')
select * 
        FROM
          seq_date
        ) X
        LIMIT 10;
+------------+
| log_date   |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
| 2020-01-04 |
| 2020-01-05 |
| 2020-01-06 |
| 2020-01-07 |
| 2020-01-08 |
| 2020-01-09 |
| 2020-01-10 |
+------------+
10 rows in set (0.00 sec)

WITH 表达式使用非常灵活，不同的场景可以有不同的写法，的确可以简化日常 SQL 的编写。

三、性能优化与限制

替代派生表
CTE 可替代子查询，避免重复计算，提升代码可读性。

递归深度控制
默认递归深度限制为1000层，可通过参数cte_max_recursion_depth调整。超限时需优化查询或调整配置。

权限与兼容性
CTE 无需单独创建表的权限，但需注意 MySQL 8.0 之前的版本不支持此特性。

四、总结

WITH 表达式通过以下场景显著提升SQL编写效率：

复杂查询模块化：将多层嵌套子查询拆解为可读性更强的 CTE。
递归数据处理：轻松处理树形结构或生成连续序列。
动态DML操作：结合 UPDATE/DELETE 实现基于临时结果集的数据更新。
建议在实际开发中结合EXPLAIN分析执行计划，确保递归查询或大数据量操作的性能。更多高级用法可参考 MySQL 官方文档，
WITH (Common Table Expressions)官方文档链接：https://dev.mysql.com/doc/refman/8.0/en/with.html

墨力计划 mysql

最后修改时间：2025-04-13 21:21:36

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者