写在前面，本文是博主在做数据库适配时遇到的问题。原项目用的是国产数据库人大金仓，因用户要求，需要迁移到mysql5.7上，在搜索解决方法之前有了大致思路就是建立物化视图表，然后使用存储过程或者触发器去更新物化视图表就可以了，但想要找有没有更优的方案，于是就进行了搜索。转载的本篇文字思路与我所想基本一致，步骤比较详细，转载留存一下。

---

在一次sql优化中一个select count(*)语句因数据量实在太大，已经无法从简单的索引什么进行优化了，在同事的推荐下考虑到了物化视图

物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样

首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切操作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，是代码变得复杂。

视图是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了），他的存在未了在设计上的方便性

实现视图的方法有两种，分别为合并算法和临时表算法，合并算法是指查询视图是将视图定义的sql合并到查询sql中，比如create view v1 as select * from user where sex = m;当我们要查询视图时，mysql会将select id,name from v1;并合成select id,name fromwhere sex= m;  临时表算法是将视图查出来的数据保存到一个临时表中，查询的时候查这个临时表。不管是合并算法和临时算法，都会给数据库带来额外的开销，切如果使用临时表后会使mysql的优化变得很困难，比如索引，而且视图还引入了一些其他的问题，是的其背后的逻辑非常复杂。

当然，视图在某些情况下还是可以帮助提升性能的，单视图的性能很难预测，且在mysql的优化器中，视图的代码执行路径也完全不同，无法直观预测其性能。

物化视图是是查询结果的预运算，物化视图的结果一般存储于表中。物化视图用于需要对查询立即做出响应，而又需要耗费长时间获得结果。物化视图必须能快速更新。它去介于对更新频率和内容的准确性的要求。一般来说物化视图能够在易订时间内及时更新。

Mysql本来不支持视图的。但是在5.0以上的版本，支持了视图功能，但是可惜的是不提供物化视图，但是这也难不住咱们，自己动手丰衣足食。

1. 
实现自己的物化视图

看一个它是如何实现的简单的查询实例：

SELECT COUNT(*)

  FROM MyISAM_table;
由于计数值存储在表的头部 立即返回结果。接下来的例子会耗费几秒到数分钟。

SELECT COUNT(*) FROM innodb_huge;

对此的可能解决方案是创建一个存储所有 InnoDB 行的表。

CREATE TABLE innodb_row_count (

    id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

  , schema_name VARCHAR(64)  NOT NULL

  , table_name  VARCHAR(64)  NOT NULL

  , row_count   INT UNSIGNED NOT NULL

);
取决于对该信息结果正确性的需要，该表可以每天更新一次（花费系统资源最少，结果错误最大），一小时一次甚至是极端情况下每次改变都更新（最慢）。

另一种可能就是从信息架构中读取数据。但是信息会有高达20%的错误。

SELECT table_schema, table_name, table_rows

FROM information_schema.tables  WHERE table_type = ‘BASE TABLE’;

2.
更新物化视图

物化视图的更新方式有很多种。比如：

l         从不更新（只在开始更新，只用于静态数据）

l         根据需要（比如每天，比如每夜）

l         及时（每次数据修改之后）

一半使用的更新方法：

l         全部更新（速度慢，完全从无到有）

l         延时的（速度快，使用log表）

通过在日志表中存储变更信息，通常会产生简单的“快照”或者延时状况：

l         及时更新

l         完全更新

3.
测试

为了理解这个方法，我们举个例子，详细讲解一下。

CREATE TABLE sales (

    sales_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

  , product_name   VARCHAR(128) NOT NULL

  , product_price  DECIMAL(8,2) NOT NULL

  , product_amount SMALLINT     NOT NULL

);

INSERT INTO sales VALUES

  (NULL, 'Apple', 1.25, 1), (NULL, 'Apple', 2.40, 2),

  (NULL, 'Apple', 4.05, 3), (NULL, 'Pear', 6.30, 2),

  (NULL, 'Pear', 12.20, 4), (NULL, 'Plum', 4.85, 3);

SELECT * FROM sales;
我们要知道售价和每种产品获得的利润，就要使用到两次的分组查询，我们晓得在mysql中连接查询和分组排序是会用到临时表和filesort的，这个如果数据量大的话，是十分耗时，如题查询如下：

