Squash repeatedly typing the same query – Use a MySQL VIEW instead.

arnaud-papa-28756

Photo by Arnaud Papa on Unsplash

Do you find yourself repeatedly typing the same query? Want to save it for reuse? A VIEW to the rescue it is! In this blog post we will look at a scenario and a VIEW that fits this very need.


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.21 for these exercises.

Tables and Data

Suppose we have these two tables in a relationship with a PRIMARY KEY and a FOREIGN KEY.

mysql> DESC pipe;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| pipe_id     | smallint(6)  | NO   | PRI | NULL    | auto_increment |
| pipe_name   | varchar(25)  | NO   | UNI | NULL    |                |
| joint_num   | varchar(25)  | NO   |     | NULL    |                |
| heat        | varchar(25)  | NO   |     | NULL    |                |
| pipe_length | decimal(4,2) | YES  |     | NULL    |                |
| has_degree  | tinyint(1)   | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> DESC degree_value;
+---------------+-----------------------+------+-----+---------+-------+
| Field         | Type                  | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| pipe_id       | smallint(6)           | YES  | MUL | NULL    |       |
| degree_amount | decimal(4,2) unsigned | YES  |     | NULL    |       |
+---------------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

And let’s see what data is present in them both:

mysql> SELECT * FROM pipe;
+---------+-------------+-----------+-------+-------------+------------+
| pipe_id | pipe_name   | joint_num | heat  | pipe_length | has_degree |
+---------+-------------+-----------+-------+-------------+------------+
|     215 | Joint-28    | 39393A    | 9111  |       17.40 |          1 |
|     216 | Joint-88    | 19393Y    | 9011  |       16.60 |          0 |
|     217 | Joint-98    | 39393V    | 8171  |       10.40 |          0 |
|     218 | Joint-2228  | 34493U    | 9100  |       11.80 |          1 |
|     219 | Joint-8819  | 18393z    | 9159  |       13.00 |          0 |
|     220 | Joint-9844  | 19293Q    | 8174  |        9.13 |          0 |
|     221 | Joint-7834  | 17QTT     | 179   |       44.40 |          1 |
|     222 | Joint-171   | 34493U    | 17122 |        9.45 |          1 |
|     223 | Joint-6844  | 17297Q    | 6114  |       11.34 |          0 |
|     224 | Joint-4841  | 19395Q    | 5144  |        7.43 |          0 |
|     225 | Joint-1228A | 34493U    | 8575B |       15.22 |          1 |
|     226 | Joint-2128  | 34493C    | 91    |       13.55 |          1 |
|     227 | Joint-222B  | 34493U    | 9100B |       21.63 |          1 |
+---------+-------------+-----------+-------+-------------+------------+
13 rows in set (0.01 sec)
mysql> SELECT * FROM degree_value;
+---------+---------------+
| pipe_id | degree_amount |
+---------+---------------+
|     215 |         12.50 |
|     218 |         22.50 |
|     221 |         10.00 |
|     222 |          4.00 |
|     225 |         10.25 |
|     226 |          5.75 |
|     227 |         11.75 |
+---------+---------------+
7 rows in set (0.00 sec)

I need to briefly dive into the has_degree column.
Known as a boolean value (or MySQL’s version of one), this column is used to test for true or false.

If the value in this column is a 0 (zero) then it is considered false. Whereas anything else would be deemed true.

I chose a boolean data type for this column specifically since it is partially used. Every pipe does not have a degree value, hence not a bend.
To query and find the degree_amount, in addition to, all the columns from the pipe table, we can use an INNER JOIN

mysql> SELECT p.pipe_name, p.joint_num, p.heat, p.pipe_length, h.degree_amount
    -> FROM pipe AS p
    -> INNER JOIN degree_value AS h
    -> ON
    -> p.pipe_id = h.pipe_id
    -> WHERE p.has_degree <> 0;
+-------------+-----------+-------+-------------+---------------+
| pipe_name   | joint_num | heat  | pipe_length | degree_amount |
+-------------+-----------+-------+-------------+---------------+
| Joint-28    | 39393A    | 9111  |       17.40 |         12.50 |
| Joint-2228  | 34493U    | 9100  |       11.80 |         22.50 |
| Joint-7834  | 17QTT     | 179   |       44.40 |         10.00 |
| Joint-171   | 34493U    | 17122 |        9.45 |          4.00 |
| Joint-1228A | 34493U    | 8575B |       15.22 |         10.25 |
| Joint-2128  | 34493C    | 91    |       13.55 |          5.75 |
| Joint-222B  | 34493U    | 9100B |       21.63 |         11.75 |
+-------------+-----------+-------+-------------+---------------+
7 rows in set (0.05 sec)

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.

The key here is the WHERE clause filter. Columns with a has_degree value, not equal to zero (basically any value other than zero) are considered true according to the boolean condition. Meaning that row needs to be joined on the matching row from the degree_value table. Therefore, retrieving the degree_amount.

That’s great and wonderful but, retyping this query over and over, perhaps multiple times per day is not productive.
And unnecessary.
This presents a prime use case for a VIEW.

This is more like it:

mysql> CREATE VIEW find_degree AS (
    -> SELECT p.pipe_name, p.joint_num, p.heat, p.pipe_length, h.degree_amount
    -> FROM pipe AS p
    -> INNER JOIN degree_value AS h
    -> ON
    -> p.pipe_id = h.pipe_id
    -> WHERE p.has_degree <> 0);
Query OK, 0 rows affected (0.05 sec)

Now we can SELECT from the find_degree VIEW, same as a table.
See for yourself.

