What is a temporary table, and how do you create one in MySQL?

A temporary table is a table used to store data temporarily within a database. It exists only within the current database session or transaction. When the session or transaction ends, the temporary table is automatically dropped, thus not affecting the permanent structure of the database. This characteristic makes temporary tables ideal for handling complex queries or storing intermediate results temporarily.

The syntax for creating a temporary table in MySQL is the CREATE TEMPORARY TABLE statement. The following is an example:

sql
CREATE TEMPORARY TABLE temp_users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (id)
);

In this example, a temporary table named temp_users is created with three fields: id (an auto-incrementing integer set as the primary key), name (a string type with a maximum length of 100), and age (an integer type). This table is only valid within the current database session, and the table and its data are automatically dropped when the session ends.

A common use case for temporary tables is during complex data analysis or reporting, where multiple steps may be required to process data. In such scenarios, temporary tables can store intermediate results from each step, avoiding any impact on the original data and facilitating more efficient data transfer between steps.

For instance, if you need to filter users older than 30 from the users table and perform further analysis on this subset, you can first store the filtered data in a temporary table and then proceed with additional operations, as shown below:

sql
-- Create temporary table and insert filtered data
CREATE TEMPORARY TABLE temp_users_over_30 (
    id INT,
    name VARCHAR(100),
    age INT
);

INSERT INTO temp_users_over_30
SELECT id, name, age FROM users WHERE age > 30;

-- Perform further analysis on temp_users_over_30
SELECT AVG(age) FROM temp_users_over_30; -- Calculate average age

Using temporary tables effectively organizes and simplifies SQL code, improving data processing efficiency.