SQL temporary table

SQL temporary table

Some relationship database management systems support temporary tables. Temporary tables are a great feature that lets you use features such as SELECT, UPDATE, and JOIN to store or manipulate intermediate results just like you would a normal SQL data table.

Temporary tables are sometimes useful for saving temporary data. The most important thing you need to know about temporary tables is that they are deleted after the current terminal session ends.

Temporary tables have been supported since MySQL 3.23. If your MySQL version is older than 3.23, you can't use temporary tables, but you can use heap tables.

As mentioned earlier, temporary tables exist only during sessions. I f you manipulate the database in a PHP script, the temporary table is automatically destroyed when the script is executed. If you are connected to the MySQL database server through MySQL's client program, the temporary table will exist until you shut down the client or manually delete it.

Example

The following example shows you how to use 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)

When you issue the SHOW TABLES command, the temporary table does not appear in the results list. Now, if you exit the MySQL session and then execute the SELECT command, you won't be able to get any data back from the database, and your temporary table will no longer exist.

Delete the temporary table

By default, all temporary tables are deleted by MySQL when the database connection is closed. However, sometimes you still want to delete it during a session, when you need to use the DROP TABLE command to get there.

Here's an example of removing 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