MySQL temporary table

MySQL temporary table

MySQL temporary tables are useful when we need to save some temporary data. T emporary tables are only visible on the current connection, and MySQL automatically deletes the table and frees up all space when the connection is closed.

Temporary tables are added in MySQL version 3.23, and you can't use MySQL temporary tables if your MySQL version is lower than version 3.23. However, it is rare to use such a low version of the MySQL database service.

MySQL temporary tables are only visible in the current connection, and if you use PHP scripts to create MySQL temporary tables, they are automatically destroyed whenever the PHP script is executed.

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

Instance

The following shows a simple example of using a MySQL temporary table, and the following SQL code can be applied to the phAP script mysql_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)

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 originally created temporary table data, you will find that the table does not exist in the database because the temporary table was destroyed when you exited.

Delete the MySQL temporary table

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

Here are some examples of manually deleting temporary tables:

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 'W3CSCHOOL.SalesSummary' doesn't exist