Like other RDBMS, view meta data is stored at view creation time.
From CREATE VIEW
in the MySQL docs
The view definition is “frozen” at creation time, so changes to the underlying tables afterward [sic] do not affect the view definition.
I can't see a way to refresh this view meta data cleanly, unlike other RDBMS:
- SQL Server:
EXEC sp_refreshview 'MyView'
- Oracle:
ALTER VIEW MyView COMPILE
The system here appears to be "do this manually" with DROP/CREATE which isn't my style. The usual crowd who do this manual process are on leave so it falls to me this week.
I don't have a minion to nominate either: so any solutions please…
Best Answer
To see the view's definition, run one of the following:
SHOW CREATE VIEW viewname\G
SELECT * from information_schema.views where table_name='viewname'\G
Here is a quick example:
Whichever method you choose, you can visibly see the query making up the view. You can sculpt the CREATE OR REPLACE VIEW using whatever is shown.
Hope this helps, and Welcome to the DBA MinionExchange !!!