[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。