MySQL 临时表
MySQL 临时表技术文档
什么是MySQL 临时表？
MySQL 临时表是一种特殊的表，只在当前MySQL连接的生命周期内存在，一旦连接关闭，临时表将被自动删除。临时表存储在磁盘上或内存中，可用于在多个会话之间传递中间结果和存储临时数据。
如何创建MySQL 临时表？
MySQL 临时表创建方式和普通表类似，只需在CREATE TABLE语句前添加“TEMPORARY”关键字即可。
CREATE TEMPORARY TABLE temp_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age INT
) ENGINE=InnoDB;
如何使用MySQL 临时表？
MySQL 临时表可在当前会话中使用，自动清除，无需DROP语句手动删除。临时表的使用方式和普通表一样，可以插入数据、查询数据以及进行连接等操作。
-- 插入数据
INSERT INTO temp_table (name, age) VALUES ('John', 23), ('Mary', 24), ('Tom', 25);
-- 查询数据
SELECT * FROM temp_table;
-- 连接查询
SELECT a.id, a.name, b.grade
FROM temp_table a
INNER JOIN grade_table b ON a.name=b.name;
MySQL 临时表的优缺点是什么？
优点
- 使用MySQL 临时表可以提升查询性能，减少临时表之间的数据传输。
- MySQL 临时表可以在当前连接的任何位置查询和更新。
- 数据被处理完后，临时表自动被删除，不会占用磁盘空间。
缺点
- MySQL 临时表只在当前连接下有效，无法在其他连接中使用。
- 临时表的数量受限于服务器的内存或磁盘容量以及系统配置。
- 在临时表过多、数据过大的情况下，可能会导致系统崩溃。
综合运用MySQL 临时表
案例1：数据导入
如果需要将csv文件导入到MySQL数据库中，可以使用MySQL 临时表存储中间数据，再用INSERT INTO语句插入到MySQL常规表中。
-- 创建临时表
CREATE TEMPORARY TABLE temp_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age INT
) ENGINE=InnoDB;
-- 加载csv文件到临时表
LOAD DATA LOCAL INFILE '/var/www/csv/filename.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
-- 将临时表中数据插入到常规表中
INSERT INTO user_table (name, age) SELECT name, age FROM temp_table;
-- 清空临时表
TRUNCATE TABLE temp_table;
案例2：数据清洗
如果需要进行数据清洗，可以使用MySQL 临时表存储中间结果和备份数据。
-- 创建备份表
CREATE TABLE user_bak AS SELECT * FROM user_table;
-- 数据清洗
DELETE FROM user_table WHERE age > 30;
-- 创建临时表存储中间结果
CREATE TEMPORARY TABLE temp_table AS SELECT name, age FROM user_table;
-- 恢复备份表，并将中间结果插入到常规表中
TRUNCATE TABLE user_table;
INSERT INTO user_table (name, age) SELECT name, age FROM temp_table;
-- 清空临时表
TRUNCATE TABLE temp_table;
总结
MySQL 临时表是一种非常实用的技术，在数据处理中发挥着重要的作用。使用临时表可以有效减少多次数据传输，提升查询性能，值得运用在实际开发中。但是需要注意的是，在使用临时表时要保证服务器的内存和磁盘容量，避免出现数据过多、过大等意外情况。