如何在MySQL中使用临时表？

MySQL中的临时表是会话级别的表,在会话结束时自动删除。它有两种创建方式:

1. 使用CREATE TEMPORARY TABLE语句:

sql
CREATE TEMPORARY TABLE tmp_table (
    id INT PRIMARY KEY, 
    name VARCHAR(20)
);

这种方式创建的临时表会在当前会话结束后自动删除,其他会话无法访问。

2. 使用CREATE TABLE然后将ENGINE设置为TEMPORARY:

sql 
CREATE TABLE tmp_table (
    id INT PRIMARY KEY, 
    name VARCHAR(20)    
) ENGINE=TEMPORARY;  

这种方式创建的临时表具有相同的生命周期和访问性,但是可以使用ALTER TABLE进行结构修改,较为灵活。

临时表的特点:

1. 只在当前会话可见,会话结束后自动删除,不会记录在数据字典中。
2. DROP TABLE不能删除临时表,会话结束时自动删除。
3. 临时表的数据和结构只在当前会话中存在,退出会话后丢失。
4. 临时表可以使用相同的表名创建,数据不会互相影响。
5. 不支持触发器、外键约束等。
6. 可以通过参数keep_tables_on_commit指定提交事务后不删除临时表。

例1:
会话1:

sql
CREATE TEMPORARY TABLE tmp_table (id INT);
INSERT INTO tmp_table VALUES(1);

会话2:

sql  
SELECT * FROM tmp_table;   # 错误,无法访问
会话1:
sql
COMMIT;      # 提交事务,临时表依然存在
SELECT * FROM tmp_table;   # 返回1

退出会话1,再次访问tmp_table将报错。

临时表主要用于:

1. 存储中间结果集,进一步处理。
2. 避免在事务提交后立即删除数据。
3. 减少对INFORMATION_SCHEMA的访问。
4. 隔离一些临时数据,不进入数据字典,减少对主表的影响。