MySQL视图与用户管理全解析

在

MySQL数据库的学习过程中,除了掌握基础的表结构设计、数据增删改查之外,视图和用户管理也是非常重要的内容.视图可以帮助我们将复杂的查询结果封装起来,让数据访问更加简洁、清晰;而用户管理则关系到数据库的安全性,包括用户创建、权限分配、权限回收等操作.对于初学者来说,视图能够提升查询的可读性和复用性,用户管理则能帮助我们理解数据库权限控制的基本思路.本文将围绕MySQL中视图的创建、使用、修改与删除,以及用户管理和权限控制的相关操作进行介绍,帮助大家进一步掌握数据库在实际开发和管理中的常用功能.

1.视图的概念以及基本使用

视图是一个虚拟表,其内容由查询定义.同真实的表一样,视图包含一系列带有名称的列和行数据.视图的数据变化会影响到基表,基表的数据变化也会影响到视图.它不直接存储数据,数据仍然来自原来的表.查询视图时,MySQL 会根据视图定义去查底层表.

1.1创建视图

CREATE VIEW 视图名 ASSELECT 字段1, 字段2, ...FROM 表名WHERE 条件;

例子：

CREATE VIEW user_order_view ASSELECT     u.id AS user_id,    u.name AS user_name,    o.id AS order_id,    o.amountFROM users uJOIN orders o ON u.id = o.user_id;

之后可以像查表一样查视图：

SELECT * FROM user_order_view;

1.2修改视图

CREATE OR REPLACE VIEW user_order_view ASSELECT     u.id AS user_id,    u.name AS user_name,    o.amountFROM users uJOIN orders o ON u.id = o.user_idWHERE o.amount > 100;

或者：

ALTER VIEW user_order_view ASSELECT ...

1.3删除视图

DROP VIEW user_order_view;

1.4查看视图定义

SHOW CREATE VIEW user_order_view;

1.5视图的作用

常见用途：

• 简化复杂 SQL
• 隐藏敏感字段
• 统一查询口径
• 提升代码可读性
• 给不同用户暴露不同数据范围

例如,只给外部系统看用户基础信息：

CREATE VIEW user_public_view ASSELECT id, name, ageFROM users;

不暴露手机号、密码等字段.

1.6注意点

视图一般不会提升查询性能,因为它只是封装 SQL,不是缓存结果.

如果底层SQL很慢,查视图通常也会慢.需要优化底层表索引或 SQL.

部分视图可以更新：

UPDATE user_public_viewSET name = '张三'WHERE id = 1;

但如果视图里包含 JOIN、GROUP BY、聚合函数、DISTINCT 等,通常就不能直接更新.

2.视图规则和限制

按 MySQL 视图 View 来说,核心结论是：

视图适合封装查询和做权限隔离，但不要把它当成真实表、缓存表或性能优化工具。

它有很多限制，尤其是：不能建索引、很多视图不能更新、底层表改动可能导致视图失效。

以下按 MySQL 8.4 官方文档整理.

1.创建视图的基本规则

语法：

CREATE VIEW 视图名 ASSELECT ...FROM 表名;

完整形式常见如下：

CREATE OR REPLACE VIEW v_user ASSELECT id, name, statusFROM usersWHERE status = 'ACTIVE';

MySQL 视图可以基于表,也可以基于其他视图;视图列名必须唯一,不能重复.视图和普通表在同一个数据库中共享命名空间,所以同一个库里不能有同名的表和视图.

2.视图定义限制

MySQL 视图定义里不能随便写所有内容,常见限制如下：

这些是 MySQL 明确列出的视图定义限制.

3.SELECT *的坑

如果这样创建视图：

CREATE VIEW v_user ASSELECT * FROM users;

之后给 users 表新增字段,这个新字段不会自动出现在视图里.MySQL 在创建视图时会"冻结"当时的定义;如果底层表删除了视图依赖的字段,之后查询视图可能报错.

建议：不要在生产视图里用 SELECT *.

推荐写法：

CREATE VIEW v_user ASSELECT id, name, emailFROM users;

4.视图不能建索引

MySQL 普通视图本身不能创建索引.如果视图使用 MERGE 算法处理,底层表索引可能仍然被优化器使用;但如果使用 TEMPTABLE 算法,视图会被临时表化处理,底层表索引在临时表阶段就不再直接起作用.

也就是说：

CREATE INDEX idx_xxx ON v_user(name);

这种是不行的。

性能优化应该加在底层表上：

