New feature interpretation | MySQL 8.0 general expression (WITH) in-depth usage

Author: Yang Taotao

Senior database expert, specializing in MySQL for more than ten years. Good at backup and recovery related to open source databases such as MySQL, PostgreSQL, MongoDB, SQL tuning, monitoring operation and maintenance, and high-availability architecture design. Currently working at Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial companies.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

It has been several years since MySQL 8.0 was released. The simple purpose and usage scenarios of the WITH statement (common expression) have been introduced before, similar to the following statement:

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

It just so happens that the customer consulted me before, is it possible to use WITH with statements such as UPDATE and DELETE? In other words, are there other uses that can simplify daily SQL, which is a bit confusing. Can you write a few examples to explain briefly?

In fact, the WITH expression can have the following combinations in addition to being used with SELECT:

insert with, with update, with delete, with with, with recursive (sequence of analog numbers, dates, etc.), WITH can define multiple tables

Let's take a look one by one:

1. Use WITH expression to create data

Using the WITH expression to create data is very simple, such as the following example: add 100 records to table y1, and the date field should be random.

localhost:ytt>create table y1 (id serial primary key, r1 int,log_date date);
Query OK, 0 rows affected (0.09 sec)

localhost:ytt>INSERT y1 (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

localhost:ytt>table y1 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. Use WITH expression to update table data

The WITH expression can be used with the UPDATE statement to execute the table record to be updated:

localhost:ytt>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,
    ->   y1 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

localhost:ytt>table y1 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. Use WITH expression to delete table data

For example, to delete a row with an odd ID, you can use a delete statement in the form of WITH DELETE:

localhost:ytt>WITH recursive tmp (a) AS
    -> (SELECT
    ->   1
    -> UNION
    -> ALL
    -> SELECT
    ->   a + 2
    -> FROM
    ->   tmp
    -> WHERE a < 100)
    -> DELETE FROM y1 WHERE id IN (TABLE tmp);
Query OK, 50 rows affected (0.02 sec)

localhost:ytt>table y1 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)

When used with DELETE, one thing to note: The WITH expression itself is read-only, so the WITH expression cannot be included in a multi-table DELETE. For example, changing the above statement to a multi-table delete form will directly report the error that the WITH expression cannot be updated.

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

4. WITH and WITH together

Prerequisite: WITH expressions cannot be in the same level, only one WITH expression is allowed in a level

localhost:ytt>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 multiple expressions to JOIN

Using the above example, rewrite multiple WITH into one WITH:

localhost:ytt>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 generates date sequence

Use the WITH expression to generate a date series, similar to the generate_series table function of POSTGRESQL. For example, starting from '2020-01-01', a one-month date series is generated:

localhost:ytt>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. The with expression makes a derived table

Using the list of dates just now,

localhost:ytt>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)

The WITH expression is very flexible, and different scenarios can be written differently, which can indeed simplify the writing of daily SQL.