MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Статьи MySQL Using MySQL views

Using MySQL views

Печать PDF
Рейтинг пользователей: / 457
ХудшийЛучший 
MySQL Views using
  • Advantages and restrictions of using views
  • Creating, altering and dropping views
  • Algorithms of views
  • Setting security configuration, SQL SECURITY and DEFINER properties
  • Updatable views
  • View metadata

 

view – is a database object based on a SELECT query that has tables or other views specified inside it. In other words, this is a named query that is stored in a database as a separate object. Unlike a table, a view is not a data storage.

When referring to a view, we just receive data that is returned by the internal SELECT query.

 

 

Caching of the selection is not performed during this operation, data is determined dynamically basing on current data; i.e., if table data has changed, then, respectively, it will be reflected on the view that is based on the table.

The following example demonstrates creating a view:

CREATE VIEW view_dept AS
  SELECT
    deptno, dname, loc
  FROM
    dept;

As you can see, it's that simple. We have defined a view with the ‘view_dept’ name. If we didn’t specify a column list, then MySQL would determine the column list at the time of creating the view.

CREATE VIEW view_dept AS SELECT * FROM dept;

In case of such a query, MySQL will automatically select all the columns of the dept table and when executing the following command: ‘SHOW CREATE VIEW view_dept’ we get the following result:

CREATE VIEW `view_dept` AS select `dept`.`deptno` AS `deptno`,`dept`.`dname` AS `dname`,`dept`.`loc` AS 
`loc` from `dept`;

You must also note that modification of the table structure (underlying) may lead to the respective view being invalid. For example, it’s possible to add to the table a new column with no problems, however, if you remove or rename a column that is used in the view, the view will become not valid and you’ll get an error when referring to the view.


Advantages and restrictions of using views

 

Views may be a connecting link between storing data (tables) and application’s logic. It’s possible to flexibly setup data access permissions – tables can be hidden from access while views can be accessed; having a possibility to specify permissions for reading and modifying particular columns of a view. With the same ease, for instance, with the help of the WHERE condition in a query, it’s possible to grant an access for particular table rows; or provide the possibility to get not table data itself but the result of the selection and some actions with it. Views can be updatable – allowing to make modifications in underlying tables.

If a database structure changes, but there is no possibility to replace one of client’s applications, in this case it’s possible to create views that are analogous to the old tables the application worked with earlier.

Restrictions:

  • It’s not possible to use sub-queries in the ‘FROM’ construction (I consider this restriction quite strange, but it exists).
  • It’s not possible to create a trigger on a view.
  • It’s not possible to refer to temporary tables from views.
  • It’s not possible to refer to system and custom variables.
  • It’s not possible to refer to prepared statement parameters.
  • Execution of the DML commands - INSERT, UPDATE and DELETE is possible not for all views.


Creating, altering and dropping views

 

There are three DDL commands that allow working with views, they are CREATE VIEW – creating a view, ALTER VIEW – altering an existing view, DROP VIEW – dropping a view. The syntax of the CREATE VIEW and ALTER VIEW commands is similar and many parameters are optional.

The syntax of the CREATE VIEW command is as follows:

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

The syntax of the ALTER VIEW command is as follows:

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

OR REPLACE – allows to replace an existing view, i.e., if the view with the specified name already exists, it will first be removed and then recreated. ALGORITHM – is a MySQL extension to the standard SQL. An algorithm determines how MySQL should process all access to views. The descriptions of the algorithms are given below. DEFINER and SQL SECURIT – assigning privileges settings when accessing a view. The description is given below. (column_list) – allows to specify column names of a view explicitly.

The syntax of the DROP VIEW command is as follows:

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

IF EXISTS – allows to remove several views in one ‘DROP VIEW’ command.


Algorithms of views

 

There are three types of algorithms: MERGE, TEMPTABLE, and UNDEFINED. As it was mentioned above, an algorithm determines how MySQL should handle queries to views.

MERGE – when the text of the query that refers to the view and the internal SELECT query of the view merge into one general query and then execute. Let’s review one of the uses of the MERGE algorithm on an example. Let’s assume we have the following view that returns a list of salesmen:

CREATE ALGORITHM = MERGE VIEW view_salesman AS
  SELECT
    empno, ename, job, sal
  FROM
    emp
  WHERE
    job = 'SALESMAN';

We can refer to the view as follows:

SELECT * FROM view_salesman;

Here, the query that is specified in the view’s body will be executed. However, let’s add the condition to our SELECT query – to select the salesmen with the salary greater than 1500 from the list:

SELECT * FROM view_salesman WHERE sal > 1500;

MySQL will detect the ability to merge the two WHERE conditions and execute one general SELECT query:

SELECT empno, ename, job, sal FROM emp job = 'SALESMAN' AND sal > 1500;

