Views
A view is a stored SELECT that behaves like a table: it holds no data, caches nothing, and re-runs every time you query it.
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]A view is a SELECT statement stored under a name. Query the view and MySQL runs that SELECT again; the view owns no rows, no storage and no saved result. Views exist so that one definition of "a billable order" or "an active user" lives in the schema instead of being copy-pasted into a dozen application queries.
Creating and replacing
These two do the same job. The second is the shorthand, and it leaves no moment where the view does not exist:
DROP VIEW IF EXISTS billable_orders;
CREATE VIEW billable_orders AS SELECT ...;
CREATE OR REPLACE VIEW billable_orders AS SELECT ...;How MySQL runs it
With ALGORITHM = MERGE the view body is folded into the outer query, so your outer WHERE reaches the base tables and their indexes. With TEMPTABLE the view is materialised into an internal temporary table first and the outer WHERE can only filter that result. UNDEFINED is the default and lets MySQL choose; it prefers MERGE where the definition allows. Aggregates, DISTINCT, UNION and window functions prevent merging.
Updatable views
A view whose columns are plain column references, mapping one-to-one onto rows of a base table, accepts INSERT, UPDATE and DELETE. Aggregate or window functions, DISTINCT, GROUP BY, HAVING, UNION or ALGORITHM = TEMPTABLE make it read-only. WITH CHECK OPTION rejects a write whose resulting row would fall outside the view.
What a view is not
It is not a cache and not a performance feature. Every query against it pays the full cost of the underlying SELECT.
Example
-- One definition of "an order that counts as revenue", reused everywhere.
CREATE OR REPLACE VIEW billable_orders AS
SELECT
o.id,
o.customer_id,
o.placed_at,
o.total_cents
FROM orders AS o
WHERE o.status = 'paid'
AND o.total_cents > 0;
-- Queried exactly like a table. The definition has no aggregate or UNION,
-- so MySQL merges it: the WHERE below reaches the base table and can still
-- use an index on orders(customer_id, placed_at).
SELECT id, placed_at, total_cents
FROM billable_orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 10;
-- Confirm the merge rather than assuming it.
EXPLAIN SELECT id FROM billable_orders WHERE customer_id = 42;When to use it
- Give the application one agreed definition of "active subscription" instead of six slightly different WHERE clauses.
- Expose a per-tenant or per-region slice of a table to reporting code that must not see other rows.
- Keep a legacy column name working after a rename by defining a view with the old shape.
- Wrap a five-table join so analysts can query it without re-deriving the join conditions.
More examples
An updatable view with WITH CHECK OPTION
Every column is a simple reference to one base table, so writes pass through. WITH CHECK OPTION is what stops a write from succeeding and then making the row invisible to the same view. Reach for this when you hand a tenant or region slice to code that writes as well as reads.
CREATE OR REPLACE VIEW uk_customers AS
SELECT id, name, email, country
FROM customers
WHERE country = 'GB'
WITH CASCADED CHECK OPTION; -- CASCADED is what you get if you omit the keyword
-- Allowed: the row stays inside the view.
UPDATE uk_customers SET name = 'A. Smith Ltd' WHERE id = 7;
-- Rejected: CHECK OPTION failed 'shop.uk_customers'
UPDATE uk_customers SET country = 'FR' WHERE id = 7;
-- Allowed: every column is a plain column reference, so the view is
-- insertable as well as updatable, and 'GB' satisfies the check.
INSERT INTO uk_customers (id, name, email, country)
VALUES (900, 'New Ltd', '[email protected]', 'GB');A view that is deliberately read-only
GROUP BY and the aggregates make the view non-updatable, which is often the point: it is a reporting surface nobody can write through. It also means the aggregate is recomputed for every reader, which is the moment to consider a summary table instead.
CREATE OR REPLACE VIEW customer_totals AS
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_cents) AS spend_cents,
MAX(placed_at) AS last_order_at
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;
-- Error: The target table customer_totals of the UPDATE is not updatable
UPDATE customer_totals SET spend_cents = 0 WHERE customer_id = 42;
-- Reads are fine, and the aggregate runs in full on every single query.
SELECT * FROM customer_totals WHERE spend_cents > 100000;Inspecting and checking views
IS_UPDATABLE in information_schema.VIEWS answers the updatability question authoritatively for a given definition. CHECK TABLE is the one tool that finds views invalidated by a DROP or ALTER before a user does, so it belongs in your post-migration script.
SHOW CREATE VIEW billable_orders\G
-- IS_UPDATABLE tells you whether writes will pass through, without guessing.
SELECT TABLE_NAME, IS_UPDATABLE, CHECK_OPTION, SECURITY_TYPE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();
-- Run after any migration that drops or alters a base table: a broken view
-- stays silent until someone queries it.
CHECK TABLE billable_orders, uk_customers, customer_totals;
Discussion