適用環(huán)境MySQL 8.0。視圖可以理解為“有名字、可重復(fù)查詢的SELECT”主要用于封裝查詢、限制可見數(shù)據(jù)和提供穩(wěn)定的查詢接口1. 視圖視圖View是一張?zhí)摂M表它的內(nèi)容來自一個或多個基表、其他視圖或表達(dá)式的查詢結(jié)果創(chuàng)建視圖時MySQL 主要保存的是視圖名稱、列信息和SELECT定義而不是另存一份結(jié)果數(shù)據(jù)查詢視圖執(zhí)行視圖定義student 基表score 基表視圖有以下特點(diǎn)查詢視圖時MySQL根據(jù)視圖定義讀取當(dāng)時的基表數(shù)據(jù)基表數(shù)據(jù)改變后再次查詢視圖通常會看到新結(jié)果刪除視圖只刪除查詢定義不會刪除基表及其數(shù)據(jù)普通視圖不是數(shù)據(jù)副本、備份或快照也不會自動提高查詢速度視圖定義仍會保存在數(shù)據(jù)字典中所以“視圖不存結(jié)果數(shù)據(jù)”不等于完全不占任何空間。例如下面的視圖只展示學(xué)生編號、姓名和年齡createviewv_student_basicasselectid,name,agefromstudent;查詢視圖與查詢普通表的寫法相同select*fromv_student_basic;2. 視圖優(yōu)點(diǎn)簡化復(fù)雜查詢多表連接、篩選和計(jì)算可以封裝到視圖中。應(yīng)用以后只查詢視圖不必反復(fù)編寫同一段復(fù)雜 SQL。限制可見的行和列視圖可以不暴露密碼、身份證號等敏感列也可以通過WHERE只展示某些行。但這只有與權(quán)限控制結(jié)合才真正安全如果用戶仍擁有基表的SELECT權(quán)限他依然可以繞過視圖直接查詢基表。提供相對穩(wěn)定的查詢接口應(yīng)用統(tǒng)一查詢視圖。底層表調(diào)整后有時只需重新定義視圖即可保持視圖的列名不變減少應(yīng)用改動。但這種獨(dú)立性并非絕對刪除視圖依賴的列仍可能使視圖失效。統(tǒng)一名稱和業(yè)務(wù)口徑視圖可以為列起更清楚的名稱并把“總分如何計(jì)算”“有效記錄如何篩選”等規(guī)則集中在一處避免不同程序?qū)懗霾煌趶健?. 創(chuàng)建視圖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這個是視圖執(zhí)行算法MySQL 查詢視圖時有三種處理方式。undefined默認(rèn)、merge合并、tempable臨時表definer指定創(chuàng)建這個視圖的人。如definerrootlocalhost表示該視圖屬于root用戶sql security表示查詢視圖時使用誰的權(quán)限definer 則表示使用創(chuàng)建視圖用戶的權(quán)限invoker 則表示當(dāng)前查詢用戶自己的權(quán)限with check option防止通過視圖修改出視圖范圍之外的數(shù)據(jù)cascaded檢查所有關(guān)聯(lián)視圖local只檢查當(dāng)前視圖示例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 使用別名確定視圖列名以下示例連接學(xué)生、班級、課程和成績表。顯式JOIN ... ON ...能把連接條件與普通篩選條件分開比逗號連接更容易閱讀也能減少漏寫連接條件造成笛卡爾積的風(fēng)險。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 在視圖名后指定列名也可以統(tǒng)一列出視圖的列名createviewv_student_name_age(student_id,student_name,student_age)asselectid,name,agefromstudent;括號中的名稱數(shù)量必須與SELECT返回的列數(shù)完全相同。兩種命名方法選擇一種即可復(fù)雜查詢通常使用AS別名更直觀因?yàn)槊Q緊挨對應(yīng)表達(dá)式。3.4 創(chuàng)建時的注意事項(xiàng)視圖與表屬于同一數(shù)據(jù)庫名稱空間不能在同一數(shù)據(jù)庫中同名。建議明確寫出字段不要長期依賴SELECT *。視圖定義在創(chuàng)建時確定基表以后新增列不會自動加入既有視圖。如果基表的依賴列被刪除或改名查詢視圖可能報錯需要重新定義視圖。不要依賴視圖定義中的ORDER BY保證順序。查詢視圖時應(yīng)在最外層明確排序外層自己的ORDER BY會取代視圖內(nèi)的排序。CREATE VIEW是 DDL會觸發(fā)隱式提交不要把它混入需要回滾的業(yè)務(wù)事務(wù)。4. 查詢和使用視圖視圖可出現(xiàn)在普通表能夠出現(xiàn)的許多查詢位置并可繼續(xù)篩選、連接、分組和排序-- 查詢?nèi)恳晥D數(shù)據(jù)select*fromv_student_score;-- 對視圖結(jié)果繼續(xù)篩選和排序selectstudent_name,course_name,scorefromv_student_scorewherescore90orderbyscoredesc;-- 視圖與真實(shí)表連接selectv.student_name,v.course_name,v.score,s.enroll_datefromv_student_score vjoinstudent sons.idv.student_id;視圖也可以隱藏查詢細(xì)節(jié)。例如只對外提供姓名和總分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;用戶只能從該視圖獲得定義中已有的列不能臨時查詢未被視圖暴露的學(xué)號或各科明細(xì)。若確實(shí)需要這些字段應(yīng)修改視圖、另建視圖或在有權(quán)限時查詢基表。5. 視圖與基表數(shù)據(jù)的關(guān)系5.1 修改基表會影響視圖結(jié)果updatescoresetscore99wherestudent_id1andcourse_id1;select*fromv_student_scorewherestudent_id1andcourse_id1;UPDATE修改的是score基表。視圖沒有獨(dú)立保存舊結(jié)果所以再次查詢時會顯示修改后的成績。5.2 修改可更新視圖會影響基表創(chuàng)建一個行與基表行一一對應(yīng)的簡單視圖createviewv_class_one_studentasselectid,name,age,class_idfromstudentwhereclass_id1;updatev_class_one_studentsetage20whereid1;如果這個視圖滿足可更新條件這條語句最終修改的是student基表中id1的記錄。因此能看出視圖不是基表的副本通過視圖寫數(shù)據(jù)同樣需要事務(wù)、權(quán)限和條件控制。6. 可更新視圖視圖能夠被UPDATE、DELETE或INSERT操作的核心條件是視圖中的一行能夠明確對應(yīng)到底層表中的一行。最容易更新的是“單表 簡單列 普通WHERE”視圖。以下結(jié)構(gòu)通常會使視圖不可更新聚合函數(shù)或窗口函數(shù)如SUM()、COUNT()、AVG()DISTINCTGROUP BY、HAVINGUNION、UNION ALL查詢列表中的子查詢某些多表連接在FROM中引用不可更新視圖只查詢常量沒有可對應(yīng)的基表行明確使用ALGORITHM TEMPTABLE。例如v_student_total_points使用了SUM()和GROUP BY。一條總分記錄由多條成績記錄合成MySQL 無法判斷“把總分改成 500”應(yīng)當(dāng)修改哪一科所以它只適合查詢。ORDER BY可以出現(xiàn)在視圖定義中但不能把它簡單記成“只要有ORDER BY視圖就一定不可更新”。可更新性取決于完整定義和處理方式為了職責(zé)清楚可寫視圖通常不在內(nèi)部排序而在查詢視圖時排序?！翱筛隆币膊灰欢ù怼翱刹迦搿?。通過視圖插入時視圖還要能為基表中所有沒有默認(rèn)值的必填列提供值而且目標(biāo)列通常必須是簡單的基表列引用。檢查 MySQL 記錄的可更新狀態(tài)selecttable_name,is_updatablefrominformation_schema.viewswheretable_schemadatabase();IS_UPDATABLEYES表示該視圖可用于某些更新操作不表示任意INSERT、UPDATE、DELETE都必然合法實(shí)際操作還受列、連接方式和權(quán)限等條件限制。7. WITH CHECK OPTION普通可更新視圖有一個容易忽略的問題通過視圖修改數(shù)據(jù)后新數(shù)據(jù)可能不再滿足視圖的WHERE條件于是該行會從視圖中“消失”。createviewv_class_one_studentasselectid,name,age,class_idfromstudentwhereclass_id1;-- 若沒有檢查選項(xiàng)這次修改可能成功隨后該行不再出現(xiàn)在視圖中updatev_class_one_studentsetclass_id2whereid1;在可更新視圖后加入WITH CHECK OPTION可以阻止通過該視圖寫入不再滿足視圖條件的數(shù)據(jù)createorreplaceviewv_class_one_studentasselectid,name,age,class_idfromstudentwhereclass_id1withcheckoption;此時把class_id改為2會失敗因?yàn)樾薷暮蟮挠涗洸环蟘lass_id1。它既檢查UPDATE后的行也檢查通過視圖INSERT的行。視圖基于其他視圖時還可指定檢查范圍WITH LOCAL CHECK OPTION檢查當(dāng)前視圖的條件并按下層視圖原有的檢查設(shè)置繼續(xù)處理WITH CASCADED CHECK OPTION檢查當(dāng)前視圖及所有下層視圖的條件不寫LOCAL或CASCADED時默認(rèn)是CASCADED。沒有嵌套視圖時直接寫WITH CHECK OPTION最容易理解。8. 修改、查看與刪除視圖8.1 修改定義createorreplaceviewv_student_basicasselectid,name,age,genderfromstudent;CREATE OR REPLACE VIEW在視圖不存在時創(chuàng)建在已存在時替換。也可以使用alterviewv_student_basicasselectid,name,age,genderfromstudent;ALTER VIEW要求目標(biāo)視圖已經(jīng)存在。兩種方式都是重新定義視圖不會直接修改基表數(shù)據(jù)它們屬于 DDL同樣可能隱式提交當(dāng)前事務(wù)。8.2 查看視圖-- 查看當(dāng)前數(shù)據(jù)庫中的視圖showfulltableswheretable_typeVIEW;-- 查看完整創(chuàng)建語句排查算法、安全模式和檢查選項(xiàng)showcreateviewv_student_basic;-- 查看視圖對外提供的列descv_student_basic;-- 檢查視圖依賴是否仍然有效checktablev_student_basic;還可查詢更完整的元數(shù)據(jù)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等基表數(shù)據(jù)但如果其他視圖依賴被刪除的視圖依賴者可能變得不可用。DROP VIEW也是會隱式提交的 DDL。9. 視圖處理原理MySQL 處理視圖主要有三種算法算法基本原理主要特點(diǎn)MERGE把外層查詢與視圖定義合并成一個查詢通常更容易繼續(xù)優(yōu)化滿足其他條件時可更新TEMPTABLE先把視圖結(jié)果放入本次語句使用的內(nèi)部臨時表再查詢臨時結(jié)果該視圖不可更新臨時結(jié)果不是永久保存的物化視圖UNDEFINED由 MySQL 選擇可能優(yōu)先嘗試MERGE默認(rèn)思路通常不必手動指定例如createalgorithmmergeviewv_adult_studentasselectid,name,agefromstudentwhereage18;執(zhí)行select*fromv_adult_studentwhereid100;采用MERGE時可以近似理解為 MySQL 合并兩個條件后查詢基表selectid,name,agefromstudentwhereage18andid100;視圖不會自動擁有索引普通視圖也不能像表一樣單獨(dú)創(chuàng)建索引。查詢性能主要取決于展開后的 SQL、基表索引、數(shù)據(jù)量和優(yōu)化器選擇應(yīng)使用EXPLAIN分析最終查詢不能把“創(chuàng)建視圖”等同于“查詢加速”。10. 視圖的安全上下文完整語法中的SQL SECURITY決定執(zhí)行視圖時按照誰的權(quán)限檢查底層對象SQL SECURITY DEFINER按視圖定義者的權(quán)限執(zhí)行是默認(rèn)值SQL SECURITY INVOKER按調(diào)用視圖的用戶權(quán)限執(zhí)行。createsqlsecurityinvokerviewv_student_publicasselectid,name,agefromstudent;要使用視圖保護(hù)數(shù)據(jù)應(yīng)讓普通用戶只有所需視圖的權(quán)限而沒有敏感基表的直接權(quán)限。僅僅不把敏感列寫進(jìn)視圖并不能阻止一個本來就能查詢基表的用戶。參考MySQL 8.0CREATE VIEWMySQL 8.0視圖處理算法MySQL 8.0可更新與可插入視圖MySQL 8.0WITH CHECK OPTIONMySQL 8.0視圖元數(shù)據(jù)MySQL 8.0DROP VIEW以上是我關(guān)于MySQL的筆記分享感謝你讀到這里這也是我學(xué)習(xí)路上的一個小小記錄。