DROP VIEW x; CREATE VIEW x … to redefine a view | CREATE OR REPLACE VIEW x AS … — atomic, no window where the view is missing. (ALTER VIEW x AS … also works when it already exists) |
CREATE OR REPLACE VIEW IF NOT EXISTS …, assuming the two combine | Hard error (ER_WRONG_USAGE). OR REPLACE and IF NOT EXISTS are mutually exclusive — pick one |
Omits SQL SECURITY, expecting the caller's privileges to apply | The default is SQL SECURITY DEFINER — the view executes with the definer's privileges, not the invoker's. For caller-privilege semantics you must write SQL SECURITY INVOKER explicitly |
Treats any view as updatable / INSERT-able | A view is not updatable if its SELECT uses an aggregate, DISTINCT, GROUP BY, HAVING, UNION, a subquery in the select list, an outer join, references no base table, or refers to one base-table column more than once. ALGORITHM = TEMPTABLE is never updatable |
Sets ALGORITHM = TEMPTABLE on a view it then writes through | TEMPTABLE views are never updatable; only MERGE (or an UNDEFINED view the optimizer can merge) can be written through |
INSERTs through a view whose columns are expressions (col + 1, LOWER(col), literals) | Insertable views need every column to be a plain base-table column, must include all NOT NULL/no-default base columns, and may not repeat a base column. Otherwise ERROR 1348: Column is not updatable |
Bare WITH CHECK OPTION on a view built on another view, expecting only the local predicate to be checked | Bare WITH CHECK OPTION means CASCADED (checks this view and all underlying views). Use WITH LOCAL CHECK OPTION to check only this view's predicate |
Puts ORDER BY inside the view to guarantee output order | The view's ORDER BY is ignored whenever the outer query has its own ORDER BY. Don't rely on in-view ordering — sort in the query that reads the view |
Expects SELECT * in a view to pick up columns added to the base table later | The definition is frozen at creation: SELECT * is expanded to the columns that existed then. New base-table columns don't appear until you recreate the view |
Tries to index a view, or CREATE TEMPORARY VIEW, or put a subquery in the view's FROM | None are allowed. Index the underlying base table; there is no temporary view; a view's SELECT can't have a derived table in FROM |
| Leaves expression columns unnamed | Every view column needs a unique name — give expressions an alias, or supply an explicit (column_list) whose length matches the select list |
