在日常開發(fā)和面試中，視圖和用戶權限管理是 MySQL 最基礎也最容易被忽視的兩個核心模塊：很多新手只會用基礎的增刪改查，生產環(huán)境直接用 root 賬號操作所有庫，視圖亂用導致業(yè)務 bug 和性能問題，最終引發(fā)數據安全風險。本文從核心定義、基礎語法、實戰(zhàn)案例到使用限制，全流程拆解，面試、開發(fā)、運維一套搞定。

一. MySQL 視圖（View）全解

1.1 視圖的核心本質

視圖是一張虛擬表，其內容由 select 查詢語句定義。和真實的業(yè)務表一樣，視圖包含帶名稱的列和行數據，但它本身不存儲任何真實數據，數據全部來自視圖定義時依賴的底層基表。

視圖和基表的數據是強關聯的：

• 視圖的數據修改，會直接影響底層基表；
• 基表的數據修改，也會實時同步反映到視圖中。

它的核心價值在于：

• 簡化復雜的多表關聯查詢，一次定義多次復用；
• 實現行列級別的數據權限控制，屏蔽敏感字段；
• 屏蔽底層表結構的變化，對外提供統一的查詢接口。

1.2 視圖的基礎使用

我們以經典的員工表emp、部門表dept為案例，完整演示視圖的創(chuàng)建、查詢、修改、刪除全流程，和參考文檔案例完全對齊。

1.2.1 創(chuàng)建視圖

基礎語法

create view 視圖名 as select查詢語句;

實戰(zhàn)案例：創(chuàng)建員工姓名 + 部門名稱的關聯視圖，屏蔽員工薪資、編號等敏感字段

-- 創(chuàng)建視圖v_ename_dname，關聯員工表和部門表
create view v_ename_dname as
select ename, dname
from emp, dept
where emp.deptno = dept.deptno;

1.2.2 查詢視圖

視圖的查詢語法和普通表完全一致，支持排序、篩選、聚合等所有 select 操作

-- 基礎查詢
select * from v_ename_dname;

-- 帶排序的查詢
select * from v_ename_dname order by dname;

1.2.3 視圖與基表的雙向數據聯動

這是視圖最核心的特性，參考文檔中重點強調了視圖和基表的互相影響，我們通過案例完整演示。
① 修改視圖，影響基表

-- 修改視圖中的員工姓名
update v_ename_dname set ename='test' where ename='clark';

-- 查詢基表，數據已被同步修改
select * from emp where ename='clark';
select * from emp where ename='test';

② 修改基表，影響視圖

-- 修改基表中員工的部門編號
update emp set deptno=10 where ename='james';

-- 查詢視圖，部門名稱已同步更新
select * from v_ename_dname where ename='james';

1.2.4 刪除視圖

drop view 視圖名;

-- 示例：刪除剛才創(chuàng)建的視圖
drop view v_ename_dname;

1.3 視圖的使用規(guī)則與限制

• 命名唯一性：視圖名必須和庫內其他視圖、表名唯一，不能重名；
• 創(chuàng)建數量無限制：可以基于業(yè)務創(chuàng)建任意數量的視圖，但要注意復雜嵌套查詢的視圖會嚴重影響性能；
• 索引與觸發(fā)器限制：視圖不能創(chuàng)建索引，也不能關聯觸發(fā)器、設置默認值；
• 權限要求：視圖的使用需要對應的訪問權限，創(chuàng)建視圖必須有查詢基表的權限；
• 排序覆蓋規(guī)則：視圖定義中可以使用 order by，但如果從該視圖查詢的 select 語句中也包含 order by，視圖中的排序會被外部的排序覆蓋；
• 混合使用：視圖可以和普通業(yè)務表一起進行關聯查詢、嵌套查詢；
• 更新限制：只有簡單的單表視圖支持 update/insert/delete，多表關聯、聚合函數、分組、去重的視圖無法直接更新。

二. MySQL 用戶管理與權限控制

2.1 為什么必須做用戶管理？

核心痛點：生產環(huán)境直接使用 root 用戶存在極大的安全隱患。

• root 賬號擁有 MySQL 的最高權限，誤操作drop database會直接導致全庫數據丟失；
• 多業(yè)務、多人員共用 root 賬號，無法做權限隔離和操作審計；
• 一旦 root 賬號泄露，整個 MySQL 實例的所有數據都會完全失控。

正確的做法是：按業(yè)務、按人員創(chuàng)建獨立用戶，只分配最小必要權限。 比如張三只能操作 mytest 庫，李四只能操作 msg 庫，互不影響，風險可控。

2.2 MySQL 用戶的核心存儲（查詢系統用戶以及核心字段解釋）

MySQL 中的所有用戶信息，都存儲在系統數據庫mysql的user表中，這是用戶管理的核心。

查詢系統用戶：

-- 切換到mysql系統庫
use mysql;

-- 查詢核心用戶信息
select host, user, authentication_string from user;

核心字段解釋：

2.3 用戶的核心操作（創(chuàng)建、刪除、修改密碼）

2.3.1 創(chuàng)建用戶

基礎語法

create user '用戶名'@'登陸主機/ip' identified by '密碼';

實戰(zhàn)案例：創(chuàng)建僅能本機登錄的用戶 Lotso，密碼為 12345678

create user 'Lotso'@'localhost' identified by '12345678';