EXPLAIN

SELECT product_name

     , SUM(product_price) AS price_sum, SUM(product_amount) AS amount_sum

     , AVG(product_price) AS price_avg, AVG(product_amount) amount_agg

     , COUNT(*)

  FROM sales

 GROUP BY product_name

 ORDER BY price_sum /G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: sales

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 6

        Extra: Using temporary; Using filesort

1 row in set (0.00 sec)
因为表中记录较少，因此速度很快，但是如果记录量很大这种，查询将会花费很多时间。

3.1
创建物化视图

DROP TABLE sales_mv;

CREATE TABLE sales_mv (

product_name VARCHAR(128)  NOT NULL ,

 price_sum    DECIMAL(10,2) NOT NULL,

 amount_sum   INT           NOT NULL,

 price_avg    FLOAT         NOT NULL,

 amount_avg   FLOAT         NOT NULL,

 sales_cnt    INT           NOT NULL,

 UNIQUE INDEX product (product_name)

);

INSERT INTO sales_mv

SELECT product_name, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount), COUNT(*)

  FROM sales

GROUP BY product_name;
最简单的方法，我们得到了预期的正确结果：

mysql> SELECT * FROM sales_mv /G

*************************** 1. row ***************************

product_name: Apple

   price_sum: 7.70

  amount_sum: 6

   price_avg: 2.56667

  amount_avg: 2

   sales_cnt: 3

*************************** 2. row ***************************

product_name: Pear

   price_sum: 18.50

  amount_sum: 6

   price_avg: 9.25

  amount_avg: 3

   sales_cnt: 2

*************************** 3. row ***************************

product_name: Plum

   price_sum: 4.85

  amount_sum: 3

   price_avg: 4.85

  amount_avg: 3

   sales_cnt: 1

3 rows in set (0.01 sec)
这会导致我们刚才提到的“从不更新”模式失败。但是这不是我们想要的。

3.2
按需更新物化视图

根据需要更新物化视图，我们可以用存储过程来实现

DROP PROCEDURE refresh_mv_now;

DELIMITER $$

CREATE PROCEDURE refresh_mv_now (

OUT rc INT

)

BEGIN

TRUNCATE TABLE sales_mv;

INSERT INTO sales_mv

SELECT product_name

, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount)

, COUNT(*)

FROM sales

GROUP BY product_name;

SET rc = 0;

END;

$$

DELIMITER ;

好！我们看下他的运行结果：

CALL refresh_mv_now(@rc);

SELECT * FROM sales_mv /G

*************************** 1. row ***************************

product_name: Apple

price_sum: 7.70

amount_sum: 6

price_avg: 2.56667

amount_avg: 2

sales_cnt: 3

*************************** 2. row ***************************

product_name: Pear

price_sum: 18.50

amount_sum: 6

price_avg: 9.25

amount_avg: 3

sales_cnt: 2

*************************** 3. row ***************************

product_name: Plum

price_sum: 4.85

amount_sum: 3

price_avg: 4.85

amount_avg: 3

sales_cnt: 1

3 rows in set (0.00 sec)

INSERT INTO sales VALUES

(NULL, ‘Apple’, 2.25, 3), (NULL, ‘Plum’, 3.35, 1)

, (NULL, ‘Pear’, 1.80, 2);

CALL refresh_mv_now(@rc);

SELECT * FROM sales_mv /G

*************************** 1. row ***************************

product_name: Apple

price_sum: 7.70

amount_sum: 6

price_avg: 2.56667

amount_avg: 2

sales_cnt: 3

*************************** 2. row ***************************

product_name: Pear

price_sum: 18.50

amount_sum: 6

price_avg: 9.25

amount_avg: 3

sales_cnt: 2

*************************** 3. row ***************************

product_name: Plum

price_sum: 4.85

amount_sum: 3

price_avg: 4.85

amount_avg: 3

sales_cnt: 1

3 rows in set (0.00 sec)

3.3
即时更新物化视图

每条语句之后做全部更新没有任何意义。但是我们想要合适的结果。做到这一点还是有点复杂的。

