2006
MySQLERRORNotableSchemaHIGH confidence
View column list does not match SELECT
Production Risk
Low — view creation is rejected.
What this means
A CREATE VIEW statement specifies an explicit column list, but the number of column names does not match the number of columns returned by the SELECT statement.
Why it happens
- 1Column list in CREATE VIEW (col1, col2, ...) has a different count than the SELECT columns.
- 2Adding or removing columns from the SELECT without updating the view column list.
How to reproduce
trigger — this will error
trigger — this will error
CREATE VIEW v (a, b) AS SELECT id, name, email FROM users;
expected output
ERROR 2006 (HY000): View's SELECT and view's field list have different column counts.
Fix 1
Match the column list count to the SELECT
Match the column list count to the SELECT
CREATE VIEW v (id, name, email) AS SELECT id, name, email FROM users;
Why this works
The column alias list must have exactly as many names as the SELECT has columns.
Fix 2
Remove the explicit column list to auto-derive column names
Remove the explicit column list to auto-derive column names
CREATE VIEW v AS SELECT id, name, email FROM users;
Why this works
Without an explicit column list, MySQL uses the SELECT expression names.
Sources
Official documentation ↗
MySQL 8.0 — 2006 ER_VIEW_WRONG_LIST
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev