MySQL Temporary Table

Summary: in this tutorial, you will learn about MySQL Temporary table, how to create, use and drop temporary tables with examples.

What is a Temporary Table?

A temporary table is temporary in nature that means it is not permanent in the database. These types of tables are session-specific. After login into the database, you create a temporary table, use it and log off. The database engine automatically drops the table definition along with the data from the database as soon as you terminate the session. After re-login into the database, you cannot find the temporary table which you have created in the earlier session.

Introduction to MySQL Temporary Table

MySQL provides the feature of the temporary table from its release 3.23. MySQL Temporary table is very useful when you are working with MySQL. This table is mainly used for storing intermediate results while processing queries.

Following are the important point you need to keep in mind while working with the MySQL Temporary Table.

  • A temporary table in MySQL is created by CREATE TEMPORARY TABLE statement. Note the keyword TEMPORARY which is used between the CREATE and TABLE keywords.
  • For creating a temporary table, you must have the CREATE TEMPORARY TABLE privileges on the database.
  • The temporary tables are not shareable between sessions that means the table is only visible and accessible to the session that creates it. Two different sessions can use the same table name as there will be no conflict with them. However, you cannot create the same table name for two different tables.
  • A temporary table in MySQL drops automatically when the session is terminated. However, the table can be dropped explicitly using the DROP TEMPORARY TABLE statement if the table is no longer required.
  • You can create a temporary table with the same name as the permanent table. For example, if there is a table named customer already exists in the database, still you can create a customer temporary table. However, you refer to the table named customer in the query, the query will refer only to the temporary table, not the permanent one. Once the temporary table is dropped, the permanent will be accessible.

MySQL Temporary Table Syntax

Following is the syntax of creating a temporary table in MySQL.

CREATE TEMPORARY TABLE table_name(
column_1_definition,
column_2_definition,
...,
table_constraints
);

This is exactly the same as the normal table creation except for the TEMPORARY keyword which is between the CREATE and TEMPORARY keywords.

For creating a temporary table whose structure is the same as an existing table in the database, then you can use the following syntax.

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;

MySQL Temporary Table Example

Let us now understand the concept of the temporary table with the help of some examples.

The following statement creates a temporary table named teachers in the selected database.

CREATE TEMPORARY TABLE teachers (
  id int AUTO_INCREMENT PRIMARY KEY,
  full_name varchar(50) NOT NULL,
  age int NOT NULL,
  salary decimal(10,2),
  email varchar(100)
);

Now, insert some dummy data into it as below.

INSERT INTO teachers (full_name, age, salary, email) values
  ('Albert',35,5000,'albert@hotmail.com'),
  ('Robinson',32,3500,'hellorobin@gmail.com'),
  ('Peterson',42,6200,'perterson@yahoo.com');

Finally, we can check the data from the table using the SELECT statement.

SELECT * FROM teachers;

We can check the output of the above statements in the below image.

mysql temporary table

As you can see from the above that the temporary table behaves like a normal table in MySQL. However, if we do the show tables command then the temporary tables are not listed. But we can check the structure of the temporary table using the DESCRIBE TABLE command. If we close the session and execute the SELECT statement again, MySQL will issue an error saying that the table does not exist.

Creating a temporary table whose structure based on a query

In this example, we will check how to create a temporary table whose structure is based on some query. Suppose, we have two tables named courses and enrolls in the mysqltutorial database with the data as below.

mysql temporary table

In the below example, the structure of the temporary table is derived from the SELECT query.

CREATE TEMPORARY TABLE course_enrolled
SELECT c.id, c.course_name, count(c.id) as total_enrollment FROM courses c
INNER JOIN enrolls e
ON c.id = e.course_id
GROUP BY c.id
ORDER BY c.id;

After the completion of the above query, we can execute queries on the course_enrolled table same as a permanent table. The below query shows the same.

SELECT 
 id,
 course_name,
 total_enrollment
FROM course_enrolled;

Output:

mysql temporary table

Dropping Temporary Tables

A temporary table in MySQL can be dropped using the DROP TABLE statement. However, it is a good practice to use the TEMPORARY keywords between the DROP and TABLE keywords. This keyword ensures that the permanent tables will not be deleted in case the permanent table and the temporary table has the same name. Hence, it is recommended to use the TEMPORARY keyword always while dropping a temporary table. Here is the syntax:

DROP TEMPORARY TABLE table_name;

Using the above syntax, you cannot delete any permanent table. If you try to drop a table using the above syntax, MySQL issue an error saying ERROR 1051 (42S02): Unknown table 'table_name'.

For dropping a temporary table, you use the following statement.

mysql> DROP TEMPORARY TABLE course_enrolled;

Also, we already know that the temporary table is session-specific. So, if you disconnect the session from MySQL, the table will be dropped automatically.