Complete Guide to MySQL Temporary Tables: Create, Manage, Use

1. Introduction

When utilizing MySQL, “Temporary Tables” are a convenient means for temporarily storing and processing data. By using temporary tables, you can temporarily store data, reduce the load of complex queries, and improve the efficiency of batch processing.

In this article, we will explain in detail what MySQL temporary tables are, their uses, and benefits.

What is a Temporary Table?

A temporary table (Temporary Table) refers to a table that is valid only during the session.
Unlike regular tables, it is not permanently stored in the database, and is automatically deleted when the session ends.

The features of temporary tables are summarized as follows.

• Exists per session (inaccessible from other connections)
• Automatically deleted when the session after table creation ends
• Can be used without interference even if a regular table with the same name exists
• Often used for performance improvement

Temporary tables are suitable for data analysis and temporary data processing, and are commonly used as assistance for batch processing and aggregation processing.

Benefits of Using Temporary Tables

By utilizing temporary tables, you can improve the efficiency of data processing. Here, we introduce three main benefits.

1. Improve Query Performance

Normally, when handling large amounts of data, using multiple JOINs or subqueries complicates the processing and puts a load on the database. By using temporary tables, you can filter and save data in advance, speeding up query execution.

2. Ideal for Temporary Data Storage

In batch processing or data transformation, there are cases where data is temporarily stored and necessary processing is performed. Using temporary tables allows temporary storage of data, enabling high-speed processing in memory.

3. Safely Preserve Existing Data

Directly operating on production environment data is risky. By utilizing temporary tables, you can perform processing without modifying production data, reducing the risk of errors.

Summary

MySQL temporary tables are convenient tools for temporary data storage and processing.

• Valid per session and deleted at the end of the session
• Effective for performance improvement and batch processing
• Allows safe data manipulation without modifying production data

2. Basics of Temporary Tables

MySQL’s temporary tables (Temporary Tables) differ from regular tables in that they are used to store data temporarily. This section explains the basic concepts of temporary tables in detail, focusing on their differences from regular tables and internal temporary tables.

Differences Between Temporary Tables and Regular Tables

Temporary tables and regular tables have significant differences in data storage duration and access mechanisms. The following table summarizes the main differences.

Key Points

• Temporary tables are independent per session and invisible to other users.
• Can be created without error even if a regular table with the same name exists.
• Created explicitly using CREATE TEMPORARY TABLE, and automatically deleted when the session ends.

Differences Between Temporary Tables and Internal Temporary Tables

MySQL has, in addition to the “temporary tables” that users create explicitly, “internal temporary tables” that the MySQL engine creates automatically. These two are similar but differ in purpose and management.

What are Internal Temporary Tables?

• MySQL internally creates temporary tables to optimize certain queries (GROUP BY, ORDER BY, DISTINCT, etc.).
• End users cannot manage them directly (cannot create them explicitly like CREATE TEMPORARY TABLE).
• They are created as needed during query execution and automatically deleted when the query completes.

Examples of Internal Temporary Tables

Executing a query like the following may cause MySQL to create an internal temporary table for processing.

SELECT category, COUNT(*) 
FROM products 
GROUP BY category
ORDER BY COUNT(*) DESC;

In this case, MySQL creates an internal temporary table to temporarily store the GROUP BY results and uses it to compute the final results.

Summary

• Temporary tables are temporary tables explicitly created by users and are automatically deleted when the session ends.
• Unlike regular tables, they cannot be accessed from other sessions.
• Internal temporary tables are temporary tables automatically created and deleted by MySQL, and users cannot operate them directly.

3. How to Create Temporary Tables

MySQL’s temporary tables can be created using the CREATE TEMPORARY TABLE statement. This section explains in detail from the basic method of creating temporary tables to creating them based on existing tables.

Basic Method for Creating Temporary Tables

In MySQL, use the CREATE TEMPORARY TABLE statement to create temporary tables.

Basic Syntax

CREATE TEMPORARY TABLE table_name (
    column_name data_type constraint,
    column_name data_type constraint,
    ...
);

Sample Code

The following SQL creates a temporary table named users_temp with three columns: id (integer type), name (string type), and email (string type).

