mysql.h

CREATE VIEW

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

La sentencia CREATE VIEW crea una nueva vista o reemplaza una vista existente si se especifica la cláusula OR REPLACE. Si la vista no existe, CREATE OR REPLACE VIEW es lo mismo que CREATE VIEW. Si la vista existe, CREATE OR REPLACE VIEW la reemplaza.

select_statement es una sentencia SELECT que proporciona la definición de la vista. (Seleccionar desde selecciona usando la sentencia SELECT.) select_statement puede hacer selecciones desde tablas base o desde otras vistas.

La definición de la vista queda "congelada" en el momento de su creación y no es afectada por cambios posteriores en la definición de las tablas subyacentes. Por ejemplo, su una vista es definida como SELECT * en una tabla, las nuevas columna añadidas a la tabla más tarde no serán parte de la vista, y las columnas eliminadas de la tabla producirán un error cuando se seleccionen desde la vista.

La cláusula ALGORITHM afecta al modo en que MySQL procesa la vista. Las cláusulas DEFINER y SQL SECURITY especifican el contexto de seguridad a usar cuando se verifiquen los privilegios de acceso a la vista durante su invocación. La cláusula WITH CHECK OPTION se puede añador para restringir inserciones o actualizaciones de filas en las tablas referenciadas por la vista. Estas cláusulas se describen más abajo.

La sentencia CREATE VIEW requiere el privilegio CREATE VIEW para la vista, y algunos privilegios más para cada columna seleccionada por la sentencia SELECT. Para las columnas usasdas fuera de la sentencia SELECT, se debe tener el privilegio SELECT. Si la cláusula OR REPLACE está presente, también se debe tener el privilegio DROP para la vista. Si la cláusula DEFINER está presente, los privilegios requeridos dependen el valor del usuario.

Más adelante se describle el modo en que se verifican los privilegios cuando referencia una vista.

Una vista pertenece a una base de datos. Por defecto, una nueva vista es creada en la base de datos por defecto. Para crear la vista en una base de datos explícitae, se debe usar la sintaxis db_name.view_name para calificar en nombre de la vista con el nombre de la base de datos:

CREATE VIEW test.v AS SELECT * FROM t;

Las tablas o los nombres de vista sin calificar en una sentencia SELECT también se interpretan con respecto a la base de datos por defecto. Una vista puede hacer referencia a tablas o vistas en otras bases de datos calificando la tabla o el nombre de la vista con el nombre de base de datos apropiado.

Dentro de una base de datos, las tablas base y vistas comparten el mismo espacio con nombre, así que una tabla y una vista no pueden tener el mismo nombre.

Las columnas recuperadas por la sentencia SELECT pueden ser referencias simples a columnas de tabla o expresiones que usen funciones, valores constantes, etc.

Una vista debe tener nombres de columnas únicos sin duplicidades, igual que una tabla base. Por defecto, para la vista se usan los nombres de las columnas recuperadas por la sentencias SELECT. Para definir nombres explícitamente para las columnas de la vista hay que especificar la cláusula opcional column_list como una lista de identificadores separados con comas. El número de nombres en una column_list debe ser el mismo que el número de columnas recuperadas por la sentencia SELECT.

Una vista puede ser creada desde muchos tipos de sentencias SELECT. Se puede referir a tablas base o a otras vistas. Puede usar composiciones, uniones y subconsultas. El SELECT no necesita referirse a ninguna tabla:

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

El siguiente ejemplo define una vista que selecciona dos columnas de otra tabla y una expresión calculada a partir de esas columnas:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

Una definición de vista está sujeta a las siguientes restricciones:

  • La sentencia SELECT no puede contener una subconsulta en la cláusula FROM.
  • La sentencia SELECT no puede referirse a variables de sistema o a variables definidas por el usuario.
  • En el interior de un programa almacenado, la sentencia SELECT no puede referirse a parámetros del programa o a variables locales.
  • La sentencia SELECT no puede hacer referencia a parámetros de sentencia preparados.
  • Cualquier tabla o vista referenciada en la definición debe existir. Si, después de que la vista haya sido creada, una tabla o vista a la que se refiera la definición es borrada, el uso de la vista produce un error. Para verificar si una definición de vista tiene problemas de este tipousar la sentencia CHECK_TABLE.
  • La definición no puede hacer referencia a una table TEMPORARY, y no se puede crear una vista TEMPORARY.
  • No se puede asociar un disparador con una vista.
  • Para los alias de los nombres de columna en la sentencia SELECT se comprueba que la longitud de columna máxima es de 64 caracteres (no la longitud máxima de alias de 256 caracteres).

