MySQL Temporary Table

Last Updated: 17 June, 2023

In MySQL, a temporary table is a special type of table that is created and exists only for the duration of the current database session. If the current session is closed, expires, or the connection is terminated, MySQL deletes all the temporary tables automatically.

There are two types of temporary tables in MySQL:

1. Session temporary table: These tables are visible only to the current session and are automatically dropped when the session ends.
2. Global temporary table: These tables are visible to all sessions, but they are dropped when the last session using the table ends.

To create a temporary table in MySQL, we can use the CREATE TEMPORARY TABLE statement followed by the table definition.

To create a temporary table in MySQL, we can use the following syntax:

  CREATE TEMPORARY TABLE temp_table_name(
    column_1 datatype,
    column_2 datatype,
    ...,
    table_constraints
  );  

Example:

  CREATE TEMPORARY TABLE student(
    stu_id INT NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(50) NOT NULL,
    stu_class VARCHAR(15),
    stu_roll SMALLINT,
    PRIMARY KEY (stu_id)
  );  

In the current session, if our requirement is to delete the temporary table, then we can also delete it using the DROP TEMPORARY TABLE statement followed by table name.

To drop a temporary table in MySQL, we can use the following syntax:

  DROP TEMPORARY TABLE temp_table_name;  

Example:

  DROP TEMPORARY TABLE student;  

Temporary tables are useful in the following situations:

• When we need to store and manipulate data within a session without affecting the original data in the database.
• When we need to join large tables or perform complex queries, as they can help optimize performance by reducing the amount of data that needs to be processed.

A temporary table can have the same name as an existing table in a database. For example, in the school_DB database, if we create a temporary table named student, the existing student table becomes inaccessible. Every query we execute against the student table now refers to the temporary table student. When we drop the temporary table student, the permanent student table becomes available and accessible.