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 Now, just to point something out, let's create a standard (permanent) table. Here's how: CREATE TABLE t (
    c INT
); This table, t, will persist in the database even after we exit the client: exit 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 SHOW TABLES; +----------------+
| Tables_in_demo |
+----------------+
| t              |
+----------------+ All this is pretty obvious, but now, let's recreate this table and try something different: CREATE OR REPLACE TEMPORARY TABLE t (
    c INT
); 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, and 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)
) We can create a temporary table that mimics the structure of products: CREATE TEMPORARY TABLE t LIKE products; We can confirm this by running: DESCRIBE t; +-------------+--------------+------+-----+---------+----------------+
| 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    |                |
+-------------+--------------+------+-----+---------+----------------+ 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%'; After running this command, if we query the temporary table t: SELECT * FROM t; +----+--------+------------------+---------------------------------------------------+
| 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. |
+----+--------+------------------+---------------------------------------------------+ 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. Let's explore how temporary tables work in MariaDB. First, we have to connect to the server. For example (use your own connection details): temporary tables mariadb -h 127.0.0.1 -u root -p"RootPassword!" --database demo mariadb -h 127.0.0.1 -u root -p"RootPassword!" --database demo Now, just to point something out, let's create a standard (permanent) table. Here's how: CREATE TABLE t (
    c INT
); CREATE TABLE t (
    c INT
); This table, t , will persist in the database even after we exit the client: t exit exit When we reconnect and check the existing tables using SHOW TABLES; , the table t will still be listed: SHOW TABLES; t mariadb -h 127.0.0.1 -u root -p"RootPassword!" --database demo mariadb -h 127.0.0.1 -u root -p"RootPassword!" --database demo SHOW TABLES; SHOW TABLES; +----------------+
| Tables_in_demo |
+----------------+
| t              |
+----------------+ +----------------+
| Tables_in_demo |
+----------------+
| t              |
+----------------+ All this is pretty obvious, but now, let's recreate this table and try something different: CREATE OR REPLACE TEMPORARY TABLE t (
    c INT
); CREATE OR REPLACE TEMPORARY TABLE t (
    c INT
); 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, and 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. TEMPORARY SHOW TABLES; SHOW TABLES; t 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: products 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)
) 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)
) We can create a temporary table that mimics the structure of products : products CREATE TEMPORARY TABLE t LIKE products; CREATE TEMPORARY TABLE t LIKE products; We can confirm this by running: DESCRIBE t; DESCRIBE t; +-------------+--------------+------+-----+---------+----------------+
| 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    |                |
+-------------+--------------+------+-----+---------+----------------+ +-------------+--------------+------+-----+---------+----------------+
| 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    |                |
+-------------+--------------+------+-----+---------+----------------+ 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: t products t INSERT INTO t
SELECT * FROM products
WHERE code LIKE '%0%'; INSERT INTO t
SELECT * FROM products
WHERE code LIKE '%0%'; After running this command, if we query the temporary table t : t SELECT * FROM t; SELECT * FROM t; +----+--------+------------------+---------------------------------------------------+
| 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. |
+----+--------+------------------+---------------------------------------------------+ +----+--------+------------------+---------------------------------------------------+
| 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. |
+----+--------+------------------+---------------------------------------------------+ 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.

Packages for Store Routines in MariaDB 11.4: What You Need to Know

Achieving High Availability with MariaDB MaxScale

Visit my personal Blog!

Nominated for 2022 - HackerNoon Contributor of the Year - Database

Nominated for 2022 - HackerNoon Contributor of the Year - Git

Nominated for 2022 - HackerNoon Contributor of the Year - Java

Nominated for 2022 - HackerNoon Contributor of the Year - Docker

Nominated for 2022 - HackerNoon Contributor of the Year - Sql

Nominated for 2022 - HackerNoon Contributor of the Year - Analytics

Too Long; Didn't Read

Make resilience your competitive advantage

How to Use Temporary Tables in MariaDB

How to Use Temporary Tables in MariaDB

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

9 Useful Interactive CLI Tools for Linux

10 Steps To Digital Transformation While Simultaneously Cutting Costs

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

10 Cool CI/CD Tools For Your Project

121 Stories To Learn About Databases

9 Useful Interactive CLI Tools for Linux

10 Steps To Digital Transformation While Simultaneously Cutting Costs

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

10 Cool CI/CD Tools For Your Project

121 Stories To Learn About Databases

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps