mysql 视图

在MySQL中，创建视图的完整语法如下：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

其对应的语法变量信息如下：

[OR REPLACE]

中括号中的OR REPLACE关键字是可选的。如果当前数据库中已经存在指定名称的视图时，没有该关键字，将会提示错误信息；如果使用了OR REPLACE关键字，则当前正在创建的视图会覆盖掉原来同名的视图。

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

ALGORITHM子句是可选的，它表示使用何种算法来处理视图。此外，它并不属于标准SQL的一部分，而是MySQL对标准SQL进行的功能扩展。ALGORITHM可以设置三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，则默认值为UNDEFINED(未定义的)。对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

之所以提供TEMPTABLE选项，是因为TEMPTABLE在创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。

此外，MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果视图包含聚合函数(SUM(), MIN(), MAX(), COUNT()等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL、没有基表的引用文字值(例如：SELECT 'hello';)等结构中的任何一种，将失去一对一的关系，此时必须使用临时表取而代之。

指定视图处理方式

　　MySQL数据库的CREATE/ALTER VIEW语句中的ALGORITHM子句是对标准SQL语句所做的扩展。它的功能是确定MySQL如何处理视图。

　　一般ALGORITHM有三个值：MERGE、TEMPTABLE或UNDEFINCED，默认值是UNDEFINED(当没有指定ALGORITHM子句时)。

MERGE：将查询视图的语句与视图的定义语句合并处理。
TEMPTABLE：视图查询的结果保存到临时表，而后在该临时表基础上执行查询视图的语句；
UNDEFINED：由MySQL选择使用哪种算法，一般会首选MERGE，因为MERGE更有效率，而且TEMPTABLE不支持更新。

　　这些概念听起来都比较抽象，对于生在新中国长在红旗下普遍善良单纯无条件依附强权的无数蚁族来说，可能理解起来非常困难，其实用不着太过自卑，你这不是笨，你只是单纯。

　　下面结合例子来说明白，希望能够帮助理解。先来说说MERGE的情况，例如有一个名为v_merge的视图有下列的定义：

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100;

　　那么当有查询时，它是怎么个合并处理法呢，首先说当执行下列查询时：

SELECT * FROM V_MERGE;

　　MySQL在处理时按照下列的逻辑：

v_merge映射为t；
*映射成vc1,vc2，实际上是t表的c1,c2；
而后增加where语句。

　　那么实际执行的语句就是：

SELECT c1,c2 FROM t where c3>100;

　　如果执行下列语句的话：

SELECT * FROM V_MERGE WHERE vc1<100;

　　对于这个语句，WHERE之前的处理逻辑与前面的示例是相同的，最后再附加上视图的where语句，就变成了：

SELECT c1,c2 FROM t where c3>100 and c1<100

　　按照官方文档上的说法，实际上组合WHERE语句时是遵照这样的格式：

WHERE (select WHERE) AND (view WHERE)

　　不知道大家理解清楚了没有，接下来再看看TEMPTABLE的方式，这个就简单了，我感觉可以将其理解成类似ORACLE物化视图的概念，但在MySQL中它是个临时的物化视图，怎么个临时法呢，就是说物化只存在于查询那一刻---将视图的定义SQL生成实体临时表，对于视图的查询条件会在临时表上进行，包括传统的WHERE子句，以及像MERGE方式无法支持的DISTINCT、GROUP BY、HAVING等。

　　TEMPTABLE方式的一个特点--不可更新，也与其临时的实现特性有关。

　　那么，我们知道了MERGE方式的话无法支持像聚合查询、分组统计等应用，而TEMPTABLE方式的话又non-updatable，我们可能有时候无法确定视图究竟会怎么用，那就可以选择标准的UNDEFINED方式，也是默认的方式，由MySQL自己视情况确定了。

[(column_list)]

(column_list)用于自定义视图中各个字段的名称。如果没有该命令选项，那么通过视图查询到的各个字段的名称和视图所使用到的数据表的字段名称保持一致。下面是一个常见的用于创建视图的SQL语句：

CREATE OR REPLACE VIEW v_user
AS
SELECT id, username FROM user;

由于user表中的字段名称为id和username，因此视图v_user中的两个字段名称也分别默认为id和username。现在，我们将视图v_user中的字段名称分别自定义为uid和uname。

CREATE OR REPLACE VIEW v_user (uid, uname)
AS
SELECT id, username FROM user;

select_statement

select_statement用于指定视图的内容定义。简而言之，这里就是用户自定义的一个SELECT语句。

[WITH [CASCADED | LOCAL] CHECK OPTION]

该选项中的CASCADED为默认值，LOCAL CHECK OPTION用于在可更新视图中防止插入或更新行。由于此选项一般不使用，因此不再赘述，具体信息请参考MySQL官方网站上的相关信息。

如果在创建视图的时候制定了“WITH CHECK OPTION”，那么更新数据时不能插入或更新不符合视图限制条件的记录一般默认加上

删除视图

在MySQL中删除视图的方法非常简单，其详细语法如下：

--删除指定名称的一个或多个视图
DROP VIEW [IF EXISTS]
 view_name [, view_name2]...

其中，关键字IF EXISTS用于防止因视图不存在而提示出错，此时，只有存在该视图才会执行删除操作。DROP VIEW语句可以一次性删除多个视图，只需要在多个视图名称之间以英文逗号隔开即可。如果多个视图存在于不同的数据库中，不数据当前数据库的视图名称之前还必须加上db_name.前缀。

--删除视图v_user
DROP VIEW v_user;

修改视图

请参考创建视图语法中的OR REPLACE关键字，只要具备该关键字的视图创建语句就是修改视图的SQL语句。

查看视图

在MySQL中，show tables不仅可以用于查看当前数据库中存在哪些数据表，同时也可以查看到当前数据库中存在哪些视图。

--执行show tables
mysql> show tables;
--以下是输出结果
+----------------+
| Tables_in_test |
+----------------+
| user           |
| v_user         |
+----------------+
2 rows in set (0.00 sec)


查看视图详情 

查看视图详情的方法有两种，一种是和查看表详情一样使用desc 视图名，另外一种方法是show fields from 视图名：

>DESC 视图名;
或者
>SHOW FIELDS FROM 视图名;

不过，仅仅使用show tables语句，在输出结果中，我们根本无法区分到底哪些才是视图哪些才是真实的数据表(当然，视图的命名我们可以统一约定以"v_"开头)。此时，我们需要使用命令show full tables，该命令可以列出额外的table_type列，如果对应输出行上该列的值为"VIEW"，则表示这是一个视图。

--执行show full tables
mysql> show full tables;
--以下是输出结果
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| user           | BASE TABLE |
| v_user         | VIEW       |
+----------------+------------+
2 rows in set (0.00 sec)

当我们通过上述命令找到了我们所需要的视图之后，我们可以使用如下命令查看创建该视图的详细语句：

show create view view_name

例如，我们使用该命令查看创建视图v_user的SQL语句：

--由于该语句的输出结果较为杂乱，因此使用G命令进行格式化输出
mysql> show create view v_user G;
--以下是格式化的输出结果
*************************** 1. row ***************************
                View: v_user
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_user` AS select `user`.`id` AS `uid`,`user`.`username`
AS `uname` from `user`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

-- ----------------------------
-- View structure for `view_user_course`
-- ----------------------------
DROP VIEW
IF EXISTS `view_user_course`;

CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER 
VIEW `view_user_course` AS (
    SELECT
        `uc`.`id` AS `id`,
        `u`.`name` AS `username`,
        `c`.`name` AS `coursename`
    FROM
        (
            (
                `user` `u`
                LEFT JOIN `user_course` `uc` ON ((`u`.`id` = `uc`.`userid`))
            )
            LEFT JOIN `course` `c` ON ((`uc`.`courseid` = `c`.`id`))
        )
);

几点说明（MySQL中的视图在标准SQL的基础之上做了扩展）：

ALGORITHM=UNDEFINED：指定视图的处理算法；

DEFINER=`root`@`localhost`：指定视图创建者；

SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；

procedure与function、trigger等创建时紧接着CREATE都有个definer可选项，该definer规定了访问该procedure等的安全控制。

4. 存储过程中有个DEFINER
(1) DEFINER的意思是“定义者”，也就是指明此存储过程有哪个用户定义的，它跟存储过程的使用权限无关；

可以说，存储和过程是没有使用限制的，任何人都可以使用存储过程，那么为什么还要加上DEFINER指定存储过

程的定义者呢，因为，存储过程虽然不限制任何人使用，但是它本身的行为必须受到权限限制，也就是存储过程

本身内部具备哪些对数据库的访问权限，而这访问权限便是DEFINER用户对数据库的访问权限。
(2) DEFINER被定义为一个普通的username@hostname用户就不必说了，如果定义为root@%，有什么特别的吗？没

有。只不过数据库中要存在root@%用户，否则创建的时候没事，调用的时候就提示root@% is not registered，

也就是用户不存在。
(3) DEFINER也可以省略掉，这样存储过程默认的定义者是root@localhost，而一般数据库在安装的时候都会有

一个root@localhost用户，所以，该存储过程也能正常在不同的机器上被使用。
5. 既然上述存储过程任何人都以访问，那么它本身运行时的访问权限怎么限制呢。可以通过SQL SECURITY

INVOKER ，也就是，其本身的访问权限由调用者权限设置。
6.命令分隔符DELIMITER

MySQL默认是以分号作为两个命令的分割点的。但有些命令块中包含分号，比如存储过程或触发器的定义中。为了使MySQL不把命令块中的分号误认做命令的分割点，需要在执行这种命令块前临时改一下命令分隔符。尤其注意，SQLYog里面写存储过程的时候，必须自行定义命令分隔符。

SQL Security指定DEFINER 表示执行者拥有创建者的权限

当然可以调用此程序, 而且执行这个程序时拥用定义者(root)的权限

invoker 调用-- 按调用者权限来执行这个程序, 默认状况
definer 定义-- 按定义者权限来执行这个程序.

为什么要有SQL DEFINER, 以一个例子说明一下:
假如一个普通权限的用户, 你不想让它拥有超级者的权限, 然而要让它在某种行动拥有超级者的权限, 你就把这种行动包装成一个存储过程,并定义为SQL DEFINNER ="root".
(比如某个表的某行所有人可读, 其他行只有超级者可读)

一、DEFINER

如：创建procedure

说明：

上面示例指定definer为用户'admin'@'localhost'，所以任意用户A访问该PROCEDURE 时，能否成功取决于A是否有调用该PROCEDURE的权限，以及definer是否有procedure中的SELECT的权限。

二、SQL SECURITY

DEFINER默认为当前用户，也可指定其他用户。如果想通过访问者来判断是否具有访问该PROCEDURE 的权限，则可用SQL SECURITY指定。

说明：

该示例虽然指定了DEFINER ，但同时也指定了SQL SECURITY 类型为INVOKER ，SQL SECURITY 优先级高，所以安全类型为INVOKER，用户能否访问取决于用户是否有执行该PROCEDURE 的权限及该PROCEDURE 中的SELECT 权限（与select操作的表有关）。

当然，也可用SQL SECURITY 指定DEFINER：SQL SECURITY DEFINER