創(chuàng)建完成后，再次查詢 user 表，就能看到新增的用戶信息。

??避坑提示：如果創(chuàng)建時出現ERROR 1819 (HY000): Your password does not satisfy the current policy requirements報錯，是因為 MySQL 開啟了密碼強度校驗。

?? 解決方案：通過show variables like 'validate_password%';查看密碼策略要求，設置符合復雜度的密碼，或臨時調整密碼策略。

• 關于新增用戶這里，需要大家注意，不要輕易添加一個可以從任意地方登陸的user。
• select host,user, authentication_string from user; – 可以用這個查看下，但是要先選擇mysql這個庫

2.3.2 刪除用戶

基礎語法

drop user '用戶名'@'主機名';

錯誤示范

-- 直接寫用戶名會報錯，默認匹配%主機，和創(chuàng)建的localhost用戶不匹配
drop user Lotso;

正確示范

-- 必須和創(chuàng)建時的用戶名+主機名完全匹配
drop user 'Lotso'@'localhost';

2.3.3 修改用戶密碼

① 用戶自己修改自己的密碼

set password=password('新的密碼');

② root 用戶修改指定用戶的密碼（生產環(huán)境常用）

set password for '用戶名'@'主機名'=password('新的密碼');

實戰(zhàn)案例：修改 Lotso 用戶的密碼為 87654321

set password for 'Lotso'@'localhost'=password('87654321');

2.4 MySQL 權限體系

權限列表我們按使用場景分類整理，方便大家按需分配：

權限粒度說明：

• *.*：MySQL 實例中所有數據庫的所有對象（表、視圖、存儲過程等）
• 庫名.*：指定數據庫中的所有對象
• 庫名.表名：指定數據庫中的指定表

2.5 權限的核心操作（授權、回收、查看）

2.5.1 給用戶授權

剛創(chuàng)建的用戶默認沒有任何權限，只能登錄 MySQL，無法查看任何業(yè)務庫，必須手動授權。

基礎語法

grant 權限列表 on 庫.對象名 to '用戶名'@'登陸位置' [identified by '密碼'];

語法說明：

• 多個權限用英文逗號分隔，比如select,insert,update；
• identified by是可選的：如果用戶已存在，授權的同時會修改密碼；如果用戶不存在，會直接創(chuàng)建該用戶；
• 授權完成后，若權限未生效，執(zhí)行flush privileges;刷新權限。

實戰(zhàn)案例 1：給 Lotso 用戶分配 test 庫下所有表的只讀權限

grant select on test.* to 'Lotso'@'localhost';

-- 刷新權限,這個別忘了
flush privileges;

授權后，用 whb 賬號登錄，就能看到 test 庫，并且只能執(zhí)行 select 查詢，無法執(zhí)行 delete、update 等操作，和參考文檔效果完全一致。

實戰(zhàn)案例 2：給 Lotso 用戶分配 test 庫的所有權限

grant all privileges on test.* to 'Lotso'@'localhost';

-- 刷新權限
flush privileges;

2.5.2 查看用戶權限

show grants for '用戶名'@'主機名';

-- 示例：查看Lotso用戶的權限
show grants for 'Lotso'@'localhost';

-- 示例：查看root用戶的權限
show grants for 'root'@'%';

2.5.3 回收用戶權限

基礎語法

revoke 權限列表 on 庫.對象名 from '用戶名'@'登陸位置';

實戰(zhàn)案例：回收 Lotso 用戶對 test 庫的所有權限

revoke all on test.* from 'Lotso'@'localhost';

-- 刷新權限
flush privileges;

回收完成后，Lotso 賬號再次登錄，就無法看到 test 庫了

2.6 生產環(huán)境權限最佳實踐

• 最小權限原則：只給用戶分配業(yè)務必需的權限，絕不分配 all privileges 全局權限；
• 登錄限制：普通業(yè)務用戶絕不設置%任意地址登錄，只允許指定業(yè)務服務器 IP 登錄；
• 禁止 root 遠程登錄：root 用戶僅允許localhost本機登錄，杜絕遠程爆破風險；
• 按業(yè)務分用戶：不同的業(yè)務系統、不同的微服務創(chuàng)建獨立的用戶，只分配對應業(yè)務庫的權限；
• 定期權限審計：定期清理無用賬號，回收過度授權的權限，避免權限泄露。

三. 全文總結

視圖核心總結

• 視圖是虛擬表，僅存儲查詢定義，不存儲真實數據，數據全部來自基表；
• 視圖和基表數據雙向聯動，修改一方會同步影響另一方；
• 視圖不能創(chuàng)建索引、觸發(fā)器，復雜嵌套視圖會影響性能；
• 核心用途：簡化復雜查詢、數據權限隔離、統一查詢口徑。

用戶與權限核心總結

• MySQL 用戶唯一標識是 '用戶名'@'主機名'，二者缺一不可；
• 用戶信息全部存儲在mysql.user系統表中，密碼加密存儲；
• 授權用grant，回收用revoke，權限變更后需flush privileges刷新；
• 生產環(huán)境嚴格遵守最小權限原則，禁止濫用 root 賬號。

到此這篇關于MySQL入門實戰(zhàn)：視圖+用戶權限管理使用方法（圖文+代碼）的文章就介紹到這了,更多相關MySQL的視圖和用戶權限管理內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論