尧图网站建设 
视图一行数据都不存我为什么还敢把核心报表全压在它上面有段三表连接加聚合的统计 SQL被我抄进了十几个报表和定时任务底表字段一改名全局搜索改了一下午还是漏挂了两个生产报表。这篇把视图View的创建、修改、删除、可更新判定一次讲透看完你能把重复 SQL 收敛到一处也能避开我踩过的四个坑。一、场景同一段 SQL 抄了十几遍改一处漏一片报表要展示员工、部门、城市还要带部门平均工资原始写法是一段三表连接加窗口函数每个用到它的服务都得抄一遍。SELECTe.employee_id,e.last_name,d.department_name,l.city,AVG(e.salary)OVER(PARTITIONBYe.department_id)ASdept_avg_salaryFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;这段 SQL 出现在报表服务、定时任务、导出接口里。连接逻辑一改十几处都得同步更麻烦的是每个开发抄的版本还略有出入统计口径慢慢就对不上了。问题就变成同一段查询逻辑能不能只写一份、处处复用二、踩坑视图上线后四个坑轮流找上门我把它封成视图确实省事可新坑在一周内就凑齐了。坑现象触发时机底表改列视图失效查询视图直接报列不存在Oracle 中视图变 INVALID基础表 DROP / RENAME 列视图仍引用旧列视图写不进去报目标表不可更新、非键保留表视图含多表连接或聚合却执行 UPDATE / INSERT改完视图权限丢了业务账号突然查不了视图用 DROP 再 CREATE 的方式改视图授权未重建视图套视图性能崩简单查询嵌套四五层执行计划巨大上层视图再建视图优化器无法合并这四个坑我都在生产环境背过。底表改列是结构问题视图写不进去是定义问题授权丢失是运维习惯问题嵌套视图是性能问题它们的根因都指向同一件事——视图到底是个什么对象三、底层原理数据库里根本没有这张“表”视图View是存储在数据字典Data Dictionary里的一条 SELECT 语句对外呈现为虚拟表Virtual Table。普通视图不保存任何数据行只保存定义本身。查询视图时数据库执行视图合并View Merging把视图定义中的 SELECT 与外层查询拼接改写再基于基础表Base Table生成执行计划。整个过程对调用方透明你以为在查一张表引擎查的还是底表三者的行为差别由此而来。对比项基础表视图子查询是否存数据存占物理空间不存只存定义不存生命周期持久持久可反复引用只在当前语句内能否独立授权能能不能结构变更影响涉及数据迁移依赖底表可能失效改完即走注意查视图就是临时执行一段被保存的 SELECT物理空间里没有这张表只有这句话。四、解决方案建、改、删、授权一次做对4.1 创建视图标准语法如下方括号为可选项。CREATEVIEWview_name[(column_name[,...])]ASSELECT_statement[WITHCHECKOPTION];把开头那段查询收敛成视图连接逻辑从此只有一份。CREATEVIEWemp_location_infoASSELECTe.employee_id,e.last_name,d.department_name,l.cityFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;之后所有报表都查同一处还可以直接追加条件。SELECT*FROMemp_location_infoWHEREcitySeattle;需要固定列名、屏蔽底层列名变化时显式写出列清单。CREATEVIEWemp_location_info_v2(emp_id,emp_name,dept_name,city)ASSELECTe.employee_id,e.last_name,d.department_name,l.cityFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;聚合统计也能封装部门薪资汇总只需维护一次。CREATEVIEWdept_salary_summaryASSELECTdepartment_id,COUNT(*)ASemp_count,AVG(salary)ASavg_salary,MAX(salary)ASmax_salaryFROMemployeesGROUPBYdepartment_id;4.2 修改视图只替换定义别裸 DROP改视图有三种路径权限结果完全不同。方式适用数据库授权是否保留CREATE OR REPLACE VIEWMySQL、Oracle保留ALTER VIEWSQL Server、MySQL保留DROP CREATE通用丢失需重新授权MySQL、Oracle 用替换写法。CREATEORREPLACEVIEWemp_location_infoASSELECTe.employee_id,e.last_name,d.department_name,l.city,l.state_provinceFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;SQL Server 用 ALTER 写法。ALTERVIEWemp_location_infoASSELECT...;替换只改定义视图对象本身没被删除GRANT 出去的权限还在DROP 再 CREATE 属于新建对象旧授权不会跟着回来。4.3 删除视图DROPVIEWview_name;视图可能不存在、不想让脚本报错时用 IF EXISTS。DROPVIEWIFEXISTSview_name;删视图只删定义基础表的数据一行不动但其他视图、存储过程若引用了它会变成失效状态需要重新编译或修复。4.4 用视图做行列权限隔离employees 表含工资等敏感列。给外部账号只开放编号、姓名、部门三列先建视图。CREATEVIEWemp_public_infoASSELECTemployee_id,last_name,department_idFROMemployees;只授权视图不授权基础表。GRANTSELECTONemp_public_infoTOuser_a;限制行同理只允许看到 10 号部门的数据。CREATEVIEWemp_dept10ASSELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREdepartment_id10;4.5 可更新视图与 WITH CHECK OPTION视图支持 INSERT、UPDATE、DELETE要同时满足下列条件。条件要求数据来源仅基于单个基础表禁止结构无 GROUP BY、HAVING、聚合函数、DISTINCT、UNION列要求无计算列包含主键或唯一键用于定位行带 WHERE 条件的可更新视图加上检查选项WITH CHECK OPTION可以防止写入后数据“逃出”视图范围。CREATEVIEWv_dept10_checkASSELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREdepartment_id10WITHCHECKOPTION;注意改视图只换定义DROP 再 CREATE 会把授权一起带走改视图的默认动作是替换不是重建。五、实操验证判定、拦截、依赖三个动作5.1 先判定视图能不能写拿到一个陌生视图按下面的顺序过一遍定义。┌────────────────────────────────────┐ │ 检查视图定义 │ └──────────────┬─────────────────────┘ ▼ ┌──────────────────────────────────────┐ │ 是否命中以下任一项 │ │ · 多表 JOIN / 子查询 │ │ · GROUP BY / 聚合函数 / HAVING │ │ · DISTINCT / UNION │ │ · 计算列 / 伪列 │ └──────────────┬───────────────────────┘ │ ┌───────┴────────┐ ▼ 命中任一项 ▼ 全部没有且含主键/唯一键 ┌───────────────┐ ┌──────────────┐ │ 只读不可更新 │ │ 可更新视图 │ └───────────────┘ └──────────────┘5.2 验证 CHECK OPTION 的拦截视图内部调整工资部门没变正常执行。UPDATEv_dept10_checkSETsalary12000WHEREemployee_id200;Query OK, 1 row affected更新后仍满足 department_id 10语句放行。尝试把部门改成 20。UPDATEv_dept10_checkSETdepartment_id20WHEREemployee_id200;ERROR 1369 (HY000): CHECK OPTION failed hr.v_dept10_check更新后该行不再属于视图被 WITH CHECK OPTION 拦截数据未变更。5.3 动底表前先查依赖基础表改列前先确认哪些视图引用了它各库查询入口如下。数据库依赖/定义查询对象OracleUSER_DEPENDENCIES、ALL_DEPENDENCIESSQL Serversys.sql_expression_dependenciesMySQLinformation_schema.VIEWSPostgreSQLpg_depend、information_schema.view_table_usageMySQL 下直接查视图定义。SELECTtable_name,view_definitionFROMinformation_schema.viewsWHEREtable_schemahr;变更窗口里先跑一遍依赖查询再决定改列还是先替换视图能省掉一次视图失效告警。注意视图能不能写定义说了算写不进去先查 SELECT 结构别硬怼。六、总结延伸什么时候该用什么时候别碰场景建议多表连接、聚合统计反复出现用视图封装统一口径敏感列、敏感行需要隔离用视图 只授视图权限表结构变更、旧系统兼容用视图维持原接口高频复杂查询、性能敏感普通视图无效考虑物化视图视图上再套多层视图别这么干合并难度和性能都会失控普通视图不存数据每次查询动态展开物化视图Materialized View会把结果集真实存下来用刷新换性能是数据仓库里的主力。注意视图是权限边界不是安全边界是逻辑封装不是性能优化。参考链接MySQL 8.4 Reference Manual — CREATE VIEWOracle Database 19c — CREATE VIEWSQL Server — CREATE VIEWPostgreSQL — CREATE VIEW术语速查表术语英文解释视图View存储在数据字典中的 SELECT 定义虚拟表基础表Base Table真实存放数据的表视图的数据来源数据字典Data Dictionary存储数据库对象定义的系统表集合视图合并View Merging查询视图时将视图定义与外层 SQL 拼接改写检查选项WITH CHECK OPTION限制通过视图写入后数据必须仍满足视图条件物化视图Materialized View实际存储查询结果的视图以刷新换性能行级安全Row Level Security按行控制数据可见性的数据库安全策略 mysql数据库视图笔记：创建、修改、删除与适用场景
视图一行数据都不存我为什么还敢把核心报表全压在它上面有段三表连接加聚合的统计 SQL被我抄进了十几个报表和定时任务底表字段一改名全局搜索改了一下午还是漏挂了两个生产报表。这篇把视图View的创建、修改、删除、可更新判定一次讲透看完你能把重复 SQL 收敛到一处也能避开我踩过的四个坑。一、场景同一段 SQL 抄了十几遍改一处漏一片报表要展示员工、部门、城市还要带部门平均工资原始写法是一段三表连接加窗口函数每个用到它的服务都得抄一遍。SELECTe.employee_id,e.last_name,d.department_name,l.city,AVG(e.salary)OVER(PARTITIONBYe.department_id)ASdept_avg_salaryFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;这段 SQL 出现在报表服务、定时任务、导出接口里。连接逻辑一改十几处都得同步更麻烦的是每个开发抄的版本还略有出入统计口径慢慢就对不上了。问题就变成同一段查询逻辑能不能只写一份、处处复用二、踩坑视图上线后四个坑轮流找上门我把它封成视图确实省事可新坑在一周内就凑齐了。坑现象触发时机底表改列视图失效查询视图直接报列不存在Oracle 中视图变 INVALID基础表 DROP / RENAME 列视图仍引用旧列视图写不进去报目标表不可更新、非键保留表视图含多表连接或聚合却执行 UPDATE / INSERT改完视图权限丢了业务账号突然查不了视图用 DROP 再 CREATE 的方式改视图授权未重建视图套视图性能崩简单查询嵌套四五层执行计划巨大上层视图再建视图优化器无法合并这四个坑我都在生产环境背过。底表改列是结构问题视图写不进去是定义问题授权丢失是运维习惯问题嵌套视图是性能问题它们的根因都指向同一件事——视图到底是个什么对象三、底层原理数据库里根本没有这张“表”视图View是存储在数据字典Data Dictionary里的一条 SELECT 语句对外呈现为虚拟表Virtual Table。普通视图不保存任何数据行只保存定义本身。查询视图时数据库执行视图合并View Merging把视图定义中的 SELECT 与外层查询拼接改写再基于基础表Base Table生成执行计划。整个过程对调用方透明你以为在查一张表引擎查的还是底表三者的行为差别由此而来。对比项基础表视图子查询是否存数据存占物理空间不存只存定义不存生命周期持久持久可反复引用只在当前语句内能否独立授权能能不能结构变更影响涉及数据迁移依赖底表可能失效改完即走注意查视图就是临时执行一段被保存的 SELECT物理空间里没有这张表只有这句话。四、解决方案建、改、删、授权一次做对4.1 创建视图标准语法如下方括号为可选项。CREATEVIEWview_name[(column_name[,...])]ASSELECT_statement[WITHCHECKOPTION];把开头那段查询收敛成视图连接逻辑从此只有一份。CREATEVIEWemp_location_infoASSELECTe.employee_id,e.last_name,d.department_name,l.cityFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;之后所有报表都查同一处还可以直接追加条件。SELECT*FROMemp_location_infoWHEREcitySeattle;需要固定列名、屏蔽底层列名变化时显式写出列清单。CREATEVIEWemp_location_info_v2(emp_id,emp_name,dept_name,city)ASSELECTe.employee_id,e.last_name,d.department_name,l.cityFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;聚合统计也能封装部门薪资汇总只需维护一次。CREATEVIEWdept_salary_summaryASSELECTdepartment_id,COUNT(*)ASemp_count,AVG(salary)ASavg_salary,MAX(salary)ASmax_salaryFROMemployeesGROUPBYdepartment_id;4.2 修改视图只替换定义别裸 DROP改视图有三种路径权限结果完全不同。方式适用数据库授权是否保留CREATE OR REPLACE VIEWMySQL、Oracle保留ALTER VIEWSQL Server、MySQL保留DROP CREATE通用丢失需重新授权MySQL、Oracle 用替换写法。CREATEORREPLACEVIEWemp_location_infoASSELECTe.employee_id,e.last_name,d.department_name,l.city,l.state_provinceFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_id;SQL Server 用 ALTER 写法。ALTERVIEWemp_location_infoASSELECT...;替换只改定义视图对象本身没被删除GRANT 出去的权限还在DROP 再 CREATE 属于新建对象旧授权不会跟着回来。4.3 删除视图DROPVIEWview_name;视图可能不存在、不想让脚本报错时用 IF EXISTS。DROPVIEWIFEXISTSview_name;删视图只删定义基础表的数据一行不动但其他视图、存储过程若引用了它会变成失效状态需要重新编译或修复。4.4 用视图做行列权限隔离employees 表含工资等敏感列。给外部账号只开放编号、姓名、部门三列先建视图。CREATEVIEWemp_public_infoASSELECTemployee_id,last_name,department_idFROMemployees;只授权视图不授权基础表。GRANTSELECTONemp_public_infoTOuser_a;限制行同理只允许看到 10 号部门的数据。CREATEVIEWemp_dept10ASSELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREdepartment_id10;4.5 可更新视图与 WITH CHECK OPTION视图支持 INSERT、UPDATE、DELETE要同时满足下列条件。条件要求数据来源仅基于单个基础表禁止结构无 GROUP BY、HAVING、聚合函数、DISTINCT、UNION列要求无计算列包含主键或唯一键用于定位行带 WHERE 条件的可更新视图加上检查选项WITH CHECK OPTION可以防止写入后数据“逃出”视图范围。CREATEVIEWv_dept10_checkASSELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREdepartment_id10WITHCHECKOPTION;注意改视图只换定义DROP 再 CREATE 会把授权一起带走改视图的默认动作是替换不是重建。五、实操验证判定、拦截、依赖三个动作5.1 先判定视图能不能写拿到一个陌生视图按下面的顺序过一遍定义。┌────────────────────────────────────┐ │ 检查视图定义 │ └──────────────┬─────────────────────┘ ▼ ┌──────────────────────────────────────┐ │ 是否命中以下任一项 │ │ · 多表 JOIN / 子查询 │ │ · GROUP BY / 聚合函数 / HAVING │ │ · DISTINCT / UNION │ │ · 计算列 / 伪列 │ └──────────────┬───────────────────────┘ │ ┌───────┴────────┐ ▼ 命中任一项 ▼ 全部没有且含主键/唯一键 ┌───────────────┐ ┌──────────────┐ │ 只读不可更新 │ │ 可更新视图 │ └───────────────┘ └──────────────┘5.2 验证 CHECK OPTION 的拦截视图内部调整工资部门没变正常执行。UPDATEv_dept10_checkSETsalary12000WHEREemployee_id200;Query OK, 1 row affected更新后仍满足 department_id 10语句放行。尝试把部门改成 20。UPDATEv_dept10_checkSETdepartment_id20WHEREemployee_id200;ERROR 1369 (HY000): CHECK OPTION failed hr.v_dept10_check更新后该行不再属于视图被 WITH CHECK OPTION 拦截数据未变更。5.3 动底表前先查依赖基础表改列前先确认哪些视图引用了它各库查询入口如下。数据库依赖/定义查询对象OracleUSER_DEPENDENCIES、ALL_DEPENDENCIESSQL Serversys.sql_expression_dependenciesMySQLinformation_schema.VIEWSPostgreSQLpg_depend、information_schema.view_table_usageMySQL 下直接查视图定义。SELECTtable_name,view_definitionFROMinformation_schema.viewsWHEREtable_schemahr;变更窗口里先跑一遍依赖查询再决定改列还是先替换视图能省掉一次视图失效告警。注意视图能不能写定义说了算写不进去先查 SELECT 结构别硬怼。六、总结延伸什么时候该用什么时候别碰场景建议多表连接、聚合统计反复出现用视图封装统一口径敏感列、敏感行需要隔离用视图 只授视图权限表结构变更、旧系统兼容用视图维持原接口高频复杂查询、性能敏感普通视图无效考虑物化视图视图上再套多层视图别这么干合并难度和性能都会失控普通视图不存数据每次查询动态展开物化视图Materialized View会把结果集真实存下来用刷新换性能是数据仓库里的主力。注意视图是权限边界不是安全边界是逻辑封装不是性能优化。参考链接MySQL 8.4 Reference Manual — CREATE VIEWOracle Database 19c — CREATE VIEWSQL Server — CREATE VIEWPostgreSQL — CREATE VIEW术语速查表术语英文解释视图View存储在数据字典中的 SELECT 定义虚拟表基础表Base Table真实存放数据的表视图的数据来源数据字典Data Dictionary存储数据库对象定义的系统表集合视图合并View Merging查询视图时将视图定义与外层 SQL 拼接改写检查选项WITH CHECK OPTION限制通过视图写入后数据必须仍满足视图条件物化视图Materialized View实际存储查询结果的视图以刷新换性能行级安全Row Level Security按行控制数据可见性的数据库安全策略