WITH
      cte_name [(col_name [, col_name] ...)] AS (subquery)
      [, cte_name [(col_name [, col_name] ...)] AS (subquery)] …
SELECT [(col_name [, col_name] ...)] FROM cte_name;

mysql> SELECT * FROM  (SELECT YEAR(NOW())) AS year;
+-------------+
| YEAR(NOW()) |
+-------------+
|        2025 |
+-------------+
1 row in set (0.01 sec)

mysql> WITH year AS (SELECT YEAR(NOW())) SELECT * FROM year;
+-------------+
| YEAR(NOW()) |
+-------------+
|        2025 |
+-------------+
1 row in set (0.01 sec)

mysql> WITH cte_year_month (year, month) AS
  (SELECT YEAR(NOW()) AS year, MONTH(NOW()) AS month)
  SELECT * FROM cte_year_month;
+------+-------+
| year | month |
+------+-------+
| 2025 |     8 |
+------+-------+
1 row in set (0.02 sec)

mysql> WITH cte1(cte1_year, cte1_month) AS
  (SELECT YEAR(NOW()) AS cte1_year, MONTH(NOW()) AS cte1_month),
  cte2(cte2_year, cte2_month) AS
  (SELECT (cte1_year+1) AS cte2_year, (cte1_month + 1) AS cte2_month FROM cte1) 
  SELECT * FROM cte1 JOIN cte2;
+-----------+------------+-----------+------------+
| cte1_year | cte1_month | cte2_year | cte2_month |
+-----------+------------+-----------+------------+
|      2025 |          8 |      2026 |          9 |
+-----------+------------+-----------+------------+
1 row in set (0.01 sec)

WITH RECURSIVE
      cte_name [(col_name [, col_name] ...)] AS (subquery)
      [, cte_name [(col_name [, col_name] ...)] AS (subquery)] …
SELECT [(col_name [, col_name] ...)] FROM cte_name;

mysql> WITH RECURSIVE cte_num(num) AS
  ( SELECT 1 UNION ALL
  SELECT num + 1 FROM cte_num WHERE num < 8
  )
  SELECT * FROM cte_num;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
+-----+
8 rows in set (0.02 sec)

mysql> CREATE TABLE t_area(
  id INT NOT NULL,
  name VARCHAR(30),
  pid INT
  );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t_area (id, name, pid)
  VALUES
  (1, '河北省', NULL),
  (2, '邯鄲市', 1),
  (3, '邯山區(qū)', 2),
  (4, '復(fù)興區(qū)', 2),
  (5, '河南省', NULL),
  (6, '鄭州市', 5),
  (7, '中原區(qū)', 6);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_area;
+----+--------+------+
| id | name   | pid  |
+----+--------+------+
|  1 | 河北省 | NULL |
|  2 | 邯鄲市 |    1 |
|  3 | 邯山區(qū) |    2 |
|  4 | 復(fù)興區(qū) |    2 |
|  5 | 河南省 | NULL |
|  6 | 鄭州市 |    5 |
|  7 | 中原區(qū) |    6 |
+----+--------+------+
7 rows in set (0.03 sec)

mysql> WITH RECURSIVE area_depth(id, name, path) AS
  (
  SELECT id, name, CAST(id AS CHAR(300))
  FROM t_area WHERE pid IS NULL
  UNION ALL
  SELECT a.id, a.name, CONCAT(ad.path, '->', a.id)
  FROM area_depth AS ad 
  JOIN t_area AS a
  ON ad.id = a.pid
  )
  SELECT * FROM area_depth ORDER BY path;
+----+--------+---------+
| id | name   | path    |
+----+--------+---------+
|  1 | 河北省 | 1       |
|  2 | 邯鄲市 | 1->2    |
|  3 | 邯山區(qū) | 1->2->3 |
|  4 | 復(fù)興區(qū) | 1->2->4 |
|  5 | 河南省 | 5       |
|  6 | 鄭州市 | 5->6    |
|  7 | 中原區(qū) | 5->6->7 |
+----+--------+---------+
7 rows in set (0.02 sec)

mysql>  WITH RECURSIVE cte_num (n) AS
  (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM cte_num
  )
  SELECT * FROM cte_num;
Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

mysql> SHOW VARIABLES LIKE 'cte_max%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| cte_max_recursion_depth | 1000  |
+-------------------------+-------+
1 row in set (0.02 sec)

mysql> SET SESSION cte_max_recursion_depth=999999999;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'cte_max%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| cte_max_recursion_depth | 999999999 |
+-------------------------+-----------+
1 row in set (0.02 sec)

mysql> SHOW VARIABLES LIKE 'max_execution%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SET SESSION max_execution_time=1000; 
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_execution%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.02 sec)

mysql> WITH RECURSIVE cte(n) AS
  (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM CTE
  )
  SELECT * FROM cte;
Query execution was interrupted, maximum statement execution time exceeded

mysql>  CREATE TABLE t_genearted_column(
  a DOUBLE,
  b DOUBLE,
  c DOUBLE AS (a * a + b * b)
  );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t_genearted_column
  (a, b)
  VALUES
  (1, 1),
  (2, 2),
  (3, 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_genearted_column;
+---+---+----+
| a | b | c  |
+---+---+----+
| 1 | 1 |  2 |
| 2 | 2 |  8 |
| 3 | 3 | 18 |
+---+---+----+
3 rows in set (0.02 sec)

mysql> INSERT INTO t_genearted_column
  (a, b, c)
  VALUES
  (4, 4, 32);
3105 - The value specified for generated column 'c' in table 't_genearted_column' is not allowed.

mysql>  INSERT INTO t_genearted_column
  (a, b, c)
  VALUES
  (4, 4, DEFAULT);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_genearted_column;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    2 |
|    2 |    2 |    8 |
|    3 |    3 |   18 |
|    4 |    4 |   32 |
+------+------+------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE t_column_virsual (
  a DOUBLE,
  b DOUBLE,
  c DOUBLE GENERATED ALWAYS AS (a + b) VIRTUAL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t_column_virsual
  (a, b)
  VALUES
  (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_column_virsual;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 2 |
+---+---+---+
1 row in set (0.02 sec)

mysql> CREATE TABLE t_add_column(
  a DOUBLE,
  b DOUBLE
  );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t_add_column (a, b) VALUES (2, 2);
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE t_add_column ADD COLUMN c DOUBLE GENERATED ALWAYS AS(a * a + b * b) STORED;
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_add_column;
+---+---+---+
| a | b | c |
+---+---+---+
| 2 | 2 | 8 |
+---+---+---+
1 row in set (0.02 sec)

mysql> ALTER TABLE t_add_column
  MODIFY COLUMN c DOUBLE
  GENERATED ALWAYS AS (a * b)
  STORED;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>  SELECT * FROM t_add_column;
+---+---+---+
| a | b | c |
+---+---+---+
| 2 | 2 | 4 |
+---+---+---+
1 row in set (0.02 sec)

mysql> ALTER TABLE t_add_column DROP COLUMN c;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_add_column;
+---+---+
| a | b |
+---+---+
| 2 | 2 |
+---+---+
1 row in set (0.02 sec)