在每次insert sales 表我们都要更新物化视图。我们可以在sales表中透明的使用insert/update/delete触发器实现。

现在来创建需要的触发器：

DELIMITER $$

CREATE TRIGGER sales_ins

AFTER INSERT ON sales

FOR EACH ROW

BEGIN

  SET @old_price_sum = 0;

  SET @old_amount_sum = 0;

  SET @old_price_avg = 0;

  SET @old_amount_avg = 0;

  SET @old_sales_cnt = 0;

  SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(amount_avg, 0)

       , IFNULL(sales_cnt, 0)

    FROM sales_mv

   WHERE product_name = NEW.product_name

    INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_sales_cnt;

  SET @new_price_sum = @old_price_sum + NEW.product_price;

  SET @new_amount_sum = @old_amount_sum + NEW.product_amount;

  SET @new_sales_cnt = @old_sales_cnt + 1;

  SET @new_price_avg = @new_price_sum / @new_sales_cnt;

  SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;

  REPLACE INTO sales_mv

  VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_amount_avg, @new_sales_cnt)

  ;

END;

$$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER sales_del

AFTER DELETE ON sales

FOR EACH ROW

BEGIN

  SET @old_price_sum = 0;

  SET @old_amount_sum = 0;

  SET @old_price_avg = 0;

  SET @old_amount_avg = 0;

  SET @old_sales_cnt = 0;

  SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(amount_avg, 0)

       , IFNULL(sales_cnt, 0)

    FROM sales_mv

   WHERE product_name = OLD.product_name

    INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_sales_cnt

  ;

  SET @new_price_sum = @old_price_sum - OLD.product_price;

  SET @new_amount_sum = @old_amount_sum - OLD.product_amount;

  SET @new_price_avg = @new_price_sum / @new_amount_sum;

  SET @new_sales_cnt = @old_sales_cnt - 1;

  SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;

  REPLACE INTO sales_mv

  VALUES(OLD.product_name, @new_price_sum, @new_amount_sum, IFNULL(@new_price_avg, 0)

       , IFNULL(@new_amount_avg, 0), @new_sales_cnt)

  ;

END;

$$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER sales_upd

AFTER UPDATE ON sales

FOR EACH ROW

BEGIN

  SET @old_price_sum = 0;

  SET @old_amount_sum = 0;

  SET @old_price_avg = 0;

  SET @old_amount_avg = 0;

  SET @old_sales_cnt = 0;

  SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(amount_avg, 0)

       , IFNULL(sales_cnt, 0)

    FROM sales_mv

   WHERE product_name = OLD.product_name

    INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_sales_cnt

  ;

  SET @new_price_sum = @old_price_sum + (NEW.product_price - OLD.product_price);

  SET @new_amount_sum = @old_amount_sum + (NEW.product_amount - OLD.product_amount);

  SET @new_sales_cnt = @old_sales_cnt;

  SET @new_price_avg = @new_price_sum / @new_sales_count;

  SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;

  REPLACE INTO sales_mv

  VALUES(OLD.product_name, @new_price_sum, @new_amount_sum, IFNULL(@new_price_avg, 0)

       , IFNULL(@new_amount_avg, 0), @new_sales_cnt)

  ;

END;

$$

DELIMITER ;
现在来看结果：

INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);

INSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);

INSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);

INSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);

INSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);

INSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);

DELETE FROM sales WHERE sales_id = 5;

DELETE FROM sales WHERE sales_id = 4;

UPDATE sales SET product_amount = 3 where sales_id = 2;

SELECT * from sales_v;
来看一下结果：

INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);

INSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);

INSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);

INSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);

INSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);

INSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);

DELETE FROM sales WHERE sales_id = 5;

DELETE FROM sales WHERE sales_id = 4;

UPDATE sales SET product_amount = 3 where sales_id = 2;

SELECT * from sales_v;
3.4
具有快速拍摄功能的物化视图

与上面例子的不同之处在于改变并不立即使用，改变存放在日志表中在一定时间之后才更新物化视图。

除了以上的的例子我们需要另外的日志表

