MySQL/MariaDB視圖

標籤：16px   tin   結構   not   表名   alter   row   HERE   view   

本文目錄：
1.建立、修改視圖
2.視圖演算法merge、temptable
3.刪除、查看視圖資訊
4.檢查無效視圖

視圖是表運算式的一種，所以它也是虛擬表。對視圖操作的時候會通過語句動態從表中臨時擷取資料。

1.建立、修改視圖

CREATE [OR REPLACE]    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW [IF NOT EXISTS] view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]ALTER     [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]

當使用or replace時，如果視圖存在則此語句相當於alter view，如果視圖不存在，則等價於create view。

關於algorithm，後文詳細說明。

with [local|cascaded] check option：它的對象是可更新視圖(即merge演算法的視圖)。對於可更新視圖，可給定WITH CHECK OPTION子句來防止插入或更新非法記錄，除非作用在行上的select_statement中的WHERE子句為"true"。其中local表示只要滿足本視圖的篩選條件即可插入或更新，cascaded表示必須滿足所有視圖的篩選條件才可插入或更新。預設是with cascaded check option。

例如，下面的語句定義了3個視圖，其中後兩個視圖是以第一個視圖作為基表建立的。在向view2和view3插入記錄的時候，如果記錄中欄位a=10：由於view2預設使用的是cascaded選項，a=10不滿足view1的條件，所以插入失敗；而view3使用的是local選項，只需滿足view3的條件即可，所以a=10滿足條件,即可以成功插入。

create view view1 as select * from t where a<10;create view view2 as select * from view1 where a>5;create view view3 as select * from view1 where a>5 with local check option;

MySQL/MariaDB中視圖建立後，列的定義是"已固化"狀態。也就是說，如果視圖定義語句中的select語句中使用了星號"*"表示所有列，在建立視圖的時候會轉化為對應的列名儲存在視圖定義語句中，所以如果基表中新增了列將不會被視圖的SQL語句檢索到。

例如：

create or replace view v_cityasselect * from world.city where id>200;

查看視圖的定義語句：可以看到，select語句中的星號是替換為了對應的列名來表示的。

