Summary of MySQL view principles and usage examples

This article summarizes the principles and usage of MySQL views. Share with you for your reference, the details are as follows:

1. What is a view?

A view is a virtual table in a computer database whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values ​​in the database. The row and column data comes from the tables referenced by the query that defines the view and is generated dynamically when the view is referenced. Simply put, a view is a table consisting of the results of its definition;

Example: define a class table class (ID, name) and a student table student (id, class_id, name);

When the data table structure is complex, but we only care about part of the data, we can use views to define the data we care about.

Create a view:

Query view v_stu

select * from v_stu

2. Why use views?

1. Security . This is generally done like this: create a view and define the data that the view operates on. Then bind the user permissions to the view. This method is used

A feature is added: the grant statement can grant permissions for views.

2. Improved query performance .

3. When there are flexible functional requirements, the structure of the table needs to be changed, resulting in a large workload. Then you can use a virtual table to achieve less modification.

This is more useful in actual development.

Example: If for some reason, table a and table b need to be merged to form a new table c. Finally, neither table a nor table b will exist. Since the SQL statements in the original program were based on queries to table A and table B, a large amount of SQL statements need to be rewritten (to operate on table C). And through the view, you can achieve no modification. Define two views with the same names as the original tables a and b. Views a and b complete the extraction of content from table c.

Note: When using this solution, the more detailed your understanding of the view, the better. Because using a view is no different from using a table in terms of syntax. For example, if the view name is a, then the query is still " select * from a ".

4. Complex query requirements. You can decompose the problem and then create multiple views to obtain the data. Combining the views together will give you the desired result.

Working mechanism of view: When the view is called, the SQL in the view will be executed to perform data retrieval operations. The contents of a view are not stored, but rather the data is derived when the view is referenced. This does not take up space, and because it is an immediate reference, the content of the view is always consistent with the content of the real table. What are the benefits of designing the view like this? To save space and if the content is always consistent, we do not need to maintain the content of the view. By maintaining the content of the real table, we can ensure the integrity of the view.

3. View Execution Method

The view does not store real data, but only references the data in the real table. The referenced data is determined by its definition. When we query the view, we actually get the data from the definition.

There is a database called infomation_schema in MySQL. It is a database that comes with MySQL and stores some metadata of the MySQL database. The so-called metadata refers to the table information of MySQL. View information, column information, etc. Simply put, it is equivalent to a directory of the MySQL database. All views have storage descriptions in the views table; from this we can see how MySQL works;

select * from v_stu;

v_stu can also be a table name. Since the physical structures of views and tables are different, real data can be found in the table, while the view needs to find the definition to get the required data. How do we know that v_stu is a view? This is because there is a directory-viewing routine that does this.

View mechanism:

There are two mechanisms for view processing, replacement and materialization;

① Replacement type : When operating a view, the view name is directly replaced by the view definition, and the result becomes select * from (select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id) , which is then submitted to MySQL for execution;

② Concrete form : MySQL first obtains the result of view execution, which forms an intermediate result and is temporarily stored in memory. Afterwards, the outer select statement calls these intermediate results (temporary tables).

It seems that both aim to achieve results, there are differences in form, but it seems that the essential difference has not been appreciated. What are the differences between the two methods?

The replacement method is to replace the view formula and process it as a whole SQL. The concrete approach is to process the view results first and then process the external query requirements.

The replacement method can be summarized as preparation first, then execution.

The specific way of summarizing it is to handle them separately.

Which way is better? have no idea. MySQL will determine which method to use for processing. You can also specify which method to use when defining the view.

example:

grammar:

CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
VIEW view name [(property list)]
AS SELECT statement [WITH [CASCADED|LOCAL] CHECK OPTION];

ALGORITHM has three parameters: merge, TEMPTABLE, UNDEFINED

merge : The processing method is replacement, which can update the data in the real table;

TEMPTABLE : Materialized. Since the data is stored in a temporary table, update operations are not allowed!

When your parameter definition is UNDEFINED (no ALGORITHM parameter defined). MySQL prefers to choose the replacement method. Because it is more effective.

example:

create ALGORITHM=merge view v_stu as select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id
update v_stu set c_name = '' where c_name = '';

Execution Success

create ALGORITHM=TEMPTABLEview v_stu as select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id

Execution failed, cannot update!

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.