在MySQL中使用XML數據—數據格式化

前沿：在MySQL中使用XML數據可以更好的管理數據，將所有數據進行了格式化，這個過程是怎麼樣的呢？

在存儲過程中使用ExtractValue()函數

上面使用的方法最大的缺陷就是在代碼中寫死了要檢索的行，這樣很不靈活，下面我們使用一個存儲過程來解決這個問題。

MySqlDump

有Igor Romanenko編寫的MySqlDump客戶端最初是一個備份程序，它可以備份數據庫，或將一個數據庫轉移到另一個數據庫(MySQL或其它數據庫)，它的這個備份過程其實是一個創建表，填充表的過程。此外，MySqlDump還可以生成CSV，XML或其它由分隔符控制的文本文件。

雖然不止一種執行MySqlDump的方法，但我想使用下面的語法：

mysqldump --xmldatabasename[tables] 

你也可以使用標准的Unix/Dos方法輸出到一個文件，如下面的命令將一個表轉成一個XML文件：　

C:\>mysqldump --xml temp_table_article client > c:\\sqldump.xml 

打開sqldump.xml看看，我們發現MySqlDump客戶端包括了比--xml選項更多的信息，但它卻是按每一行、字段名和值的方式輸出的，MySqlDump將表結構和表數據單獨輸出。

<?xml version=1.0?
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
<database name="temp_table_article">  
name="client">  
client_id Type=int(10)unsignedNull=NO"Key=PRI" Extra=auto_increment/>
Field="date_of_birth" Type="date" Null="YES" Key="" Extra="" />  
                 <field Field="gender" Type="char(1)" Null="YES" Key="" Extra="" />  
                 <field Field="logical_delete_indicator" Type="tinyint(1)" Null="NO" Key="" Default="0" Extra="" />  
                 <key Table="client" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="client_id"   
                   Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />  
                 <key Table="client" Non_unique="0" Key_name="client_id" Seq_in_index="1" Column_name="client_id"   
                   Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />  
                 <options Name="client" Engine="InnoDB" Version="10" Row_format="Compact" Rows="4" Avg_row_length="4096"   
                   Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="10485760"   
                   Auto_increment="5"   
                   Create_time="2009-10-05 17:51:34" Collation="latin1_swedish_ci" Create_options=""   
                   Comment="" />  
         </table_structure>  
         <table_data name="client">  
         <row>  
                 <field name="client_id">1</field>  
                 <field name="date_of_birth">1976-02-12</field>  
                 <field name="gender">M</field>  
                 <field name="logical_delete_indicator">1</field>  
         </row>  
         <row>  
                 <field name="client_id">2</field>  
                 <field name="date_of_birth">1944-01-15</field>  
                 <field name="gender">F</field>  
                 <field name="logical_delete_indicator">0</field>  
         </row>  
         <row>  
                 <field name="client_id">3</field>  
                 <field name="date_of_birth">1956-06-04</field>  
                 <field name="gender">M</field>  
                 <field name="logical_delete_indicator">1</field>  
         </row>  
         <row>  
                 <field name="client_id">4</field>  
                 <field name="date_of_birth">1938-11-19</field>  
                 <field name="gender">F</field>  
                 <field name="logical_delete_indicator">0</field>  
         </row>  
         </table_data>  
</database>  
</mysqldump> 

我們使用這個工具創建XML數據結構的原因是已經有存儲過程可以將MySqlDump XML數據插入到表中，MySQL開發人員Alexander Barkov編寫了這樣一個存儲過程xmldump_load，這個存儲過程可以從mysqldump --xml命令輸出的XML文檔中提取數據，並將提取到的數據插入到MySQL表的列中，下面是這個存儲過程的全部代碼。

DELIMITER |  
DROP PROCEDURE IF EXISTS xmldump_load |  
CREATE PROCEDURE xmldump_load(     file_name VARCHAR(128),  
                          database_name VARCHAR(128),  
                          table_name VARCHAR(128))  
BEGIN 
  DECLARE xml TEXT;  
   DECLARE nrows INT;  
  DECLARE rownum INT DEFAULT 1;  
   DECLARE ncols INT;  
  DECLARE colnum INT DEFAULT 1;  
   DECLARE ins_list TEXT DEFAULT '';  
  DECLARE val_list TEXT DEFAULT '';  
   DECLARE tmp VARCHAR(255);  
   # 將XML文件的內容載入到字符串中  
  SET xml = LOAD_FILE(file_name);  
   # 獲得這個表中<row>的數量  
  SET nrows = ExtractValue(xml,   
   'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row)');  
   # 獲得這個表中<filed>的數量  
  SET ncols = ExtractValue(xml,   
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[1]/field)');  
   # 對於每一個 <row>  
  WHILE rownum <= nrows DO  
    # 對於每一個 <field> (列)  
    WHILE colnum <= ncols DO  
      SET tmp = ExtractValue(xml,   
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]/@name');  
      SET ins_list = CONCAT(ins_list, tmp, IF(colnum<ncols, ',', ''));  
      SET tmp = ExtractValue(xml,   
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]');  
      SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum<ncols, ',', ''));  
      SET colnum = colnum + 1;  
    END WHILE;  
     SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');  
     SET ins_list = '';  
    SET val_list = '';  
     PREPARE stmt FROM @ins_text;  
    EXECUTE stmt;  
     SET rownum = rownum + 1;  
    SET colnum = 1;  
  END WHILE;  
END |  
DELIMITER ; 

仔細閱讀代碼你就會發現其實這個存儲過程使用了我們前面使用到的工具，如LOAD_FILE()和ExtractValue()函數，它只不過增加了兩個嵌套的While循環，遍歷每一行和列。

下面說說導入sqldump.xml文件的步驟。首先須創建xmldump_load存儲過程。

C:\> mysql db_name < text_file  
C:\> mysql temp_table_article < c:\xmldump_load.sql 

你也可以使用source或\.命令從MySQL客戶端載入存儲過程。

mysql> source c:xmldump_load.sql;  
OR 
mysql> \. c:xmldump_load.sql; 

這個存儲過程接受下面三個輸入參數：

我們還是以前面創建的sqldump.xml文件，數據庫和表名為例。

mysql> call xmldump_load('c:\sqldump.xml ', 'client_info', 'client'); 

這條命令將向client_info數據庫client表中插入sqldump.xml文件中的內容。

即將推出的新特性

MySQL 6.0可以接受新的SQL語句，6.0.3版本將提供LOAD XML功能，直接導入XML文件，再也不用啥存儲過程了，在寫本文的時候還是5.4 Beta版本，因此還需耐心等候一段時間。
 

上文詳細介紹了在MySQL中使用XML數據—數據格式化，這個看上去全是代碼，可能理解起來並不是那麼容易，所以就需要大家深入其中，認真去理解，希望對大家有用處。