MySQL 分析CREATE ALGORITHM=UNDEFINED DEFINER = root@localhost SQL SECURITY DEFINER VIEW的各个参数的含义及使用方法|极客教程

MySQL 分析CREATE ALGORITHM=UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW的各个参数的含义及使用方法

MySQL是常用的关系型数据库管理系统。在MySQL中，用户除了可以创建表之外，还可以创建视图。视图是由一个或多个select语句组合而成的虚拟表。视图与表的主要区别在于，视图并不直接存储数据，而是对数据的一种逻辑展现形式。同时，由于视图可以使用select语句来筛选、过滤数据，因此使用视图可以简化查询过程，使查询结果更加准确。

CREATE ALGORITHM=UNDEFINED DEFINER = root@localhost SQL SECURITY DEFINER VIEW指的是视图创建的语法。其中，CREATE表示创建视图；ALGORITHM指定了视图的算法类型，UNDEFINED表示采用默认的视图算法；DEFINER指定了创建视图的用户，root@localhost表示由root用户在localhost上创建；SQL SECURITY指定了视图所采用的安全性，DEFINER表示视图的安全性与创建者一致。

本文将从视图的基本语法开始讲起，然后分析CREATE ALGORITHM=UNDEFINED DEFINER = root@localhost SQL SECURITY DEFINER VIEW的各个参数的含义及使用方法，最后结合实际案例进行分析。

阅读更多：MySQL 教程

视图的基本语法

视图的创建语法如下：

CREATE VIEW [IF NOT EXISTS] view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

其中，CREATE VIEW表示创建视图的语句；view_name表示视图的名称；column_list表示视图中的列名列表，可以省略；select_statement表示由select语句组成的视图定义；WITH CHECK OPTION表示该视图可以执行更新操作时的限制条件。

以下是一个简单的例子：

CREATE VIEW empview AS SELECT empno, ename, sal FROM emp WHERE deptno = 10;

该语句将查询员工号、姓名、薪水信息，并将结果储存为视图empview。其中，SELECT语句定义了视图的数据来源。视图创建成功后，我们可以使用SELECT语句查询empview，来看到只包含部门编号为10的员工信息。

CREATE ALGORITHM=UNDEFINED DEFINER的详细说明

CREATE ALGORITHM的参数

CREATE ALGORITHM用于指定创建视图时所采用的算法类型。MySQL支持三种算法：MERGE、TEMPTABLE和UNDEFINED。其中，MERGE算法是默认算法，它将视图定义与查询语句合并，执行查询时一次性获取视图定义的数据；TEMPTABLE算法则是将视图的数据复制到临时表中，执行查询时从临时表中获取数据；UNDEFINED算法则是由MySQL根据查询情况自动选择合适的算法。

如果使用UNDEFINED算法，则定义视图时不需要指定算法类型。例如：

CREATE VIEW empview AS SELECT empno, ename, sal FROM emp WHERE deptno = 10;

即为采用默认算法创建视图empview。如需设置算法类型，则应按照以下方式编写CREATE VIEW语句：

CREATE ALGORITHM = algorithm_type VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

例如，如下语句将定义一个使用TEMPTABLE算法的视图：

CREATE ALGORITHM=TEMPTABLE VIEW empview_temp AS SELECT empno, ename, sal FROM emp WHERE deptno = 10;

DEFINER参数

DEFINER参数指定了视图的创建者。DEFINER可以是任意一个MySQL用户。在视图被查询时，该用户所拥有的权限将被应用于该视图。若DEFINER被省略，则表示视图创建者默认为当前用户，即与CREATE VIEW语句所在用户一致。DEFINER通常指定了该视图的安全性，这将影响用户在更新视图时所能够执行的操作。

如果需要修改视图的DEFINER，可以使用ALTER VIEW语句进行修改：

ALTER VIEW view_name DEFINER = new_user;

SQL SECURITY参数

SQL SECURITY参数用于指定视图的安全性。该参数接受两个值：DEFINER和INVOKER。DEFINER表示视图的安全性与创建者一致，即在更新视图时，所执行的操作与创建者所拥有的权限一致；INVOKER表示视图的安全性与执行者一致，即在更新视图时，所执行的操作与执行者所拥有的权限一致。

例如，以下语句创建了一个SQL SECURITY DEFINER的视图：

CREATE SQL SECURITY DEFINER VIEW empview_definer AS SELECT empno, ename, sal FROM emp WHERE deptno = 10;

在更新视图时，执行者将受到视图创建者的权限限制。

实例分析

假设我们有一个学生信息表student，其中包含学号（stu_id）、姓名（stu_name）、性别（stu_gender）、年龄（stu_age）和学院（stu_college）等信息。现在，我们需要分析学生数据中各学院的男女比例。以下是我们的分析过程：

首先，我们可以使用如下语句创建学生信息视图：

CREATE ALGORITHM=UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW stu_info AS
SELECT stu_id, stu_name, stu_gender, stu_age, stu_college FROM student;

表示视图采用默认算法，并由root用户在localhost上创建，采用视图创建者的权限限制进行更新。

然后，我们可以使用COUNT函数结合GROUP BY子句统计每个学院中男女学生的数量：

SELECT stu_college, stu_gender, COUNT(*) AS num FROM stu_info GROUP BY stu_college, stu_gender;

最后，我们可以使用CASE语句将统计结果进行格式化输出：

SELECT stu_college, 
  SUM(CASE WHEN stu_gender = '男' THEN num ELSE 0 END) AS male_num,
  SUM(CASE WHEN stu_gender = '女' THEN num ELSE 0 END) AS female_num
FROM 
  (SELECT stu_college, stu_gender, COUNT(*) as num FROM stu_info GROUP BY stu_college, stu_gender) AS temp
GROUP BY
  stu_college;

这样，我们就得到了学生数据中各学院的男女比例。

总结

本文介绍了MySQL中CREATE ALGORITHM=UNDEFINED DEFINER = root@localhost SQL SECURITY DEFINER VIEW的各个参数及其使用方法，并结合一个实例进行了演示。视图是MySQL中常见的一种数据结构，可用于简化查询过程，使查询结果更加准确。我们应根据实际需求，灵活地运用视图以提高查询效率。