MySQL临时表

最新推荐文章于 2025-06-06 14:43:57 发布

jesseyoung

最新推荐文章于 2025-06-06 14:43:57 发布

阅读量3k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据仓库-MySQL 文章标签： mysql 临时表

本文链接：https://blog.youkuaiyun.com/jesseyoung/article/details/37809849

数据仓库-MySQL 专栏收录该内容

41 篇文章

订阅专栏

本文深入探讨了SQL中临时表的特性、创建方法、与持久表的区别、使用限制及实例演示，帮助开发者掌握如何高效利用临时表进行数据处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 临时表简介

通常情况下我们创建的表具有一个较长的生命周期，它使用CREATE TABLE语句创建，我们也称之为持久表。而有时候我们需要临时表，来临时存储复杂的select语句结果。

临时表的生命周期较短，而且只能对创建它的session（当前连接）可见，当session退出时临时表被删除，也可使用drop table语句删除。在两个不同的链接里使用相同的临时表名不会产生冲突，它们只对当前session有效。

临时表的名字可以和一个已有的持久表的名字相同，持久表不会被删除，但会被当前session的临时表隐藏。

临时表和内存表都是数据存放在内存中（临时表表结构也在内存中，内存表表结构在磁盘上），临时表最大所需内存需要通过tmp_table_size = 1024M设定，内存表最大所需内存需要通过max_heap_table_size=1024M设定。当数据超过临时表的最大值设定时，自动转为基于磁盘的MyISAM表，存储在指定的tmpdir目录，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，会提示数据满错误。

2 临时表创建

2.1 创建语法

CREATE TEMPORARY TABLE ...

2.2 例如：

mysql> create temporary table tmptable (c1 int, c2 int);

当创建临时表后，会在操作系统/tmp文件夹下生成类似临时文件

[root@localhost tmp]# ls
#sqla5a_3_2.frm

3 临时表操作

3.1 插入数据

mysql> insert into tmptable values(1,2);
mysql> select * from tmptable;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
+------+------+

3.2 和持久表的对比

在当前临时表所在的数据库下创建持久表

mysql> create table tmptable (c1 int);
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| tmptable        |
+-----------------+
mysql> insert into tmptable values(1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

持久表创建成功，但被临时表隐藏，只显示一个表。
4 临时表删除

mysql> drop table tmptable;
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| tmptable        |
+-----------------+
mysql> insert into tmptable values(1);
mysql> select * from tmptable;
+------+
| c1   |
+------+
|    1 |
+------+

临时表删除后持久表显示表现出来，并可对其操作。

5 临时表使用限制

5.1 同一个query中，临时表只能被打开一次（持久表可以被多次打开），对比如下：

持久表

mysql> select * from tmptable;
+------+
| c1   |
+------+
|    1 |
+------+
mysql> select * from tmptable, tmptable AS t2;
+------+------+
| c1   | c1   |
+------+------+
|    1 |    1 |
+------+------+

临时表

mysql> select * from tmptable;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
+------+------+
mysql> select * from tmptable, tmptable AS t2;
ERROR 1137 (HY000): Can't reopen table: 'tmptable'

**********************************************************************************************
转载请注明出处：http://blog.youkuaiyun.com/jesseyoung/article/details/37809849
**********************************************************************************************