MySQL处理xml文件

最新推荐文章于 2024-10-05 23:41:14 发布

原创最新推荐文章于 2024-10-05 23:41:14 发布 · 6.7k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #xml

mysql 专栏收录该内容

13 篇文章

订阅专栏

参考资料

[1]. 13.2.7 LOAD XML Syntax
https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

导出xml格式的文件

mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

读取文件的语法格式

LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE [db_name.]tbl_name
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<tagname>']
    [IGNORE number {LINES | ROWS}]
    [(field_name_or_user_var
        [, field_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

这个语句支持三种不同的XML格式：

列名称作为属性和列值作为属性值：

<row column1="value1" column2="value2" .../>

列名称作为标签和列值作为这些标签的内容

<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>

列名称是标签的name属性，值是这些标签的内容：

<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>

在MySQL 5.5.46之前，LOAD XML没有正确处理表单中的空XML元素。（错误＃67542，错误＃16171518）

操作实例

创建一个空表

USE test;

CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL,
    created TIMESTAMP
);

准备一个简单的XML文件 person.xml

<list>
  <person person_id="1" fname="Kapek" lname="Sainnouine"/>
  <person person_id="2" fname="Sajon" lname="Rondela"/>
  <person person_id="3"><fname>Likame</fname><lname>Örrtmons</lname></person>
  <person person_id="4"><fname>Slar</fname><lname>Manlanth</lname></person>
  <person><field name="person_id">5</field><field name="fname">Stoma</field>
    <field name="lname">Milu</field></person>
  <person><field name="person_id">6</field><field name="fname">Nirtam</field>
    <field name="lname">Sklöd</field></person>
  <person person_id="7"><fname>Sungam</fname><lname>Dulbåd</lname></person>
  <person person_id="8" fname="Sraref" lname="Encmelt"/>
</list>

将数据从person.xml导入到person表

# 使用关键字LOCAL，文件存储在本地的模式
LOAD XML LOCAL INFILE '/root/person.xml'
INTO TABLE person
ROWS IDENTIFIED BY '<person>';

# 不使用关键字LOCAL，查看数据存储地址，并将文件放入，即文件存放在数据库的存储文件夹
show variables like '%datadir%';
LOAD XML INFILE 'person.xml'
INTO TABLE person
ROWS IDENTIFIED BY '<person>';

创建另外一个空表

CREATE TABLE individual (
individual_id INT NOT NULL PRIMARY KEY,
name1 VARCHAR(40) NULL,
name2 VARCHAR(40) NULL,
made TIMESTAMP
);

将数据导入

因为字段不一样，所以需要使用set指定对应的字段

LOAD XML INFILE '../bin/person-dump.xml'
INTO TABLE test.individual (@person_id, @fname, @lname, @created)
SET individual_id=@person_id, name1=@fname, name2=@lname, made=@created;