MySQL临时表的理解和用法

在MySQL中,临时表可以用于临时存储数据。与普通表格相比,临时表只能用于当前会话,并且会话结束后临时表会被自动删除。

临时表特性

  • 会话结束或数据库连接断开后,MySQL会自动删除临时表,如果有需要,也可以手动删除;

  • 临时表只能在当前会话中访问,不同会话可以创建同名的临时表,且不会互相影响;

  • 临时表的名称可以和已有的普通表一致(不建议这么做),出现这种情况时,优先使用临时表,普通表将无法访问,直到临时表被删除。

  • show tables语句不会显示临时表;

  • 同一个临时表在一次查询中最多使用一次,否则会报错, 例如:

    1
    2
    3
    4
    5
    select * from tmp_admin where id in (select * from tmp_admin where id >2);

    select * from tmp_admin join tmp_admin as t2;

    select * from tmp_admin union select * from tmp_admin;

    以上SQL将会提示:

    1
    ERROR 1137 (HY000): Can't reopen table: 'tmp_admin'

创建和使用临时表

创建临时表可以使用CREATE TEMPORARY TABLE语句,跟普通表格相比,多了一个TEMPORARY关键字。例如:

1
2
3
4
5
CREATE TEMPORARY TABLE user ( 
id INT,
name VARCHAR(32),
age INT
);

向临时表插入几条测试数据,与普通表一样操作:

1
insert into user values(1, "Mao", 30), (2, "Bob", 28), (3, "Nik", 26);

查询临时表中的数据:

1
2
3
4
5
6
7
8
9
MariaDB [test]> select * from user;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | Mao | 30 |
| 2 | Bob | 28 |
| 3 | Nik | 26 |
+------+------+------+
3 rows in set (0.00 sec)

基于已有表格创建临时表

也可以基于其它表格的结构和数据来创建临时表。例如基于admin表格的结构和所有数据来创建临时表:

1
2
CREATE TEMPORARY TABLE tmp_admin
select * from admin;

创建临时表时,只需要admin表的结构,不要数据:

1
2
CREATE TEMPORARY TABLE tmp_admin 
select * from admin limit 0;

admin表格查询数据并插入临时表中:

1
2
insert into tmp_admin (id, admin_name, age) 
select id, admin_name,age from admin;

删除临时表

数据库连接断开后,临时表会被自动删除。不过有时候也需要手动删除,比如使用数据库连接池的时候,应用程序运行结束,但是连接没有被释放,下一次应用会复用数据库连接,就可以看得到之前创建的临时表。

删除临时表使用DROP TEMPORARY TABLE语句,例如删除tmp_admin临时表:

1
DROP TEMPORARY TABLE tmp_admin;

需要注意的是,与普通表一样,可以使用drop table语句来删除临时表,不过不建议这么做,可能由于误操作使用drop table把普通表删除了。

使用DROP TEMPORARY TABLE可以保证删除的肯定是临时表,如果尝试使用DROP TEMPORARY TABLE删除一个普通表,则会报错:

1
ERROR 1051 (42S02): Unknown table 'admin'

参考

  1. MySQL 5.7 Reference Manual :: CREATE TEMPORARY TABLE Syntax
  2. MySQL 5.7 Reference Manual :: TEMPORARY Table Problems