Está permitido ORDER BY en una definición de vista, pero será ignorado si se elecciona desde una vista uasndo una sentencia que tenga us propio ORDER BY.

Para otras opciones o cláusulas en la definición, son añadidas a las opciones o cláusulas de la sentencias que referencia a la vista, pero el efecto es indefinido. Por ejemplo, si una definición de vista incluye una cláusula LIMIT, y se selecciona desde la vista usando una sentencia que tiene su propia cláusula el límite que se aplica queda indefinido. Este mismo principio se aplica a opciones como ALL, DISTINCT o SQL_SMALL_RESULT que siguen a la palabra reservada SELECT, y a cláusulas como INTO, FOR UPDATE, LOCK IN SHARE MODE u PROCEDURE.

Los resultdos obtenidos desde una vista pueden verse afectados si se modifica el entorno de procesamiento de consultas mediante el cambio de variables de sistema:

mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc   |
+-------+
1 row in set (0.00 sec)

Las cláusulas DEFINER y SQL SECURITY determinan qué cuenta MySQL se usará cuando se comprueben los privilegios de acceso para la vista cuando se ejecute una sentencia que referencie a la vista. Los valores válidos característicos para SQL SECURITY son DEFINER (por defecto) e INVOKER. Estos indican que los privilegios requeridos deben ser mantenidos por el usuario que definió o invocó la vista, respectivamente.

Si la cláusula DEFINER está presente, el valor de usuario debe ser una cuenta MySQL especificada como 'user_name'@'host_name', CURRENT_USER o CURRENT_USER(). Los valores de usuario permitidos dependen de los privilegios que se posean.

Si se omite la cláusula DEFINER, el definidor por defecto es el usuario que ejecuta la sentencia CREATE VIEW. Es lo mismo que especificar DEFINER = CURRENT_USER explícitamente.

En el interior de una definición de vista, la función CURRENT_USER devuelve el valor del definidor de la vista por defecto. Para vistas definidas con las características SQL SECURITY INVOKER, CURRENT_USER retrona la cuenta para el invocador de la vista.

En el interior de una rutina almacenada que es definida con características SQL SECURITY DEFINER, CURRENT_USER devuelve el valor de DEFINER de la rutina. Esto también afecta a una vista definida dentro de una rutina, si la definición de la vista contiene un valor DEFINER de CURRENT_USER.

MySQL comprueba los privilegios de las vistas así:

  • Durante la definición de la vista, el creador debe tener los privilegios necesarios para usar los objetos de alto nivel accedidos por la vista. Por ejemplo, si la definición de la vista hace referencia a columnas de tabla, el creador debe tener algún privilegio para cada columna en la lista seleccionada de la definición, y el privilegio SELECT para cada columna usada en cualquier otra parte de la definición. Si la definición hace referencia a una función almacenada, sólo se verifican los privilegios necesarios para invocar la función. Los privilegios necesarios en el momento de la invocación de la función sólo pueden ser verificados cuando se ejecuta: Para diferentes invocaciones, diferentes caminos de ejecución deberán tomarse dentro de la función.
  • El usuario que referencia la vista debe tener los privilegios apropiados para acceder a ella (SELECT para acceder desde ella, INSERT para insertar, etc).
  • Cuando una vista ha sido referenciada, se comprueban los privilegios para objetos accedidos por la vista se cotejan con los privilegios de la cuenta DEFINER o con el invocador, dependiendo de si la característica SQL SECURITY es DEFINER o INVOKER, respectivamente.
  • Si la referencia a una vista provoca la ejecución de una función almacenada, la verificación de privilegios para sentencias ejecutadas dentro de la función depende de si la caracteristica SQL SECURITY es DEFINER o INVOKER. Si la característica de seguridad es DEFINER, la función se ejecuta con los privilegios de la cuenta DEFINER. Si la característica es INVOKER, la función se ejecuta con los privilegios determinados por la característica SQL SECURITY de la vista.

Ejemplo: Una vista puede depender de una función almacenada, y esa función puede invocar a otras rutinas almacenadas. Por ejemplo, la siguiente vista invoca una función almacenada f():

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);

Supongamos que f() contiene una sentencia como esta:

IF name IS NULL then
  CALL p1();
ELSE
  CALL p2();
END IF;