It can be useful for optimization a query execution. Also, it must be noticed that only ‘MERGE’ views can be updatable. However, in case of the ‘MERGE’ algorithm a number of conditions should be taken into account as this algorithm does not always work. Firstly, a one-to-one ratio consistency must be achieved between the rows that are returned by the view and the rows of the table from which data is read; if, when defining the view, the following is used:

  • Aggregate functions (AVG, COUNT, MIN, MAX...)
  • Constructions (GROUP BY, HAVING, DISTINCT, UNION)
  • Sub-queries or literals/constants in the SELECT clause then the one-to-one ratio consistency will not be achieved.

In this case recording of data to temporary table is required. For that very reason the second algorithm is called TEMPTABLE.

Below is an example of such a view:

CREATE ALGORITHM = TEMPTABLE VIEW view_avg_sal AS
  SELECT
    job,
    AVG(sal) sal_avg
  FROM
    emp
  GROUP BY
    job;

TEMPTABLE – the result of the view is, firstly, recorded to the temporary table and then the actions that were specified when accessing the view are performed. This algorithm has the advantage – after retrieving data (that means filling of the temporary table), the table from which data is read will be immediately released.

UNDEFINED – the default algorithm. It’s used in most cases. When using this algorithm, MySQL determines which mode is better to use.


Setting security configuration, SQL SECURITY and DEFINER properties

 

SQL SECURITY and DEFINER constructions indicate whose privileges to use when calling a view, i.e. which MySQL account to use to check permissions. There are two variants of SQL SECURITY - DEFINER and INVOKER. DEFINER allows to explicitly specify account that will be used when referring to a view; in this case it’s possible to specify CURRENT_USER to use the privileges of the view creator. When specifying the INVOKER type, the privileges of the user that is directly referring to the view will be used. As an example, let’s create two views with different settings of SQL SECURITY and try to refer to them under different users.

  1. CREATE SQL SECURITY INVOKER VIEW view_invoker AS SELECT * FROM dept;

    The query below will be executed only for the users that have the SELECT privilege for the ‘dept’ table in addition to the ‘SELECT’ privilege for the view:

    SELECT * FROM view_invoker;
  2. CREATE DEFINER = 'root'@'localhost' VIEW view_definer AS SELECT * FROM dept;

    The query below will always be executed even for the users that have no the ‘SELECT’ privilege for the ‘dept’ table:

    SELECT * FROM view_definer;

    It’s enough to have only the ‘SELECT’ privilege for the view. That’s because when referring to the ‘view_definer’ view, the privileges of the 'root'@'localhost' user will be applied. Usually, such a user (including our case) has all permissions.


Updatable views

 

Views can be updatable; it allows altering table data (underlying) through a view. It’s possible to apply DML commands: INSERT, UPDATE, and DELETE to such views. To make a view updatable, a number of conditions need to be achieved; at least the one-to-one ratio consistency condition must be achieved for the rows that are returned by the view and rows from the underlying table. There are cases that can make a view not updatable, the most common of them are given below:

  • using aggregate functions (AVG, COUNT, MIN, MAX...)
  • using constructions (GROUP BY, HAVING, DISTINCT, UNION)
  • using sub-queries or literals/constants in the SELECT clause

It must be noted that if you need to create an updatable view, every case has to be dealt with separately as there are many particularities to make it possible to use the INSERT, UPDATE, and DELETE commands.

Let’s try to create a view with the ability to update table data.

Let’s assume we have the ‘properties’ table with the following dataset:

+----+---------+-------+--------+
| id | item_id | prop  | value  |
+----+---------+-------+--------+
|  1 |       1 | color | blue   |
|  2 |       1 | size  | large  |
|  3 |       2 | color | orange |
|  4 |       3 | size  | small  |
|  5 |       4 | color | violet |
|  6 |       5 | color | green  |
+----+---------+-------+--------+

Let’s create a view that returns some dataset:

CREATE VIEW view_size_properties AS
  SELECT * FROM properties WHERE prop = 'size';

The view will return some dataset:

SELECT * FROM view_size_properties;
+----+---------+------+-------+
| id | item_id | prop | value |
+----+---------+------+-------+
|  2 |       1 | size | large |
|  4 |       3 | size | small |
+----+---------+------+-------+

Let’s update the table data through the view:

UPDATE view_size_properties SET value = 'middle' WHERE item_id = 3;
Query OK, 1 row affected

Checking the updating:

SELECT * FROM view_size_properties;
+----+---------+------+--------+
| id | item_id | prop | value  |
+----+---------+------+--------+
|  2 |       1 | size | large  |
|  4 |       3 | size | middle |
+----+---------+------+--------+

 

The WITH CHECK OPTION construction allows you to restrict updating of data by only the records that are returned by a view.


View metadata

 

 

  • List of views and their properties - INFORMATION_SCHEMA.VIEWS

    SELECT * FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'view_name';
  • List of columns - INFORMATION_SCHEMA.COLUMNS

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'view_name';
  • Show CREATE VIEW statement (old syntax) - SHOW CREATE VIEW

    SHOW CREATE VIEW `view_name`;

 
mysql tools