CREATE INDEX idx_users_status ON users(status);

5.一个视图最多引用 61 张表

MySQL 规定,一个视图定义中最多可以引用 61 张表.这个限制一般很少碰到,但复杂报表视图、多层嵌套视图可能会遇到.

6.ORDER BY和LIMIT的限制

视图里可以写 ORDER BY：

CREATE VIEW v_order ASSELECT *FROM ordersORDER BY created_at DESC;

但如果外层查询也写了 ORDER BY,外层的排序会覆盖或影响最终结果;MySQL 文档明确说明,视图定义中的 ORDER BY 在外层查询也有 ORDER BY 时会被忽略.

不推荐依赖视图内部排序.正确写法是查询时排序：

SELECT *FROM v_orderORDER BY created_at DESC;

LIMIT 也类似：如果视图定义里有 LIMIT,外层查询也有 LIMIT,最终哪个限制生效可能不明确.

7.哪些视图不能更新？

不是所有视图都能 INSERT、UPDATE、DELETE。

MySQL 判断视图是否可更新的核心原则是：

视图中的每一行，必须能一一对应到底层表中的某一行。

只要视图包含以下内容，通常就不可更新：

这些是MySQL官方列出的不可更新场景.

例如这个视图不可更新：

CREATE VIEW v_order_summary ASSELECT user_id, SUM(amount) AS total_amountFROM ordersGROUP BY user_id;

因为它是聚合结果,不对应底层表的单独一行.

8.JOIN 视图的更新限制

多表 JOIN 视图有时可以更新,但限制很严格：

CREATE VIEW v_user_order ASSELECT     u.id AS user_id,    u.name,    o.id AS order_id,    o.amountFROM users uJOIN orders o ON u.id = o.user_id;

这种视图如果可更新,也通常只能更新其中一个底层表的字段,不能一次同时更新多个表.MySQL 文档说明,多表视图要可更新,通常需要能用 MERGE 算法处理,并且只能更新视图定义中的一个表.

例如可能可以：

UPDATE v_user_orderSET amount = 100WHERE order_id = 1;

但不要指望复杂 JOIN 视图都能稳定更新.生产中建议：视图主要用于查询,不建议通过复杂视图做写操作.

9.视图可插入 INSERT 的额外限制

一个视图即使可以 UPDATE,也不一定可以 INSERT.

要能插入,通常还需要满足：

例如这个视图通常不适合插入：

CREATE VIEW v_user ASSELECT id, UPPER(name) AS name_upperFROM users;

因为 name_upper 是表达式,不是底层表的简单字段引用.MySQL 明确要求可插入视图的列必须是简单列引用,不能是表达式.

10.WITH CHECK OPTION限制写入范围

WITH CHECK OPTION 用来防止通过视图插入或更新"不符合视图条件"的数据.

例子：

CREATE VIEW v_active_user ASSELECT id, name, statusFROM usersWHERE status = 'ACTIVE'WITH CHECK OPTION;

这样插入时必须满足：

INSERT INTO v_active_user(id, name, status)VALUES (1, '张三', 'ACTIVE');

下面这种会失败：

INSERT INTO v_active_user(id, name, status)VALUES (2, '李四', 'DISABLED');

因为插入后这行数据不满足 status = 'ACTIVE',不会出现在视图里.MySQL 文档说明,WITH CHECK OPTION 会阻止插入不满足视图 WHERE 条件的行,也会阻止把可见行更新成不可见行.

11.权限规则：DEFINER和 INVOKER

视图有权限上下文,常见两种：

SQL SECURITY DEFINER

表示使用定义视图的人的权限执行,这是默认值.

SQL SECURITY INVOKER

表示使用调用视图的人的权限执行.

MySQL 官方文档说明,视图和存储对象的权限由 DEFINER 和 SQL SECURITY 控制;默认是 DEFINER.如果使用 DEFINER,调用者只需要有访问视图的权限,底层表权限按定义者检查;如果使用 INVOKER,则按调用者自己的权限检查.

示例：

CREATE SQL SECURITY INVOKER VIEW v_user ASSELECT id, nameFROM users;

建议：

• 做权限隔离、隐藏底层表：常用 DEFINER
• 希望调用者必须自己有底层权限：用 INVOKER
• 避免使用高权限账号作为 DEFINER

实用建议

日常开发中建议这样用视图：

CREATE OR REPLACE VIEW v_user_public ASSELECT     id,    name,    emailFROM usersWHERE deleted_at IS NULL;

适合：