Los privilegios requeridos para ejecutar sentencias dentro de f() necesitan ser comprobados cuando se ejecute f(). Esto significa que se necesitarán privilegios para p1() o p2(), dependiendo de la ruta de ejecución dentro de f(). Esos privilegios deben ser comprobados durante la ejcución, y el usuario que debe poseer los privilegios es determinado por los valores SQL SECURITY de la vista v y la función f().

Las cláusulas DEFINER y SQL SECURITY para las vistas son extensiones al SQL estándar. El SQL estándar, las vistas son manejadas usando las reglas para SQL SECURITY DEFINER. El estándar dice que el definidor de la vista, que es el mismo que el dusño del esquema de la vista, obtiene privilegios aplicables a la vita (por ejemplo, SELECT) y puede otorgarlos. MySQL no tiene el concepto de "dueño" de un esquema, así que MySQL añade una cláusula para identificar al definidor. La cláusula DEFINER es una extensión donde la intención es tener lo que tiene el estándar; es decir, un registro permanente sobre quién difinió la vista. Esto es por lo que el valor por defecto para DEFINER es la cuenta del creador de la vista.

La cláusula opcional ALGORITHM es una extensión MySQL al SQL estándar. Afecta al modo en que MySQL procesa la vista. ALGORITHM puede tomar tres valores: MERGE, TEMPTABLE o UNDEFINED. El algoritmo por defecto es UNDEFINED si no se incluye la cláusula ALGORITHM.

Algunas vistas son actualizables. Es decir, se pueden usar en sentencias UPDATE, DELETE o INSERT para actualizar el contenido de una tabla subyacente. Para que una vista sea actulizable, debe haber una relación de uno a uno entre las filas en la vista y las filas en la tabla subyacente. También hay algunas otras construcciones que hacen que una vista no sea actualizable.

Una columna generada en una vista es considerada actualizable porque es posible asignarla. Sin embargo, si esa columna es actualizada explícitamente, el único valor permitido es DEFAULT.

Con la cláusula WITH CHECK OPTION pueden evitarse inserciones o actualizaciones de filas en una vista actualizable dada excepto para las que la cláusula WHERE en la select_statement sea verdadera.

En una cláusula WITH CHECK OPTION para una vista actualizable, las palabras clave LOCAL y CASCADED determinan el alcance de las pruebas de verificación cuando la vista esté definida en función de otra vista. La palabra LOCAL restringe la CHECK OPTION sólo a la vista que se está definiendo. CASCADED también hace que se evalúen las comprobaciones en las vistas subyacentes. Si no se especifia ninguna de la dos, el valor por defecto es CASCADED.

Las vistas creadas antes de MySQL 5.7.3 que contengan ORDER BY integer pueden producir errore en el momento de la evaluación de la vista. Consideremos estas definiciones de vista, que usan ORDER BY con un número ordinal:

CREATE VIEW v1 AS SELECT x, y, z FROM t ORDER BY 2;
CREATE VIEW v2 AS SELECT x, 1, z FROM t ORDER BY 2;

En el primer caso, ORDER BY 2 se refiere a la columna con nombre y. En el segundo caso se refiere a una constante 1. Para consultas que seleccionen desde cualquiera de las vistas menos de 2 columnas (el número nombrado en la cláusula ORDER BY), se produce un error si el servidor evalúa la vista usando el algoritmo MERGE. Ejemplos:

mysql> SELECT x FROM v1;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'
mysql> SELECT x FROM v2;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'

A partir de MySQL 5.7.3, para manejar definiciones de vista como estas, el servidor las escribe de forma diferente en el fichero .frm que almacena la definición de la vista. Esta diferencia es visible con SHOW CREATE VIEW. Anteriormente, el fichero .frm contenía esto para la cláusula ORDER BY 2:

For v1: ORDER BY 2
For v2: ORDER BY 2

Desde la versión 5.7.3, el fichero .frm file contiene esto:

For v1: ORDER BY `t`.`y`
For v2: ORDER BY ''

Es decir, para v1, 2 se reemplaza por una referencia al nombre de la columna a la que se refiere. Para v2, 2 es reemplazado por una expresión de cadena constante (ordenar por una constante no tiene efectos, así que ordenar por cualquier constante funciona).

Si se experimentan errores en la evaluación de vistas del modo descrito, hay que eliminar y recrear la vista para que el fichero .frm contenga la representación actualizada de la vista. Alternativamente, para vistas como v2 que ordenan por un valor constante, se puede eliminar y recrear la vista sin la cláusula ORDER BY.