CREATE TEMPORARY TABLE users_temp (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

This table is automatically deleted when the session ends, so it does not affect the persistent database.

Creating Temporary Tables Based on Existing Tables

Instead of creating temporary tables from scratch, it is also possible to create them by copying the structure of existing tables.

Using CREATE TEMPORARY TABLE ... SELECT

In MySQL, you can create a temporary table based on the results of a SELECT statement.

Basic Syntax

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM existing_table_name;

Sample Code

For example, to copy the data structure of the users table and create a new temporary table users_temp, write it as follows.

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users;

With this method, the column structure of the users table is inherited as is by users_temp, but constraints such as PRIMARY KEY or INDEX are not copied.

If you want to copy only the table structure without including the data, add WHERE 1=0.

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users WHERE 1=0;

In this SQL, the column definitions of the users table are copied, but no data is included.

Notes When Creating Temporary Tables

1. Temporary Tables Are Valid per Session

• Temporary tables are only valid within the session in which they were created.
• They cannot be accessed from other connections or different users.

2. Can Be Created Even If a Regular Table with the Same Name Exists

• For example, even if there is a regular table named users in the database, you can create a temporary table with the same name users.
• In this case, within the session, the temporary table takes precedence, and the regular table becomes invisible.

3. Impact of Storage Engine

• Temporary tables use the MEMORY engine by default, but if the data volume is large, they may be stored in the temporary area of InnoDB.
• To explicitly specify the MEMORY engine, write it as follows.
  CREATE TEMPORARY TABLE users_temp ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) ENGINE=MEMORY;
• The MEMORY engine is fast but has data size limitations, so consider using InnoDB for handling large amounts of data.

Summary

• Temporary tables are created using CREATE TEMPORARY TABLE.
• It is also possible to create temporary tables by copying existing tables (using SELECT * FROM).
• Using the MEMORY engine enables fast processing, but InnoDB is suitable for large amounts of data.
• Temporary tables are managed per session and are automatically deleted when the session ends.

4. How to Use Temporary Tables

MySQL temporary tables (Temporary Tables) can perform operations such as data insertion, updates, deletions, and queries just like regular tables. This section provides a detailed explanation of each operation method.

Data Insertion

To add data to a temporary table, use the standard INSERT INTO statement.

Basic Syntax

INSERT INTO temporary_table_name (column_name1, column_name2, ...)
VALUES (value1, value2, ...);

Sample Code

The following SQL is an example of inserting data into a temporary table called users_temp.

INSERT INTO users_temp (id, name, email)
VALUES (1, 'Yamada Taro', 'taro@example.com');

Additionally, it is possible to copy and insert data from an existing table.

INSERT INTO users_temp (id, name, email)
SELECT id, name, email FROM users WHERE age >= 18;

This SQL inserts data of users aged 18 and above from the users table into the temporary table.

Data Updates

To modify data in a temporary table, use the standard UPDATE statement.

Basic Syntax

UPDATE temporary_table_name
SET column_name = new_value
WHERE condition;

Sample Code

For example, to change the name of the user with id=1 in the users_temp table, write it as follows.

UPDATE users_temp
SET name = 'Sato Ichiro'
WHERE id = 1;

Data Deletion

To delete unnecessary data, use the DELETE statement.

Basic Syntax

DELETE FROM temporary_table_name WHERE condition;

Sample Code

For example, to delete the data with id=1 in the users_temp table, execute the following SQL.

DELETE FROM users_temp WHERE id = 1;

To delete all data in the table, you can omit the WHERE condition.

DELETE FROM users_temp;

Note that even when using the DELETE statement, the table itself is not deleted; only the data is deleted.

Data Retrieval

To retrieve data stored in a temporary table, use the SELECT statement.

Basic Syntax

SELECT column_name FROM temporary_table_name WHERE condition;

Sample Code

For example, to retrieve all data from the users_temp table, execute the following SQL.

SELECT * FROM users_temp;

To retrieve data that matches specific conditions, use the WHERE clause.

SELECT * FROM users_temp WHERE email LIKE '%@example.com';

This SQL retrieves only the data with email addresses containing @example.com.

Notes on Using Temporary Tables

1. Data is Deleted When the Session Ends

• Temporary tables are managed per session, and the data is deleted when the session ends.
• For long-running processes, it is recommended to back up data periodically.

2. An Error Occurs If a Temporary Table with the Same Name Already Exists

• Attempting to create a temporary table with the same name using CREATE TEMPORARY TABLE will result in an error.
• As a way to avoid errors, it is good to execute DROP TEMPORARY TABLE IF EXISTS beforehand.
  DROP TEMPORARY TABLE IF EXISTS users_temp; CREATE TEMPORARY TABLE users_temp (...);

3. There Are Constraints Due to the Storage Engine

