Mysql: create view as select *

Question

When you create a view with

CREATE VIEW view1 AS SELECT * FROM table1

mysql changes this internally to

CREATE VIEW view1 AS SELECT f1, f2, f3 FROM table1

If you add a new column f4 to table1, view1 still has only the first 3 columns. You have to recreate your view. This is annoying, if you have a lot of views.

Is there a way to make mysql to save SELECT * in the view declaration so it always selects all the fields no matter if the table has changed?

Answer 1

MySql has a right behaviour.

When you create a view as SELECT * FROM table1, MySql changes your code with a full list of fields.

When you change your table structure, the view must be changed by user.

If exists this automatic behaviour you can create an issue of performance when you use a view.

Suppose you add 50 fields, and you use your view in a form, you can change the data load from 3 to 53 fields. No good.

So this behaviour is correct.

Answer 2

A MySql View does not use for return a complete definition of a table it's basic use to return a part of the tables with a specification or marge data.
Views would not be of much use without it.The principle also applies if you select from a view that selects from the table if the view selects from the table in a subquery and the view is evaluated using the merge algorithm.View processing is not optimized. You can see this blog to know what is right with a view to MySql.View Restrictions