Rechercher dans le manuel MySQL
C.5 Restrictions on Views
View processing is not optimized:
It is not possible to create an index on a view.
Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).
There is a general principle that you cannot modify a table and select from the same table in a subquery. See Section C.4, “Restrictions on Subqueries”.
The same principle also applies if you select from a view that selects from the table, if the view selects from the table in a subquery and the view is evaluated using the merge algorithm. Example:
If the view is evaluated using a temporary table, you
can select from the table in the view
subquery and still modify that table in the outer query. In this
case the view will be stored in a temporary table and thus you are
not really selecting from the table in a subquery and modifying it
“at the same time.” (This is another reason you might
wish to force MySQL to use the temptable algorithm by specifying
ALGORITHM = TEMPTABLE
in the view definition.)
You can use DROP TABLE
or
ALTER TABLE
to drop or alter a
table that is used in a view definition. No warning results from
the DROP
or ALTER
operation,
even though this invalidates the view. Instead, an error occurs
later, when the view is used. CHECK
TABLE
can be used to check for views that have been
invalidated by DROP
or ALTER
operations.
With regard to view updatability, the overall goal for views is that if any view is theoretically updatable, it should be updatable in practice. MySQL as quickly as possible. Many theoretically updatable views can be updated now, but limitations still exist. For details, see Section 24.5.3, “Updatable and Insertable Views”.
There exists a shortcoming with the current implementation of
views. If a user is granted the basic privileges necessary to
create a view (the CREATE VIEW
and
SELECT
privileges), that user will
be unable to call SHOW CREATE VIEW
on that object unless the user is also granted the
SHOW VIEW
privilege.
That shortcoming can lead to problems backing up a database with mysqldump, which may fail due to insufficient privileges. This problem is described in Bug #22062.
The workaround to the problem is for the administrator to manually
grant the SHOW VIEW
privilege to
users who are granted CREATE VIEW
,
since MySQL doesn't grant it implicitly when views are created.
Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view is not permitted.
SHOW CREATE VIEW
displays view
definitions using an AS
clause for each
column. If a column is created from an expression, the default
alias is the expression text, which can be quite long. Aliases for
column names in alias_name
CREATE VIEW
statements are checked against the maximum column length of 64
characters (not the maximum alias length of 256 characters). As a
result, views created from the output of SHOW
CREATE VIEW
fail if any column alias exceeds 64
characters. This can cause problems in the following circumstances
for views with too-long aliases:
View definitions fail to replicate to newer slaves that enforce the column-length restriction.
Dump files created with mysqldump cannot be loaded into servers that enforce the column-length restriction.
A workaround for either problem is to modify each problematic view
definition to use aliases that provide shorter column names. Then
the view will replicate properly, and can be dumped and reloaded
without causing an error. To modify the definition, drop and
create the view again with DROP
VIEW
and CREATE VIEW
, or
replace the definition with
CREATE OR REPLACE
VIEW
.
For problems that occur when reloading view definitions in dump
files, another workaround is to edit the dump file to modify its
CREATE VIEW
statements. However,
this does not change the original view definitions, which may
cause problems for subsequent dump operations.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-view-restrictions.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.