DEV Community

Cover image for Using Temporary Tables in MariaDB
Alejandro Duarte
Alejandro Duarte

Posted on

4

Using Temporary Tables in MariaDB

Let's explore how temporary tables work in MariaDB. First, we have to connect to the server. For example (use your own connection details):

mariadb -h 127.0.0.1 -u root -p"RootPassword!" --database demo
Enter fullscreen mode Exit fullscreen mode

Now, just to point something out, let's create a standard (permanent) table. Here's how:

CREATE TABLE t (
    c INT
);
Enter fullscreen mode Exit fullscreen mode

This table, t, will persist in the database even after we exit the client:

exit
Enter fullscreen mode Exit fullscreen mode

When we reconnect and check the existing tables using SHOW TABLES;, the table t will still be listed:

mariadb -h 127.0.0.1 -u root -p"RootPassword!" --database demo
Enter fullscreen mode Exit fullscreen mode
SHOW TABLES;
Enter fullscreen mode Exit fullscreen mode
+----------------+
| Tables_in_demo |
+----------------+
| t              |
+----------------+
Enter fullscreen mode Exit fullscreen mode

All this is pretty obvious, but now, let's recreate this table and try something different:

CREATE OR REPLACE TEMPORARY TABLE t (
    c INT
);
Enter fullscreen mode Exit fullscreen mode

Notice the TEMPORARY keyword. After creating this table, if we run SHOW TABLES;, it appears in the list. We can insert data into it, query it, join it with other tables. It behaves like a normal table during the current session. However, if we exit the client, then reconnect, and perform a SHOW TABLES; again, the temporary table t will not be listed. A temporary table only exists for the duration of the session in which it was created and other sessions won't be able to see it.

Use Case for Temporary Tables

Temporary tables are quite useful for transient data operations. For instance, consider a table called products in our database:

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(100) NOT NULL,
  name VARCHAR(250) NOT NULL,
  description TEXT DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY code (code)
)
Enter fullscreen mode Exit fullscreen mode

We can create a temporary table that mimics the structure of products:

CREATE TEMPORARY TABLE t LIKE products;
Enter fullscreen mode Exit fullscreen mode

We can confirm this by running:

DESCRIBE t;
Enter fullscreen mode Exit fullscreen mode
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| code        | varchar(100) | NO   | UNI | NULL    |                |
| name        | varchar(250) | NO   |     | NULL    |                |
| description | text         | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
Enter fullscreen mode Exit fullscreen mode

Initially, t will be empty. However, suppose we want to transfer some data from products to t. Let’s assume we only want to include products that contain the number 0 in their code:

INSERT INTO t
SELECT * FROM products
WHERE code LIKE '%0%';
Enter fullscreen mode Exit fullscreen mode

After running this command, if we query the temporary table t:

SELECT * FROM t;
Enter fullscreen mode Exit fullscreen mode
+----+--------+------------------+---------------------------------------------------+
| id | code   | name             | description                                       |
+----+--------+------------------+---------------------------------------------------+
|  1 | BG2024 | BugBlaster       | Eradicates software bugs with a single scan.      |
|  3 | FW001  | FireWhale        | An oversized, comprehensive firewall solution.    |
|  4 | CLD404 | CloudNine Finder | Find your way back from cloud outages and errors. |
+----+--------+------------------+---------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

We see the filtered data.

Conclusion

Temporary tables offer a powerful way to handle data for temporary processing without affecting the persistent data store. They are particularly useful in scenarios where data needs to be manipulated or transformed temporarily. You can use permanent tables for this kind of data manipulation but temporary tables are useful when you need automatic cleanup, reduced risk of naming conflicts, isolation and security, and resource management for query performance.

API Trace View

Struggling with slow API calls? 👀

Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay