Shulou(Shulou.com)05/31 Report--

The editor will share with you an example analysis of the principle of view in mysql. I hope you will gain something after reading this article. Let's discuss it together.

I. what is a view?

A view refers to a view in a computer database, which is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named columns and rows of data. However, the view does not exist in the database as a set of stored data values. Row and column data to freely define the table referenced by the view's query, and dynamically generated when the view is referenced. Simply put, a view is a table made up of the results of its definition.

Example: class rating form class (ID,name) Student form student (id,class_id,name)

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

Create a view:

The copy code is as follows:

Create view v_stu as select c.name as c_name, s.name as stu_name from student class c where c.id = s.class_id

Query View v_stu

Select * from v_stuc_namestu_namewu Class 1 li Class 2

Second, why use views

1. Security. This is generally done: create a view and define the data that the view operates on. The user rights are then bound to the view. This way is to use the

There is a feature: the grant statement can grant permissions to the view.

two。 Query performance is improved.

3. After the flexible functional requirements, it is necessary to change the structure of the table, resulting in a large amount of work. Then we can use the form of virtual table to achieve the effect of less modification.

This is more useful in practical development.

Example: if, for some reason, table an and table b need to be merged to form a new table c In the end, neither table a nor table b will exist. As the original program to write sql is based on table an and table b query, which means that you need to rewrite a large number of sql (to the c table to manipulate the data). It can be done without modification through the view. Define the two view names or the original table names an and b. Views an and b complete the extraction of content from table c.

Description: with this solution, it is better to know as much detail as possible based on the details of the view. Because the syntax of using a view is no different from using a table. 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 get the data. By combining the views, you can get the desired results.

How the view works: when the view is called, the sql in the view is executed to fetch data. The contents of the view are not stored, but the data is derived when the view is referenced. This does not take up space, and because it is an instant reference, the contents of the view are always consistent with the contents of the real table. What are the benefits of designing the view in this way? Save space, the content is always consistent, then we do not need to maintain the contents of the view, maintain the contents of the real table, we can ensure the integrity of the view.

Third, the execution mode of the view

There is no real data saved in the view, only the data in the real table is referenced, and the referenced data has its own definition. When we query the view, we actually take the data from the definition.

There will be a database like infomation_schema in MySQL, which is a database that comes with mysql, which stores some metadata of mysql database. The so-called metadata refers to the table information of mysql. View information, column information, etc. To put it simply, it is equivalent to a directory of the mysql database. All views are stored in the views table; from this we can see how mysql works

Select * from v_stu

V_stu can also be a table name, because the physical structure of the view and the table is different, the table can find the real data, and the view needs to find the definition, so as to get the required data, how do we know that v_stu is a view? Because there is a routine that looks at the directory to do it.

View mechanism:

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

① replacement: when you manipulate 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 submitted to mysql for execution.

② formalization: mysql first gets the result of view execution, which forms an intermediate result that is temporarily stored in memory. After that, the external select statement invokes these intermediate results (temporary tables).

It seems that they all want to get results, but there is a difference in form, as if they do not realize the essential difference. What is the difference between the two ways?

Instead, the view formula is replaced and treated as a whole sql. Specify the way, first deal with the view results, and then deal with the external query requirements.

The replacement method can be summarized as, prepare first, then execute.

The concrete way is summarized and understood as dealing with it separately.

Which way is better? I have no idea. Mysql will decide for itself which way to handle it. You can also specify how to use when defining the view.

Example:

Syntax:

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

ALGORITHM has three parameters: merge, TEMPTABLE, and UNDEFINED.

Merge: processing mode replacement, you can update the data in the real table

TEMPTABLE: materialized, because the data is stored in a temporary table, you can't update it!

When your parameter definition is UNDEFINED (no ALGORITHM parameter is defined). Mysql is more likely to choose the alternative. Because it's more effective.

Example:

Create ALGORITHM=merge view v_stu as select c.name as c_name, s.name as stu_name from student class c where c.id = s.class_idupdate v_stu set c_name =''where c_name =''

Successful execution

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

Execution failed and cannot be updated!

After reading this article, I believe you have a certain understanding of "sample Analysis of View principles in mysql". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.