MySQL数据库：视图

适用环境MySQL 8.0。视图可以理解为“有名字、可重复查询的SELECT”主要用于封装查询、限制可见数据和提供稳定的查询接口1. 视图视图View是一张虚拟表它的内容来自一个或多个基表、其他视图或表达式的查询结果创建视图时MySQL 主要保存的是视图名称、列信息和SELECT定义而不是另存一份结果数据查询视图执行视图定义student 基表score 基表视图有以下特点查询视图时MySQL根据视图定义读取当时的基表数据基表数据改变后再次查询视图通常会看到新结果删除视图只删除查询定义不会删除基表及其数据普通视图不是数据副本、备份或快照也不会自动提高查询速度视图定义仍会保存在数据字典中所以“视图不存结果数据”不等于完全不占任何空间。例如下面的视图只展示学生编号、姓名和年龄createviewv_student_basicasselectid,name,agefromstudent;查询视图与查询普通表的写法相同select*fromv_student_basic;2. 视图优点简化复杂查询多表连接、筛选和计算可以封装到视图中。应用以后只查询视图不必反复编写同一段复杂 SQL。限制可见的行和列视图可以不暴露密码、身份证号等敏感列也可以通过WHERE只展示某些行。但这只有与权限控制结合才真正安全如果用户仍拥有基表的SELECT权限他依然可以绕过视图直接查询基表。提供相对稳定的查询接口应用统一查询视图。底层表调整后有时只需重新定义视图即可保持视图的列名不变减少应用改动。但这种独立性并非绝对删除视图依赖的列仍可能使视图失效。统一名称和业务口径视图可以为列起更清楚的名称并把“总分如何计算”“有效记录如何筛选”等规则集中在一处避免不同程序写出不同口径。3. 创建视图3.1 基本语法createview视图名[(视图列名列表)]asselect查询列from表名[where条件];较完整的 MySQL 语法为create[orreplace][algorithm{undefined|merge|temptable}][definer用户][sqlsecurity {definer|invoker}]view视图名[(视图列名列表)]asselect_statement[with[cascaded|local]checkoption];algorithm这个是视图执行算法MySQL 查询视图时有三种处理方式。undefined默认、merge合并、tempable临时表definer指定创建这个视图的人。如definerrootlocalhost表示该视图属于root用户sql security表示查询视图时使用谁的权限definer 则表示使用创建视图用户的权限invoker 则表示当前查询用户自己的权限with check option防止通过视图修改出视图范围之外的数据cascaded检查所有关联视图local只检查当前视图示例createorreplacealgorithmmergedefinerrootlocalhostsqlsecuritydefinerviewv_java_student(student_id,student_name,class_name)asselects.id,s.name,c.namefromstudent_design2 sjoinclass_design2 conc.ids.class_idwherec.nameJava001班withcascadedcheckoption;3.2 使用别名确定视图列名以下示例连接学生、班级、课程和成绩表。显式JOIN ... ON ...能把连接条件与普通筛选条件分开比逗号连接更容易阅读也能减少漏写连接条件造成笛卡尔积的风险。createviewv_student_scoreasselects.idasstudent_id,s.nameasstudent_name,s.sno,s.age,s.gender,s.enroll_date,c.idasclass_id,c.nameasclass_name,co.idascourse_id,co.nameascourse_name,sc.idasscore_id,sc.scorefromstudent sjoinclass conc.ids.class_idjoinscore sconsc.student_ids.idjoincourse coonco.idsc.course_id;来自不同表的列可能同名例如四张表都可能有id。视图中的列名必须唯一所以要用AS改成student_id、class_id等明确名称。3.3 在视图名后指定列名也可以统一列出视图的列名createviewv_student_name_age(student_id,student_name,student_age)asselectid,name,agefromstudent;括号中的名称数量必须与SELECT返回的列数完全相同。两种命名方法选择一种即可复杂查询通常使用AS别名更直观因为名称紧挨对应表达式。3.4 创建时的注意事项视图与表属于同一数据库名称空间不能在同一数据库中同名。建议明确写出字段不要长期依赖SELECT *。视图定义在创建时确定基表以后新增列不会自动加入既有视图。如果基表的依赖列被删除或改名查询视图可能报错需要重新定义视图。不要依赖视图定义中的ORDER BY保证顺序。查询视图时应在最外层明确排序外层自己的ORDER BY会取代视图内的排序。CREATE VIEW是 DDL会触发隐式提交不要把它混入需要回滚的业务事务。4. 查询和使用视图视图可出现在普通表能够出现的许多查询位置并可继续筛选、连接、分组和排序-- 查询全部视图数据select*fromv_student_score;-- 对视图结果继续筛选和排序selectstudent_name,course_name,scorefromv_student_scorewherescore90orderbyscoredesc;-- 视图与真实表连接selectv.student_name,v.course_name,v.score,s.enroll_datefromv_student_score vjoinstudent sons.idv.student_id;视图也可以隐藏查询细节。例如只对外提供姓名和总分createviewv_student_total_pointsasselects.idasstudent_id,s.nameasstudent_name,sum(sc.score)astotal_pointsfromstudent sjoinscore sconsc.student_ids.idgroupbys.id,s.name;selectstudent_name,total_pointsfromv_student_total_pointsorderbytotal_pointsdesc;用户只能从该视图获得定义中已有的列不能临时查询未被视图暴露的学号或各科明细。若确实需要这些字段应修改视图、另建视图或在有权限时查询基表。5. 视图与基表数据的关系5.1 修改基表会影响视图结果updatescoresetscore99wherestudent_id1andcourse_id1;select*fromv_student_scorewherestudent_id1andcourse_id1;UPDATE修改的是score基表。视图没有独立保存旧结果所以再次查询时会显示修改后的成绩。5.2 修改可更新视图会影响基表创建一个行与基表行一一对应的简单视图createviewv_class_one_studentasselectid,name,age,class_idfromstudentwhereclass_id1;updatev_class_one_studentsetage20whereid1;如果这个视图满足可更新条件这条语句最终修改的是student基表中id1的记录。因此能看出视图不是基表的副本通过视图写数据同样需要事务、权限和条件控制。6. 可更新视图视图能够被UPDATE、DELETE或INSERT操作的核心条件是视图中的一行能够明确对应到底层表中的一行。最容易更新的是“单表 简单列 普通WHERE”视图。以下结构通常会使视图不可更新聚合函数或窗口函数如SUM()、COUNT()、AVG()DISTINCTGROUP BY、HAVINGUNION、UNION ALL查询列表中的子查询某些多表连接在FROM中引用不可更新视图只查询常量没有可对应的基表行明确使用ALGORITHM TEMPTABLE。例如v_student_total_points使用了SUM()和GROUP BY。一条总分记录由多条成绩记录合成MySQL 无法判断“把总分改成 500”应当修改哪一科所以它只适合查询。ORDER BY可以出现在视图定义中但不能把它简单记成“只要有ORDER BY视图就一定不可更新”。可更新性取决于完整定义和处理方式为了职责清楚可写视图通常不在内部排序而在查询视图时排序。“可更新”也不一定代表“可插入”。通过视图插入时视图还要能为基表中所有没有默认值的必填列提供值而且目标列通常必须是简单的基表列引用。检查 MySQL 记录的可更新状态selecttable_name,is_updatablefrominformation_schema.viewswheretable_schemadatabase();IS_UPDATABLEYES表示该视图可用于某些更新操作不表示任意INSERT、UPDATE、DELETE都必然合法实际操作还受列、连接方式和权限等条件限制。7. WITH CHECK OPTION普通可更新视图有一个容易忽略的问题通过视图修改数据后新数据可能不再满足视图的WHERE条件于是该行会从视图中“消失”。createviewv_class_one_studentasselectid,name,age,class_idfromstudentwhereclass_id1;-- 若没有检查选项这次修改可能成功随后该行不再出现在视图中updatev_class_one_studentsetclass_id2whereid1;在可更新视图后加入WITH CHECK OPTION可以阻止通过该视图写入不再满足视图条件的数据createorreplaceviewv_class_one_studentasselectid,name,age,class_idfromstudentwhereclass_id1withcheckoption;此时把class_id改为2会失败因为修改后的记录不符合class_id1。它既检查UPDATE后的行也检查通过视图INSERT的行。视图基于其他视图时还可指定检查范围WITH LOCAL CHECK OPTION检查当前视图的条件并按下层视图原有的检查设置继续处理WITH CASCADED CHECK OPTION检查当前视图及所有下层视图的条件不写LOCAL或CASCADED时默认是CASCADED。没有嵌套视图时直接写WITH CHECK OPTION最容易理解。8. 修改、查看与删除视图8.1 修改定义createorreplaceviewv_student_basicasselectid,name,age,genderfromstudent;CREATE OR REPLACE VIEW在视图不存在时创建在已存在时替换。也可以使用alterviewv_student_basicasselectid,name,age,genderfromstudent;ALTER VIEW要求目标视图已经存在。两种方式都是重新定义视图不会直接修改基表数据它们属于 DDL同样可能隐式提交当前事务。8.2 查看视图-- 查看当前数据库中的视图showfulltableswheretable_typeVIEW;-- 查看完整创建语句排查算法、安全模式和检查选项showcreateviewv_student_basic;-- 查看视图对外提供的列descv_student_basic;-- 检查视图依赖是否仍然有效checktablev_student_basic;还可查询更完整的元数据selecttable_name,is_updatable,check_option,security_typefrominformation_schema.viewswheretable_schemadatabase();8.3 删除视图dropviewifexistsv_student_basic;-- 一次删除多个视图dropviewifexistsv_student_score,v_student_total_points;IF EXISTS可避免视图不存在时直接报错。删除视图不会删除student、score等基表数据但如果其他视图依赖被删除的视图依赖者可能变得不可用。DROP VIEW也是会隐式提交的 DDL。9. 视图处理原理MySQL 处理视图主要有三种算法算法基本原理主要特点MERGE把外层查询与视图定义合并成一个查询通常更容易继续优化满足其他条件时可更新TEMPTABLE先把视图结果放入本次语句使用的内部临时表再查询临时结果该视图不可更新临时结果不是永久保存的物化视图UNDEFINED由 MySQL 选择可能优先尝试MERGE默认思路通常不必手动指定例如createalgorithmmergeviewv_adult_studentasselectid,name,agefromstudentwhereage18;执行select*fromv_adult_studentwhereid100;采用MERGE时可以近似理解为 MySQL 合并两个条件后查询基表selectid,name,agefromstudentwhereage18andid100;视图不会自动拥有索引普通视图也不能像表一样单独创建索引。查询性能主要取决于展开后的 SQL、基表索引、数据量和优化器选择应使用EXPLAIN分析最终查询不能把“创建视图”等同于“查询加速”。10. 视图的安全上下文完整语法中的SQL SECURITY决定执行视图时按照谁的权限检查底层对象SQL SECURITY DEFINER按视图定义者的权限执行是默认值SQL SECURITY INVOKER按调用视图的用户权限执行。createsqlsecurityinvokerviewv_student_publicasselectid,name,agefromstudent;要使用视图保护数据应让普通用户只有所需视图的权限而没有敏感基表的直接权限。仅仅不把敏感列写进视图并不能阻止一个本来就能查询基表的用户。参考MySQL 8.0CREATE VIEWMySQL 8.0视图处理算法MySQL 8.0可更新与可插入视图MySQL 8.0WITH CHECK OPTIONMySQL 8.0视图元数据MySQL 8.0DROP VIEW以上是我关于MySQL的笔记分享感谢你读到这里这也是我学习路上的一个小小记录。