MySQL Temporary Tables

MySQL temporary tables are very useful when we need to save some temporary data.

Temporary tables are only visible to the current connection. When the connection is closed, MySQL automatically deletes the tables and releases all space.

In MySQL, a temporary table is a table that exists in the current session and is automatically destroyed when the session ends.

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 also be automatically destroyed after the PHP script finishes executing.

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

Creating Temporary Tables

CREATE TEMPORARY TABLE temp_table_name (
  column1 datatype,
  column2 datatype,
  ...
);

Or written in shortened form:

CREATE TEMPORARY TABLE temp_table_name AS
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Inserting Data into Temporary Tables

INSERT INTO temp_table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Querying Temporary Tables

SELECT * FROM temp_table_name;

Modifying Temporary Tables

Modifying temporary tables is similar to modifying ordinary tables. You can use the ALTER TABLE command.

ALTER TABLE temp_table_name
ADD COLUMN new_column datatype;

Dropping Temporary Tables

Temporary tables are automatically destroyed when the session ends, but you can also explicitly delete them using DROP TABLE.

DROP TEMPORARY TABLE IF EXISTS temp_table_name;

Example

Temporary tables are very useful when you need to store intermediate result sets or perform complex queries within a session.

The scope of a temporary table is limited to the session that created it. Other sessions cannot directly access or reference the temporary table. When sharing data between multiple sessions, you can consider using ordinary tables instead of temporary tables.

Note that temporary tables are automatically dropped at the end of the session, but you can also explicitly drop them using DROP TEMPORARY TABLE to release resources earlier.

Example

The following shows a simple example of using a MySQL temporary table. The following SQL code can be applied to 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 useSHOW TABLEScommand to display the list of data tables, you will not be able to see the SalesSummary table.

If you exit the current MySQL session and then useSELECTcommand to read the data of the originally created temporary table, you will find that the table no longer exists in the database, because the temporary table has already been destroyed when you exited.

Dropping MySQL Temporary Tables

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

The following is an example of manually dropping 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 'EXAMPLE.SalesSummary' doesn't exist