MySQL Temporary Table – Create & Drop Temporary Tables

By /
Working With MySQL Temporary Table

Temporary Tables are particular types of tables that can be created in MySQL. These tables are created when we want temporary data stored in a database. Temporary Tables are bound to the current session, when the current session is over, these types of tables are freed from the memory by MySQL itself.

The temporary table was introduced by MySQL in version 3.23 and upwards. If you are using older versions you won’t be able to create a temporary table. One more interesting thing about this table is that it is loosely coupled with the database it is created in. Meaning, that if the database is dropped and the current session is up the temporary table created in that database will still be there.

To create a temporary table, one needs to have to CREATE TEMPORARY TABLE privileges and to drop a table one must have DROP TEMPORARY TABLE privileges. These tables are exactly like any normal table in a database the only difference between them is that when the session is over or you log out of MySQL the normal table persists in the memory, whereas a temporary table will be dropped automatically.

Let’s summarize the temporary table features in a list one by one:

  1. We use CREATE TEMPORARY TABLE command to create a temporary table.
  2. A temporary table has a narrow scope and it is only available to the clients who have created it. All the clients with CREATE TEMPORARY TABLE privileges can create a temporary table with the same name. The temporary table’s scope is only for the client who has created it.
  3. Once the session is over, that means you logged out of MySQL, a temporary table is automatically dropped.
  4. One can create a temporary table with the same name as the normal table in the database. For example, if a temporary table with the name customer is created when there already exists a customer table, then it is acceptable by the MySQL server. The only thing is that the normal table will be inaccessible till the time the temporary table exists in the database. This means when the temporary table is dropped the normal table again becomes accessible.

Creating a Temporary table in MySQL

In this section, we will see how we can create a temporary table in MySQL. We’ll see a few examples for the same but before that let’s see the syntax for the same.

Create Temporary Table Syntax:

CREATE TEMPORARY TABLE table_name (
field1 DATATYPE,
field2 DATATYPE,
...
);

The above syntax resembles to the CREATE TABLE command only, but the major difference between these two commands is that one creates a temporary table whereas the other creates a permanent table.

Let’s create a temporary table for students where we will store the first name, last name, and percentage of the students.

CREATE TEMPORARY TABLE student (
fname VARCHAR(30);
lname VARCHAR(30);
percentage INT(30);
);

Now, what if you want to create a table that is the same as an existing table in your database, let’s take an example of a table called employee and we are going to create a temp_employee table by replicating the employee table. Let’s see how we can do the same:

CREATE TEMPORARY TABLE temp_employee SELECT * FROM employee LIMIT 0;

These were the two different ways through which you can create MySQL Temporary table, in these tables you can perform all the operations like INSERT, UPDATE, DELETE, etc just in the same way as we work on normal or permanent tables.

Dropping a Temporary Table in MySQL

When the session ends temporary table is automatically dropped. But we can also drop a temporary table manually. Here’s the syntax for dropping the temporary table and an example of it.

Drop Temporary Table Syntax:

DROP TEMPORARY TABLE table_name;

Let’s take an example, we want to drop the temp_employee table that we created in the last section. To do that let’s see the below code:

DROP TEMPORARY TABLE temp_employee;Code language: SQL (Structured Query Language) (sql)

Conclusion

The takeaway of this tutorial is the concept of temporary tables in MySQL, although the syntax and overall look of both the temporary tables and the normal tables are the same, their concept is very different. After going through this tutorial, you can try creating and working on temporary tables on your own.

Click here for the official document.