Mysql Temporary Tables

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. When the connection is closed, MySQL automatically deletes the table and frees all the space.

In MySQL, a temporary table is a table that exists within 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 be automatically destroyed whenever the PHP script finishes executing.

If you use another MySQL client program 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. Of course, you can also destroy it manually.

Creating a Temporary Table

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

Or abbreviated as:

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

Inserting Data into a Temporary Table

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

Querying a Temporary Table

SELECT * FROM temp_table_name;

Modifying a Temporary Table

Modifying a temporary table is similar to modifying a regular table. You can use the ALTER TABLE command.

ALTER TABLE temp_table_name ADD COLUMN new_column datatype;

Dropping a Temporary Table

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

DROP TEMPORARY TABLE IF EXISTS temp_table_name;

Example

-- Create a temporary table
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders WHERE order_date >= '2023-01-01';

-- Query the temporary table
SELECT * FROM temp_orders;

-- Insert data into the temporary table
INSERT INTO temp_orders (order_id, customer_id, order_date)
VALUES (1001, 1, '2023-01-05');

-- Query the temporary table again
SELECT * FROM temp_orders;

-- Drop the temporary table
DROP TEMPORARY TABLE IF EXISTS temp_orders;

Temporary tables are very useful for storing intermediate result sets or performing 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 that temporary table. When sharing data between multiple sessions, consider using regular tables instead of temporary tables.

Please note that temporary tables are automatically deleted when the session ends, but you can also explicitly delete them using DROP TEMPORARY TABLE to free up resources earlier.

Example

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

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 originally 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. Of course, you can also manually delete a temporary table in the current MySQL session using the DROP TABLE command.

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

YouTip