mysql> SELECT * FROM find_degree;
+-------------+-----------+-------+-------------+---------------+
| pipe_name   | joint_num | heat  | pipe_length | degree_amount |
+-------------+-----------+-------+-------------+---------------+
| Joint-28    | 39393A    | 9111  |       17.40 |         12.50 |
| Joint-2228  | 34493U    | 9100  |       11.80 |         22.50 |
| Joint-7834  | 17QTT     | 179   |       44.40 |         10.00 |
| Joint-171   | 34493U    | 17122 |        9.45 |          4.00 |
| Joint-1228A | 34493U    | 8575B |       15.22 |         10.25 |
| Joint-2128  | 34493C    | 91    |       13.55 |          5.75 |
| Joint-222B  | 34493U    | 9100B |       21.63 |         11.75 |
+-------------+-----------+-------+-------------+---------------+
7 rows in set (0.03 sec)

Not only can you SELECT all columns, you can choose any number of them you need.

mysql> SELECT pipe_name, pipe_length, degree_amount
    -> FROM find_degree;
+-------------+-------------+---------------+
| pipe_name   | pipe_length | degree_amount |
+-------------+-------------+---------------+
| Joint-28    |       17.40 |         12.50 |
| Joint-2228  |       11.80 |         22.50 |
| Joint-7834  |       44.40 |         10.00 |
| Joint-171   |        9.45 |          4.00 |
| Joint-1228A |       15.22 |         10.25 |
| Joint-2128  |       13.55 |          5.75 |
| Joint-222B  |       21.63 |         11.75 |
+-------------+-------------+---------------+
7 rows in set (0.00 sec)

As a matter of fact, If you find that result set useful, Views can be created from other Views.
Let’s do that now.

mysql> CREATE VIEW closer_look AS (
    -> SELECT pipe_name, pipe_length, degree_amount
    -> FROM find_degree);
Query OK, 0 rows affected (0.05 sec)

Selecting from the closer_look VIEW:

mysql> SELECT * FROM closer_look;
+-------------+-------------+---------------+
| pipe_name   | pipe_length | degree_amount |
+-------------+-------------+---------------+
| Joint-28    |       17.40 |         12.50 |
| Joint-2228  |       11.80 |         22.50 |
| Joint-7834  |       44.40 |         10.00 |
| Joint-171   |        9.45 |          4.00 |
| Joint-1228A |       15.22 |         10.25 |
| Joint-2128  |       13.55 |          5.75 |
| Joint-222B  |       21.63 |         11.75 |
+-------------+-------------+---------------+
7 rows in set (0.00 sec)

Will A View Have Current Data?

What if you INSERT data into the tables that comprise the VIEW‘s definition?
Will that data be available whenever selecting against the VIEW?
Let’s find out with a quick example.

mysql> INSERT INTO pipe(pipe_name, joint_num, heat, pipe_length, has_degree) VALUES ('Joint-1619', '93935', 'T494', 9.55, 1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO degree_value(pipe_id, degree_amount)
VALUES(LAST_INSERT_ID(),6.25);
Query OK, 1 row affected (0.03 sec)

Let’s query the find_degree VIEW and determine if it was updated with the previously inserted record:

mysql> SELECT * FROM find_degree;
+-------------+-----------+-------+-------------+---------------+
| pipe_name   | joint_num | heat  | pipe_length | degree_amount |
+-------------+-----------+-------+-------------+---------------+
| Joint-28    | 39393A    | 9111  |       17.40 |         12.50 |
| Joint-2228  | 34493U    | 9100  |      -11.50 |         22.50 |
| Joint-7834  | 17QTT     | 179   |       44.40 |         10.00 |
| Joint-171   | 34493U    | 17122 |        9.45 |          4.00 |
| Joint-1228A | 34493U    | 8575B |       15.22 |         10.25 |
| Joint-2128  | 34493C    | 91    |       13.55 |          5.75 |
| Joint-222B  | 34493U    | 9100B |        0.00 |         11.75 |
| Joint-1619  | 93935     | T494  |        9.55 |          6.25 |
+-------------+-----------+-------+-------------+---------------+
8 rows in set (0.01 sec)

The new record with pipe_name ‘Joint-1619’ is present, proving Views display current data.

Benefits Of A View

These are a few benefits of a VIEW…

  • In a sense, views provide a named, reusable query.
  • Views can abstract away sensitive data from users with limited credentials and minimal privileges.
  • Views can provide an interface to end users without exposing any details of the back-end.

Limitations And Drawbacks

While studying Views, I found the below conclusions from the official documentation relevant and want to share them with you.

  • A VIEW, and I quote the MySQL View documentation, is what is referred to as “frozen” at the time of creation. Signifying, any changes to the source table SELECT statement that provides the definition, will not affect the VIEW. For example, if a column is dropped, selecting that column from the VIEW would result in an error. Likewise, if a column is added to the source table, it would not be available in the VIEW.
  • A Views definition has the following restrictions in regards to the SELECT statement:
    • Cannot refer to system or user-defined variables.
    • Cannot refer to prepared statement parameters
    • Cannot refer to parameters or local variables within a stored program.
    • Cannot refer to a TEMPORARY table.
    • Views cannot be associated with a TRIGGER.

    How’s Your View?

    I hope through the examples provided in this blog post, you can implement your own VIEW when you need it. If you find yourself typing the same query over and over again, consider encapsulating it in a VIEW.
    Be sure to explore the official MySQL 5.7 Online Manual for an in-depth overview of all topics covered in this post.

    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.
    Have I mentioned how much I love a cup of coffee?!?!

    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.