Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 75 ยท Tables, Constraints and Schema Design

Views

Learn MySQL views: CREATE VIEW, MERGE and TEMPTABLE algorithms, updatable views, WITH CHECK OPTION, SQL SECURITY DEFINER vs INVOKER and the limits of views.

A view is a saved SELECT statement with a name. You query it like a table, but MySQL stores no rows for it: every time you read the view, the server runs the saved query. Views are a simple way to hide a complicated join, give application code a stable shape while the tables underneath change, and expose only some columns or rows to certain users.

This lesson covers CREATE VIEW and its options, how MySQL executes a view, when you can write through a view, WITH CHECK OPTION, the security clause, and the limits that make views a poor fit for some jobs. If you need the basics of SELECT and joins first, see Joins in MySQL and Keys and Constraints.

What a view is

A saved query Only the SELECT is stored, never the rows.
A window You read through it as if it were a table.
Always current Each read runs the query on today's data.

A view costs nothing to store and nothing to keep in sync.

1 / 2

Creating a view

The basic form is CREATE VIEW name AS select_statement. Add OR REPLACE to redefine an existing view in one step, and an optional column list when you want different names from the ones the SELECT produces:

SQL
CREATE OR REPLACE VIEW book_catalog AS
SELECT b.id, b.title, a.name AS author, b.price
FROM books AS b
JOIN authors AS a ON a.id = b.author_id;

SELECT * FROM book_catalog WHERE price < 12;

The view lives in a database, can be listed with SHOW FULL TABLES WHERE Table_type = 'VIEW', shows its definition with SHOW CREATE VIEW book_catalog, and is removed with DROP VIEW. ALTER VIEW changes the definition and keeps the privileges you granted on it.

SQLite has CREATE VIEW too, so the core idea runs live. Below, the view hides a join and a computed column. Notice that the second statement changes the base table and the view immediately reflects it:

SQL A view is recomputed on every read ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The second result has one more row than the first, because The Salt Road now costs less than 12. Nothing was refreshed or rebuilt. Views are not caches, and MySQL has no materialized views: if you need stored results, you build a summary table yourself and refresh it (Project: Analytics and Reporting Queries shows the pattern).

A few rules apply to the SELECT inside a view in MySQL:

  • It may not refer to a user variable, a session variable, or a prepared-statement parameter.
  • It may not refer to a temporary table, and it may not be created as a TEMPORARY view (there is no such thing).
  • Column names must be unique. Alias duplicates, such as two columns called id, or MySQL rejects the view.

How MySQL runs a view: MERGE and TEMPTABLE

A view has an ALGORITHM that decides how the server uses it:

Algorithm What happens
MERGE The view text is merged into the outer query, as if you had written the SELECT inline. The optimizer sees one query and can use indexes on the base tables.
TEMPTABLE The view is run first and its rows are stored in a temporary table. The outer query then reads that table. No base-table indexes help the outer filter.
UNDEFINED (default) MySQL picks MERGE when it can and falls back to TEMPTABLE.
SQL
CREATE ALGORITHM = MERGE VIEW cheap_books AS
SELECT id, title, price FROM books WHERE price < 12;

MERGE is almost always what you want, so leave the algorithm alone unless you have a reason. A view cannot be merged when its query uses an aggregate function (SUM, COUNT...), DISTINCT, GROUP BY, HAVING, LIMIT, UNION, a window function, or certain subqueries. For those views MySQL materializes the result into a temporary table on every use. That is fine for small results, but a view that groups ten million rows is expensive each time somebody filters it for one customer.

SQL
-- Cannot be merged: the outer WHERE cannot be pushed below the aggregate
CREATE VIEW sales_per_customer AS
SELECT customer_id, SUM(quantity) AS items
FROM orders
GROUP BY customer_id;

Newer MySQL versions can push a condition on a grouping column into a materialized derived table, so a query such as WHERE customer_id = 7 may still be fast. Check with EXPLAIN instead of assuming (EXPLAIN and EXPLAIN ANALYZE).

Updatable views

Some views accept INSERT, UPDATE and DELETE, and MySQL translates them into changes to the underlying table. A view is updatable when each of its rows maps to exactly one row of one base table. Anything that breaks that one-to-one link makes it read-only: aggregates, DISTINCT, GROUP BY, HAVING, UNION, window functions, LIMIT, or a subquery in the select list that refers to the table in FROM.

Views over a join can be partly updatable: an UPDATE can change columns that belong to a single base table, but a DELETE is not allowed, and an INSERT works only if every column it names belongs to one table.

SQL
CREATE VIEW cheap_books AS
SELECT id, title, price, stock FROM books WHERE price < 12;

UPDATE cheap_books SET stock = stock + 10 WHERE id = 3;   -- works: changes books
DELETE FROM cheap_books WHERE stock = 0;                   -- works: deletes from books

An INSERT into a view must supply every base-table column that has no default and is not in the view, otherwise it fails. Columns that the view computes (price * 1.2 AS gross) cannot be written.

