MySQL 临时表原理及创建方法解析

什么是 MySQL 临时表

MySQL 临时表是在数据库连接结束时自动删除的表，也就是说，它们只存在于当前连接中，也只能在当前连接范围内使用。

临时表可以通过 CREATE TEMPORARY TABLE 或 CREATE TEMPORARY TABLE IF NOT EXISTS 语句创建。与普通表不同的是，临时表会被写入到临时文件夹或内存中，而不是被存储在磁盘上。临时表支持索引、存储引擎和各种数据类型。

在某些情况下，创建临时表可以提高数据库查询的性能。临时表也可以用于用于存储一些中间结果。

创建临时表

创建临时表的方法与创建普通表类似，只需要在前面加上 TEMPORARY 关键字即可。可以通过以下 SQL 语句创建一个简单的临时表：

CREATE TEMPORARY TABLE temp_table (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

上述语句会在当前连接中创建一个名为 temp_table 的临时表，包含 id 和 name 两个字段。临时表使用 InnoDB 存储引擎，并使用 utf8mb4 字符集。其中 id 字段为主键，自增 ID 约束。

我们也可以通过 IF NOT EXISTS 语句创建一个已存在的临时表：

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用临时表

在通过 CREATE TEMPORARY TABLE 创建了临时表后，我们可以像普通表一样对其进行增删改查操作。例如，可以通过以下语句向临时表中插入数据：

INSERT INTO temp_table (name) VALUES ('Tom'), ('Jerry'), ('Bob');

我们也可以通过以下语句查询临时表中的数据：

SELECT * FROM temp_table;

当我们使用 DROP TABLE 或 TRUNCATE TABLE 语句删除普通表时，数据是永久性删除的。但是在临时表中，数据只会在当前连接结束时被删除。如果我们需要手动删除临时表，可以使用以下语句：

DROP TEMPORARY TABLE IF EXISTS temp_table;

临时表的使用场景

1. 一般情况下，我们可以通过查询优化来提高查询性能。但是在某些情况下，创建临时表可以更有效地提高查询性能。例如，在查询结果集较大时，将结果存储到临时表中进行排序、分页甚至二次查询等，可以提高查询性能。

2. 有些情况下，我们需要使用某些统计函数，如 COUNT()、SUM() 等，但是又不需要全部结果集。这时候我们可以使用临时表来存储中间结果，以提高查询效率。

总结

MySQL 临时表是一种临时性的表，只存在于当前连接，当连接结束后，临时表也会被自动删除。创建临时表可以提高查询性能和存储查询结果。但是在大量数据的情况下，可能会对内存或磁盘空间造成压力，需要谨慎使用。