• Temporary tables use the MEMORY engine by default, but if the data volume is large, they are automatically saved to InnoDB’s temporary area.
• When handling large amounts of data, it is recommended to use InnoDB temporary tables.

Summary

• Temporary tables can perform data insertion, updates, deletions, and queries in the same way as regular tables.
• When the session ends, the temporary table’s data is automatically deleted.
• By executing DROP TEMPORARY TABLE IF EXISTS in advance, you can avoid errors when creating tables with the same name.
• When handling large amounts of data, it is recommended to use InnoDB temporary tables.

5. Managing and Deleting Temporary Tables

MySQL’s temporary tables are automatically deleted when the session ends. However, in some cases, it may be necessary to explicitly delete them. This section provides a detailed explanation of how to manage and delete temporary tables.

Methods for Deleting Temporary Tables

To explicitly delete a temporary table, use the DROP TEMPORARY TABLE statement.

Basic Syntax

DROP TEMPORARY TABLE table_name;

Sample Code

For example, to delete a temporary table named users_temp, execute the following SQL.

DROP TEMPORARY TABLE users_temp;

Executing this SQL will delete the users_temp table, making it unavailable for reuse within the session.

Automatic Deletion at Session End

Temporary tables are automatically deleted when the session ends.

Mechanism of Automatic Deletion

1. CREATE TEMPORARY TABLE to create a temporary table
2. Data can be manipulated while the session is maintained
3. When the session (connection) is disconnected, the temporary table is automatically deleted

However, caution is needed in the following cases.

• If the session is maintained for a long time
  → It is recommended to execute DROP TEMPORARY TABLE as appropriate, as unnecessary temporary tables may occupy memory.
• When handling large amounts of data
  → It is important to properly delete tables to avoid straining storage.

Utilizing DROP TEMPORARY TABLE IF EXISTS

When deleting a temporary table, using IF EXISTS is convenient to prevent errors if it does not exist.

Basic Syntax

DROP TEMPORARY TABLE IF EXISTS table_name;

Sample Code

DROP TEMPORARY TABLE IF EXISTS users_temp;

Executing this SQL will delete users_temp if it exists, and skip without error if it does not.

Common Errors and Solutions

Error 1: “Table not found”

Conditions for Occurrence:

• When trying to delete a non-existent table with DROP TEMPORARY TABLE
• Since temporary tables are managed per session, they cannot be deleted from another session

Solution:

• Add IF EXISTS to prevent the error

  DROP TEMPORARY TABLE IF EXISTS users_temp;

• Execute the deletion in the correct session

Error 2: “Table already exists”

Conditions for Occurrence:

• Attempting to create a temporary table with the same name, but it already exists

Solution:

• Execute DROP TEMPORARY TABLE IF EXISTS beforehand

  DROP TEMPORARY TABLE IF EXISTS users_temp;
  CREATE TEMPORARY TABLE users_temp (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      email VARCHAR(100)
  );

Best Practices for Temporary Table Management

1. Explicitly delete when no longer needed

• Execute DROP TEMPORARY TABLE as appropriate to free up unnecessary tables.

1. Use IF EXISTS to avoid errors

• Use DROP TEMPORARY TABLE IF EXISTS to prevent errors when deleting non-existent tables.

1. Be mindful of session management

• If the session is maintained for a long time, temporary tables may strain memory, so delete them as appropriate.

1. Understand the impact of storage engines

• Using the MEMORY engine is fast but has data size limitations.
• When using InnoDB, consider disk space requirements.

Summary

• Temporary tables can be explicitly deleted with DROP TEMPORARY TABLE.
• They are automatically deleted at session end, but manual deletion is recommended for long sessions.
• Using DROP TEMPORARY TABLE IF EXISTS prevents errors during deletion.
• It is useful to know how to handle “Table not found” errors or “Table already exists” errors.

6. Examples of Using Temporary Tables

MySQL’s temporary tables (Temporary Tables) are used to efficiently handle temporary data storage and processing. This section introduces typical scenarios for using temporary tables and provides detailed explanations of their implementation methods.

1. Using as an Intermediate Table During Data Aggregation

When performing data analysis or report creation, directly processing large amounts of data can slow down query execution. By using temporary tables, you can temporarily organize the data before processing it, thereby improving performance.

Scenario

• The sales table stores one year of sales data.
• Calculate the total sales per month and perform further detailed analysis.

Implementation Example

