How to Create a Temporary Table in MySQL

This article explains how to create and use temporary tables in MySQL. You will learn the exact SQL syntax required to build a temporary table, understand how these tables isolate data within a specific session, and see how they automatically destroy themselves once your database connection ends.

In MySQL, you can create a temporary table by adding the TEMPORARY keyword to a standard CREATE TABLE statement. These tables are ideal for storing intermediate results from complex queries or performing multi-step data manipulations without affecting your permanent schema.

The Syntax for MySQL Temporary Tables

To create a basic temporary table from scratch, use the following SQL syntax:

CREATE TEMPORARY TABLE temp_sales_summary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50),
    total_sales DECIMAL(10,2)
);

You can also create a temporary table and immediately populate it with data from an existing table using a SELECT statement:

CREATE TEMPORARY TABLE temp_active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE status = 'active';

Key Characteristics of Temporary Tables

• Session Isolation: A temporary table is strictly visible to the client session that created it. Other database connections cannot see, access, or query your temporary table, preventing data leaks and concurrency conflicts.
• Automatic Deletion: When your database connection or session ends (for example, when you close your SQL client or your script finishes executing), MySQL automatically drops the temporary table and frees up the memory or disk space it used.
• Name Shadowing: You can create a temporary table with the same name as an existing permanent table. While your session is active, MySQL will prioritize and reference the temporary table. To avoid confusion, however, it is best practice to use a prefix like temp_.
• Manual Deletion: Although MySQL cleans them up automatically, you can manually delete a temporary table during your active session using the DROP TEMPORARY TABLE command:

DROP TEMPORARY TABLE IF EXISTS temp_sales_summary;