MySQL Temporary Tables: Create, Drop & Manage Table

MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications among others.

MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. This Server is availed as a separate program and handles all the database instructions, commands, and statements.

This blog talks about the different aspects of MySQL Temporary Tables in great detail. It encapsulates the key features and benefits of MySQL as well before diving into the different operations that you can execute with MySQL Temporary Tables.

What is MySQL?

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a large number of use cases like web applications. 

Since it comes standard in the LAMP Stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, and PHP; MySQL is the go-to choice for scalable web applications.

Let’s talk about a few salient features that make MySQL such a great catch. Also, see how you can integrate from MySQL to PostgreSQL. 

Key Features of MySQL

• Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
• Long History: MySQL has been around for over 20 years since its first release in 1995.
• Frequent Updates: MySQL is made more robust with frequent updates with new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
• MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
• A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
• Open-Source: MySQL is also a Free and Open-Source Relational Database Management System (RDBMS).
• Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, and Zendesk to name a few.

MySQL is a lightweight database that can be installed and used by developers on production application servers with large multi-tier applications. Leveraging MySQL as a part of your workflow has several advantages:  

• Features like Master-Slave Replication and Scale-Out are supported by MySQL.
• Offload Reporting, Geographic Data Distribution is also supported by MySQL.
• There’s a very low overhead with the MyISAM storage engine when used for read-only applications.
• For frequently used tables, MySQL provides support for the Memory Storage Engine.
• For repeatedly used statements there exists a Query Cache. 
• MySQL is easy to learn and troubleshoot given a wide number of helpful sources like blogs, white papers, and books on the subject. 
• MySQL is a highly flexible and scalable Database Management System.

What is a Temporary Table?

MySQL Temporary Tables are special types of tables that let you store a temporary result set. You can reuse this temporary result set various times in a single session within MySQL.

A Temporary Table is needed when it is either expensive or virtually impossible to query data that needs a single SELECT statement to work in tandem with JOIN clauses. Here, you can leverage MySQL Temporary Tables to store the immediate result and use a different query to analyze or process it.

What are the Key Aspects of MySQL Temporary Tables?

This section will cover the key aspects of MySQL Temporary Tables to help you understand them better.

What are the Key Features of MySQL Temporary Tables?

• You can create a Temporary Table in MySQL by leveraging the CREATE TEMPORARY TABLE statement. This table is automatically removed by MySQL at the end of a session or if a connection gets terminated.
• You can also leverage the DROP TABLE statement to get rid of a temporary table when not in use.

• Even if you can have a temporary table that has the same name as a permanent table, it is recommended to give temporary tables a unique name. This is because having the same name might lead to confusion and might go so far as to cause an unexpected data loss.
• For instance, if the connection to the database server is lost and you reconnect to the server automatically, you won’t be able to distinguish between the permanent table and the temporary table. 

When to Use MySQL Temporary Tables?

Temporary tables in MySQL are generated when:

• We execute ALTER TABLE operations on big data collections.
• We simultaneously execute UPDATE commands on various tables.
• We need some DISTINCT values, and we need them to be in a specific order.
• In a table, we wish to count distinct values.
• We can see that MySQL uses temporary tables in various different situations by consulting the MySQL documentation.
• The relationship between the MySQL Temporary Table and the Database Schema is decoupled, so when we destroy the MySQL database, it does not always also drop the database’s temporary tables.

How to Use the MySQL CREATE TEMPORARY TABLE Statement?

Here are the salient aspects of the MySQL CREATE TEMPORARY TABLE Statement that will be covered in this section:

• Creating a MySQL Temp Table Example
• Creating a MySQL Temporary Table with a Query-Based Structure

Creating a MySQL Temp Table Example

You can use the syntax of creating a normal MySQL Table to create a Temporary Table, except for the TEMPORARY keyword. Here’s the syntax for the same:

mysql> CREATE TEMPORARY TABLE table_name (  
   column_1, column_2, ..., table_constraints

If you wish to create a MySQL Temporary Table whose structure resembles an existing table in the database, then the above statement wouldn’t work. Instead, you can leverage the syntax mentioned below:

Mysql> CREATE TEMPORARY TABLE temporary_table_name SELECT * FROM original_table_name LIMIT 0;  

• Step 1: Use this statement to create a temporary table called Students for our example:

mysql> CREATE TEMPORARY TABLE Students( student_name VARCHAR(40) NOT NULL, total_marks DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_subjects INT UNSIGNED NOT NULL DEFAULT 0);  

This is the result of the aforementioned query:

• Step 2: Now that you have created the Temporary Table, you need to populate it with values. Here’s a code snippet to help you understand how to do this:

mysql>INSERT INTO Students(student_name, total_marks, total_subjects) VALUES ('Joseph', 150.75, 2), ('Peter', 180.75, 2);  

Having executed this statement, here’s what the result of a populated temporary table in MySQL would look like:

•  Step 3: Next, run the following query to get a result:

mysql> SELECT * FROM Students; 

After executing the aforementioned query, this is the result you can expect:

• Step 4: A point to note here is that if you execute the query SHOW TABLES, then this temporary table would not make the list. Also, if you close the current session and try executing the SELECT statement, you will be looking at a message that says no data is available in the requested database.

Creating a MySQL Temporary Table with a Query-Based Structure

This example will help demonstrate how you can create a Temporary Table in MySQL that stores the top 10 customers based on the revenue they bring in. The structure of the Temporary Table is extracted from a SELECT statement as follows:

CREATE TEMPORARY TABLE top_customers
SELECT p.customerNumber, 
       c.customerName, 
       ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;

Having created the temporary table top_customers, you can query data just like from a normal table in MySQL. Here’s the code snippet for the same:

SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top_customers
ORDER BY sales;

On executing the aforementioned query, this is the result you can expect:

How to Drop a MySQL Temporary Table?

In MySQL, you can drop the temporary table with the DROP TABLE statement. However, to avoid confusion it is a good recommended practice to use the TEMPORARY keyword with the DROP TABLE statement.

Here’s the syntax for dropping a MySQL Temporary table:

mysql> DROP TEMPORARY TABLE table_name;  

• A point to note here is that you can’t use this statement to get rid of a permanent table. If you mistakenly try to delete a permanent table by leveraging the aforementioned query, the system will throw an error message telling you that the table you are attempting to delete is not known.
• For instance, if you wish to remove the top_customers table from the previous example, here’s how you can do it:

DROP TEMPORARY TABLE top_customers;

• If you create an application that leverages Contiguous Connections or Connection Pooling, it is not a given that the temporary tables would be removed automatically on the termination of your application.
• This is because the database connection that is leveraged by the application might still be open and placed in a connection pool for other clients to reuse at a future date.
• So, it is recommended that you always remove the Temporary Tables when they have served their purpose.

How Can You Check if a Temporary Table Exists?

MySQL doesn’t supply a statement or a function to directly check if a temporary table exists. However, you can develop a stored procedure that can check if a Temporary Table exists as follows:

DELIMITER //
CREATE PROCEDURE check_table_exists(table_name VARCHAR(100)) 
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @err = 1;
    SET @err = 0;
    SET @table_name = table_name;
    SET @sql_query = CONCAT('SELECT 1 FROM ',@table_name);
    PREPARE stmt1 FROM @sql_query;
    IF (@err = 1) THEN
        SET @table_exists = 0;
    ELSE
        SET @table_exists = 1;
        DEALLOCATE PREPARE stmt1;
    END IF;
END //
DELIMITER ;

In this stored procedure, you can try to select the data from a designated temporary table. If the temporary table exists, the @table_exists variable returns the value 1.

If no such temporary table exists, the @table_exists variable will return the value 0.

The following statement calls the stored procedure, check_table_exists to verify if the temporary table Students exists:

CALL check_table_exists('Students');
SELECT @table_exists;

This is the output of the aforementioned statement:

Conclusion

This blog elucidates the different aspects of MySQL Temporary Tables after a brief introduction to MySQL and its integral benefits and features.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day!