MySQL Temporary Table

MySQL has a feature to create a special table called a Temporary Table that allows us to keep temporary data. We can reuse this table several times in a particular session. It is available in MySQL for the user from version 3.23, and above so if we use an older version, this table cannot be used. This table is visible and accessible only for the current session. MySQL deletes this table automatically as long as the current session is closed or the user terminates the connection. We can also use the DROP TABLE command for removing this table explicitly when the user is not going to use it.

If we use a PHP script to run the code, this table removes automatically as long as the script has finished its execution. If the user is connected with the server through the MySQL client, then this table will exist until the user closes the MySQL client program or terminates the connection or removed the table manually.

A temporary table provides a very useful and flexible feature that allows us to achieve complex tasks quickly, such as when we query data that requires a single SELECT statement with JOIN clauses. Here, the user can use this table to keep the output and performs another query to process it.

A temporary table in MySQL has many features, which are given below:

MySQL uses the CREATE TEMPORARY TABLE statement to create a temporary table.
This statement can only be used when the MySQL server has the CREATE TEMPORARY TABLES privilege.
It can be visible and accessible to the client who creates it, which means two different clients can use the temporary tables with the same name without conflicting with each other. It is because this table can only be seen by that client who creates it. Thus, the user cannot create two temporary tables with the same name in the same session.
A temporary table in MySQL will be dropped automatically when the user closes the session or terminates the connection manually.
A temporary table can be created by the user with the same name as a normal table in a database. For example, if the user creates a temporary table with the name student, then the existing student table cannot be accessible. So, the user performs any query against the student table, is now going to refer to the temporary student table. When the user removes a temporary table, the permanent student table becomes accessible again.

Syntax of Creating Temporary Table

In MySQL, the syntax of creating a temporary table is the same as the syntax of creating a normal table statement except the TEMPORARY keyword. Let us see the following statement which creates the temporary table:

mysql> CREATE TEMPORARY TABLE table_name (
   column_1, column_2, ..., table_constraints
);

If the user wants to create a temporary table whose structure is the same as an existing table in the database, then the above statement cannot be used. Instead, we use the syntax as given below:

Mysql> CREATE TEMPORARY TABLE temporary_table_name SELECT * FROM original_table_name LIMIT 0;

MySQL Temporary Table Example

Let us understand how we can create a temporary table in MySQL. Execute the following statement that creates a temporary table in the selected database:

mysql> CREATE TEMPORARY TABLE Students( student_name VARCHAR(40) NOT NULL, total_marks DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_subjects INT UNSIGNED NOT NULL DEFAULT 0);

We can see the below image:

Next, we need to insert values in the temporary table:

mysql>INSERT INTO Students(student_name, total_marks, total_subjects) VALUES ('Joseph', 150.75, 2), ('Peter', 180.75, 2);

After executing the above statement, it will give the below output:

Now, run the following query to get the result:

After the successful execution of the above statement, we will get the output as below:

It is to be noted that when we run a SHOW TABLES command, then our temporary table will not be shown on the list. Also, if we close the current session and then will execute the SELECT statement, we will get a message saying that no data available in the database, and even the temporary table will not exist.

A Temporary Table whose structure is based on a normal table

In this example, we are going to create a temporary table whose structure is based on the already available tables in the database.

Suppose our database has the following table as permanent:

Here, the structure of a temporary table is created by using the SELECT statement and merge two tables using the INNER JOIN clause and sorts them based on the price. Write the following statement in the MySQL prompt:

CREATE TEMPORARY TABLE temp_customers
SELECT c.cust_name, c.city, o.prod_name, o.price 
FROM orders o
INNER JOIN customer c ON c.cust_id = o.order_id
ORDER BY o.price DESC;

When we execute the above statement, we will get the following message:

Now, run the below command to see the temporary table:

We can also perform queries from the above temporary table "temp_customers" similar to the querying data from a permanent table. The following query explains it more clearly:

After executing the above statement, it will give the output as below:

NOTE: It is noted that we can use IF NOT EXISTS keyword to avoid the "table already exists" error.

How to Drop Temporary Table in MySQL

MySQL allows us to remove the temporary table using the DROP TABLE statement. But, it"s a good practice to use the TEMPORARY keyword with the DROP TABLE statement. This keyword helps us to avoid the mistake of deleting a permanent table when the temporary table and permanent table have the same name in the current session. So, it is recommended to use the following query for removing the temporary table:

This query will not remove a permanent table of the database that means it only deletes a temporary table. If we try to delete a permanent table with this statement, it will throw an error message saying that you are deleting a table is unknown. For example, if we want to remove the above temporary table "temp_customers", we need to use the following statement:

Next TopicMySQL Copy Table

← prev next →