Detailed explanation of mysql view in one article

mysql view concept

The view itself is a virtual table and does not store any data. When using SQL statements to access views, the data obtained is generated by MySQL from other tables, and the view and table are in the same namespace. View query data is relatively safe. It can hide some data and structures and only allow users to see the data within their permissions, making complex queries easy to understand and use.

View usage

The following is the basic usage of the demo view based on user and order management. (Online learning video tutorial sharing: mysql video tutorial)

Basic table structure

CREATE TABLE v01_user (    
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  user_name VARCHAR(20) DEFAULT NULL COMMENT '用户名',
    phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    pass_word VARCHAR(64) DEFAULT NULL COMMENT '密码',
    card_id VARCHAR(18) DEFAULT NULL COMMENT '身份证ID',
    pay_card VARCHAR(25) DEFAULT NULL COMMENT '卡号',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';
CREATE TABLE v02_order (    
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    user_id INT(11) NOT NULL COMMENT '用户ID',
    order_no VARCHAR(32) DEFAULT NULL COMMENT '订单编号',
    good_name VARCHAR(60) DEFAULT NULL COMMENT '商品名称',
    good_id INT(11) DEFAULT NULL COMMENT '商品ID',    
    num INT(11) DEFAULT NULL COMMENT '购买数量',
    total_price DECIMAL(10,2) DEFAULT NULL COMMENT '总价格',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '订单表';

Basic syntax

CREATE OR REPLACE VIEW view_name 
AS select_statement

Note: Tables and views share the same namespace in the database, therefore, the database cannot contain tables and views with the same name.

User order view

CREATE OR REPLACE VIEW user_order_view AS SELECT
    t1.id,t1.user_name,t2.order_no,t2.good_id,
    t2.good_name,t2.num,t2.total_price
FROM v01_user t1
LEFT JOIN v02_order t2 ON t2.user_id = t1.id;

View call

This is basically the same as MySQL table query, and various query conditions can be used.

SELECT * FROM user_order_view WHERE user_name='Cicada';
查看视图
SHOW CREATE VIEW user_order_view ;
修改视图
ALTER VIEW view_name AS select_statement ;
删除视图
DROP VIEW [IF EXISTS] view_name ;

View update

If the specified conditions allow, you can update, delete, or even write data on the view to update the information involved in the view. Related tables.

UPDATE user_order_view SET user_name='smile' WHERE id='1';

Here, the v01_user table data is updated by performing an update operation on the view. If the view is defined using special operations such as aggregate functions and grouping, it cannot be updated. MySQL does not support creating triggers on views.

View implementation

1. Temporary table algorithm

The server will save the data of the view query SQL in the temporary table. The structure of the temporary table is as follows: The view field structure must be consistent. This is the most taboo operation in SQL query optimization. A slightly larger amount of data will seriously affect performance. If the view cannot produce a one-to-one mapping relationship with the original table, a temporary table will be generated. This also shows that the view is not a very simple, or even very complex function.

2. Merge algorithm

The server executes the query based on the table used in the view, and finally merges the query structure and returns it to the client.

3. Difference method

Execute the following query statement to analyze the execution performance parameters.

EXPLAIN SELECT * FROM user_order_view ;

Observe the select_type field in the query result. If it is DERIVED, it means a temporary table is used. Here, the syntax of SQL execution analysis will be explained in detail later in the optimization section.

Notes

1. Performance issues

MySQL does not support creating indexes in views. When using views, it may cause a lot of query performance problem, so it is recommended to be cautious when using it, examine and test it from multiple angles.

2. Special usage

View-based query can modify part of the table structure. As long as the fields are not used in the view, it will not affect the query of the view.

Recommended related articles and tutorials: mysql tutorial

The above is the detailed content of Detailed explanation of mysql view in one article. For more information, please follow other related articles on the PHP Chinese website!