【mysql的編程專題⑥】視圖 · Mysql的進階與高可用架構

#【mysql的編程專題⑥】視圖標簽（空格分隔）： Mysql --- > 視圖是表的一個映射,是一張虛表,在結構上視圖和普通的表沒什么區別,一樣可以用sql語句來增刪改查; > 視圖創建后是一直存在數據庫內 ## 操作 ### 創建視圖 **語法** ```sql CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}] VIEW 視圖名 [(字段清單)] AS SELECT 語句 [WITH [CASCADED|LOCAL] CHECK OPTION]; ``` + algorithm表示視圖選擇的算法（可選參數） * Merge: 當引用視圖時,引用視圖的sql語句與定義sql視圖的語句合并(相當于只存儲了sql). * Temptable:當引用視圖時,根據視圖的創建語句建立一個臨時表.該臨時表是一直存儲在mysql數據庫中的,除非認為刪除; * Undefined(默認):未定義,自動,讓系統幫你選. **Merge** ```sql -- Merge,意味著視圖只是一個規則,語句規則, 當查詢視圖時,把查詢視圖的語句(比如where那些)與創建時的語句where子句等合并,分析.再形成一條新的select語句. -- 創建視圖的語句: CREATE altorethm=merge VIEW `g2` AS SELECT `goods_id`,`cat_id`,`goods_name`,`shop_price` FROM `goods` ORDER BY `cat_id` ASC,`shop_price` DESC; --查詢視圖的語句: SELECT * FROM `g2` GROUP BY `cat_id`; -- 最終執行的語句: SELECT `goods_id`,`cat_id`,`goods_name`,`shop_price` FROM `goods` GROUP BY `cat_id` ORDER BY `cat_id` ASC,`shop_price` DESC; ``` **temptable** ```sql -- temptable是根據創建語句瞬間創建一張臨時表,然后查詢視圖的語句從該臨時表查數據. CREATE ALGORITHM=TEMPTABLE VIEW `g2` AS SELECT `goods_id`,`cat_id`,`goods_name`,`shop_price` FROM `goods` ORDER BY `cat_id` ASC,`shop_price` DESC; -- 查詢視圖的語句: SELECT * FROM `g2` GROUP BY `cat_id`; -- 最終執行的2句話: 取數據并放在臨時表,然后去查臨時表. ``` + 字段清單表示視圖中的列名，默認與SELECT查詢結果中的列名相同（可選參數） + WITH CHECK OPTION表示更新視圖時要保證在該試圖的權限范圍之內（可選參數）**注意,with check option對于沒有where條件的視圖不起作用的** * CASCADED：更新視圖時要滿足所有相關視圖和表的條件,創建試圖時最好加上WITH CASCADED CHECK OPTION參數，這種方式比較嚴格,可以保證數據的安全性;cascaded是 WITH CHECK OPTION的默認值 * LOCAL：更新視圖時，要滿足該視圖本身定義的條件即可 **with [cascaded] check option** ```sql mysql> create view v1 as select * from goods where stock > 100 WITH check OPTION; -- 使用了with check option 這個選項那后面對視圖的插入只限于包含stock的表; Query OK, 0 rows affected -- ↓↓ 只允許插入包含stock字段的sql mysql> INSERT into v1(gname) values('zhouzhou'); 1369 - CHECK OPTION failed 'test.v1' mysql> INSERT into v1(gname,stock) values('zhouzhou',8000); Query OK, 1 row affected -- ↓↓ 不影響刪除 mysql> delete from v1 where gname = 'ganggang'; Query OK, 2 rows affected -- ↓↓ 貌似也不影響更新 mysql> update v1 set gname = 'test' where gid = 11; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 -- 其實錯誤,因為更新后查詢出來的表字段里面能查詢到stock > 100;所以可以更新; -- ↓↓ 如果更新后的結構不能再通過stock>100來查詢就不能進行更新; mysql> update v1 set stock = 99; 1369 - CHECK OPTION failed 'test.v1' ``` **with local check option** > 以后研究... wait for **Example1: 單表創建視圖** ```sql CREATE VIEW v1 AS SELECT user_id, nickname, IF (sex = 1, '男', '女') as sex, user_money FROM users ORDER BY user_money DESC LIMIT 10; -- question: 該視圖不能被更新? update v1 set user_money=5000 where user_id = 2659; [Err] 1288 - The target table v2 of the UPDATE is not updatable ``` **Example1: 多表創建視圖** ```sql CREATE VIEW v3 AS SELECT u.user_id, nickname, lhmoney, user_recommend, goods FROM users AS u JOIN COMMENT AS c ON u.user_id = c.user_id ORDER BY user_id ASC WITH CASCADED CHECK OPTION; ``` > 創建視圖時注意: [Err] 1349 - View's SELECT contains a subquery in the FROM clause -- 不能在SELECT的FROM子句中包含子查詢。 ### 刪除視圖 ```sql mysql> drop view v1; -- drop view 視圖名; Query OK, 0 rows affected ``` ### 修改視圖 #### CREATE OR REPLACE VIEW 修改視圖 CREATE OR REPLACE語句非常靈活,在視圖存在的情況下可對視圖進行修改，視圖不在的情況下可創建視圖,其基本用法和CREATE VIEW 幾乎一致 ```sql CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2(uid,nickname,money) AS SELECT user_id, nickname, user_money FROM users ORDER BY user_money DESC LIMIT 10; ``` #### ALTER VIEW 修改視圖 ```sql AlTER VIEW v3(uid,cid,nickname,lhb,content,goods) AS SELECT u.user_id, user_comment_id, nickname, lhmoney, user_recommend, goods FROM users AS u JOIN COMMENT AS c ON u.user_id = c.user_id ORDER BY user_id ASC WITH CASCADED CHECK OPTION; ``` ### 關于更新視圖數據視圖是表的查詢結果映射,那原表的數據改變了,視圖也當然會跟著做相應的改變。但，如果視圖里面的數據改變了呢？首先，視圖增刪改是會影響表的;也就是說，可以在諸如UPDATE、DELETE或INSERT等語句中使用它們，以更新基表的內容。但是,視圖并不是總是能增刪改的。對于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關系。還有一些特定的其他結構，這類結構會使得視圖**不可更新**。更具體地講，如果視圖包含下述結構中的任何一種，那么它就是不可更新的： + 聚合函數（SUM(), MIN(), MAX(), COUNT()等）。 + DISTINCT + GROUP BY + HAVING + UNION或UNION ALL + 位于選擇列表中的子查詢 + Join + FROM子句中的不可更新視圖 + WHERE子句中的子查詢，引用FROM子句中的表。 + 僅引用文字值（在該情況下，沒有要更新的基本表）。 + ALGORITHM = TEMPTABLE（使用臨時表總會使視圖成為不可更新的）。 ```sql mysql> update v4 set sex=5 where user_id = 32; -- 一定程度上保證了基表數據的安全性 1288 - The target table v4 of the UPDATE is not updatable ``` **關于視圖的可插入性：insert** 如果視圖滿足關于視圖列的下述額外要求，可更新的視圖也是可插入的： + 不得有重復的視圖列名稱。 + 視圖必須包含沒有默認值的基表中的所有列。 + 視圖列必須是簡單的列引用而不是導出列。導出列不是簡單的列引用，而是從表達式導出的。下面給出了一些導出列示例： * 3.14159 * col1 + 3 * UPPER(col2) * col3 / col4 * (subquery) ### 查看視圖 + 查看視圖數據與查看普通表一樣 + 查看視圖字段結構 ```sql mysql> describe v3; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | user_id | int(11) | NO | | 0 | | | nickname | varchar(25) | NO | | | | | lhmoney | int(11) | NO | | 0 | | | user_recommend | longtext | YES | | NULL | | | goods | int(10) | NO | | 0 | | +----------------+-------------+------+-----+---------+-------+ ``` + 查看視圖基本信息 ```sql mysql> show table status like 'v3'\G -- 該命令與查看普通表的命令是一樣的,而此處大多數為NULL正是證明視圖是一張虛表; *************************** 1. row *************************** Name: v3 Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULL Max_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: VIEW 1 row in set (0.01 sec) mysql> show table status like 'users'\G *************************** 1. row *************************** Name: users Engine: InnoDB Version: 10 Row_format: Compact Rows: 171650 Avg_row_length: 82 Data_length: 14172160 Max_data_length: 0 Index_length: 11567104 Data_free: 66060288 Auto_increment: 169781 Create_time: 2015-05-01 23:03:29 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 用戶信息表 1 row in set (0.01 sec) ``` + 查看視圖詳細信息 ```sql mysql> show create view v3\G *************************** 1. row *************************** View: v3 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIE W `v3` AS select `u`.`user_id` AS `user_id`,`u`.`nickname` AS `nickname`,`u`.`lhmoney` AS `lhmoney`, `c`.`user_recommend` AS `user_recommend`,`c`.`goods` AS `goods` from (`users` `u` join `comment` `c` on((`u`.`user_id` = `c`.`user_id`))) order by `u`.`user_id` WITH CASCADED CHECK OPTION character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.01 sec) ``` + 在information_schema的views表中查看視圖詳細信息; ```sql mysql> SELECT * FROM information_schema.views\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: v1 VIEW_DEFINITION: select `test`.`users`.`user_id` AS `user_id`,`test`.`users`.`nickname` AS `nic kname`,if((`test`.`users`.`sex` = 1),'男','女') AS `sex`,`test`.`users`.`user_money` AS `user_money` from `test`.`users` order by `test`.`users`.`user_money` desc limit 10 CHECK_OPTION: NONE IS_UPDATABLE: NO DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci ``` > information_schema是Mysql自帶的一個數據庫,詳細請參考我的另外一邊博文《探尋mysql自帶的數據庫》 ## 作用 1. 可以簡化查詢,我們把復雜而又頻繁的查詢先存儲為視圖,下次查詢的時候,直接在這個視圖里面找; 2. 可以進行權限控制,Mysql的權限可以精確到表,但不能精確到列,但可以把表的權限封閉,另外創建一個視圖,該視圖中就是只存儲開放了相應的權限列; 3. 大數據分表時可以用到: 比如,表的行數超過200萬行時,就會變慢,這時可以把一張的表的數據拆成4張表來存放. **思路1:** News表,分成四表,每表給一個id Newsid, 1,2,3,4 News1,news2,news3,news4表把一張表的數據分散到4張表里,分散的方法很多, 最常用可以用id取模來計算. Id%4+1 = [1,2,3,4] 比如 ```sql $_GET['id'] = 17, 17%4 + 1 = 2, $tableName = 'news'.'2' Select * from news2 where id = 17; ``` **思路2:** 還可以用視圖, 把4張表形成一張視圖 Create view news as select from n1 union select from n2 union.........