Creating a temporary table in MySQL
Start your free 7-days trial now!
To create a temporary table in MySQL use the following syntax:
CREATE TEMPORARY TABLE table_name (column_name datatype);
To create a temporary table based on an existing permanent table:
CREATE TEMPORARY TABLE new_table SELECT * FROM existing_table LIMIT 0;
This will create a new temporary table with the same structure as the existing table, however, the temporary table will not be populated with records from the existing table due to the LIMIT 0 restriction (i.e. the temporary table will be created with zero records).
A few points to note regarding temporary tables:
A temporary table is available only within the current session and is automatically dropped at the end of the session.
To create a temporary table, you must have the
CREATE TEMPORARY TABLESprivilege.Temporary tables are often used when the results of a complex query are required as inputs for another query. As this allows us to break a single complex query into several smaller less complex queries, it greatly improves efficiency.
Examples
Creating temporary table from scratch
To create a temporary table called temp1 with one column shop_number that is of type INT:
CREATE TEMPORARY TABLE temp1 (shop_number INT NOT NULL);
Query OK, 0 rows affected (0.00 sec)
Creating temporary table based on existing table
Given an existing table drivers_license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
To create a temporary table populated with data for student_id=1:
CREATE TEMPORARY TABLE temp_drivers_license SELECT * FROM drivers_license WHERE student_id =1;
Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0
To check the contents of the created temporary table temp_drivers_license:
SELECT * FROM temp_drivers_license;
+------------+------------+------+-------------+| student_id | full_name | age | has_license |+------------+------------+------+-------------+| 1 | Sky Towner | 17 | 0 |+------------+------------+------+-------------+