• 封装复杂查询
• 隐藏敏感字段
• 固定查询口径
• 给外部系统提供只读数据
• 简化报表 SQL

不建议：

• 通过复杂视图做写操作
• 指望视图提升性能
• 在视图里堆很多层嵌套
• 在视图里依赖 ORDER BY
• 用 SELECT *
• 把视图当缓存表

推荐原则：视图优先当“只读查询接口”使用;真正需要性能提升时,优化底层表索引,或考虑汇总表/物化方案.

3.针对actor表创建视图actor_name_view(实战OJ题)

CREATE VIEW actor_name_view ASSELECT first_name AS first_name_v,       last_name AS last_name_vFROM actor;

4.用户管理

MySQL用户管理 来讲,常用操作主要是：创建用户、设置密码、授权、撤权、查看权限、删除用户、角色管理.如果我们只能使用root用户,这样存在安全隐患.这时,就需要使用MySQL的用户管理.

4.1用户信息

MySQL中的用户,都存储在系统数据库mysql的user表中

字段解释：

host： 表示这个用户可以从哪个主机登陆,如果是localhost,表示只能从本机登陆.

user： 用户名.

authentication_string： 用户密码通过password函数加密后的.

*_priv： 用户拥有的权限.

4.2创建用户

语法：

create user '用户名'@'登陆主机/ip' identified by '密码';

此时便可以使用新账号新密码进行登陆啦--备注：可能实际在设置密码的时候，因为mysql本身的认证等级比较高，一些简单的密码无法设置，会爆出如下报错：-- ERROR 1819 (HY000): Your password does not satisfy the current policyrequirements-- 解决方案：https://blog.csdn.net/zhanaolu4821/article/details/93622812--查看密码设置相关要求：SHOW VARIABLES LIKE 'validate_password%';-- 这个大家下来自己玩玩--关于新增用户这里，需要大家注意，不要轻易添加一个可以从任意地方登陆的user。

4.3删除用户

语法：

drop user '用户名'@'主机名'

4.4修改用户密码

语法：

set password=password('新的密码');--自己改自己密码--自己下来试试

--root用户修改指定用户的密码set password for '用户名'@'主机名'=password('新的密码')；

5.数据库的权限

数据库的权限通常分三层看：

1.实例/服务器级权限

控制能不能连接、创建数据库、管理用户、备份恢复等.

例如：

• 创建用户
• 创建数据库
• 查看所有数据库
• 管理权限
• 备份/恢复
• 修改全局配置

适合给：DBA、运维、平台管理员.

2.数据库级权限

控制某个数据库里的整体操作.

常见权限：

• CONNECT：连接数据库
• CREATE：创建表、视图、函数等对象
• USAGE：使用 schema、序列等
• TEMP：创建临时表

适合给：应用服务账号、开发人员、数据分析人员.

3.表/对象级权限

最常见,控制具体表、视图、函数的操作.

常见权限：

MySQL数据库提供的权限列表：

5.1给用户授权

刚创建的用户没有任何权限.需要给用户授权.

语法:

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

说明：权限列表,多个权限用逗号分开.

grant select on ...grant select, delete, create on ....grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限

. : 代表本系统中的所有数据库的所有对象(表,视图,存储过程等).

库.* : 表示某个数据库中的所有数据对象(表,视图,存储过程等).

identified by可选.如果用户存在,赋予权限的同时修改密码,如果该用户不存在,就是创建用户.

备注：特定用户现有查看权限mysql> show grants for 'lcz'@'%';+-----------------------------------------------+| Grants for lcz@% |+-----------------------------------------------+| GRANT USAGE ON *.* TO 'lcz'@'%' || GRANT ALL PRIVILEGES ON `test`.* TO 'lcz'@'%' |+-----------------------------------------------+2 rows in set (0.00 sec)mysql> show grants for 'root'@'%';+-------------------------------------------------------------+| Grants for root@% |+-------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |+-------------------------------------------------------------+1 row in set (0.00 sec)注意：如果发现赋权限后，没有生效，执行如下指令：flush privileges;

5.2回收权限

语法：

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；

告别SQL混乱！快速掌握MySQL视图,简化复杂查询；学会用户管理,精准控制数据权限,本文手把手教你创建修改视图、分配回收权限,提升数据库安全与效率告别SQL混乱！快速掌握MySQL视图,简化复杂查询；学会用户管理,精准控制数据权限,本文手把手教你创建修改视图、分配回收权限,提升数据库安全与效率