SQLite views are always read-only unless you attach INSTEAD OF triggers, so you cannot try MySQL's updatable behavior live. What you can check is that writing to a view fails:

SQL SQLite views are read-only (MySQL's simple ones are not) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The last statement is meant to fail: SQLite reports that it cannot modify cheap_books because it is a view. The lines before it run. On MySQL it would succeed.

WITH CHECK OPTION

An updatable view with a WHERE clause has a gap: you can insert or update a row through the view so that the row no longer matches the filter, and it vanishes from the view. WITH CHECK OPTION closes that gap by rejecting any write that would produce a row the view cannot show:

SQL
CREATE VIEW cheap_books AS
SELECT id, title, price, stock
FROM books
WHERE price < 12
WITH CHECK OPTION;

UPDATE cheap_books SET price = 15 WHERE id = 3;
-- ERROR 1369 (HY000): CHECK OPTION failed 'shop.cheap_books'

There are two modes. WITH CASCADED CHECK OPTION (the default when you write WITH CHECK OPTION) checks the view's own condition and the conditions of every view it is built on. WITH LOCAL CHECK OPTION checks only this view's condition, and the conditions of underlying views only if those views have their own check option. Use CASCADED unless you are sure you need the looser rule.

A plain CHECK constraint on the table is often a better tool for rules that must always hold (Keys and Constraints). WITH CHECK OPTION is for "this role may only create rows of this shape".

Views and security

A view can restrict what a user sees: give a support team SELECT on a view that omits a salary column, but no access to the table. The clause SQL SECURITY decides whose privileges are checked when the view is read:

SQL
CREATE
  ALGORITHM = MERGE
  DEFINER = 'reports'@'localhost'
  SQL SECURITY DEFINER
VIEW public_staff AS
SELECT id, name, department FROM employees;
Setting Checked against
SQL SECURITY DEFINER (default) The privileges of the account named in DEFINER. Callers need only SELECT on the view.
SQL SECURITY INVOKER The privileges of whoever runs the query. They also need rights on the base tables.

DEFINER is the right choice for the "show a safe subset" pattern, because users never need table access. Two cautions: the definer must keep its privileges (if that account is dropped, the view stops working with an error about a missing definer), and a definer with broad rights makes the view a privilege-escalation path if the SELECT is careless. Use a dedicated low-privilege account as the definer, and INVOKER when you want ordinary permission checks to apply. Users, grants and roles are covered in Users, Privileges and Roles.

Limits and good habits

  • Views do not speed anything up. They hide complexity, not cost. A view over five joins runs five joins.
  • No indexes on views. Index the base tables. A materialized (TEMPTABLE) view has no usable index for the outer query.
  • Stacked views get slow and hard to read. Avoid views of views of views; the optimizer must expand all the layers.
  • SELECT * is frozen at creation. The column list is expanded when the view is created. Adding a column to the base table later does not add it to the view. Name your columns explicitly.
  • Dependencies are not tracked for you. Dropping or renaming a base table leaves the view in place but broken until you fix one of them. CHECK TABLE view_name reports such problems.
  • Name views clearly. A prefix or suffix such as v_ or _view makes it obvious that you are not looking at a table.

Good uses are reporting queries, a compatibility layer after a table is renamed, and access control. Poor uses are "performance helpers" and anything that hides expensive aggregates behind an innocent-looking name.

What ran live here

CREATE VIEW and querying a view ran in SQLite, which also showed that a view reflects base-table changes at once and that SQLite views cannot be written to. The ALGORITHM, DEFINER, SQL SECURITY, updatable-view behavior and WITH CHECK OPTION are MySQL features and are shown as read-only code.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does MySQL store when you create a view?

    A view is just a stored query. The rows are read from the base tables each time the view is used, so MySQL has no materialized views.
  2. 2. Which view definition is NOT updatable in MySQL?

    A grouped view returns rows that do not map to single base-table rows, so writes through it are rejected. The others are simple one-table views.
  3. 3. What does WITH CHECK OPTION do?

    Without it, you can update a row through the view so that it no longer matches the WHERE clause. With it, MySQL raises error 1369 for such a write.
  4. 4. Which algorithm lets the optimizer use indexes on the base tables as if you had written the query inline?

    MERGE folds the view's text into the outer query. TEMPTABLE builds a temporary table first, and MATERIALIZED and INVOKER are not view algorithms.

Summary

  • A view is a named, stored SELECT. MySQL keeps no rows for it and reruns the query on every read.
  • MERGE folds the view into the outer query; TEMPTABLE materializes it first. Aggregates, DISTINCT, LIMIT and UNION prevent merging.
  • A view is updatable when each row maps to one base-table row; WITH CHECK OPTION stops writes that would leave the view.
  • SQL SECURITY DEFINER uses the definer's rights, INVOKER the caller's. Use a low-privilege definer.
  • Views hide complexity but add no speed and are not indexed. List columns explicitly instead of SELECT *.

Next: SELECT: MySQL Specifics.