mysql> mysql> show create view v_city\G*************************** 1. row ***************************                View: v_city         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.100.%` SQL SECURITY DEFINER VIEW `v_city` AS select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where (`city`.`ID` > 200)character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)

在MySQL/MariaDB中視圖定義語句中的select部分中，from後面不能是子查詢。在這一點上MySQL/MariaDB和其他類型的資料庫有些不一樣。如果在某種條件下，視圖的定義語句from字句正好需要的是子查詢，可以將這個子查詢先定義成視圖，再將視圖放在from字句中。 更新視圖時，實際上是轉到對應的基表上進行更新。

2.視圖演算法merge、temptable

algorithm={undefined|merge|temptable}是視圖選擇演算法。視圖的演算法會影響MySQL/MariaDB處理視圖的方式：

1. merge會將引用視圖的語句與視圖定義語句合并起來，使得視圖定義的某一部分取代語句的對應部分。例如在引用視圖時會將視圖名替換成基表名，將查詢涉及的列替換成基表中的列名等。
2. temptable將視圖的結果放入暫存資料表中，然後使用該表的資料執行對應語句操作。
3. undefined是讓MySQL/MariaDB自己選擇merge還是temptable，它更傾向於merge。這是未指定algorithm時的預設值。

例如，以下是merge的一個特殊例子，很能說明merge演算法：

MariaDB [test]> create or replace table t                (id int auto_increment,                 name char(20),                 age int,                 primary key(id));MariaDB [test]> insert into t(name,age) values     (‘chenyi‘,21),     (‘huanger‘,22),     (‘zhangsan‘,23),     (‘lisi‘,24),     (‘wangwu‘,25),    (‘zhaoliu‘,26);MariaDB [test]> select * from t;+----+----------+------+| id | name     | age  |+----+----------+------+|  1 | chenyi   |   21 ||  2 | huanger  |   22 ||  3 | zhangsan |   23 ||  4 | lisi     |   24 ||  5 | wangwu   |   25 ||  6 | zhaoliu  |   26 |+----+----------+------+ # 建立一個id<5的視圖my_viewMariaDB [test]> create or replace algorithm=merge view my_view(vf1,vf2) as                 select id,name from t where age<24;MariaDB [test]> select * from my_view;+-----+----------+| vf1 | vf2      |+-----+----------+|   1 | chenyi   ||   2 | huanger  ||   3 | zhangsan |+-----+----------+

返回的結果是3行記錄。

由於是merge演算法的視圖，在引用視圖(此處是查詢操作)的時候，會將視圖中的各項替換為基表t中的各項。包括：

1. "*"號替換為vf1和vf2，它們又替換為t表中的id和name。
2. from子句中的my_view替換為表t。
3. 加上視圖定義語句中的where子句。

因此，select * from my_view;在執行的時候，會轉換為下面的查詢語句：

select id,name from t where age<24;

如果查詢my_view的時候，使用下面的語句：

MariaDB [test]> select * from my_view where vf1<2; +-----+--------+| vf1 | vf2    |+-----+--------+|   1 | chenyi |+-----+--------+

在執行的時候，該語句將替換為下面的語句：

select id,name from t where id<2 and age<24;

只有使用merge演算法的時候，視圖才是可更新視圖，因為temptable演算法操作的是填充到暫存資料表中的資料，無法結合基表進行資料更新。

因為merge演算法結合了基表，因此它有一些限制，出現了以下情況時不能使用merge演算法：

1. HAVING
2. LIMIT
3. GROUP BY
4. DISTINCT
5. UNION
6. UNION ALL
7. 使用了彙總函式，如MAX(), MIN(), SUM() or COUNT()
8. 在select列表中有子查詢
9. 沒有基表，因為可能引用的是純值，例如create view va as select 2。

之所以有以上限制，是因為使用了它們之後，視圖的結構和基表的機構不一致，無法和基表一一對應，也就無法作為可更新視圖。

3.刪除、查看視圖

可以一次性刪除多個視圖。

DROP VIEW [IF EXISTS] view_name [, view_name] ...

MySQL/MariaDB中不存在show view status語句。可以使用show table status表和視圖的狀態資訊，使用show tables顯示出資料庫中的表和視圖。

SHOW TABLE STATUS LIKE  ‘v_city‘;

查看視圖定義語句：

show create view view_name;

還可以從information_schema.views表中查看相關資訊，但是要注意的是，在views表中視圖名所在的欄位稱為table_name而不是view_name。如下：

select * from information_schema.views where table_name=‘view_name‘;

4.檢查無效視圖

在建立視圖的時候，要求它的基表已存在，否則會報錯。但是在視圖建立成功後，視圖的基表可能會刪除掉，或者更新基表中的引用欄位。這時視圖就已經是無效視圖。

如何檢測這些無效視圖？

可以先在information.schema中尋找出有哪些視圖，然後再使用check table語句檢測。

例如：

check table my_view,my_view2

以下是無效視圖檢查結果：

MariaDB [test]> check table my_view\G*************************** 1. row ***************************   Table: test.my_view      Op: checkMsg_type: ErrorMsg_text: Table ‘test.t‘ doesn‘t exist*************************** 2. row ***************************   Table: test.my_view      Op: checkMsg_type: ErrorMsg_text: View ‘test.my_view‘ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them*************************** 3. row ***************************   Table: test.my_view      Op: checkMsg_type: errorMsg_text: Corrupt3 rows in set (0.000 sec)

 

回到Linux系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到網站架構系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到資料庫系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
轉載請註明出處：http://www.cnblogs.com/f-ck-need-u/p/8870908.html

註：若您覺得這篇文章還不錯請點擊右下角推薦，您的支援能激發作者更大的寫作熱情，非常感謝！

MySQL/MariaDB視圖