mysql 临时表与内存表_MySQL-临时表

mysql 临时表与内存表

MySQL-临时表 (MySQL - 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.

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

什么是临时表？ (What are Temporary Tables?)

Temporary tables were added in the MySQL Version 3.23. If you use an older version of MySQL than 3.23, you cannot use the temporary tables, but you can use Heap Tables.

在MySQL 3.23版中添加了临时表。 如果您使用MySQL版本早于3.23，则不能使用临时表，但可以使用堆表 。

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.

如前所述，临时表仅在会话处于活动状态时才持续存在。 如果您在PHP脚本中运行代码，则脚本执行完后，临时表将自动销毁。 如果您通过MySQL客户端程序连接到MySQL数据库服务器，则临时表将存在，直到您关闭客户端或手动销毁该表为止。

例 (Example)

The following program is an example showing you the usage of the temporary table. The same code can be used in PHP scripts using the mysql_query() function.

以下程序是一个示例，向您显示临时表的用法。 可以使用mysql_query()函数在PHP脚本中使用相同的代码。

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)

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 will not exist.

发出SHOW TABLES命令时，临时表不会在列表中列出。 现在，如果您要退出MySQL会话，然后发出SELECT命令，那么数据库中将找不到可用的数据。 甚至您的临时表也将不存在。

删除临时表 (Dropping Temporary Tables)

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 the DROP TABLE command.

默认情况下，当数据库连接终止时，MySQL将删除所有临时表。 仍然，如果要在它们之间删除它们，则可以通过发出DROP TABLE命令来删除它们。

The following program is an example on dropping a temporary table −

以下程序是删除临时表的示例-

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)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist

翻译自: https://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm

mysql 临时表与内存表