CREATE TABLE sales_mvl (

    product_name   VARCHAR(128) NOT NULL

  , product_price  DECIMAL(8,2) NOT NULL

  , product_amount SMALLINT     NOT NULL

  , sales_id       INT UNSIGNED NOT NULL

  , product_ts     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP()

);
不是更新mv日志是填充：

DROP TRIGGER sales_ins;

DELIMITER $$

CREATE TRIGGER sales_ins

AFTER INSERT ON sales

FOR EACH ROW

BEGIN

  INSERT INTO sales_mvl

  VALUES (NEW.product_name, NEW.product_price, NEW.product_amount, NEW.sales_id, NULL);

END;

$$

DELIMITER ;

DROP TRIGGER sales_del;

DELIMITER $$

CREATE TRIGGER sales_del

AFTER DELETE ON sales

FOR EACH ROW

BEGIN

  DELETE FROM sales_mvl

  WHERE sales_id = OLD.sales_id;

END;

$$

DELIMITER ;

DROP TRIGGER sales_upd;

DELIMITER $$

CREATE TRIGGER sales_upd

AFTER UPDATE ON sales

FOR EACH ROW

BEGIN

  UPDATE sales_mvl

     SET product_name = NEW.product_name

       , product_price = NEW.product_price

       , product_amount = NEW.product_amount

       , sales_id = NEW.sales_id

       , product_ts = CURRENT_TIMESTAMP()

  WHERE sales_id = OLD.sales_id;

END;

$$

DELIMITER ;
建立一个更新物化视图的存储过程。可能模式：

l         完全更新（更新至当前）

l         更新（更新到特定时间戳）

l         重建（全部重建清除MV日志表）

DELIMITER $$

CREATE PROCEDURE refresh_mv (

    IN method VARCHAR(16)

  , IN ts TIMESTAMP

  , OUT rc INT

)

BEGIN

IF UPPER(method) = 'REBUILD' THEN

  TRUNCATE TABLE sales_mvl;

  TRUNCATE TABLE sales_mv;

  INSERT INTO sales_mv

  SELECT product_name

      , SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount)

      , COUNT(*)

    FROM sales

  GROUP BY product_name

  ;

ELSEIF UPPER(method) = 'REFRESH FULL' THEN

  REPLACE INTO sales_mv

  SELECT product_name, SUM(price_sum), SUM(amount_sum)

       , SUM(price_sum)/SUM(sales_cnt), SUM(amount_sum)/SUM(sales_cnt)

       , SUM(sales_cnt)

    FROM (

          SELECT product_name, price_sum,  amount_sum, sales_cnt

            FROM sales_mv

          UNION ALL

          SELECT product_name

              , SUM(product_price), SUM(product_amount), COUNT(*)

            FROM sales_mvl

          GROUP BY product_name

        ) x

  GROUP BY product_name

  ;

  TRUNCATE TABLE sales_mvl;

  SET rc = 0;

ELSEIF UPPER(method) = 'REFRESH' THEN

  REPLACE INTO sales_mv

  SELECT product_name, SUM(price_sum), SUM(amount_sum)

       , SUM(price_sum)/SUM(sales_cnt), SUM(amount_sum)/SUM(sales_cnt)

       , SUM(sales_cnt)

    FROM (

          SELECT product_name, price_sum,  amount_sum, sales_cnt

            FROM sales_mv

          UNION ALL

          SELECT product_name

              , SUM(product_price), SUM(product_amount), COUNT(*)

            FROM sales_mvl

          WHERE product_ts < ts

          GROUP BY product_name

        ) x

  GROUP BY product_name

  ;

  DELETE

    FROM sales_mvl

   WHERE product_ts < ts

  ;

  SET rc = 0;

ELSE

  SET rc = 1;

END IF;

END;

$$

DELIMITER ;
检验是否正常工作：

INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);

wait some time

INSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);

wait some time

INSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);

wait some time

INSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);

wait some time

INSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);

wait some time

INSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);

SELECT * from sales_mvl;

SELECT * from sales_v;

CALL refresh_mv('REFRESH', '2006-11-06 10:57:55', @rc);

SELECT * from sales_v;

CALL refresh_mv('REFRESH FULL', NULL, @rc);

SELECT * from sales_v;

CALL refresh_mv('REBUILD', NULL, @rc);

