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 TABLEstatement. Note the keyword
TEMPORARYwhich is used between the
- For creating a temporary table, you must have the
CREATE TEMPORARY TABLEprivileges 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 TABLEstatement 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
customeralready exists in the database, still you can create a
customertemporary table. However, you refer to the table named
customerin 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
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,'firstname.lastname@example.org'), ('Robinson',32,3500,'email@example.com'), ('Peterson',42,6200,'firstname.lastname@example.org');
Finally, we can check the data from the table using the
SELECT * FROM teachers;
We can check the output of the above statements in the below image.
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
enrolls in the
mysqltutorial database with the data as below.
In the below example, the structure of the temporary table is derived from the
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;
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
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.