1.9.4.6 Представления

Представления планируется реализовать примерно в версии сервера MySQL 5.0.

Представления полезны в основном для случая, когда требуется предоставлять пользователям доступ к набору связей как к одной таблице (только в режиме чтения). Во многих базах данных SQL не обеспечивается возможность обновлять какие-либо строки в представлении - такие обновления необходимо выполнять в отдельных таблицах.

Поскольку сервер MySQL применяется в основном в приложениях и веб-системах, где разработчик приложения имеет полный контроль над использованием базы данных, большинство из наших пользователей не считают представления достаточно важной функциональной возможностью (по крайней мере, никто не заинтересовался ими настолько, чтобы выразить готовность финансировать реализацию представлений).

Для сервера MySQL нет необходимости в применении представлений для ограничения доступа к столбцам, так как в нем реализована хорошо продуманная система привилегий (see section 4.2 Общие проблемы безопасности и система привилегий доступа MySQL).

User Comments

Okay i want to say that you people are griping
about not having views and even going so far as
to use unprofessional language, and I don't see
anybody offering to help finance the technology
to do that.. As For Me I don't know what a view
is. Everything that i need done can done with a
SELEECT statement and maybe some coding..

So Stop Your Griping! Its Free For the love of God
Keep up the Good Work MySQL Developers

For development teams of more than one views are
essential.

well written sql views accessing the data at the
backend -> simple sql accessing the view from the
frontend

This makes for faster development and allows fron
end developers to concentrate on the user
interface.

If you want MySql to compete effectively with the
proprietary RDBMS then views are an essential
first step.

Views serve another useful purpose, in that you
can control the visibility on views separately
from the tables on which they are based.

This is good when you want to have a table
structure that's optimized for execution
efficiency, but you don't want casual users
messing with columns they don't need to or should
touch or even see.

Another vote for views - with complex databases (and yes, web databases are becoming complex!) views are a must! They enable a database developer to summarise common complex relationships into a simple SELECT statement for FRONT-END programmers.



Without this relies on inefficient development practices AND inefficient code! Precomiled views can also reduce query execution time and server loading.



They ARE A MUST HAVE !!!

my company mantain an uerope-wide, multilingual
portal. Its strenght is in the data it can
provide to any user in europe. Unfortunately we
have now such a complex DB structure that complex
queries slow the page generation a lot. Having
views would surely make mySQL make a quantum
leap. I can imagine the ease with which we would
be able to import-export data from partners/other
sources. I think views are extremely important
when coping with extensive data representations,
and hope they will soon be available.

Views are a very helpful way to keep complex
selects away from the application developers in
complex databases. I can't imagin to develop
complex systems without having views.

Can only ageee about views being essential, I am
developing a reasonably complex database and
views would act as a highly useful level of
abstraction. Yes I can code around them, but it
would be much more effiecent not to have to.

If your application has the nature of a Web application service, then you absolutely
require views, so that each client can see only that client's data.

I'm writing Distributed Db Applications for small
businesses, so speed is an issue. And I can't
imagine speed when i have to send the entire SQL-
script to the broker instead of "opening"a view
that runs completely at the Server-side.
So please, please, please, implement views in
this fast, no-nonsense, reliable and affordable
product.

First of all: VIEW is necessary to make an interim
instance between application and database table.
What if there are many applications are working
currently with some table, and your new application
needs this table to be restructured or even divided
to many tables? What is easier? To fix all SQLs in
all of applications that work with this table, or just
recreate the views that use this table?
Second: in common case, when there is a
scripting language applications work with the
MySQL database, and ther is some huge SELECT
needs to be created, in terms of resources usage it
always better to reduce the script code usage and
transfer logic to the database.
Third: in fact, VIEW is an only SQL function that
returns table rows as a result. Other functions
return only elementary types. It's again about
moving resource usage to the database.
I don't know how many else arguments do we need
to understand that VIEW is a full participant of the
RDBMS concept.

I add my vote for views. I'm writing my very first
PHP & NMySQL application and have found that using
a view of a specific table would help reduce the
coding effort. Specifically, the application
allows the user to filter the records and then
find a record within that subset. It would be
great to use a view for the filter and then find
single records within the view by applying a
SELECT against the filter view.

Frankly, I could really use VIEW's. For those
who don't know what a view is it would look
something like:
CREATE VIEW view_name (view_definition)

A view *looks* like a table to any application
doing a read only operation. In the case that
prompted me to look up views for MySQL, I need
to provide specific table and column names for
an application that I can't change. I already
have all the tables/data in another application
and want to hook the non-editable application
into the existing data (the data is in different
tables). There are two ways to do this that I
would be happy with, a VIEW or a TRIGGER and a
sparate table. I'm about to go looking for
triggers, but I suspect MySQL doesn't have them.

I think the implementation of views would promote a
faster migration from M$ SQL users to MySql. I
have an application that uses many views and the
hold up from migration to MySql is that I would have
to rewrite the entire application. When Views are
implemented, I will be the first to jump in line for a
support contract when I switch from M$Sql to My
Sql.

1) From a developers perspective, Views provide a
central location for commonly used SQL. If two
development groups are accessing the same
database, why not allow them to share functionality
at the database level?

2) Views allow developers to abstract themselves
from having to deal with potential table changes. If
a table/column name changes, your application
breaks. But if you use Views, all you have to do is
modify your View to use the new table name, or
alias a column name and your application continues
working without modifying, testing, and redeploying
your code.

Once a friend of mine looked at a DB with 50 views
and said: "If they need so many views, the tables
were wrong in the first place!". After a while, he
developed a converter package which would have
been _useless_ without views (because it took
whole tables/views as input). Views are a level
of abstraction, and our profession is made up of
abstraction, so I vote for views. My wish list is:
stored procedures, then views, NEVER triggers
(they add complexity instead of removing it)!

As far as Im concern
I use many database instance
such as oracle,sybase and sql server. We have a
bank of over 500 customers using are software and
mainly the software is using views to access the
database we also use crystal report. Since
crystal report have some limit for the number or
interrelation table we are using views to
overcome this limitation. So no views no try out
of MY sql. The database it self of Mysql seems
very good but I wont be able to use it in a
comercial purposes for My compagny if no views
are available. Also views permit (like point in
previous comments) to do in one select mulitiple
inter relation between tables. Most of the time
the view will also have a faster execution then
the multiple select to access different tables.
It also provide a flexibility when either many
tables are may be not properly build up or when
you have a software in constant growing. It give
you the flexibility to put multiple tables to be
seen true only one select. There is no perfect
world so to try to by pass those imperfection in
table building analyses the view is a good
compromise.

I would give adding Views to MySQL a very high
priority.

(My)SQL is about the relational model
Relational modelling is about
normalization.
Normalization is just good practice
when designing a database.
Even a straightforward database
application produces a lot of small tables in 4NF.
Views allow users and developers to
reconstruct a (from their point of view) logical
view of the data.

Yes I can use my Perl script to create a sequence
of similar multi-table queries but it would be
simpler, faster and more reliable if the database
engine were to do it.

I am sure Mr Codd and Mr Date would want to have
Views.

IMHO, until VIEWS are supported natively in MySQL,
the best way to provide a developer with a pseudo-
VIEW would be to create a table in their DB called
VIEWS where the queries are stored.

A VARCHAR column called VIEW_NAME holds what
you want to call the VIEW and a VARCHAR column
called VIEW_SQL could contain the query. Then you
have your developers write code that retrieves the
SQL query stored in VIEW_SQL, SELECTing by
VIEW_NAME then execute the retrieved contents of
the pseudo-VIEW. OK, it ups the processor cycles in
your script interpreter/application and your database
is accessed twice, but if everyone's main concern is
the filtering of data returned to the application or
web page then surely this is an acceptable
temporary solution.

When MySQL eventually supports VIEWs directly, the
only change made to the source code for the
app/script is to SELECT the VIEW directly, losing the
second "execute" step of the process. This reduces
the code re-development required.

Or did I miss something?

Another way to emulate views is by creating a
library that creates a temporary table:
create table() select * from table..

I love MySQL and have used it to implement several
large commercial systems with spectacular success.
However, for the system I am currently working on I
have decided (reluctantly) to take a "backwards
step" and use MS SQL Server/Windows 2000
Server. This is purely because
I now need to use views to overcome a
performance issue and MySQL doesn't support
them yet. Shame, since the rest of the application
has been implemented using "open source" tools.
When you have implemented views I shall advise our
client to migrate to MySQL/Linux and pay the
commercial license fee.

MS SQL is better than MySQL only because store
procedures, views and triggers. I have more than 5
years working with MS SQL and now I'm trying to
migrate to MySQL, but is to much work to recreate
all the code to simulate views and complex store
procedures. It would be great to INSERT, UPDATE or
DELETE data from the view and the associated
tables.
MySQL is a great solution to small-medium complex
systems.