CREATE TEMPORARY TABLE monthly_sales (
    month_year DATE,
    total_sales DECIMAL(10,2)
);

INSERT INTO monthly_sales (month_year, total_sales)
SELECT DATE_FORMAT(sale_date, '%Y-%m-01') AS month_year, SUM(amount) 
FROM sales
GROUP BY month_year;

SELECT * FROM monthly_sales;

2. Retaining Temporary Data in Batch Processing

Temporary tables are also useful in batch processing (bulk data processing). For example, by filtering data based on certain conditions and storing only the target data in a temporary table for processing, you can efficiently manipulate the data.

Scenario

• From the users table, send emails only to users who have logged in within the past year.
• Save the target data to a temporary table in advance and process it sequentially.

Implementation Example

CREATE TEMPORARY TABLE active_users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

INSERT INTO active_users
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

SELECT * FROM active_users;

3. Simplifying Complex Queries

Executing complex queries directly can degrade performance and reduce code readability. By using temporary tables, you can reduce subqueries and simplify the queries.

Scenario

• From the orders table, retrieve the top 10 products by sales.
• Use temporary tables without subqueries.

Implementation Example

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

4. Temporary Table Operations Without Rollback

Temporary tables are managed on a per-session basis, so they are not affected by transactions. Therefore, they are suitable for managing temporary data that does not require rollback.

Scenario

• During transaction processing, retain temporary calculation results.
• However, avoid having temporary data rolled back in case of errors.

Implementation Example

START TRANSACTION;

CREATE TEMPORARY TABLE temp_results (
    user_id INT,
    score INT
);

INSERT INTO temp_results
SELECT user_id, SUM(points) FROM game_scores GROUP BY user_id;

-- Commit the transaction
COMMIT;

SELECT * FROM temp_results;

Summary

• Temporary tables can be used for various purposes, such as data aggregation, batch processing, and query simplification.
• As intermediate tables, they help improve performance and organize data.
• In batch processing, you can extract target data in advance and skip processing unnecessary data.
• For simplifying complex queries, they reduce subqueries and improve SQL readability.
• Since they are not affected by transactions, they are suitable for data processing that does not require rollback.

7. Alternatives to Temporary Tables and Their Limitations

MySQL’s temporary tables (Temporary Tables) are a convenient feature, but they have several limitations. In some cases, considering alternatives such as views or subqueries can allow for more efficient data processing. This section explains the main limitations of temporary tables and alternatives to avoid them.

Main Limitations of Temporary Tables

Temporary tables have several limitations that regular tables do not. Understanding these will help you identify appropriate use cases.

1. Managed on a Per-Session Basis

• Temporary tables are only valid within the session in which they are created, and cannot be accessed from other connections or users.
• Even if a regular table with the same name exists, within the same session, the temporary table takes precedence (the regular table cannot be accessed).

2. Temporary Table Schemas Are Not Preserved

• Regular tables can have their schema retrieved with SHOW CREATE TABLE, but temporary tables disappear when the session ends, so the schema information is not retained.

3. Index Limitations

• With CREATE TEMPORARY TABLE, PRIMARY KEY or INDEX are not created automatically unless specified.
• To add an index to a temporary table, it must be created manually.

4. The Default Storage Engine is MEMORY

• With the MEMORY engine, when the data volume increases, swapping to disk occurs, leading to performance degradation.
• Specifying InnoDB allows handling large data volumes, but it increases disk usage accordingly.

5. Not Affected by Transactions

• Temporary tables are not affected even by ROLLBACK.
• Therefore, they are not suitable for processes that need to maintain transaction consistency.

Alternatives to Temporary Tables

To avoid the above limitations, using views (View) or subqueries instead of temporary tables allows for more flexible data processing.

1. Using Views (View)

Views (View) can be used as a means to reference temporary data similar to temporary tables. Views act as virtual tables and do not require temporary data storage, thus avoiding storage limitations.

Creating a View

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

Using a View

SELECT * FROM active_users;

Advantages of Using Views

✅No Storage Consumption (Data is referenced directly, no need for temporary storage)
✅Not Dependent on Sessions (Can be used by other users or connections)
✅Schema Maintenance Possible (View definitions can be checked with SHOW CREATE VIEW)

Disadvantages of Views

❌Updates Are Difficult (Direct INSERT or UPDATE on views has restrictions)
❌Performance May Degrade When Handling Large Data

2. Using Subqueries

It is also possible to use subqueries as a method for temporary data processing without using temporary tables.

Case Using Temporary Tables

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