SELECT * from sales_v;
4
结论

上面有说道可以通过存储过程和mysql的触发器去进行更新，不过如果是项目中的一些后台展示功能，不是很紧要的展示功能可以通过java的scheduled执行定时任务，比如，一个小时查询一次新增数据。

MySQL中的触发器（5.0.27）是不是非常快。

大量数据时，物化视图可以提高及时查询速度。

如果插入数据速度上不受限制，这个方法可以提高查询速度。

在大量数据，或者是非内存表、或者内存不够大时，可以提高性能(即，在基础表中查询耗时较大时)

注：感谢小康同学，对本文档的部分翻译

参考文献：

http://www.fromdual.com/mysql-materialized-views

然后还可以通过 Justin Swanhart的开源工具Flexviews（http://code.google.com/p/flexviews/）去实现，Flexviews比完全自己实现的解决方案更惊喜，并且提供了很多不错的功能使得可以更简单的创建和维护视图。

它由下面这些部分组成：

变更数据抓取（Change Data Capture,y CDC）功能，可以读取服务器的二进制日志并且解析相关行的变更。

　　一系列可以帮助创建和管理视图的定义的存储过程。

　　一些可以应用变更到数据库中的物化视图的工具。

　　对比传统的维护汇总表和缓存表的方法，Flexviews 通过提取对源表的更改，可以增量地重新计算物化视图的内容。这意味着不需要通过查询原始数据来更新视图。例如，如果创建了一张汇总表用于计算每个分组的行数，此后增加了一行数据到源表中，Flexviews简单地给相应的组的行数加一即可。同样的技术对其他的聚合函数也有效，例如SUM（）和AVG（）。这实际上是有好处的，基于行的二进制日志包含行更新前后的镜像，所以Flexviews 不仅仅可以获得每行的新值，还可以不需要查找源表就能知道每行数据的旧版本。计算增量数据比从源表中读取数据的效率要高得多。

　　因为版面的限制，这里我们不会完整地探讨怎么使用Flexviews,但是可以给出一个概略。先写出一个SELECT 语句描述想从已经存在的数据库中得到的数据。这可能包含关联和聚合（GROUP BY）。Flexviews 中有一个辅助工具可以转换SQL 语句到Flexviews 的API 调用。Flexviews 会做完所有的脏活、累活：监控数据库的变更并且转换后用于更新存储物化视图的表。现在应用可以简单地查询物化视图来替代查询需要检索的表。

　　Flexviews 有不错的SQL 覆盖范围，包括一些棘手的表达式，你可能没有料到一个工具可以在MySQL 服务器之外处理这些工作。这一点对创建基于复杂SQL 表达式的视图很有用，可以用基于物化视图的简单、快速的查询替换原来复杂的查询。

---

视图

视图（View）是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图其实就是一个select返回的结果集，用于方便我们查询而创建的"临时表"，简化我们的查询语句。

• 视图的优势
  • 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
  • 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

使用视图的大部分情况是为了保障数据安全性，提高查询效率。

创建测试表：

CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `db01`;

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'产品');


DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`class_id`) values 
(1,'张三',1),
(2,'李四',1),
(3,'王五',2),
(4,'赵刘',1),
(5,'钱七',3);

1.1 创建视图

• 语法

CREATE [O
R 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]

• OR REPLACE：如图视图名重复，则表示替换原有视图

• ALGORITHM：表示创建视图时所使用的算法

  • UNDEFINED：默认值，没有指定算法时一般采用merge算法
  • MERGE：合并算法，在基于视图创建新的视图时，将创建视图时所使用的select语句与将要创建新的视图的select语句进行合并处理，效率较高。
  • TEMPTABLE：临时表算法，在基于视图创建新的视图时，先执行原有视图的select语句，然后再执行新视图的select语句，查询两次，效率较低。

示例：

create or replace ALGORITHM=MERGE view test1 as 
select * from user;

创建test1视图，如果此前已经有test1视图，那么替换他，并且该视图的算法为merge。（合并算法）

• DEFINER：指出谁是视图的创建者
  • USER：指定创建视图的用户
  • CURRENT_USER：不指定创建视图的用户（默认是当前操作用户）

