MySQL Tables and Views

A base table persists rows. A view persists a query definition and presents its result as a table-like object. SHOW FULL TABLES distinguishes them, and INFORMATION_SCHEMA.TABLES.TABLE_TYPE reports BASE TABLE or VIEW.

InnoDB Row Formats #

InnoDB supports REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED row formats. DYNAMIC is the current default. For variable-length columns, InnoDB can store long values on overflow pages and retain a reference in the clustered record. The precise inline and overflow behavior depends on the row format, page size, column type, and value length; schema design should avoid assuming that a TEXT or BLOB value occupies a fixed in-row size.

Inspect a table’s effective configuration:

SHOW TABLE STATUS LIKE 'orders';
SHOW CREATE TABLE orders;

Views #

Create a view with a stable, explicit column list:

CREATE VIEW active_customer_orders AS
SELECT o.id, o.customer_id, o.created_at, o.total
FROM orders AS o
WHERE o.status = 'active';

A view does not store a separate table by default. The optimizer can merge a mergeable view into an outer query, or it can materialize a temporary table when the definition requires it, such as for aggregation, DISTINCT, or some derived-table operations. ALGORITHM = MERGE and ALGORITHM = TEMPTABLE express a preference, but a view definition must still satisfy the selected algorithm’s restrictions.

Some views are updatable when each affected row maps unambiguously to one base table. Views containing aggregation, GROUP BY, DISTINCT, window functions, set operations, or non-updatable joins are not updatable. Use the base table for writes when the view’s update behavior is not part of the required interface.

The DEFINER account and SQL SECURITY setting determine whose privileges apply during view execution. Review both when a view exposes a restricted projection to an application account.

WITH CHECK OPTION prevents writes through an updatable view from creating rows that the view cannot see. CASCADED checks the view and underlying view conditions, while LOCAL checks only the current view’s condition:

CREATE SQL SECURITY INVOKER VIEW active_customer_orders AS
SELECT id, customer_id, created_at, total, status
FROM orders
WHERE status = 'active'
WITH CASCADED CHECK OPTION;

MySQL stores a view definition in the data dictionary and resolves referenced objects when it executes the view, but a schema change can still make the view invalid. Use SHOW CREATE VIEW and CHECK TABLE after changing dependent tables, and order migrations so callers never observe incompatible definitions.

MySQL has no native, automatically maintained materialized-view object. Use a base table populated by a scheduled transaction, change-data pipeline, or application job when precomputation is required. Define refresh atomicity and freshness explicitly; swapping or updating that table has different consistency and metadata-lock behavior from querying a normal view.

References #

• InnoDB Row Formats
• View Processing Algorithms
• Updatable Views and Check Options