Skip to content

What is a MySQL view and how do you use one?

Introduction

If you’re building an application with MySQL as the database, and performance is at the top of your list, you should consider using MySQL views. From the outside, a MySQL view looks just like a table. It is sometimes also referred to as a virtual table for this same reason. They are stored as a set of instructions that return a result set. Views in MySQL are static in nature and cannot be modified easily once created, unlike tables.

What is a MySQL view?

Imagine having a set of interrelated tables. To begin retrieving useful results, you would want to have a starting point with enough information from these tables. This might require multiple joins and aggregate functions. What if all of this was packaged together so that you can use that as a table in itself? This is where a view comes into play.

A view acts as a virtual table that you can query and use like a normal table. It also shows up in the list of tables when you run the command SHOW TABLES in MySQL. The table so created could use data from multiple tables consisting of joins and aggregate functions. See the section below to learn how to create MySQL views and why they’re useful.

CREATE VIEW statement

For this example, we’re going to use the Sakila database from MySQL. This is the demo data that gets added to your MySQL instance at the time of installation. We are using MySQL Workbench to run all our examples. See Sakila installation for more information if you don’t have it already. The tables that we’re going to use are:

  • FILM
  • FILM_CATEGORY
  • CATEGORY
  • LANGUAGE

The FILM table contains information about movies. But to get the full information about a particular movie, we must join it with other tables. The FILM_CATEGORY table contains the IDs of the film and the IDs of the categories they’re related to. The CATEGORY table lists these categories based on their IDs. The LANGUAGE table lists the languages with their unique IDs. To get a list of movie names with their language and category, use the following SELECT statement:

SELECT FILM.FILM_ID, FILM.TITLE, LANGUAGE.NAME AS LANGUAGE, CATEGORY.NAME AS CATEGORY

FROM FILM

INNER JOIN LANGUAGE on FILM.LANGUAGE_ID = LANGUAGE.LANGUAGE_ID

INNER JOIN FILM_CATEGORY ON FILM.FILM_ID = FILM_CATEGORY.FILM_ID

INNER JOIN CATEGORY ON FILM_CATEGORY.CATEGORY_ID = CATEGORY.CATEGORY_ID

ORDER BY FILM_ID ASC;

Executing this query gives you the following results:

There are 1000 films in the table and the above screenshot shows the first seven rows only. But notice how much SQL we had to write to get just a small bit of information from four tables combined. To replace this with a view, we need to use the CREATE VIEW statement.

How to create a view in MySQL

You must use the CREATE VIEW statement to create a MySQL view. The syntax is as shown below:

CREATE VIEW VIEW_NAME AS SQL_STATEMENTS;

Replace VIEW_NAME with an arbitrary name and SQL_STATEMENTS with the required SQL to return the data. For our previous example, the above syntax to create a new view would look like this:

CREATE VIEW FILM_INFO AS

SELECT FILM.FILM_ID, FILM.TITLE, LANGUAGE.NAME AS LANGUAGE, CATEGORY.NAME AS CATEGORY

FROM FILM

INNER JOIN LANGUAGE on FILM.LANGUAGE_ID = LANGUAGE.LANGUAGE_ID

INNER JOIN FILM_CATEGORY ON FILM.FILM_ID = FILM_CATEGORY.FILM_ID

INNER JOIN CATEGORY ON FILM_CATEGORY.CATEGORY_ID = CATEGORY.CATEGORY_ID

ORDER BY FILM_ID ASC;

As mentioned before, you can use the SHOW TABLES command to view FILM_INFO added as an entry. This, however, is not a real table, and is only a virtual table. To retrieve the same data as before, you can simply use the following query after creating the MySQL view:

SELECT * FROM FILM_INFO;

This returns the exact same data as before, and you can use this information to join with other tables or use it as a starting point for more complex queries.

How to modify and drop a view in MySQL

ALTER VIEW statement

There are two ways to update views. You can use the ALTER VIEW statement to edit the query of a MySQL view. This is useful to update your view when there is a change in business logic, the names of the underlying tables and columns, or if you’ve just made a mistake. You cannot create another view with the same name, or a new table with the same name. Instead, you must update the existing view. For instance, let’s say we rename the NAME column on the CATEGORY table to just CATEGORY.

ALTER TABLE CATEGORY RENAME COLUMN NAME TO CATEGORY;

