DEV Community

loading...
Cover image for TIGEW: MySQL Views

TIGEW: MySQL Views

saramccombs profile image Sara McCombs (she/her) Updated on ・2 min read
Photo by Paul Skorupskas on Unsplash

I'm starting off a new series based around all the things I google each week (TIGEW). Not only as a way to cement and improve my understanding on a topic, but to also show early-career individuals that, no matter the amount of experience, we're forever googling things.

The intent here is to provide a high-level who/what/why/when/where/how of the topic paired with any links that were super helpful. Let's get started!

What is a MySQL view?

A view is a virtual table that does NOT store any data of its own but WILL display data that is stored in other tables.

Views are nothing but saved MySQL queries that can be interacted with like native tables.

CREATE VIEW view_name AS SELECT statement
Enter fullscreen mode Exit fullscreen mode

The CREATE view_name tells MySQL to create a view object in the database named "view_name".

The AS SELECT statement are the SQL statements used to populate the view.

While its possible to also use a view with INSERT, UPDATE, and DELETE queries, its strongly NOT recommended as these queries can have unpredictable results and will lack any table validations that might be built into the view referenced tables.

Why use a view?

There are couple of reasons you might want to use a view, here are just a few.

  1. Views can help in data security by only exposing certain information to its users. Views help to mask/hide sensitive data in this manner.

  2. Views can increase re-usability. Complex queries involving joins won't need to be repeatedly created, all the complexity can be converted into a single line query using a view. This condensed code is easier to integrate into an existing code base as well as help prevent the nightmare typo.

  3. Safety net for legacy code in database refactors. The legacy code will see the original schema via the view.

Resources

  1. MySQL Views - MySQLTUTORIAL

Discussion (3)

pic
Editor guide
Collapse
darkain profile image
Vincent Milum Jr

Something to be aware of though, VIEWs are super wonky on some database engines when it comes to key selection by the query planner. So larger, complex tables may end up doing extremely slow, full table scans, just because of the VIEW. Using the exact same query manually without a VIEW generally yields better performance because of this.

Collapse
sethburtonhall profile image
Seth Hall

More of these please!

Collapse
kirkcodes profile image
Kirk Shillingford

I love this series idea. Also great intro to views!