2010/05/30

[MySQL] Using XML to simulate field changeable data

資料欄位的不確定性一向是資料庫設計時的痛,但也是在評估架構大小時極為有趣的部份。雖然軟體界的先驅,孜孜不倦地要求著我們一定要先做好需求分析,之後再進行架構設計與程式的實作,然而我們永遠都避免不了程式上線後,使用者提出新增或調整輸入內容的需求,而且更糟的是,使用者甚至願意捧著一大筆錢請你實踐這個功能,讓你老闆無法為公司拒絕這樣誘人的條件。

或者試想這樣的情況,你現在正在設計一個API提供使用者上傳資料,雖然第一版API提供了一種型態三個變數用以上傳,但是經過半個月之後,你發現必須要開發第二版API,多新增一個型態含四個變數做處理,而原本的變數,因為前端UI功能增加,必須多增添兩個變數以滿足要求。

一般說來,最直觀的作法,就是將第一版API對應的資料表新增兩個欄位供新功能使用,而過去的所有已輸入資料通通設定為null;而第二版API則另開一個新資料表做儲存,幾乎等於重新開發一個API專屬另一個型態來使用。

但隨著需求的擴增,你的資料表開得越來越多,若開發時只設計一個API入口,那後端程式就必須相對的去維護多個資料庫,又若資料庫程式很先進的使用了O/R Mapping進行實作,那你就會看到堆有著相似性目的,卻只是組成不同的資料庫物件在你的程式裡頭。

為了處理這樣的窘境,我們可以將這類的狀況統整成一個單一資料表,將之間差異的型態以及變數,以XML進行儲存,把ROW×COL的資料表格式,成為不確定COL大小的模式。

建構的資料表範例如下
-- ----------------------------
--  Table structure for `sample`
-- ----------------------------
CREATE TABLE `sample` (
  `alias` int(10) unsigned NOT NULL auto_increment COMMENT '主鍵',
  `type` varchar(255) NOT NULL default '' COMMENT '資料分類',
  `xml` text NOT NULL COMMENT '動態XML資料',
  PRIMARY KEY  (`alias`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


-- ----------------------------
--  Records of `sample`
-- ----------------------------
INSERT INTO  VALUES  
   ( '1',  'HT',  '<bio><sys>140</sys><dias>100</dias></bio>' ) ,
   ( '2',  'GLU',  '<bio><glu>100</glu></bio>' ) ,   
   ( '3',  'HT',  '<bio><sys>139</sys><dias>85</dias></bio>' ) ;

資料的建立不是問題,如何parsing xml,將資料庫的XML資料取出為我們一般使用的資料型態,而不再依靠外在實作的程式碼完成,才是實踐動態欄位資料表核心的處理部分。在MySQL 5.1版本之後,提供了兩個function,分別叫做ExtractValue()以及UpdateXML(),搭配XPath,用以進行XML Data的處理,。

假設我們目前要取得type值'HT'的所有欄位
SELECT *,
ExtractValue(xml,'/bio/sys') AS systolic,
ExtractValue(xml,'/bio/dias') AS diastolic
FROM sample
WHERE type = 'HT';
結果為


但實務上,我們總是常針對目標欄的數值進行比較操作來進行篩選,而不是單純依照分類欄位來篩選所有資料。而XML內容是單純的text,無法執行數值比較,所以我們便需要利用cast()進行處理。

假設我們現在要取得所有type欄位值'HT',且sys值大於139的資料
SELECT *,
ExtractValue(xml,'/bio/sys') AS systolic,
ExtractValue(xml,'/bio/dias') AS diastolic
FROM sample
WHERE type = 'HT'
 AND cast(ExtractValue(xml,'/bio/sys') AS DECIMAL(10,2)) > 139;
結果為



為了避免取出的資料為數值字串,還需要另外轉型,所以也可以直接利用cast將數值預先轉型
SELECT *,
cast(ExtractValue(xml,'/bio/sys') AS DECIMAL(10,2)) AS systolic,
cast(ExtractValue(xml,'/bio/dias') AS DECIMAL(10,2)) AS diastolic
FROM sample
WHERE type = 'HT'
 AND cast(ExtractValue(xml,'/bio/sys') AS DECIMAL(10,2)) > 139;
結果為

若要「暫時」更新某列的XML資料,但不想要更動原資料,則可利用UpdateXML()這個function。

假設目前要將流水號為1的xml資料,取出後的sys元素內容更新為150,範例如下
SELECT UpdateXML(xml,'/bio/sys','150') AS new_XML 
  FROM sample
  WHERE alias=1;
結果為

若是要更新實際XML內容,就用一般的SQL UPDATE處理即可。而ExtractValue(), UpdateXML(), XPath以及cast()更詳細的用法,請直接參閱MySQL的Reference。

No comments:

Post a Comment

有什麼想說的嗎?

肉包

小明總是在住家附近的肉包店買肉包,20 年來,肉包從一顆 10 元漲到一顆 30 元,從一天可以吃三顆,到一天只能吃一顆,今天他心血來潮問了老闆為何這些年漲了這麼多,老闆很驕傲地回答... 「這區的店租漲價了啊!然後你沒發現我們現在店面不但有冷氣,又有座位,還有 80"...