MySQL中用with as 解决临时表的问题

MySQL中用with as 解决临时表的问题

1. MySQL不能创建临时表

写存储过程中，经常需要做过渡的临时表，主要是字段多，但是MySQL不支持，或者调整参数。

create table ts_tmp as 
select * from test_grade f where f.math>60 and f.english>70;
	

报错：

1786 - Statement violates GTID consistency: CREATE TABLE … SELECT.
时间: 0.002s

2. With As

WITH 子句，称为 Common Table Expressions（CTE），是一种在 SQL 查询中创建临时结果集的方法，存在于单个语句的范围内，以便在查询中多次引用。它可以使 SQL 查询更加模块化和可读。
实现临时表的效果和目的

语法：

WITH temporary_table_name (column1, column2, ...) AS (
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
)
SELECT * FROM temporary_table_name;

3. 用例

（1）简单查询

filter_class 实际上可以理解为是一个临时表名，后面对这个临时表进行查询。

mysql> with filter_class as
    -> (select * from test_grade t where t.classno='Class1')
    -> select * from filter_class f where f.math>60 and f.english>70;
+----------+------------+---------+------+---------+---------+-----------+
| stu_id   | test_date  | classno | math | english | physics | chemistry |
+----------+------------+---------+------+---------+---------+-----------+
| jxBeehzP | 2024-07-28 | Class1  |   65 |      84 |      27 |        81 |
| ZRb81xrr | 2024-06-20 | Class1  |   92 |      93 |      91 |        74 |
| 7Mot1vl3 | 2024-05-23 | Class1  |   68 |      79 |      91 |        16 |
+----------+------------+---------+------+---------+---------+-----------+
3 rows in set (0.01 sec)

mysql>

（2）组合查询

有两个CTE，filter_class1 和 filter_class2 ，视为两个表，使用标准的where语句即可。

mysql> with filter_class1 as
    -> (select * from test_grade t where t.classno='Class1'),
    -> filter_class2 as
    -> (select * from test_grade t where t.physics>90 and t.math>80)
    -> select a.* from filter_class1 a,filter_class2 b where a.stu_id=b.stu_id;
+----------+------------+---------+------+---------+---------+-----------+
| stu_id   | test_date  | classno | math | english | physics | chemistry |
+----------+------------+---------+------+---------+---------+-----------+
| nRZdY132 | 2024-06-06 | Class1  |   94 |      56 |      95 |        10 |
| ZRb81xrr | 2024-06-20 | Class1  |   92 |      93 |      91 |        74 |
| bzaVZYKH | 2024-05-18 | Class1  |   99 |      18 |      94 |        17 |
+----------+------------+---------+------+---------+---------+-----------+
3 rows in set (0.01 sec)

4.小结

• 使用with子句可以让子查询重用相同的with查询块，通过select调用（with子句只能被select查询块引用），一般在with查询用到多次情况下。在引用的select语句之前定义，同级只能定义with关键字只能使用一次，多个用逗号分割。
• 最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割，with 子句的查询必须用括号括起来。
• 前面with子句定义的查询在后面的with子句中可以使用。但同一with子句内部不能嵌套with子句。
• 当一个查询块名字和一个表名或其他的对象相同时，解析器从内向外搜索，优先使用子查询块名字。