Mysql 简明教程
MySQL - Temporary Tables
What are Temporary Tables?
Temporary Tables 表是在数据库中创建的，用于暂时存储数据。一旦当前的客户端会话被终止或结束，这些表将会被自动删除。此外，如果用户决定手动删除，这些表也可以被明确删除。你可以对临时表执行各种 SQL 操作，就像永久表一样，包括 CREATE、UPDATE、DELETE、INSERT、JOIN 等。
The Temporary Tables are the tables that are created in a database to store data temporarily. These tables will be automatically deleted once the current client session is terminated or ends. In addition to that, these tables can be deleted explicitly if the users decide to drop them manually. You can perform various SQL operations on temporary tables, just like you would with permanent tables, including CREATE, UPDATE, DELETE, INSERT, JOIN, etc.
临时表是在 MySQL 3.23 版中引进的。如果你使用的是比 3.23 旧的 MySQL 旧版本，你将不能使用临时表，而必须用 Heap Tables 代替。
Temporary tables were introduced in MySQL version 3.23. If you’re using an older version of MySQL that’s older than 3.23, you won’t be able to use temporary tables, instead you can use the Heap Tables.
如前所述，临时表只会持续到会话结束为止。如果您在 PHP 脚本中运行代码，那么临时表将在脚本执行完毕后自动销毁。如果您通过 MySQL 客户端程序连接到 MySQL 数据库服务器，那么该临时表将一直存在到您关闭客户端或手动销毁表为止。
As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.
Creating Temporary Tables in MySQL
在 MySQL 中创建临时表非常类似于创建常规数据库表。但是，我们使用 CREATE TEMPORARY TABLE 语句来代替使用 CREATE TABLE。
Creating a temporary table in MySQL is very similar to creating a regular database table. But, instead of using CREATE TABLE, we use CREATE TEMPORARY TABLE statement.
Syntax
以下是在 MySQL 中创建临时表的语法：
Following is the syntax to create a temporary table in MySQL −
CREATE TEMPORARY TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);Example
首先，让我们用下列查询来创建一个名为 CUSTOMERS 的临时表：
First of all, let us create a temporary table named CUSTOMERS using the below query −
CREATE TEMPORARY TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);类似于正常表，我们可以使用 INSERT 语句来向临时表插入记录。我们这里向上面创建的临时表中插入 3 条记录：
Similar to normal tables, we can insert records into a temporary table using the INSERT statement. Here, we are inserting three records into the above created temporary table −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 );执行以下查询来显示临时表 CUSTOMERS 中的所有记录。
Execute the following query to display all the records of the temporary table CUSTOMERS.
SELECT * FROM CUSTOMERS;以下是 CUSTOMERS 表的记录：
Following are the records of CUSTOMERS table −
当我们发布一则 SHOW TABLES 指令时，我们的临时表不会被显示在表中。若要验证临时表是否存在，我们需要使用 SELECT 语句取得其数据。我们结束会话时，临时表将被删除，因此如果我们退出登录 MySQL 并尝试发布 SELECT 指令，我们不会在数据库中看到临时表。
When we issue a SHOW TABLES command, our temporary table won’t be displayed in the list of tables. To verify if the temporary table exists, we need to use the SELECT statement to retrieve its data. Temporary tables will be deleted when we ends our session, so if we log out of MySQL and then try to issue the SELECT command, we won’t find the temporary table in the database.
Dropping Temporary Tables in MySQL
虽然 MySQL 会在您的数据库连接结束时自动移除临时表，但如果我们愿意，我们仍可以用 DROP TEMPORARY TABLE 指令自行删除它们。
Though MySQL automatically removes temporary tables when your database connection ends, we can still delete them ourselves by using the DROP TEMPORARY TABLE command if we want to.
Syntax
以下是 MySQL 中用于删除临时表的语法 −
Following is the syntax for dropping a temporary table in MySQL −
DROP TEMPORARY TABLE table_name;Example
在以下查询中，我们正在删除前一个示例中创建的临时表 CUSTOMERS −
In the following query, we are dropping the temporary table CUSTOMERS that was created in the previous example −
DROP TEMPORARY TABLE CUSTOMERS;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.00 sec)Verification
现在，让我们通过使用以下查询取得 CUSTOMERS 记录来验证该临时表 −
Now, let us verify the temporary table CUSTOMERS by retrieving it’s records using the following query −
SELECT * FROM CUSTOMERS;由于我们移除了临时表 CUSTOMERS，因此它会生成一张错误报告，表明该表不存在。
Since we removed the the temporary table CUSTOMERS, it will generate an error saying the table does not exist.
ERROR 1146: Table 'TUTORIALS.CUSTOMERS' doesn't existCreating Temporary table Using a Client Program
除了使用 MySQL 查询在 MySQL 数据库中创建一张临时表外，我们还可以在一张表上使用一个客户端程序来执行 “TEMPORARY TABLE” 操作。
In addition to create a temporary table in MySQL Database using the MySQL query, we can also perform the "TEMPORARY TABLE" operation on a table using a client program.