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

一. MySQL 視圖（View）全解

1.1 視圖的核心本質(zhì)

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

視圖和基表的數(shù)據(jù)是強(qiáng)關(guān)聯(lián)的：

• 視圖的數(shù)據(jù)修改，會(huì)直接影響底層基表；
• 基表的數(shù)據(jù)修改，也會(huì)實(shí)時(shí)同步反映到視圖中。

它的核心價(jià)值在于：

• 簡(jiǎn)化復(fù)雜的多表關(guān)聯(lián)查詢，一次定義多次復(fù)用；
• 實(shí)現(xiàn)行列級(jí)別的數(shù)據(jù)權(quán)限控制，屏蔽敏感字段；
• 屏蔽底層表結(jié)構(gòu)的變化，對(duì)外提供統(tǒng)一的查詢接口。

1.2 視圖的基礎(chǔ)使用

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

1.2.1 創(chuàng)建視圖

基礎(chǔ)語法

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

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

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

1.2.2 查詢視圖

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

-- 基礎(chǔ)查詢
select * from v_ename_dname;

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

1.2.3 視圖與基表的雙向數(shù)據(jù)聯(lián)動(dòng)

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

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

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

② 修改基表，影響視圖

-- 修改基表中員工的部門編號(hào)
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ī)則與限制

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

二. MySQL 用戶管理與權(quán)限控制

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

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

• root 賬號(hào)擁有 MySQL 的最高權(quán)限，誤操作drop database會(huì)直接導(dǎo)致全庫(kù)數(shù)據(jù)丟失；
• 多業(yè)務(wù)、多人員共用 root 賬號(hào)，無法做權(quán)限隔離和操作審計(jì)；
• 一旦 root 賬號(hào)泄露，整個(gè) MySQL 實(shí)例的所有數(shù)據(jù)都會(huì)完全失控。

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

2.2 MySQL 用戶的核心存儲(chǔ)（查詢系統(tǒng)用戶以及核心字段解釋）

MySQL 中的所有用戶信息，都存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)mysql的user表中，這是用戶管理的核心。

查詢系統(tǒng)用戶：

-- 切換到mysql系統(tǒng)庫(kù)
use mysql;

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

核心字段解釋：

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

2.3.1 創(chuàng)建用戶

基礎(chǔ)語法

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

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

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

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

??避坑提示：如果創(chuàng)建時(shí)出現(xiàn)ERROR 1819 (HY000): Your password does not satisfy the current policy requirements報(bào)錯(cuò)，是因?yàn)?MySQL 開啟了密碼強(qiáng)度校驗(yàn)。

?? 解決方案：通過show variables like 'validate_password%';查看密碼策略要求，設(shè)置符合復(fù)雜度的密碼，或臨時(shí)調(diào)整密碼策略。

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

2.3.2 刪除用戶

基礎(chǔ)語法

drop user '用戶名'@'主機(jī)名';

錯(cuò)誤示范

-- 直接寫用戶名會(huì)報(bào)錯(cuò)，默認(rèn)匹配%主機(jī)，和創(chuàng)建的localhost用戶不匹配
drop user Lotso;

正確示范

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

2.3.3 修改用戶密碼

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

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

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

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

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

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

2.4 MySQL 權(quán)限體系

權(quán)限列表我們按使用場(chǎng)景分類整理，方便大家按需分配：

權(quán)限粒度說明：

• *.*：MySQL 實(shí)例中所有數(shù)據(jù)庫(kù)的所有對(duì)象（表、視圖、存儲(chǔ)過程等）
• 庫(kù)名.*：指定數(shù)據(jù)庫(kù)中的所有對(duì)象
• 庫(kù)名.表名：指定數(shù)據(jù)庫(kù)中的指定表

2.5 權(quán)限的核心操作（授權(quán)、回收、查看）

2.5.1 給用戶授權(quán)

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

基礎(chǔ)語法

grant 權(quán)限列表 on 庫(kù).對(duì)象名 to '用戶名'@'登陸位置' [identified by '密碼'];

語法說明：

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

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

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

-- 刷新權(quán)限,這個(gè)別忘了
flush privileges;

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

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

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

-- 刷新權(quán)限
flush privileges;

2.5.2 查看用戶權(quán)限

show grants for '用戶名'@'主機(jī)名';

-- 示例：查看Lotso用戶的權(quán)限
show grants for 'Lotso'@'localhost';

-- 示例：查看root用戶的權(quán)限
show grants for 'root'@'%';

2.5.3 回收用戶權(quán)限

基礎(chǔ)語法

revoke 權(quán)限列表 on 庫(kù).對(duì)象名 from '用戶名'@'登陸位置';

實(shí)戰(zhàn)案例：回收 Lotso 用戶對(duì) test 庫(kù)的所有權(quán)限

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

-- 刷新權(quán)限
flush privileges;

回收完成后，Lotso 賬號(hào)再次登錄，就無法看到 test 庫(kù)了

2.6 生產(chǎn)環(huán)境權(quán)限最佳實(shí)踐

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

三. 全文總結(jié)

視圖核心總結(jié)

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

用戶與權(quán)限核心總結(jié)

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

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

最新評(píng)論