会员投稿
投稿指南 
RSS订阅栏目导航
热点推荐
- mysql排序的妙用(类似置顶)
- 优化MySQL的数据库性能的八种方
- MySQL配置文件my.cnf中文版
- linux下mysql命令
- 如何用MySQL-Front远程连接MySql
- 如何修改MySQL 5.1 data文件夹路
- Linux mysql安装修改root密码服
- MySQL Server 5.0安装教程(图解)
- PHP中的MYSQL常用函数
- mysql常见错误提示及解决方法
- MySQL常见错误问答!
- 使用MySQL时的一些常见错误
阅览排行
如何在MySQL数据库中使用XML数据
www.jz123.cn 2009-12-25 来源: 中国建站 责任编辑(袁袁) 我要投递新闻
通过XML使系统之间的数据交换变得更简单，因为它与编程语言无关，刚引入XML的概念时，是通过一个脚本或应用程序解析XML数据，将其转换为适合于数据库和底层系统的有效格式，后来，随着数据库技术的发展，数据库开始支持XML数据，这样就不用转换程序了。今天我将给大家介绍一下MySQL数据库对XML的支持，着重介绍如何导入XML数据到MySQL数据库中，以及如何从MySQL数据库导出XML格式数据。
使用--xml选项将数据导出为XML格式
在MySQL 5.0之前的版本中，使用MySQL命令行客户端以XML格式导出数据时有诸多限制，执行一个命令或查询时使用--xml或-X选项告诉MySQL客户端将结果输出为XML数据，例如，下面的命令将输出所有以version开头的数据库变量。
C:>mysql -u <userid> -p<password> -e "SHOW VARIABLES LIKE '%version%'" –-xml
最终输出的XML包括下面的标准格式：
整个数据集都包括在节点中;
每一行对应一个节点;
所有列包括在一个节点中;
列名出处在name属性中;
列值是一个文本节点。
<?xml version="1.0"?>
<resultset statement="SHOW VARIABLES LIKE '%version%'"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">protocol_version</field>
<field name="Value">10</field>
</row>
<row>
<field name="Variable_name">version</field>
<field name="Value">5.1.30-community</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">MySQL Community Server (GPL)</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">ia32</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">Win32</field>
</row>
</resultset>
<resultset statement="SHOW VARIABLES LIKE '%version%'"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">protocol_version</field>
<field name="Value">10</field>
</row>
<row>
<field name="Variable_name">version</field>
<field name="Value">5.1.30-community</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">MySQL Community Server (GPL)</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">ia32</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">Win32</field>
</row>
</resultset>
为了便于对比，下面给出上面的命令表格化输出结果。
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.22-beta-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | suse-linux-gnu |
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.22-beta-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | suse-linux-gnu |
+-------------------------+---------------------+
同样的语法可以应用到SELECT语句中，在我以前的一篇文章“借助临时表让你的MySQL查询效率更高”中，最开始的查询中我就用logical_delete_indicator=0的记录填充client_citizenship临时表。
INSERT INTO client_citizenship
SELECT cl.client_id,
cl.date_of_birth,
cl.gender,
cit.citizenship_id,
cit.country_code,
cit.primary_citizenship
FROM temp_table_article.client AS cl,
temp_table_article.citizenship AS cit,
temp_table_article.client_citizenship_rel AS rel
WHERE cl.client_id = rel.client_id
AND cit.citizenship_id = rel.citizenship_id
AND cit.logical_delete_indicator = 0
AND cl.logical_delete_indicator = 0;
SELECT cl.client_id,
cl.date_of_birth,
cl.gender,
cit.citizenship_id,
cit.country_code,
cit.primary_citizenship
FROM temp_table_article.client AS cl,
temp_table_article.citizenship AS cit,
temp_table_article.client_citizenship_rel AS rel
WHERE cl.client_id = rel.client_id
AND cit.citizenship_id = rel.citizenship_id
AND cit.logical_delete_indicator = 0
AND cl.logical_delete_indicator = 0;
在这个临时表上执行“SELECT *”查询返回下面的记录：
client_id, date_of_birth, gender, citizenship_id, country_code, primary_citizenship
2, 1944-01-15, F, 4, 20, 0
2, 1944-01-15, F, 7, 77, 1
2, 1944-01-15, F, 4, 20, 0
2, 1944-01-15, F, 7, 77, 1
当我加上--xml选项后，“SELECT *”查询将返回下面的XML格式结果：
<?xml version="1.0"?>
<resultset statement="SELECT * FROM client_citizenship"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="client_id">2</field>
<field name="date_of_birth">1944-01-15</field>
<field name="gender">F</field>
<field name="citizenship_id">4</field>
<field name="country_code">20</field>
<field name="primary_citizenship">0</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="citizenship_id">7</field>
<field name="country_code">77</field>
<field name="primary_citizenship">1</field>
</row>
</resultset>
<resultset statement="SELECT * FROM client_citizenship"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="client_id">2</field>
<field name="date_of_birth">1944-01-15</field>
<field name="gender">F</field>
<field name="citizenship_id">4</field>
<field name="country_code">20</field>
<field name="primary_citizenship">0</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="citizenship_id">7</field>
<field name="country_code">77</field>
<field name="primary_citizenship">1</field>
</row>
</resultset>
使用--xml选项的缺点是从关联的数据到XML的映射是固定的，因此无法修改输出。如果你想做某些字符串操作，可以去掉--xml选项，按你自己的方式生成XML代码。
继续沿用前面的查询，我们再来看看如何修改输出的XML，使其符合下面的格式要求：
<client>
<client_id>2</client_id>
<date_of_birth>1944-01-15</date_of_birth>
<gender>F</gender>
<citizenship_id>7</citizenship_id>
<country_code>77</country_code>
<primary_citizenship>1</primary_citizenship>
</client>
<client_id>2</client_id>
<date_of_birth>1944-01-15</date_of_birth>
<gender>F</gender>
<citizenship_id>7</citizenship_id>
<country_code>77</country_code>
<primary_citizenship>1</primary_citizenship>
</client>
其中一个可行的办法是使用CONCAT() 和 GROUP_CONCAT()字符串函数。
mysql>SELECT CONCAT('n<client>n',
->GROUP_CONCAT('<client_id>', client_id, '</client_id>n' SEPARATOR ''),
->GROUP_CONCAT('<date_of_birth>',date_of_birth,'</date_of_birth>n' SEPARATOR ''),
->GROUP_CONCAT('<gender>',gender,'</gender>n' SEPARATOR ''),
->GROUP_CONCAT('<citizenship_id>',citizenship_id,'</citizenship_id>n' SEPARATOR ''),
->GROUP_CONCAT('<country_cd>',country_cd,'</country_cd>n' SEPARATOR ''),
->GROUP_CONCAT('<primary_citizenship>',primary_citizenship,'</primary_citizenship>n' SEPARATOR ''),
->'</client>') AS xmldoc
->FROM client_citizenshipG
->GROUP_CONCAT('<client_id>', client_id, '</client_id>n' SEPARATOR ''),
->GROUP_CONCAT('<date_of_birth>',date_of_birth,'</date_of_birth>n' SEPARATOR ''),
->GROUP_CONCAT('<gender>',gender,'</gender>n' SEPARATOR ''),
->GROUP_CONCAT('<citizenship_id>',citizenship_id,'</citizenship_id>n' SEPARATOR ''),
->GROUP_CONCAT('<country_cd>',country_cd,'</country_cd>n' SEPARATOR ''),
->GROUP_CONCAT('<primary_citizenship>',primary_citizenship,'</primary_citizenship>n' SEPARATOR ''),
->'</client>') AS xmldoc
->FROM client_citizenshipG
虽然我们使用这种变通的方法达到了目标，但这样做还不如使用脚本或编程语言执行格式化，当然这得看你是否熟悉相关脚本或编程语言了。
0
上一篇：MYSQL教程:MySQL Show命令 下一篇：在MySQL中使用XML数据—数据格式化