Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Refresh MySQL views?

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?

like image 315
William W Avatar asked Aug 05 '10 23:08

William W


People also ask

How to update view in MySQL?

Here’s how to update view in MySQL. There are multiple ways to update view in SQL. You can update an SQL view’s query, or data. We will look at each of these ways to update view in MySQL. You can update view in MySQL using ALTER statement. This will replace the SQL query for view, not underlying data.

How to refresh all the views in a database?

To help solve this, you will need to refresh the views in the database. If you have a requirement to refresh all the views in a database, use the script. Change the database name at the top with your required database name and run the script. It should work. Also if you have any requirement to refresh all views in all the databases, ...

What is a view in MySQL?

For this reason, sometimes, a view is referred to as a virtual table. MySQL allows you to create a view based on a SELECT statement that retrieves data from one or more tables. This picture illustrates a view based on columns of multiple tables: In addition, MySQL even allows you to create a view that does not refer to any table.

What are MySQL updatable views?

Introduction to MySQL updatable views. In MySQL, views are not only query-able but also updatable. It means that you can use the INSERT or UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.


1 Answers

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

like image 59
Mark Byers Avatar answered Sep 23 '22 02:09

Mark Byers