本文共 3255 字,大约阅读时间需要 10 分钟。
from: http://wenku.baidu.com/link?url=Iwr112wEjDzZbtSSn1EZkiZqH34vkjHtdAfZE9ntNQ9sAWf5G2Gaazu9VAEmzZGn15OnyYeNtwvM2HAShaNU7SjGZUbmfHqKcmLVTJ1xpf_
1,输出数据库版本变量为xml格式
Shell> mysql -X -uroot -proot -e "use test; show variables like '%version%';" 参数X表示生成xml格式的输出 , 参数e表示执行后面的命令 <?xml version="1.0"?> <resultset statement="show variables like '%version%'"> <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.0.22-community-nt-log</field> </row> <row> <field name="Variable_name">version_comment</field> <field name="Value">MySQL Community Edition (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> my test: mysql -u ccc bank -e "select * from person;" mysql -X -u ccc bank -e "select * from person;" ********************************************************************* 2,导出表数据到xml文件 Shell> mysql -X -uroot -proot -e "use test; select * from test;" > ./a.xml Shell> more a.xml <?xml version="1.0"?> <resultset statement="select * from test"> <row> <field name="id">111</field> </row> <row> <field name="id">10</field> </row> <row> <field name="id">1</field> </row> <row> <field name="id">110</field> </row> </resultset> my test: mysql -u ccc bank -e "select * from person;" > ./mysql_noxml.txt mysql -X -u ccc bank -e "select * from person;" > ./mysql_xml.xml ********************************************************************* 3,换一种方式导出表结构和内容,其中第一个db_name是数据库名,第二个test是表名 Shell> mysqldump --xml -uroot -proot db_name test <?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="db_name"> <table_structure name="test"> <field Field="id" Type="int(11)" Null="YES" Key="" Extra="" /> <options Name="test" Engine="InnoDB" Version="10" Row_format="Compact" Rows="4" Avg_row_length="4096" Data_length="16384" Max_data_length="0" Index_length="0" Data_free="0" Create_time ="2008-09-04 02:45:12" Collation="utf8_general_ci" Create_options="" Comment="InnoDB free: 11264 kB" /> </table_structure> <table_data name="test"> <row> <field name="id">111</field> </row> <row> <field name="id">10</field> </row> <row> <field name="id">1</field> </row> <row> <field name="id">110</field> </row> </table_data> </database> </mysqldump> my tests: Shell> mysqldump --xml -u ccc bank person ********************************************************************* 4,导入xml文件的内容到数据库表,这里主要用到了load_file()函数 mysql> create table xmlt( -> id int , -> doc blob -> ); mysql> insert into xmlt values(1,load_file('/home/a.xml') ); mysql> select * from xmlt; +------+-------------------------------------- ---------------------------------------------- | id | doc +------+-------------------------------------- ---------------------------------------------- | 1 | <?xml version="1.0"?> <resultset statement="select * from test"> <row> <field name="id">111</field> </row> <row> <field name="id">10</field> </row> <row> <field name="id">1</field> </row> <row> <field name="id">110</field> </row> </resultset> 后记:由此类推还可以使用存储过程的方式将xml数据导入和导出转载地址:http://vbqzb.baihongyu.com/