Views are great if you are trying to hide, or
adjust the way the information is seen before it
gets where it is going. I am working on bringing
together two systems with similar data, but
instead of rewriting the whole thing I could use
views to make the systems think they are using
the same old system, but in effect work with
each others data. Later on I could re-write the
selection code for the separate systems so that
they work with the new tables but if I had
views, I could help my company move up to 1
week faster.

If mysql had triggers, a procedural language, and
nested sub queries, then views would be easy to
simulate. For example, say you wanted a view that
looked like "SELECT a.foo, a.bar, b.baz FROM apple
a, banana b WHERE ...". You could just make a
(empty) table with columns foo, bar, and baz (of
the appropriate types) and then have a trigger
that fires before selects on the foobarbaz table
and returns the results of a dynamicaly written
(sub)query instead of passing the query onto the
foobarbaz table. Triggers can also take care of
inserts and updates in a simalar way (but
honestly, what kind of person would want to update
a view?:). If performance becomes an issue, you
can drop the query rewritting trigger and populate
(index, etc) the foobarbaz table without touching
any client code. In that case, you would only have
to ammend the insert / update triggers to maintain
your "materialised view" foobarbaz table.

In data warehousing applications, there is frequently
a requirement to have the same table in the schema
in several places. For example, a database allowing
users to analyze shipments of goods would have an
orders date table (dimension), a shipment date
table, arrival date etc. The date table stores the date
with additional attributes need for analytical purposes
such as month, week, holiday and weekend
indicators etc. Each 'table' is foreign key joined with
a central fact table. Without views such a schema
would require three copies of the same table to
allow sensible joining of the fact table with more
than one of the date tables. The space problem
becomes worse when you have large dimensions
such as customer and location that have roles. So
for data warehousing, we need views please!

Views are absolutely essential to complex relational
design. I love open source software but without the
functionality of the alternatives even with their cost it
is impossible to use MySql for the more complex
projects. Views are the last essential missing piece
before I can start suggesting MySql as a valid option
to my clients. Until then I have to grit my teeth and
suggest alternatives that cost more but keep the
deveopment team from having to hack a solution
such as a four or five table join in numerous SQL
Statements.

I agree that the implementation of views would make database development more easy and feasible for developers. However, until such time as it is developed, could you not store complex SQL statements in a flatfile on the server and access them through scripting such as PHP. This would provide a quick means of getting the appropriate information without having to recreate the complex SQL statement in every document you use it. You simply access the file and set the appropriate statement into a variable which is used in scripting to return the wanted information. Or you could store the statements in a separate table and call on them when you need to.

Just a thought. I'm going to try and see how they work.

Firstly, my compliments to the MySQL developers for a great product.

With regard to VIEWS - having just completed development on a very large system using a multitude of views, I find I am lost without them now that I am using MySQL. VIEWS definitely are needed, especially if you are using a database which has been optimized and has very little redundancy - with a view/s some of my SQL statements are horrendously long and complicated, especially when many reference tables are used.

The sooner this gets implemented, the better. Thank you

The utility of views is obvious in most of RDBMS applications, especially when one of your master table is linked via keys to many child tables.

And you can modularly increase the complexity of your queries by building views over views!

(MySQL) Having come so far, I think views should have been already implemented!!

Peps up for the MySQL team!

Views are all about abstraction; their introduction will mark a significant advance in the maturity of MySQL and its quest to become an RDBM (where it was once simply an sql-like isam front end)

That is - of course - provided that we don't simply get "screens for databases". Views are about abstracting data - or queries - to the client. Its about making MySQL a DBAs tool, and not just a place to store data. If the views allow permission and privellige assignments, a user can be denied access to a database, but granted permission to the data via a view.

Looking forward to MySQL 5.0

Ouch! No views?

We're seriously looking at moving from Oracle8i/9i to MySQL for databased online applications, but the lack of views does create a big headache in a normalised database.

I'm a keen supporter of open source, and think the developers of MySQL have every right to feel proud of it's progress.

But I'd suggest views, stored procs, constraints and to a lesser degree, triggers, should be a high priority if MySQL is seriously moving towards "business-strength" databased applications.

Max Hugen
Hugen Enterprises Pty Ltd
www.hugen.com.au
Sydney, Australia

Add your own comment.