Simple Example of a MySQL Temporary Table.

Need a table without creating a permanent one in your database? You are in luck!!! This blog post will cover using the TEMPORARY keyword in a CREATE TABLE statement to CREATE a temporary table. But don’t get attached. It won’t be around forever!


Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.3 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.

Starting out, here are the tables currently present in my practice database.

mysql> SHOW tables;
+--------------------+
| Tables_in_practice |
+--------------------+
| country_new        |
| departments        |
| dup_countries      |
| employees          |
| job_history        |
| job_histry         |
| jobs               |
| locations          |
+--------------------+
8 rows in set (0.00 sec)

Let’s look at the locations table description and data,

mysql> DESC locations;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id             | int(11)      | YES  |     | NULL    |       |
| location_id    | decimal(4,1) | YES  |     | NULL    |       |
| street_address | varchar(40)  | YES  |     | NULL    |       |
| postal_code    | varchar(12)  | YES  |     | NULL    |       |
| state          | varchar(25)  | YES  |     | NULL    |       |
| region_id      | varchar(2)   | YES  |     | NULL    |       |
| country_id     | int(11)      | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM locations;
+------+-------------+-----------------+-------------+-------+-----------+------------+
| id   | location_id | street_address  | postal_code | state | region_id | country_id |
+------+-------------+-----------------+-------------+-------+-----------+------------+
|   15 |        16.0 | Happy Lane      | 888119      | LA    | SE        |          1 |
|   19 |        19.5 | Roger Road      | 888119      | LA    | SE        |          1 |
|   12 |        12.5 | 1212 Happy Lane | 87112       | LA    | SE        |          1 |
|   13 |        13.5 | 1213 Happy Lane | 87112       | LA    | SE        |          1 |
|   14 |        14.5 | 1111 Some Drive | 87022       | MA    | NE        |          2 |
|   14 |        14.5 | 12 Road         | 87114       | MA    | NE        |          2 |
|   14 |        14.5 | 1A BLVD         | 87412       | MA    | NE        |          2 |
|   14 |        14.5 | PO Box 4848     | 87912       | MA    | NE        |          2 |
|   14 |        14.5 | 17A Apt 3       | 87119       | MA    | NE        |          2 |
|   10 |        10.5 | 89 Mountain Hwy | 92117       | OR    | NW        |          4 |
+------+-------------+-----------------+-------------+-------+-----------+------------+
10 rows in set (0.00 sec)

Suppose, you only need this temporary table to be composed of the location_id, postal_code, state and the region_id columns from the locations table.

That my friend is where CREATE TEMPORARY TABLE syntax comes into the picture.

For this table’s composition, I will utilize the SELECT statement to generate the needed columns and data from the locations table. (Check out this blog post on CREATE TABLE SELECT I wrote recently for a complete overview.)

Have a look at the statement below.

mysql> CREATE TEMPORARY TABLE my_session
-> SELECT location_id, postal_code, state, region_id
-> FROM locations;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

And checking the present data in the my_session temporary table:

mysql> SELECT * FROM my_session;
+-------------+-------------+-------+-----------+
| location_id | postal_code | state | region_id |
+-------------+-------------+-------+-----------+
|        16.0 | 888119      | LA    | SE        |
|        19.5 | 888119      | LA    | SE        |
|        12.5 | 87112       | LA    | SE        |
|        13.5 | 87112       | LA    | SE        |
|        14.5 | 87022       | MA    | NE        |
|        14.5 | 87114       | MA    | NE        |
|        14.5 | 87412       | MA    | NE        |
|        14.5 | 87912       | MA    | NE        |
|        14.5 | 87119       | MA    | NE        |
|        10.5 | 92117       | OR    | NW        |
+-------------+-------------+-------+-----------+
10 rows in set (0.00 sec)

You can query the my_session table just as you would a real table.

After logging out and back in (not shown), when re-querying the my_session table, you are returned:

mysql> SELECT * FROM my_session;
ERROR 1146 (42S02): Table 'practice.my_session' doesn't exist

The temporary table was dropped when the previous session was closed.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Something Magical

Wanna see something cool?
A bit of magic?
Check this out.
First, here are the names of all present databases.

mysql> SHOW databases;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| BowlingLeagueExample       |
| BowlingLeagueModify        |
| EntertainmentAgencyExample |
| EntertainmentAgencyModify  |
| RecipesExample             |
| SalesOrdersExample         |
| SalesOrdersModify          |
| SchoolSchedulingExample    |
| SchoolSchedulingModify     |
| asbuilt                    |
| bank                       |
| hr                         |
| mysql                      |
| northwind                  |
| performance_schema         |
| practice                   |
| projects                   |
| sakila                     |
| sys                        |
| trying_json                |
| world_x                    |
+----------------------------+
22 rows in set (0.00 sec)

Do you see a database named simulation anywhere in that list?
Me either. But keep following me here.

mysql> CREATE TEMPORARY TABLE simulation.my_table(
    -> id INTEGER,
    -> name VARCHAR(20),
    -> state VARCHAR(20));
Query OK, 0 rows affected (0.00 sec)

May as well INSERT a couple of rows of data right?

mysql> INSERT INTO my_table(id,name,state)
    -> VALUES(1,'Jimmy','Alabama'),
    -> (12,'Alannis','Rhode Island'),
    -> (33,'Martin','Louisiana');
ERROR 1046 (3D000): No database selected

Hmm…
What’s the deal with that?
In this scenario, you have to fully qualify the TEMPORARY table name with the non-existent database name.

Let’s give that another shot.

mysql> INSERT INTO simulation.my_table(id,name,state)
    -> VALUES(1,'Jimmy','Alabama'),
    -> (12,'Alannis','Rhode Island'),
    -> (33,'Martin','Louisiana');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

I’ll buy that.
And retrieving the data.

mysql> SELECT * FROM simulation.my_table;
+------+---------+--------------+
| id   | name    | state        |
+------+---------+--------------+
|    1 | Jimmy   | Alabama      |
|   12 | Alannis | Rhode Island |
|   33 | Martin  | Louisiana    |
+------+---------+--------------+
3 rows in set (0.00 sec)

Can you figure out what results will be returned, if you run a SELECT query like this?

mysql> SELECT * FROM my_table;

Let’s see.

mysql> SELECT * FROM my_table;
ERROR 1046 (3D000): No database selected

Therefore, we must understand that any references to the temporarily created table, with a nonexistent database, must be fully qualified (i.e., Prefixed with the database name).
Pretty cool stuff I’d say.

Important Highlights

I found the below conclusions informative while studying the documentation and want to share them with you.

  • A TEMPORARY table’s visibility is limited to the current session only. When the session is closed, it is automatically dropped.
  • A user must have the CREATE TEMPORARY TABLES privilege.
  • It is possible for two different sessions to use the same TEMPORARY table name without conflict. Even with an existing identically named non-TEMPORARY table, there are no troubles. For example, a TEMPORARY and an existing table sharing the same name.

Closing

My goal is that, through this example blog post, you can implement CREATE TEMPORARY TABLE should you discover a need for it. As always, I am pleased to provide great content for you all here, as I continue along this path of becoming a SQL Developer. Thank you all.
Explore the official MySQL 5.7 Online Manual for a more in-depth look.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

 

 

 

 

 

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.