Mysqli 简明教程

MySQLi - Temporary Tables

在某些情况下，临时表可能对保存临时数据非常有用。应该了解关于临时表的最重要的事情是，在当前客户端会话终止时，这些表将被删除。

The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates.

如前所述，临时表只会持续到会话结束为止。如果您在 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.

Example

以下示例展示了临时表的使用方法。相同的代码可以使用 mysqli_query() 函数在 PHP 脚本中。

Here is an example showing you usage of temporary table. Same code can be used in PHP scripts using mysqli_query() function.

mysql> CREATE TEMPORARY TABLE SalesSummary (
   → product_name VARCHAR(50) NOT NULL
   → , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   → , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   → , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
   → );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
   → (product_name, total_sales, avg_unit_price, total_units_sold)
   → VALUES
   → ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

当您发出 SHOW TABLES 命令时，您的临时表将不会列在列表中。现在，如果您注销 MySQL 会话，然后发出 SELECT 命令，那么您将发现数据库中没有可用数据。即使您的临时表也不存在。

When you issue a SHOW TABLES command, then your temporary table would not be listed out in the list. Now, if you will log out of the MySQL session and then you will issue a SELECT command, then you will find no data available in the database. Even your temporary table would also not exist.

Dropping Temporary Tables

默认情况下，当您的数据库连接终止时，所有临时表将由 MySQL 删除。如果您仍然想在两者之间进行删除，您可以发出 DROP TABLE 命令。

By default, all the temporary tables are deleted by MySQL when your database connection gets terminated. Still if you want to delete them in between, then you do so by issuing DROP TABLE command.

以下是删除临时表的示例 −

Following is the example on dropping a temporary table −

mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist