MySQL Temp Table

MySQL Temp Table

The MySQL temp table is very useful when we need to save some temporary data. Temporary tables are only visible at the current connection, and when the connection is closed, MySQL automatically deletes the table and frees all space.

Temporary tables are added in MySQL version 3.23 and you cannot use MySQL temp tables if your MySQL version is below version 3.23. However, it is rare to use this low version of MySQL database service.

The MySQL temp table is only visible on the current connection, and if you use PHP scripts to create a MySQL temp table, the temporary table will be destroyed automatically when the PHP script executes.

If you use a different MySQL client program to connect to the MySQL database server to create a temporary table, the temporary table will be destroyed only when the client program is closed, and you can, of course, manually destroy it.

Instance

The following shows a simple example of using a MySQL temp table, and the following SQL code can be applied to the PHP script's mysql_query () function.

Mysql>CREATE Temporary TABLESalessummary ( -Product_Name VARCHAR(50)Not NULL- ,Total_sales DECIMAL(12,2)Not NULL DEFAULT0.00 - ,Avg_unit_price DECIMAL(7,2)Not NULL DEFAULT0.00 - ,Total_units_sold INT UNSIGNED not NULL DEFAULT0);QueryOk, 0Rows affected(0.00Sec)Mysql>INSERT intoSalessummary - (Product_Name,Total_sales,Avg_unit_price,Total_units_sold) -VALUES- (' Cucumber ', 100.25, 90, 2);Mysql>SELECT*FromSalessummary;+--------------+-------------+----------------+------------------+|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 the show TABLES command to display a list of data tables, you will not be able to see the salessummary table.

If you exit the current MySQL session and then use the SELECT command to read the temporary table data that you created, you will find that the table does not exist in the database because the temporary table was destroyed when you exited.

Delete MySQL temp table

By default, when you disconnect from the database, the temporary table is automatically destroyed. Of course you can also manually delete the temporary table using the DROP Table command in the current MySQL session.

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

Mysql>CREATE Temporary TABLESalessummary ( -Product_Name VARCHAR(50)Not NULL- ,Total_sales DECIMAL(12,2)Not NULL DEFAULT0.00 - ,Avg_unit_price DECIMAL(7,2)Not NULL DEFAULT0.00 - ,Total_units_sold INT UNSIGNED not NULL DEFAULT0);QueryOk, 0Rows affected(0.00Sec)Mysql>INSERT intoSalessummary - (Product_Name,Total_sales,Avg_unit_price,Total_units_sold) -VALUES- (' Cucumber ', 100.25, 90, 2);Mysql>SELECT*FromSalessummary;+--------------+-------------+----------------+------------------+|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< c15>; mysql> SELECT * from salessummary;  ERROR 1146: Table ' tutorials. Salessummary ' doesn' t exist            

MySQL Temp Table