在MySQL中使用XML数据—数据格式化
创始人
2024-07-22 12:20:41
0

前沿:MySQL中使用XML数据可以更好的管理数据,将所有数据进行了格式化,这个过程是怎么样的呢?

在存储过程中使用ExtractValue()函数

  上面使用的方法***的缺陷就是在代码中写死了要检索的行,这样很不灵活,下面我们使用一个存储过程来解决这个问题。

  MySqlDump

  有Igor Romanenko编写的MySqlDump客户端最初是一个备份程序,它可以备份数据库,或将一个数据库转移到另一个数据库(MySQL或其它数据库),它的这个备份过程其实是一个创建表,填充表的过程。此外,MySqlDump还可以生成CSV,XML或其它由分隔符控制的文本文件。

  虽然不止一种执行MySqlDump的方法,但我想使用下面的语法:

  1. mysqldump --xmldatabasename[tables] 

  你也可以使用标准的Unix/Dos方法输出到一个文件,如下面的命令将一个表转成一个XML文件: 

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

 

  打开sqldump.xml看看,我们发现MySqlDump客户端包括了比--xml选项更多的信息,但它却是按每一行、字段名和值的方式输出的,MySqlDump将表结构和表数据单独输出。

  1.  
  2.  
  3. name="client">  
  4. client_id Type=int(10)unsignedNull=NO"Key=PRI" Extra=auto_increment/>
  5. Field="date_of_birth" Type="date" Null="YES" Key="" Extra="" />  
  6.                    
  7.                    
  8.                  
  9.                    Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />  
  10.                  
  11.                    Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />  
  12.                  
  13.                    Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="10485760"   
  14.                    Auto_increment="5"   
  15.                    Create_time="2009-10-05 17:51:34" Collation="latin1_swedish_ci" Create_options=""   
  16.                    Comment="" />  
  17.            
  18.            
  19.            
  20.                  1  
  21.                  1976-02-12  
  22.                  M  
  23.                  1  
  24.            
  25.            
  26.                  2  
  27.                  1944-01-15  
  28.                  F  
  29.                  0  
  30.            
  31.            
  32.                  3  
  33.                  1956-06-04  
  34.                  M  
  35.                  1  
  36.            
  37.            
  38.                  4  
  39.                  1938-11-19  
  40.                  F  
  41.                  0  
  42.            
  43.            
  44.  
  45.  

  我们使用这个工具创建XML数据结构的原因是已经有存储过程可以将MySqlDump XML数据插入到表中,MySQL开发人员Alexander Barkov编写了这样一个存储过程xmldump_load,这个存储过程可以从mysqldump --xml命令输出的XML文档中提取数据,并将提取到的数据插入到MySQL表的列中,下面是这个存储过程的全部代码。

 

  1. DELIMITER |  
  2. DROP PROCEDURE IF EXISTS xmldump_load |  
  3. CREATE PROCEDURE xmldump_load(     file_name VARCHAR(128),  
  4.                           database_name VARCHAR(128),  
  5.                           table_name VARCHAR(128))  
  6. BEGIN 
  7.   DECLARE xml TEXT;  
  8.    DECLARE nrows INT;  
  9.   DECLARE rownum INT DEFAULT 1;  
  10.    DECLARE ncols INT;  
  11.   DECLARE colnum INT DEFAULT 1;  
  12.    DECLARE ins_list TEXT DEFAULT '';  
  13.   DECLARE val_list TEXT DEFAULT '';  
  14.    DECLARE tmp VARCHAR(255);  
  15.    # 将XML文件的内容载入到字符串中  
  16.   SET xml = LOAD_FILE(file_name);  
  17.    # 获得这个表中的数量  
  18.   SET nrows = ExtractValue(xml,   
  19.    'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row)');  
  20.    # 获得这个表中的数量  
  21.   SET ncols = ExtractValue(xml,   
  22. 'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[1]/field)');  
  23.    # 对于每一个   
  24.   WHILE rownum <= nrows DO  
  25.     # 对于每一个  (列)  
  26.     WHILE colnum <= ncols DO  
  27.       SET tmp = ExtractValue(xml,   
  28. '/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]/@name');  
  29.       SET ins_list = CONCAT(ins_list, tmp, IF(colnum
  30.       SET tmp = ExtractValue(xml,   
  31. '/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]');  
  32.       SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum
  33.       SET colnum = colnum + 1;  
  34.     END WHILE;  
  35.      SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');  
  36.      SET ins_list = '';  
  37.     SET val_list = '';  
  38.      PREPARE stmt FROM @ins_text;  
  39.     EXECUTE stmt;  
  40.      SET rownum = rownum + 1;  
  41.     SET colnum = 1;  
  42.   END WHILE;  
  43. END |  
  44. DELIMITER ; 

  仔细阅读代码你就会发现其实这个存储过程使用了我们前面使用到的工具,如LOAD_FILE()和ExtractValue()函数,它只不过增加了两个嵌套的While循环,遍历每一行和列。

  下面说说导入sqldump.xml文件的步骤。首先须创建xmldump_load存储过程。

 

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

  你也可以使用source或\.命令从MySQL客户端载入存储过程。

 

  1. mysql> source c:xmldump_load.sql;  
  2. OR 
  3. mysql> \. c:xmldump_load.sql; 

  这个存储过程接受下面三个输入参数:

  1.    XML输入文件名
  2.    目标数据库名
  3.    要创建的表名

  我们还是以前面创建的sqldump.xml文件,数据库和表名为例。

 

  1. 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数据—数据格式化,这个看上去全是代码,可能理解起来并不是那么容易,所以就需要大家深入其中,认真去理解,希望对大家有用处。

【编辑推荐】

  1. 详解MYSQL中无重复插入数据更新语法
  2. 安全应用MySQL中对自启动配置的要求
  3. 影响MySQL中mysqld安全的4个选项有哪些?

相关内容

热门资讯

如何允许远程连接到MySQL数... [[277004]]【51CTO.com快译】默认情况下,MySQL服务器仅侦听来自localhos...
如何利用交换机和端口设置来管理... 在网络管理中,总是有些人让管理员头疼。下面我们就将介绍一下一个网管员利用交换机以及端口设置等来进行D...
施耐德电气数据中心整体解决方案... 近日,全球能效管理专家施耐德电气正式启动大型体验活动“能效中国行——2012卡车巡展”,作为该活动的...
20个非常棒的扁平设计免费资源 Apple设备的平面图标PSD免费平板UI 平板UI套件24平图标Freen平板UI套件PSD径向平...
德国电信门户网站可实时显示全球... 德国电信周三推出一个门户网站,直观地实时提供其安装在全球各地的传感器网络检测到的网络攻击状况。该网站...
为啥国人偏爱 Mybatis,... 关于 SQL 和 ORM 的争论,永远都不会终止,我也一直在思考这个问题。昨天又跟群里的小伙伴进行...
《非诚勿扰》红人闫凤娇被曝厕所... 【51CTO.com 综合消息360安全专家提醒说,“闫凤娇”、“非诚勿扰”已经被黑客盯上成为了“木...
2012年第四季度互联网状况报... [[71653]]  北京时间4月25日消息,据国外媒体报道,全球知名的云平台公司Akamai Te...