How to Create and Drop Temp Tables in MySQL?

Temporary tables are special tables that store the temporary result set and reuse it multiple times in a single session. This means that data can be accessed without using the original table. It is also used to create smaller subsets of a larger table for faster access and efficiency maintaining the consistency of data in case of errors. Temporary tables are easy to create. 

MySQL Temporary Tables can be created, and dropped just like general tables but requires a few conditions to be met. Also, there are a couple of syntax types that can be used to create a MySQL temporary table. This guide provides a comprehensive explanation of MySQL temporary tables along with the conditions that are required, the syntax for creating a temporary table, and a couple of examples including a real-life example. Also, the syntax required to drop a temporary table is also provided in detail.

Table of Content

  • What are Temporary tables in MySQL?
    • Main Facts of MySQL Temporary tables
  • Create MySQL Temporary Table
    • Prerequisites
    • Syntax
    • Example 1
    • Example 2
  • Dropping a MySQL Temporary Table
  • Conclusion

What are temporary tables in MySQL?

Temporary tables in MySQL are tables that are specialized in storing the values of the original table temporarily so that they can be reused without affecting the original data and improve efficiency. The table created for a particular session stays in that session hence making it inaccessible for other sessions resulting in the prevention of redundancy and security.

These tables act as an alternative to filters, but a one-time solution. When there are very large datasets and only a small part is required for the process, temporary tables can be used to create subsets and used in stored procedures repetitively, instead of applying filters to the table in every procedure that leads to the possibility of inconsistent and corrupt data.

Main facts about the MySQL Temporary tables:

  1. MySQL temporary tables use the decoupled relationship. This means that there is no direct connection between the database schema and the temp table. Hence when the MySQL database is dropped, it does not drop the temporary table from the database.
  2. The InnoDB engine of MySQL doesn’t support the temporary tables. If the InnoDB_strict_mode is enabled it will pop an error and when the InnoDB_strict_mode is disabled, a warning will pop up to create the table in non-compressed format.
  3. The create temporary table privilege is mandatory if the user needs to create a temporary table. You won’t require any additional privileges to update, delete or insert the data after the create temporary table privilege is granted. One main implication of this is when User 1 creates a table using the create temporary table privilege and when user 2 tries to access the same table, even without the privilege, MySQL uses the privileges of User 1 to perform changes on the table and revert back the privileges.
  4. Usually, it is not possible to create temporary tables with the MySQL schema of a table that already exists in the MySQL tablespace, InnoDB tablespace, or general tablespace. To use the MySQL schema definition of the existing table, you will have to use the output of a select statement and then create a table. you can use the following syntax:

 Select temporary table.. select * from table_name

  1. The create table command for a temporary table doesn’t cause implicit commit like in the case of the general create command.
  2. Temporary tables are allowed to have the same name as the original table in the database. The implication is that the original table cannot be accessed until the temporary table is dropped. So it is recommended to have unique names for temporary tables but if it is not feasible, then drop the temporary table as soon as the procedure is complete.

Create MySQL Temporary table

Prerequisites

  1. Requires a temporary table privilege.
  2. A temporary table name needs to be unique in most cases.

Syntax:

create temporary table table_name

    (

    Column_name_1 datatype,

    Column_name_2 datatype,

    Column_name_3 datatype,

    …

    )

The syntax of creating a MySQL temporary table is similar to creating a general table in MySQL, with the exception of just adding a temporary keyword. 

Example:

Let us create a new temporary table named Hevo_employees using the following query:

create temporary table Hevo_employees

(

emp_id int auto_increment Primary key,

emp_name varchar(500),

emp_address varchar(500),

emp_dept varchar(500)

)

After executing the above query a new temporary table will be created. Let us add the values to this table.

insert into Hevo_employees(emp_name, emp_address,emp_dept)

values

(‘Mohammed Arsalan’,’Hyderabad,India’,’Marketing’),

(‘John simon’,’Texas,USA’’,’Customer strategy’),

(‘Lovely Singh’,’Punjab,India’,’Sales and customer aqusition’);

After executing the above query the values will be added into the temporary table. When you use the select statement for the table in same session it will print the values but when the session is changed the table is inaccessible. 

You can also create a temporary table based on the output of a previous select statement.

Let us take an example of a temporary table for film details

Example 2:

create temporary table film_details

select film_title, film_description, film_release_year, film_rental_rate , film_rental_duration, film_special_features, film_replacement_cost, c.name from 

film a inner join film_category b on a.film_id=b.film_id

inner join category c on b.category_id=c.category_id

Limit 10;

Dropping the Temporary table

The temporary tables can be dropped using the following query:

Drop temporary table table_name

Note: Since there is a provision to add a temporary table with the same name as the table in the database, be careful while dropping a temporary table, and don’t forget to add the temporary keyword, or else the original table may get dropped.

Conclusion

MySQL is a leading SQL solution that is feature-rich. It provides tools that help in storing, retrieving, and working on data very efficiently, maintaining a robust, flexible, scalable solution throughout. One of the features of MySQL is the ability to create temporary tables. These tables store the data temporarily. In this guide, a detailed explanation of MySQL temporary tables is provided along with its syntax, examples, and much more. Also, the queries required to drop the temporary tables along with an important aspect are mentioned. 

TIME BUSINESS NEWS