MySQL Temporary Tables

MySql Tutorial

MySQL temporary tables are very useful when we need to save some temporary data. Temporary tables are only visible in the current connection, and MySQL will automatically delete the table and release all the space when the connection is closed. 3.23version was added, if your MySQL version is lower than 3.23version, you cannot use MySQL's temporary tables. However, now it is rare to use such a low version of MySQL database services.

MySQL temporary tables are only visible in the current connection. If you use a PHP script to create MySQL temporary tables, the temporary table will also be automatically destroyed every time the PHP script is executed.

If you use other MySQL client programs to connect to the MySQL database server to create temporary tables, the temporary tables will only be destroyed when the client program is closed, of course, you can also manually destroy them.

Online Example

The following is a simple example of using MySQL temporary tables, the following SQL code can be used in the mysql_query() function of PHP scripts.

mysql> CREATE TEMPORARY TABLE SalesSummary (
　　　　-, product_name VARCHAR(50) NOT NULL
　　　　-, total_sales DECIMAL(12,2NOT NULL DEFAULT 0.00
　　　　-, avg_unit_price DECIMAL(7,2NOT 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 use SHOW TABLEScommand to display the list of data tables, you will not see the SalesSummary table.

If you exit the current MySQL session and then use SELECTcommand to read the data of the temporary table created previously, and you will find that the table does not exist in the database because the temporary table was destroyed when you logged out.

to delete the MySQL temporary table

By default, when you disconnect from the database, the temporary table will be automatically destroyed. Of course, you can also use DROP TABLE Command to manually delete a temporary table.

The following is an example of manually deleting a temporary table:

mysql> CREATE TEMPORARY TABLE SalesSummary (
　　　　-, product_name VARCHAR(50) NOT NULL
　　　　-, total_sales DECIMAL(12,2NOT NULL DEFAULT 0.00
　　　　-, avg_unit_price DECIMAL(7,2NOT 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 'w3codebox.SalesSummary' doesn't exist