Summary: In this post, we will discuss everything about the MySQL Temporary table.

Introduction to MySQL Temporary Table

A temporary table in MySQL is a special type of table which is used to store the temporary set of results that can be reused multiples times without running any expensive SQL query.

A Temporary Table is visible only within the current session and is automatically dropped when the session is closed.

Recommended: MySQL CREATE TABLE

The general syntax to create a temporary table in MySQL is:

Note: Replace table_name with the name of the table

Recommended: MySQL CREATE TABLE … SELECT

To create a temporary table based on the definition of another table, use this syntax instead:

Some of the prominent properties of MySQL Temporary Table are:

  • A Temporary Table is visible only within the current session therefore two different sessions can use the same temporary table name without conflicting with each other.
  • Temporary table can have the same name as an existing non-temporary table. However, two Temporary tables in the same session cannot have the same name.
  • MySQL automatically deletes all the Temporary Tables for the current session when the session ends. Also, we can explicitly remove any existing Temporary table using the MySQL DROP TABLE statement.
  • A temporary table like any other normal table is stored in the disk and is fetched to the memory for the processing.

The temporary table is useful when we need to store immediate results within the MySQL server for use over a number of SQL transactions. Example, storing the results of the JOIN statement for reuse.

Let’s see an example to have a better understanding of the Temporary Table.

Example

Consider the following two tables in MySQL.

MySQL Table

The ‘programming.roll_no’ is referencing ‘students.roll_no’.

To get all student’s details using the two tables, we will have to use the following SELECT statement along with the JOIN clauses:

students table details

In the case of a large database, It would be inefficient for the MySQL server to process the same query again and again.

So it is better to store the results into a temporary table and query data from the same.

Query OK, 1 row affected (0.20 sec)

students table details

Drop MySQL Temporary Table

The general syntax to drop or delete a temporary table in MySQL is:

Example

Recommended: MySQL DROP TABLE

Deleting the ‘temp_student_details’ table which we have created in the previous example.

Query OK, 0 rows affected (0.01 sec)

The best practice for naming a temporary table is to prefix the table name with ‘temp‘ word.

Although a temporary table can have the same name as a non-temporary table, it is not recommended because it may lead to ambiguity and could cause loss of data.

For example, in the case of server disconnection, all the temporary tables will automatically be deleted by MySQL. The user unaware of the deletion will end up deleting a non-temporary table of the same name.

In this tutorial, we have learned about the MySQL temporary table. We discussed why do we use a temporary table and how to create and delete a temporary table in MySQL.

Leave a Reply