Case Using Subqueries

SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

Advantages of Using Subqueries

✅Performance Improves Since No Temporary Table Creation Is Needed✅No Storage Consumption✅Not Dependent on Sessions, Can Be Executed Anytime

Disadvantages of Subqueries

❌Readability Decreases for Complex Queries❌Difficult to Reuse Data (Need to Reference the Same Data Multiple Times)

3. Using Common Table Expressions (WITH Clause)

In MySQL 8.0 and later, Common Table Expressions (CTE: Common Table Expressions) can be used to handle data temporarily without creating temporary tables.

Example Using CTE

WITH top_products AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 10
)
SELECT * FROM top_products;

Advantages of Using CTE

✅Code Readability Improves (Easier to read than subqueries)
✅Performance Can Be Optimized (Can handle temporary data without temporary tables)

Disadvantages of CTE

❌Cannot Be Used in MySQL 5.x (Supported Only in MySQL 8.0 and Later)

Summary

8. Frequently Asked Questions (FAQ)

We have compiled common questions about MySQL temporary tables (Temporary Tables). We hope this serves as a reference for those who have questions about the behavior and limitations of temporary tables.

1. Can temporary tables be referenced from other sessions?

No, they cannot be referenced.Temporary tables are only available within the session in which they were created. They cannot be accessed from other sessions, and even if another user creates a temporary table with the same name, it is treated as an independent table in each respective session.

2. What privileges are required to create temporary tables?

To create temporary tables, you need the CREATE TEMPORARY TABLES privilege on the database.
To grant the privilege to a user, execute the following SQL.

GRANT CREATE TEMPORARY TABLES ON database_name.* TO 'username'@'host';

You can also check the current privileges using the SHOW GRANTS command.

SHOW GRANTS FOR 'username'@'host';

3. Do temporary tables affect disk space?

Yes, they do.MySQL temporary tables use the MEMORY engine by default, but if the data size exceeds a certain limit, they are stored in the InnoDB temporary area.

When handling large amounts of data, temporary tables may consume disk space, so it is recommended to explicitly delete them when no longer needed.

DROP TEMPORARY TABLE IF EXISTS table_name;

To minimize the impact on disk, if the data volume is large, it is better to create the temporary table using InnoDB instead of MEMORY.

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type
) ENGINE=InnoDB;

4. What is the difference between temporary tables and internal temporary tables?

5. Can temporary tables be shared between threads?

No, they cannot.Temporary tables are only valid within the thread (session) in which they were created and cannot be accessed from other threads or processes.

If you want to bypass this limitation, you need to create a regular table instead of a temporary table.

CREATE TABLE shared_temp_table (
    id INT PRIMARY KEY,
    data VARCHAR(255)
);

6. Can using temporary tables degrade performance?

Yes, performance can degrade in some cases.Be particularly careful in the following scenarios.

• When the data volume is too large
• The MEMORY engine has a data size limit, and once it exceeds that, it swaps to InnoDB, degrading performance.
• Countermeasure: If it exceeds the MEMORY engine’s limit, create it with InnoDB from the start.
• When appropriate indexes are not set
• Tables created with CREATE TEMPORARY TABLE ... SELECT do not copy indexes, which can slow down searches.
• Countermeasure: Add indexes using ALTER TABLE as needed.

  ALTER TABLE temporary_table_name ADD INDEX (column_name);

7. How can temporary table performance be improved?

To improve the performance of temporary tables, the following methods are effective.

✅Use the MEMORY engine (fast for small-scale data)

CREATE TEMPORARY TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

✅Select only the necessary columns (omit unnecessary columns)

CREATE TEMPORARY TABLE users_temp AS
SELECT id, name FROM users;

✅Set appropriate indexes (speeds up searches)

ALTER TABLE users_temp ADD INDEX (name);

✅Delete them immediately when no longer needed (frees up memory)

DROP TEMPORARY TABLE IF EXISTS users_temp;

Summary

• Temporary tables cannot be referenced from other sessions or threads
• Creation requires the CREATE TEMPORARY TABLES privilege
• If the data is too large, it swaps from MEMORY to InnoDB, degrading performance
• Query speed can be improved by setting appropriate indexes
• It is recommended to delete unnecessary temporary tables using DROP TEMPORARY TABLE

This provides a detailed explanation of MySQL temporary tables, from basic knowledge and usage to constraints, alternatives, and common questions.
By using temporary tables appropriately, you can significantly improve the efficiency of data processing.