1.8.4.6 Views

We plan to implement views in MySQL Server in version 5.1

Historically, MySQL Server has been most used in applications and on web systems where the application writer has full control over database usage. Of course, usage has shifted over time, and so we find that an increasing number of users now regard views as an important aspect.

Views are useful for allowing users to access a set of relations as if it were a single table, and limiting their access to just that. Many DBMS don't allow updates to a view, instead you have to perform the updates on the individual tables.

Views can also be used to restrict access to rows (a subset of a particular table). One does not need views to restrict access to columns, as MySQL Server has a sophisticated privilege system. See section 4.2 General Security Issues and the MySQL Access Privilege System.

In designing our implementation of views, we aim toward (as fully as possible within the confines of SQL) compliance with ``Codd's Rule #6'' for relational database systems: all views that are theoretically updatable, should in practice also be updatable. This is a complex issue, and we are taking the time to make sure we get it right.

The implementation itself will be done in stages. Unnamed views (derived tables, a subquery in the FROM clause of a SELECT) are already implemented in version 4.1.

Note: If you are an enterprise level user with an urgent need for views, please contact sales@mysql.com to discuss sponsoring options. Targeted financing of this particular effort by one or more companies would allow us to allocate additional resources to it. One example of a feature sponsored in the past is replication.

User Comments

I would like to see views for purposes of simplifying queries. For instance, I can "pre-join" a few tables in a view then join that view to another table.

I am glad that views will be implemented in version 5.1. I wonder what it would take to create the equivalent of an Oracle materialized view? Very very similar to snapshot replication, which I believe MySQL already does, but on a single table basis only. The difference being that a materialized view can be (and usually is) created by a select statement. Think of a view with an actual physical table underlying it. This way, a complex select statement can always have the results ready in an indexed physical view. It can be updated periodically by differential or full updates.

This is a great performance booster, as it can be used to speed up complex queries across a wide network, locally.

Also, you don't need to create and manage more 'permanent' tables in order to do this. The application can use the permanent tables as the source of the data, but also use the physical/materialized views for specific subject areas.

I think this would be a very useful performance booster that can follow as an extension of the new view functionality. It is also a useful feature to replicate data to separate systems that may make use of the data more easily in a different layout... like a data warehouse for reporting made easy.

I've actually never seen views used to restrict access. We use views in Oracle heavily to compute calculated fields and join tables together, without having to modify multiple client programs every time a table changes.

Add your own comment.