views:

39

answers:

1

At work I'm constantly being told that when changes are made to a MySQL db that the views need to be 'refreshed'. The accepted manual solution seems to be going into Workbench, right clicking, and hitting 'Refresh All'

Is this just meant to be clearing the cache? Or does that rebuild the views from scratch, or is this totally bogus? They seem to be able to tell when views have not been 'refreshed', and I'm not sure they understand it any more than, "Because things need to be refreshed when they are changed."

If it is just clearing the cache, would 'FLUSH TABLES WITH READ LOCK' be enough?

+3  A: 

Views do not need to be refreshed when the data changes. When you query them they will fetch the newest data.

They might need to be recreated if your table structure changes:

The view definition is “frozen” at creation time, so changes to the underlying tables afterward do not affect the view definition. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view.

Source

Mark Byers
And if I wanted to add the new columns in I would need to update the view definition?
William
@William: Yes, if you want the new columns to appear in the view. But I don't think "Refresh all" does that for you. I think you have to drop the view and recreate it, or use ALTER VIEW: http://dev.mysql.com/doc/refman/5.0/en/alter-view.html
Mark Byers
Thanks, I think that's the solution. It needs to be altered.
William