MySQL TEMPORARY TABLE Statement

MySQL TEMPORARY TABLE Statement

The MySQL TEMPORARY TABLE statement is used to create a temporary table that exists only for the duration of a session. This statement is essential for storing intermediate results and data manipulation during complex queries.

Syntax

CREATE TEMPORARY TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

The CREATE TEMPORARY TABLE statement has the following components:

  • table_name: The name of the temporary table to be created. It must be unique within the session.
  • column1, column2, ...: The names of the columns in the table.
  • datatype: The data type of the column (e.g., INT, VARCHAR(100), DATE).
  • [constraints]: Optional constraints for the column (e.g., PRIMARY KEY, NOT NULL, AUTO_INCREMENT).

Example MySQL TEMPORARY TABLE Statement

Let's look at an example of the MySQL TEMPORARY TABLE statement and how to use it:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Temporary Table

Create a temporary table to work with:

CREATE TEMPORARY TABLE temp_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates a temporary table named temp_employees with columns for id, first_name, last_name, and email.

MySQL CREATE TEMPORARY TABLE

Step 3: Inserting Data into the Temporary Table

Insert some data into the temporary table:

INSERT INTO temp_employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com'),
       ('Jane', 'Smith', 'jane.smith@example.com');

This query inserts two rows into the temp_employees table.

MySQL INSERT INTO TEMPORARY TABLE

Step 4: Querying the Temporary Table

Select data from the temporary table:

SELECT * FROM temp_employees;

This query retrieves all rows from the temp_employees table.

MySQL SELECT FROM TEMPORARY TABLE

Step 5: Dropping the Temporary Table

Drop the temporary table:

DROP TEMPORARY TABLE temp_employees;

This query deletes the temp_employees table. Since it is a temporary table, it would also be dropped automatically when the session ends.

MySQL DROP TEMPORARY TABLE

Conclusion

The MySQL TEMPORARY TABLE statement is a powerful tool for creating tables that exist only for the duration of a session. Understanding how to use the TEMPORARY TABLE statement is essential for efficient data manipulation and intermediate result storage during complex queries in MySQL.