Can mysql view create an index?

Mysql view cannot create an index. A view is a virtual table that does not actually exist in the database. It has no actual rows and columns (the row and column data come from the table used in the query that defines the view); and the index is a special The database structure is composed of one or more columns in the data table, so no index can be created in the view, there is no primary key, and triggers cannot be used.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

mysql view cannot create an index.

MySQL view (View) is a virtual table that does not actually exist in the database. It is data without actual rows and columns; rows and columns From the table used in the query that defines the view, and is also generated dynamically when the view is used.

The database only stores the definition of the view, and does not store the data in the view. These data are stored in the real table referenced by the query that defines the view. When using a view to query data, the database will retrieve the corresponding data from the real table. Therefore, the data in the view depends on the data in the real table. Once the data in the real table changes, the data displayed in the view will also change.

The index is a special database structure, which is composed of one or more columns in the data table. It can be used to quickly query records with a specific value in the data table. . This section will explain in detail the meaning, function, advantages and disadvantages of indexes. Through the index, when querying data, you do not need to read all the information in the record, but only query the index column. Otherwise, the database system will read all information of each record for matching.

Therefore: the index is composed of one or more columns in the data table, while the view is a virtual table with no actual rows and columns; further, the mysql view cannot create an index.

Extended knowledge: mysql viewMysql view is essentially a statement. When you call the view, the database management system will first Find the statement encapsulated in the view, first execute the statement that generates the view, and then query based on the view

Advantages of the view

Views and tables are essentially the same Although they are different, after the view is defined, its structure is the same as that of the table, and operations such as query, modification, update, and deletion can be performed. At the same time, views have the following advantages:

1) Customize user data and focus on specific data

In the actual application process, different users may have different requirements for different data.

For example, when databases exist at the same time, such as student basic information tables, curriculum tables, teacher information tables and other tables exist at the same time, different users can use their own data according to needs. A view for students to view and modify their basic information, a view for course personnel to view and modify course schedules and teacher information, and a view for teachers to view student information and course information tables.

2) Simplify data operations

When using queries, aggregate functions are often used, and information from other fields must also be displayed. It may also need to be related to other tables, and the statements may be very long. Long, if this action occurs frequently, you can create a view to simplify the operation.

3) Improve data security

Views are virtual and do not exist physically. You can only grant users view permissions without specifying permissions to use tables to protect the security of basic data.

4) Sharing the required data

By using views, each user does not have to define and store the data he or she needs. Instead, the data in the database can be shared. The same data only needs to be stored once. .

5) Change data format

By using views, you can reformat the retrieved data and organize the output to other applications.

6) Reuse SQL statements

The view provides an encapsulation of the query operation and does not contain data itself. The data presented is retrieved from the base table according to the view definition. If the base When data in the table is added or deleted, the view also presents the updated data. Once a view is defined and the required queries are written, the view can be easily reused.

Pay attention to the difference between the essence of a view and a data table, that is, a view is a virtual table based on a real table, and its data sources are based on the real table.

When using views, you should also pay attention to the following points:

Sufficient access rights are required to create a view.
There is no limit to the number of views you can create.
Views can be nested, i.e. queries that retrieve data from other views create views.
Views cannot be indexed and cannot have triggers, defaults, or rules associated with them.
Views can be used with tables.

• Views do not contain data, so every time you use the view, you must perform any retrieval operations required in the query. If you create complex views or nested views with multiple connections and filter conditions, you may find that your system's performance is severely degraded. Therefore, system testing should be performed when deploying a large number of view applications.

[Related recommendations: mysql video tutorial]

The above is the detailed content of Can mysql view create an index?. For more information, please follow other related articles on the PHP Chinese website!