[mysql] 5.0版新支援的view--8個錯誤的設定

分類: 技術分享 作者: andrew

16 四月 2009

緣起

之前公司的某個現有客戶想要追加新功能,因為當初這個專案並沒有參與到,所以就花了一些時間在研究資料庫結構及功能流程。在看mysql發現到有使用到以前沒有接觸過的view的概念,就上網找了些資料來詳讀。或許對很多使用非mysql資料庫的程式設計師來說,view的概念其實很簡單甚至覺得不需要特別寫這篇文章;但是其實mysql是一直到5.0版才開始支援view的概念,也因為不支援view而造成在其他資料庫轉入mysql時造成小小的困擾。這篇文章主要會介紹在撰寫view的語法時要特別注意的8個錯誤,就讓我們開始吧!

何謂view

依據筆者的瞭解,所謂的view就是把專案中常使用的sql語法存入到mysql裡,如此在程式撰寫sql語法上可以簡化,不需要每次都要寫一堆語法。舉個例子來說,以往我們要串兩張資料表撈取其中一張表的某欄位時,我們通常會寫成:

select tableA.name from tableA,tableB where tableA.id=tableB.id;

如果每次都需要這樣寫稍嫌麻煩。我們改用view的概念先把tableA與tableB先關連起來建立了一個view叫做v_tableA_tableB,以後我要撈取tableA的name欄位值時,就可以簡單寫成:

select name from v_tableA_tableB;

而達到同樣的目的。也就是說,我們可以先把一些常用的sql語法用view的方式存到mysql裡,之後要使用時只要針對這個建立的view去作存取即可。那我們該如何在mysql中建立一個view呢?來看下一段的說明。

如何建立一個view

以上一段的範例中,如果要建立v_tableA_tableB這個view,我們會寫成:

CREATE VIEW v_tableA_tableB AS select tableA.name from tableA,tableB where tableA.id=tableB.id;

create view v_tableA_tableB 表示我們要建立一個名稱叫v_tableA_tableB的view;AS 後面會接上要使用的sql語法,如此就很簡單的建立了一個view。那麼,在建立view的時候,有沒有什麼要注意的地方呢?下一段讓我們來看看本篇文章的重點--8個錯誤的設定吧!

8個錯誤的設定

1.不能對暫存資料表建立view

mysql > CREATE TEMPORARY TABLE tt (col1 INT);
mysql > CREATE VIEW v AS select * from tt; //-------(X)

mysql中不允許對一個暫存資料表建立view,這已經是一種標準。

2.在同一個資料庫中table與view不能同名

mysql > CREATE TABLE tt (col1 INT);
mysql > CREATE VIEW tt AS select * from tt; //-------(X)

在同一個資料庫中,table與view是共用同一個命名空間,所以在命名上要注意不得重複。

3.不能對不存在的table建立view

mysql > CREATE VIEW tt AS select * from no_such_table; //-------(X)

雖然在Oracle中可以用FORCE這個參數避掉這個錯誤,不過mysql中並不允許。其實mysql的作法更趨近標準。

4.不能針對沒有權限的資料表建立view
簡單來說,假使某使用者對tableA沒有權限可控管時,在建立tableA的view時同樣也會出現權限不足的錯誤訊息。

5.建立view中不得使用變數或是參數

mysql > CREATE VIEW v AS select * tt
-> WHERE s1 = @variable AND s2 = ? ; //-------(X)
ERROR 1351 (HY000) : View's SELECT contains a variable or parameter

如果在建立view的語法中加上變數或參數,馬上就會回傳錯誤訊息造成建立失敗,這個在標準化的sql語法中都是如此設定的。

6.在from 中不得包含子查詢 (sub-query)

mysql > CREATE VIEW tt AS select * from
-> (SELECT s1 FROM t1) AS x; //-------(X)
ERROR 1394 (HY000) : View's SELECT contains a subquery in the FROM clause

在建立view的語法中,如果在from後面加上子查詢 (sub-query)會發生錯誤。這個在其他資料庫如Oracle其實是允許的。

7.不能在view中使用同樣的欄位名稱

mysql > CREATE TABLE tt (col1 INT);
mysql > SELECT col1,col1 FROM tt;
mysql > CREATE VIEW v AS select col1,col1 from tt; //-------(X)
ERROR 1060 (42S41) : Duplicate column name 'col1'

在範例中如果我們在一般的table中下同名的col1去select是不會發生錯誤的,但在view則不行。在view的欄位名稱必須是唯一的。

8.欄位數量錯誤

mysql > CREATE VIEW v (s1) AS select s1,s2 from tt; //-------(X)
ERROR 1353 (HY000) : View's SELECT and view's field list have different column counts

如果在view的欄位與select中的欄位數不一致時也會造成建立view的錯誤。

總結

在看了以上的說明後,相信應該對view的正確設定上會有更深的認識。其實關於view的相關資料還有很多,這篇文章僅針對一些容易發生錯誤的設定上做了簡略的介紹,不論是官方文件或是網路上都可以找到更多關於view的資料,有興趣的人不妨可以去找來看看。

我要留言

關於這裡

這個部落格分享了哇寶在電子商務領域的技術及資訊,希望能讓更多人一起為台灣的網路產業加油。