How to Use MySQL Temporary Tables

Except for a few who have exhaustively studied the MySQL documentation, not many database nuts know that MySQL has temporary tables. A temporary MySQL table is a type of table that allows us to store data temporarily in a single session.

This tutorial will take you on a deep dive into how MySQL temporary tables work, and how we can use them in our daily database tasks.

What Is a MySQL Temporary Table? How Does It Work?

In the simplest terms, MySQL temporary table is a special kind of table within a table whose purpose is storing and getting results rows for a provisional time. You can access and use MySQL temporary tables as many times as possible within that specific session.

An example use case of MySQL temporary tables is a scenario where it is difficult to fetch or store data with a full MySQL query as it only requires SELECT and JOIN clauses. If such cases happen, we can dive into a MySQL temporary table, store the data, and use it as needed.

Features of a MySQL Temporary Table

MySQL temporary tables have the following features:

It is stored in memory. Therefore, it is available in the session in which it is created.
Only the user who has created the table can access and use the table. This means that more than one user can create a temporary table with similar names with no conflicts.
Two temporary tables with similar names cannot exist in the same session of the same user.
The temporary table takes precedence over the main table. For example, if a temporary table contains a similar name to one in a main table, the main table becomes inactive for that session. If the temporary table is removed, the main table becomes usable again. MySQL generally discourages naming temporary tables similar to main tables.
You can use the DROP TABLE to remove a temporary table.

Basic Usage

Let us now discuss how we can implement MySQL temporary tables and the scenarios where we might need to use one.

NOTE: To create a MySQL temporary table, a user should have CREATE TEMPORARY TABLES privilege.

The general syntax to creating a MySQL temporary table is as follows:

CREATE TEMPORARY TABLE tbl_name (col_1, col2… colN, tbl_constraints);

Creating a temporary table is similar to creating a regular MySQL table, except using the keyword TEMPORARY.

For example, to create a temporary table, see the query below:

CREATE DATABASE temporary;

USE temporary;

CREATE TEMPORARY TABLE users(id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50), email VARCHAR(255), PRIMARY KEY(id));

Once the table has been created, a user can perform the basic table operations, including INSERT, UPDATE, DELETE, SELECT, and DROP.

For example, let us insert some of the data into the temporary table created above:

INSERT INTO users(username, email) VALUES ("csool", "[email protected]"), ("dev12", "[email protected]"), ("nan0", "[email protected]");

How to Create a Temporary Table Based on the Existing Table

You can also create a temporary table whose structure is based on an existing table using the SELECT statement.

The general syntax to performing such an operation is as shown:

CREATE TEMPORARY TABLE tbl_name SELECT * FROM original_table LIMIT 0;

Drop MySQL Temporary Table

Deleting a temporary table from a database is simple and uses similar syntax to drop tables in MySQL. However, we add the keyword TEMPORARY to avoid removing the main table.

For example, to remove the user table created in the examples above, we use the query:

DROP TEMPORARY TABLE users;

NOTE: If you have the temporary table with the same name as the main table, ensure to use the keyword TEMPORARY in your DROP statement to avoid accidental deletion of the table.

Conclusion

In this tutorial, we discussed what MySQL temporary tables are and how to use them. Practice until you master using the temporary tables, as they might come in handy when performing complex SQL operations.