临时表
临时表可以被认为是一种复用查询结果的技术。
假设希望知道 Bookshop 应用当中最年长的作家们的一些情况,可能需要编写多个查询,而这些查询都需要使用到这个最年长作家列表。可以通过下面的 SQL 语句从 authors
表当中找出最年长的前 50 位作家作为研究对象。
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
查询结果如下:
+------------+---------------------+------+
| id | name | age |
+------------+---------------------+------+
| 4053452056 | Dessie Thompson | 80 |
| 2773958689 | Pedro Hansen | 80 |
| 4005636688 | Wyatt Keeling | 80 |
| 3621155838 | Colby Parker | 80 |
| 2738876051 | Friedrich Hagenes | 80 |
| 2299112019 | Ray Macejkovic | 80 |
| 3953661843 | Brandi Williamson | 80 |
...
| 4100546410 | Maida Walsh | 80 |
+------------+---------------------+------+
50 rows in set (0.01 sec)
在找到这 50 位最年长的作家后,希望缓存这个查询结果,以便后续的查询能够方便地使用到这组数据。如果使用一般的数据库表进行存储的话,在创建这些表时,需要考虑如何避免不同会话之间的表重名问题,而且可能在一批查询结束之后就不再需要这些表了,还需要及时地对这些中间结果表进行清理。
创建临时表
为了满足这类缓存中间结果的需求,TiDB 在 v5.3.0 版本中引入了临时表功能,对于临时表当中的本地临时表而言,TiDB 将会在会话结束的一段时间后自动清理这些已经没用的临时表,用户无需担心中间结果表的增多会带来管理上的麻烦。
临时表类型
TiDB 的临时表分为本地临时表和全局临时表:
- 本地临时表的表定义和表内数据只对当前会话可见,适用于暂存会话内的中间数据。
- 全局临时表的表定义对整个 TiDB 集群可见,表内数据只对当前事务可见,适用于暂存事务内的中间数据。
创建本地临时表
在创建本地临时表前,你需要给当前数据库用户添加上 CREATE TEMPORARY TABLES
权限。
- SQL
- Java
在 SQL 中,通过 CREATE TEMPORARY TABLE <table_name>
语句创建临时表,默认临时表的类型为本地临时表,它只能被当前会话所访问。
CREATE TEMPORARY TABLE top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
);
在创建完临时表后,你可以通过 INSERT INTO table_name SELECT ...
语句,将上述查询得到的结果导入到刚刚创建的临时表当中。
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
运行结果为:
Query OK, 50 rows affected (0.03 sec)
Records: 50 Duplicates: 0 Warnings: 0
在 Java 中创建本地临时表的示例如下:
public List<Author> getTop50EldestAuthorInfo() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
Statement stmt = conn.createStatement();
stmt.executeUpdate("""
CREATE TEMPORARY TABLE top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
);
""");
stmt.executeUpdate("""
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
""");
ResultSet rs = stmt.executeQuery("""
SELECT id, name FROM top_50_eldest_authors;
""");
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
创建全局临时表
- SQL
- Java
在 SQL 中,你可以通过加上 GLOBAL
关键字来声明你所创建的是全局临时表。创建全局临时表时必须在末尾 ON COMMIT DELETE ROWS
修饰,这表明该全局数据表的所有数据行将在事务结束后被删除。
CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors_global (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
) ON COMMIT DELETE ROWS;
在对全局临时表导入数据时,你需要特别注意,你必须通过 BEGIN
显式声明事务的开始。否则导入的数据在 INSERT INTO
语句执行后就清除掉,因为 Auto Commit 模式下,INSERT INTO
语句的执行结束,事务会自动被提交,事务结束,全局临时表的数据便被清空了。
在 Java 中使用全局临时表时,你需要将 Auto Commit 模式先关闭。在 Java 语言当中,你可以通过 conn.setAutoCommit(false);
语句来实现,当你使用完成后,可以通过 conn.commit();
显式地提交事务。事务在提交或取消后,在事务过程中对全局临时表添加的数据将会被清除。
public List<Author> getTop50EldestAuthorInfo() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.executeUpdate("""
CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
) ON COMMIT DELETE ROWS;
""");
stmt.executeUpdate("""
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
""");
ResultSet rs = stmt.executeQuery("""
SELECT id, name FROM top_50_eldest_authors;
""");
conn.commit();
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
查看临时表信息
通过 SHOW [FULL] TABLES
语句可以查看到已经创建的全局临时表,但是无法看到本地临时表的信息,TiDB 暂时也没有类似的 information_schema.INNODB_TEMP_TABLE_INFO
系统表存放临时表的信息。
例如,你可以在 table 列表当中查看到全局临时表 top_50_eldest_authors_global
,但是无法查看到 top_50_eldest_authors
表。
+-------------------------------+------------+
| Tables_in_bookshop | Table_type |
+-------------------------------+------------+
| authors | BASE TABLE |
| book_authors | BASE TABLE |
| books | BASE TABLE |
| orders | BASE TABLE |
| ratings | BASE TABLE |
| top_50_eldest_authors_global | BASE TABLE |
| users | BASE TABLE |
+-------------------------------+------------+
9 rows in set (0.00 sec)
查询临时表
在临时表准备就绪之后,你便可以像对一般数据表一样对临时表进行查询:
SELECT * FROM top_50_eldest_authors;
你可以通过表连接将临时表中的数据引用到你的查询当中:
EXPLAIN SELECT ANY_VALUE(ta.id) AS author_id, ANY_VALUE(ta.age), ANY_VALUE(ta.name), COUNT(*) AS books
FROM top_50_eldest_authors ta
LEFT JOIN book_authors ba ON ta.id = ba.author_id
GROUP BY ta.id;
与视图有所不同,在对临时表进行查询时,不会再执行导入数据时所使用的原始查询,而是直接从临时表中获取数据。在一些情况下,这会帮助你提高查询的效率。
删除临时表
本地临时表会在会话结束后连同数据和表结构都进行自动清理。全局临时表在事务结束后会自动清除数据,但是表结构依然保留,需要手动删除。
你可以通过 DROP TABLE
或 DROP TEMPORARY TABLE
语句手动删除本地临时表。例如:
DROP TEMPORARY TABLE top_50_eldest_authors;
你还可以通过 DROP TABLE
或 DROP GLOBAL TEMPORARY TABLE
语句手动删除全局临时表。例如:
DROP GLOBAL TEMPORARY TABLE top_50_eldest_authors_global;
限制
关于 TiDB 在临时表功能上的一些限制,你可以通过阅读参考文档中的临时表与其他 TiDB 功能的兼容性限制小节进行了解。