MySQL Temporary Table

A Temporary Table in MySQL is a special type of table that exists only during the session in which it is created. Temporary tables automatically drop when the session ends, making them ideal for storing intermediate data or temporary results.

Features of Temporary Tables:

Session-Specific: Temporary tables are visible only to the session that created them. Other sessions cannot access them.
Automatic Deletion: Temporary tables are automatically dropped when the session ends or the connection is closed.
Same Name as Permanent Tables: You can create a temporary table with the same name as an existing permanent table without conflict. However, within the session, the temporary table takes precedence.

Syntax to Create a Temporary Table

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

TEMPORARY: This keyword ensures the table is temporary.
table_name: The name of the temporary table.

Examples

1. Creating a Temporary Table

The following example creates a temporary table named temp_employees:

CREATE TEMPORARY TABLE temp_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. Inserting Data into a Temporary Table

Once the table is created, you can insert data just like with a regular table:

INSERT INTO temp_employees (name, department, salary)
VALUES ('Alice', 'HR', 5000),
       ('Bob', 'IT', 6000),
       ('Charlie', 'Finance', 7000);

3. Querying Data from a Temporary Table

You can query data from the temporary table:

SELECT * FROM temp_employees;

4. Dropping a Temporary Table

To drop a temporary table manually, use the DROP TABLE statement:

DROP TEMPORARY TABLE temp_employees;

Usage Scenario

Temporary tables are commonly used for:

Storing intermediate results for complex queries.
Caching data for repeated operations within a session.
Holding data for transformations before inserting it into permanent tables.

Limitations of Temporary Tables

Session-Specific: Temporary tables are not shared between sessions.
No Foreign Keys: Temporary tables do not support foreign key constraints.
Limited Scope: Temporary tables are dropped automatically when the session ends, so they cannot persist data across sessions.

Best Practices

Name Conflicts: Avoid naming temporary tables the same as critical permanent tables to prevent accidental operations.
Explicit Drops: Although temporary tables are dropped automatically, it's good practice to explicitly drop them to free up resources during long-running sessions.

Conclusion

MySQL Temporary Tables are a powerful feature for efficiently handling intermediate and temporary data during a session. By creating and using temporary tables, you can simplify complex queries, optimize performance, and easily manage session-specific data.

Souy Soeng

MySQL Temporary Table