Note:
Don't confuse yourself with MySQL memory table type which is also temporary in nature because it stores data in computers memory only. In contrast, Temporary Table in MySQL can be created on disk or as a MEMORY table type, dependent on the syntax in the CREATE TABLE statement.

How to create MySQL Temporary table

To create a Temporary Table in MySQL, we add the TEMPORARY keyword in the CREATE TABLE statement.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

For example, the following statement creates a temporary table for products above average price.

create temporary table tempProductsAboveAvgPrice
as
select distinct ProductName, UnitPrice
from Products
where UnitPrice > (select avg(UnitPrice) from Products)
order by UnitPrice

Temporary Table can only be created as type MEMORY, MyISAM, MERGE, or InnoDB. If you don't specify an engine type when creating the Temporary Table, the default storage engine for the server is used. The default storage engine is set in MySQL server configuration file my.cnf.


MySQL manual states:


Since MySQL 5.5, InnoDB becomes the default storage engine and most users never changed the default settings, and it's also recommended by MySQL that the default setting should not be changed because of the ACID (Atomicity, Consistency, Isolation, Durability) benefits of InnoDB tables, e.g. transaction safe, referential integrity enforcement, and crash recovery.

Now the temporary table tempProductsAboveAvgPrice has been created, you can query it the same way as you do for a permanent table.

select * from tempProductsAboveAvgPrice

Here is the query result. 25 records returned.

You also can create the temporary table tempProductsAboveAvgPrice in 2 steps as shown below.

Step 1:

create temporary table tempProductsAboveAvgPrice
(
    ProductName varchar(40) not null,
    UnitPrice double not null
)

Step 2:

insert into tempProductsAboveAvgPrice
select distinct ProductName, UnitPrice
from Products
where UnitPrice > (select avg(UnitPrice) from Products)
order by UnitPrice

Now issue a SHOW TABLES command for Northwind database, the temporary table tempProductsAboveAvgPrice is not listed in the result. This is because the SHOW TABLES command only lists permanent tables in the database.

show tables

Here is the query result of SHOW TABLES command. 9 records returned.

Drop Temporary table

By default, all the temporary tables created are dropped automatically by MySQL when your database connection is terminated, but you still can manually drop a temporary table by issuing DROP TABLE command.

drop table tempProductsAboveAvgPrice

When to use MySQL Temporary table

The Divide and Conquer rules - this is the single most attractive advantage of using MySQL Temporary Table.

Temporary Table offers definite advantages when it's used to hold data returned from a very complex query (so very expensive to create) for temporary use. For example, you need to retrieve a bunch of data from multiple tables with join, union, group by, etc., then you need to do some work on the resulting dataset...

Therefore, if you ever find you frequently write complex SQL queries, consider using Temporary Tables to simplify your work. This also makes other people's life easier if they need to maintain your queries or customize for some other uses.

Advantages of using Temporary Table

1. The temporary table's dataset is needed in other queries because it can dramatically reduce the complexity of the entire work (e.g. you break a complex query into pieces of small queries with the use of Temporary Tables).
2. Immediate result set is needed because the result set is required more than once in different queries, therefore eliminates the need to run the query again to get the same data.
3. You need to manipulate (add unique IDs aka auto number, update, insert, or delete) the result of a query in a temporary place, without needing to create permanent tables in a production environment for data manipulation. This is very useful in situations where you need to repeatedly perform the same action in a database. Consider to prepare the data in a Temporary Table, do all the works upfront, and when the data is ready, add the data to some table in one go by using a single set-based query such as cross update query or bulk insert query. This happens quite often in real life. Suppose you need to move 10 bricks from point A to point B. Which one is more efficient - carry 1 brick each time from A to B for 10 times, or put the 10 bricks in a trolley and push the trolley from A to B in one go?

Limitations of using Temporary Table

As of MySQL version 5.7, MySQL TEMPORARY tables have the following limitations:

1. A Temporary Table is visible only to the current session, and is dropped automatically when the session is closed. In other words, the Temporary Table is automatically removed on closure of database connection. Note that you also can use the DROP TABLE statement to explicitly drop a Temporary Table.

   Tip:
   
   
   A Temporary Table is created inside the database that the session is connected to. If the Temporary Table you create has the same name as an existing permanent table, the existing table becomes hidden until the temporary table is dropped. For example, if you create a Temporary Table called customers in Northwind Traders database, the existing permanent table customers will become invisible for any queries. When you select data from the customers table, it retrieves data from the customers Temporary Table, not the existing permanent customers table. To minimize any confusion or potential problems (e.g. dropping a permanent table accidentally), it is recommended that you always prefix the name of a Temporary Table with tmp_ or temp_ in order to differentiate it from the existing permanent tables.

   The best way to drop a MySQL Temporary Table

   You can use the DROP TABLE statement to remove a temporary table. However it is good practice to use the DROP TEMPORARY TABLE statement instead because the DROP TEMPORARY TABLE statement removes only temporary tables, not the permanent tables. If you try to drop a permanent table with the DROP TEMPORARY TABLE statement, you will get an error message stating that the table you are trying to drop is unknown.

   For example, to remove the tempProductsAboveAvgPrice temporary table, you use the following statement:

   drop temporary table tempProductsAboveAvgPrice

   If you develop an application that uses a connection pooling or persistent connections, it is not guaranteed that the temporary tables are removed automatically when your application is terminated. This is because the database connection that the application is using may be still open and is placed in a connection pool for other clients to use. This means you should always explicitly remove any temporary tables that you have created in a session whenever you are done with them in that session.

   A database session is the conversation between an application (a client application or server application) with a relational database and all the objects which persist during the dialog. For example, after you have opened SQLyog or MySQL Bench (MySQL client tool which can interact with MySQL) and connect to your MySQL database, a database session is created and maintained until SQLyog is closed. Another example is an Apache web server opens a connection to MySQL database to select data via a PHP web page.

2. CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.
3. TEMPORARY tables have a very loose relationship with database schemas (e.g. Northwind database). Dropping a database does not automatically drop any TEMPORARY tables created within that database. Also, you can create a TEMPORARY table in a non-existent database if you qualify the table name with the database name in the CREATE TABLE statement. In this case, all subsequent references to the table must be qualified with the database name.

   The following query creates a temporary table in a fake database xxx.

   create temporary table xxx.tempProductsAboveAvgPrice
   as
   select distinct ProductName, UnitPrice
   from Products
   where UnitPrice > (select avg(UnitPrice) from Products)
   order by UnitPrice

   Then when you query the table, you also need to use xxx to qualify the table name.

   select * from xxx.tempProductsAboveAvgPrice

4. Temporary Table can only be created as type MEMORY, MyISAM, MERGE, or InnoDB.
5. You cannot refer to a Temporary Table more than once in the same query, or you'll get ERROR 1137: Can't reopen table: 'tempProductsAboveAvgPrice'.

   This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

   To solve this problem, create two Temporary Tables with different names from the same SELECT query.

6. The SHOW TABLES statement does not list Temporary Table.

7. You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead.

   ALTER TABLE tempProductsAboveAvgPrice RENAME tempProductsAboveAvgPrice2

8. The CREATE TEMPORARY TABLES privilege enables the creation of temporary tables using the CREATE TEMPORARY TABLE statement.

   The following is quoted from MySQL manual:

   After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.

   One implication of this behavior is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the CREATE TEMPORARY TABLES privilege but is able to execute a DEFINER-context stored procedure that executes with the privileges of a user who does have CREATE TEMPORARY TABLES and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it.

   To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation is to create a database dedicated to the use of temporary tables. Then for that database, a user can be granted the CREATE TEMPORARY TABLES privilege, along with any other privileges required for temporary table operations done by that user.

Happy Coding!

Other tutorials in this category

1. Create MySQL table by using CREATE TABLE statement

2. Create MySQL table by using another table

3. How to disable (or enable) Safe Updates in MySQL

4. MySQL constraints explained by examples

Back to Tutorial Index Page