If we execute the previous SELECT statement, it gives an error now because it references an invalid column. To fix this, we can use the following command:

ALTER VIEW FILM_INFO AS

SELECT FILM.FILM_ID, FILM.TITLE, LANGUAGE.NAME AS LANGUAGE, CATEGORY.CATEGORY

FROM FILM

INNER JOIN LANGUAGE on FILM.LANGUAGE_ID = LANGUAGE.LANGUAGE_ID

INNER JOIN FILM_CATEGORY ON FILM.FILM_ID = FILM_CATEGORY.FILM_ID

INNER JOIN CATEGORY ON FILM_CATEGORY.CATEGORY_ID = CATEGORY.CATEGORY_ID

ORDER BY FILM_ID ASC;

You can also use the CREATE OR REPLACE VIEW statement to do the same thing. This creates the modified view if it doesn’t exist or replaces it if it does.

CREATE OR REPLACE VIEW FILM_INFO AS

SELECT FILM.FILM_ID, FILM.TITLE, LANGUAGE.NAME AS LANGUAGE, CATEGORY.CATEGORY

FROM FILM

INNER JOIN LANGUAGE on FILM.LANGUAGE_ID = LANGUAGE.LANGUAGE_ID

INNER JOIN FILM_CATEGORY ON FILM.FILM_ID = FILM_CATEGORY.FILM_ID

INNER JOIN CATEGORY ON FILM_CATEGORY.CATEGORY_ID = CATEGORY.CATEGORY_ID

ORDER BY FILM_ID ASC;

Dropping views in MySQL

To delete a view, you can use the DROP VIEW statement. This view accepts a comma-separated list of view names, and you can provide multiple MySQL views to delete them all. You can also give the IF EXISTS clause to delete only the views that exist and ignore the others.

To drop the MySQL view from our example, you can use the following command:

DROP VIEW FILM_INFO;

Limitations and considerations when working with views

It must be noted that views are not actual tables, even though you can perform a lot of the operations that you can do with regular MySQL tables. The main limitation of a MySQL view is that you cannot delete or update them if they use certain SQL statements. See view updatability for more information. If your view satisfies any of the points mentioned in that list (certain joins, subqueries, etc.), you cannot update or delete from your view.

Performance implications

Views are extremely useful to simplify queries from multiple tables. But there may be performance implications if you’re not careful. When creating a view, there is an optional clause that determines the algorithm used to merge the queries that you execute with the view.

The options available are merge, temptable, and undefined:

  • Merge: When executing a query on the view, this algorithm merges that query with the query of your view. This is a good algorithm, but cannot be used when there are subqueries, aggregate functions, etc.
  • Temptable: This algorithm creates a temporary table to store the results of your view query and then runs the new query on top of the temp table. This algorithm can cause a degradation in performance when it comes to large datasets.
  • Undefined: When no algorithm is specified, MySQL chooses between merge or temptable, depending on what is suitable.

Another issue is that if you’re already using a MySQL view and are facing performance issues, it might be difficult to understand exactly what is causing the degradation. In real-world scenarios, the underlying query of a view might be so huge that it would be difficult to pinpoint the root cause. See how PlanetScale Boost instantly increases the performance of your queries.

View dependencies

A view is a single-named object that possibly contains a large query beneath the hood. There may be many different tables from which information is gathered, so whenever there’s an update to one of those tables (change in a column name or the table itself is no longer available), the view breaks. You must also understand that when you update those tables, you get no notification regarding the dependency on the view.

Security considerations

It is important to consider security aspects to protect your data. Some key considerations are:

  • Access control: You should grant privileges to only authorized users and restrict access to sensitive data.
GRANT SELECT ON view_name TO username;

REVOKE SELECT ON view_name FROM username;
  • Sensitive data exposure: Be mindful of the columns and rows exposed by the view, as they might leak critical information. Limit the view’s scope to prevent unauthorized exposure of sensitive data.
- - used columns are limited

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name;
  • Data modification restrictions: You should avoid modifying data through views. This can result in bypassing data modification controls.
  • Parameterized views: Views that accept user input should be avoided, as improper handling can lead to security vulnerabilities such as SQL Injection (SQLi).

Conclusion

That’s the end of this MySQL tutorial on views. Knowing when to use a view is an important weapon in your arsenal. We hope you’ve understood what a MySQL view is, how to create them, their limitations, and their importance in the world of databases.