Mastering MySQL Temporary Tables: Creation, Usage, and Deletion

What is a MySQL Temporary Table?

Temporary tables are useful for storing transient data. They are visible only to the current connection; when the connection closes, MySQL automatically drops the table and frees its space.

Temporary tables were introduced in MySQL 3.23.

If you create a temporary table via a Java MySQL client, the table persists until the client program ends, unless you drop it manually.

Example

Creating a temporary table:

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
);

Inserting data:

INSERT INTO SalesSummary (product_name, total_sales, avg_unit_price, total_units_sold)
VALUES ('cucumber', 100.25, 90, 2);

Query the table as usual. Note that SHOW TABLES will not list temporary tables.

When the MySQL session ends, the temporary table disappears, so a subsequent SELECT will fail because the table no longer exists.

Dropping a Temporary Table

By default, a temporary table is removed when the connection is closed. You can also drop it manually within the same session:

DROP TABLE SalesSummary;
SELECT * FROM SalesSummary;  -- will return an error because the table was dropped

Practical Scenario

In high‑concurrency write scenarios, you can let each thread create its own temporary table, write data there, and later merge into the main tables, or control writes at the source to avoid simultaneous inserts.