In this article, we are going to learn how to create a temp table and further drop these tables. The temporary tables are session-specific tables that are created within the session. When the session closes, the table is dropped by itself. A temporary table exists within a session only; therefore, a table created by one session is not visible to another session. For example, Session ID 501 has created a temporary table then session ID 502 cannot access it or manipulate it. Two different sessions can have a temporary table with the same name.

The temporary tables are useful when you want to use a small subset of the large table, and it is being used multiple times within a stored procedure. In such cases, instead of applying the filter on the table multiple times, you can save the subset of large tables in a temporary table and use it within a stored procedure.

Important facts of the temporary tables:

1. MySQL Temporary table has a decoupled relationship with the database schema, which means that when we drop the MySQL database, it does not necessarily drop the temporary tables within the database
2. The InnoDB database engine does not support the compressed, temporary table. If you had enabled the InnoDB_strict_mode parameter, then it will return an error. If you disabled the InnoDB_strict_mode parameter, MySQL will issue a warning and create a table with a non-compressed row format
3. To create a temporary table within the stored procedure, the user must have create temporary table privilege. Once it is granted, then to update, delete, or insert the data within that temporary table does not require any special privileges. There is one implication of this behavior. Suppose the stored procedure that uses a temporary table and a **USER 1 **who had developed the procedure has the create temporary table privilege. Now, USER 2 tries to execute it, but it does not have the create temporary table permission. In such cases, MySQL uses the privileges of the USER 1 to create the temporary table. Once the procedure completes successfully, the privileges will be reverted
4. You cannot create a temporary table based on the definition that the table already has, and it resides on MySQL tablespace, a general tablespace, or InnoDB tablespace. To create a temporary table based on the definition of another table, you must use the following syntax
5. Select temporary table … select * from
6. Unlike regular create table statements, the Create temporary table statement does not cause the implicit commit
7. The temporary table can have the same name as the MySQL regular table has. For example, even though a table named employee exists on the database, you can create a temporary table named employee in the database. In such cases, the MySQL table becomes inaccessible. Once the temporary table is dropped, the physical table can be accessed again. So it is always a good practice to create a temporary table with some unique name or make sure that the application drops the temporary table immediately after its purpose is completed

MySQL Temporary table examples

The syntax to create a temp table is as follows:

create temporary table tblemployee
    (
    Column_1 datatype,
    Column_2 datatype,
    Column_3 datatype,
    …
    )

As you can see, the syntax to create a temporary table is the same as the syntax of creating a MySQL table. The only difference is that you must specify the temporary keyword between create and table keywords. This is very important because if you forget to add the keyword, it creates a regular MySQL table.

Now, let us walk through some examples of the temporary table. I am going to demonstrate the following use cases:

1. Create a temp table and insert data in the table
2. Create a temp table using the output of the select statement
3. Drop the temp table
4. Create a temp table using the structure of another table

#mysql #sql commands