示例：

create or replace definer='root' view test2 as 
select * from user;

定义test2视图是root用户创建的。搭配SQL SECURITY一起使用。

• SQL SECURITY：设置操作的视图的安全策略
  • DEFINER：只有创建视图的用户才有权访问
  • INVOKER：任何用户都可以访问（前提必须有操作权限，如select、update权限等）
    示例：

create sql security=invoker view test3 as 
select * from user;

代表任何人都具备操作test3视图的权限。

• WITH：更改视图数据时，检查数据

  • LOCAL：更改视图时，如果更改后的数据不符合当初创建视图时的条件时，则不予修改，并报如下错误：

  CHECK OPTION failed 'dbName.viewName
  

  示例：

  -- 创建视图
  mysql> create view test4 as select * from student where class_id=1 with local check option;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> update test4 set class_id=2 where id=1;
  ERROR 1369 (HY000): CHECK OPTION failed 'db01.test4'
  mysql>
  

  

  LOCAL：只会检查当前修改的条件是否符合当前视图创建时的条件，如果在创建此视图时使用到了别的视图，LOCAL方式不会检查其嵌套的视图。

  小测试：

  mysql> select * from test4;
  +----+--------+----------+
  | id | name   | class_id |
  +----+--------+----------+
  |  1 | 张三   |        1 |
  |  2 | 李四   |        1 |
  |  4 | 赵刘   |        1 |
  +----+--------+----------+
  3 rows in set (0.00 sec)
  
  mysql> create or replace view test5 as select * from test4 where name='张三' with local check option;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> update test5 set class_id=2 where id=1;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

  

  只要修改后的数据满足本次视图创建的条件即可。不需要满足创建此视图所引用的其他视图的创建条件。

  • CASCADED：在视图嵌套的过程中，修改当前视图中的数据，必须满足创建此视图所引用到的所有视图的创建条件。

  示例：

  mysql> select * from student;
  +----+--------+----------+
  | id | name   | class_id |
  +----+--------+----------+
  |  1 | 张三   |        1 |
  |  2 | 李四   |        1 |
  |  3 | 王五   |        2 |
  |  4 | 赵刘   |        1 |
  |  5 | 钱七   |        3 |
  +----+--------+----------+
  5 rows in set (0.00 sec)
  
  mysql> select * from test4;
  +----+--------+----------+
  | id | name   | class_id |
  +----+--------+----------+
  |  1 | 张三   |        1 |
  |  2 | 李四   |        1 |
  |  4 | 赵刘   |        1 |
  +----+--------+----------+
  3 rows in set (0.00 sec)
  
  mysql> create or replace view test5 as select * from test4 where name='张三' with cascaded check option;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> update test5 set class_id=2 where id=1;		
  # 不满足创建test4视图的条件
  ERROR 1369 (HY000): CHECK OPTION failed 'db01.test5'
  mysql> update test5 set name='zs' where id=1;
  # 不满足创建test5视图的条件
  ERROR 1369 (HY000): CHECK OPTION failed 'db01.test5'
  mysql>
  

  

  CASCADED不仅要满足当前视图的创建条件，也需要满足本次创建视图时引用到的其他视图的创建条件。

1.2 修改视图

1）创建时修改

• 语法：

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]

• 示例：

create or replace view test4 as select * from student where class_id=1;

2）使用alter语句更改视图

• 语法：

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]

• 示例：

alter ALGORITHM=TEMPTABLE view test4 as select * from student where class_id=3;

3）更新视图

• 语法：

update viewName set column=newValue  

• 示例：

update test4 set name='qq' where id=5;

有下列内容之一，视图不能做DML操作：

1）select子句中包含distinct

2）select子句中包含组函数

3）select语句中包含group by子句

4）select语句中包含order by子句

5）select语句中包含union 、union all等集合运算符

6）where子句中包含相关子查询

7）from子句中包含多个表

8）如果视图中有计算列，则不能更新

9）如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

1.3 删除视图

删除视图时，只能删除视图的定义，不会删除数据，也就是说不动基表

• 语法：

DROP VIEW [IF EXISTS]   
view_name [, view_name] ...

• 示例：

drop view test1,test2,test3,test4;