❮ Mysql Select Database Mysql Tutorial ❯

MySQL Temporary Tables

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

Temporary tables were added in MySQL 3.23 version. If your MySQL version is lower than 3.23, you cannot use MySQL's temporary tables. However, it is rare nowadays to use such a low version of MySQL database services.

MySQL temporary tables are only visible to the current connection. If you use a PHP script to create a MySQL temporary table, the temporary table will be automatically destroyed once the PHP script finishes execution.

If you use other MySQL client programs to connect to the MySQL database server to create a temporary table, the temporary table will only be destroyed when you close the client program. You can also manually destroy it.

Example

The following demonstrates a simple example of using MySQL temporary tables. The following SQL code can be used with the mysql_query() function in a PHP script.

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 the list of tables, you will not see the SalesSummary table.

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

Deleting MySQL Temporary Tables

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

Here is an example of manually deleting 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 'tutorialpro.SalesSummary' doesn't exist

❮ Mysql Select Database Mysql Tutorial ❯