13.2.7 LOAD 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}] ...]
LOAD XML语句将数据从 XML 文件读取到 table 中。 * file_name
必须以 Literals 字符串形式给出。可选的ROWS IDENTIFIED BY
子句中的 tagname
*也必须作为 Literals 字符串给出,并且必须用尖括号(<
和>
)包围。
LOAD XML作为在 XML 输出模式下运行mysqlClient 端的补充(即,使用--xml选项启动 Client 端)。要将 table 中的数据写入 XML 文件,您可以使用系统 Shell 程序中的--xml和-e选项调用mysqlClient 端,如下所示:
shell> mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml
要将文件读回到 table 中,请使用LOAD XML。默认情况下,<row>
元素被认为等同于数据库 table 行;可以使用ROWS IDENTIFIED BY
子句更改此设置。
该语句支持三种不同的 XML 格式:
- 列名称作为属性,列值作为属性值:
<row column1="value1" column2="value2" .../>
- 列名作为标签,列值作为这些标签的内容:
<row>
<column1>value1</column1>
<column2>value2</column2>
</row>
- 列名是
<field>
标签的name
属性,值是这些标签的内容:
<row>
<field name='column1'>value1</field>
<field name='column2'>value2</field>
</row>
这是其他 MySQL 工具使用的格式,例如mysqldump。
三种格式都可以在同一 XML 文件中使用。导入例程会自动检测每一行的格式并正确解释。根据标签或属性名称和列名称来匹配标签。
在 MySQL 5.7 中,LOAD XML
不支持源 XML 中的CDATA
部分。此限制已在 MySQL 8.0 中删除。 (缺陷号 30753708,错误#98199)
以下子句对LOAD XML的工作方式与LOAD DATA基本上相同:
-
LOW_PRIORITY
或CONCURRENT
-
LOCAL
-
REPLACE
或IGNORE
-
CHARACTER SET
-
SET
有关这些子句的更多信息,请参见第 13.2.6 节“ LOAD DATA 语句”。
(field_name_or_user_var, ...)
是一个或多个逗号分隔的 XML 字段或用户变量的列 table。用于此目的的用户变量的名称必须与 XML 文件中以@
为前缀的字段名称匹配。您可以使用字段名称仅选择所需的字段。可以使用用户变量来存储相应的字段值,以供后续重用。
IGNORE number LINES
或IGNORE number ROWS
子句会导致 XML 文件中的前* number
*行被跳过。它类似于LOAD DATA语句的IGNORE ... LINES
子句。
假设我们创建了一个名为person
的 table,如下所示:
USE test;
CREATE TABLE person (
person_id INT NOT NULL PRIMARY KEY,
fname VARCHAR(40) NULL,
lname VARCHAR(40) NULL,
created TIMESTAMP
);
进一步假设该 table 最初为空。
现在假设我们有一个简单的 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>
此示例文件中 table 示了前面讨论的每种允许的 XML 格式。
要将person.xml
中的数据导入到person
table 中,可以使用以下语句:
mysql> LOAD XML LOCAL INFILE 'person.xml'
-> INTO TABLE person
-> ROWS IDENTIFIED BY '<person>';
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
在这里,我们假设person.xml
位于 MySQL 数据目录中。如果找不到该文件,则会导致以下错误:
ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)
ROWS IDENTIFIED BY '<person>'
子句意味着 XML 文件中的每个<person>
元素都被视为等效于 table 中要将数据导入到其中的一行。在这种情况下,这是test
数据库中的person
table。
从服务器的响应可以看出,将 8 行导入到test.person
table 中。这可以通过简单的SELECT语句来验证:
mysql> SELECT * FROM person;
+-----------+--------+------------+---------------------+
| person_id | fname | lname | created |
+-----------+--------+------------+---------------------+
| 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 |
| 2 | Sajon | Rondela | 2007-07-13 16:18:47 |
| 3 | Likame | Örrtmons | 2007-07-13 16:18:47 |
| 4 | Slar | Manlanth | 2007-07-13 16:18:47 |
| 5 | Stoma | Nilu | 2007-07-13 16:18:47 |
| 6 | Nirtam | Sklöd | 2007-07-13 16:18:47 |
| 7 | Sungam | Dulbåd | 2007-07-13 16:18:47 |
| 8 | Sreraf | Encmelt | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)
如本节前面所述,这 table 明 3 种允许的 XML 格式中的任何一种或全部都可以出现在单个文件中,并可以使用LOAD XML进行读取。
刚刚显示的导入操作的相反过程(即,将 MySQLtable 数据转储为 XML 文件)可以使用系统 Shell 中的mysqlClient 端来完成,如下所示:
shell> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
shell> cat person-dump.xml
<?xml version="1.0"?>
<resultset statement="SELECT * FROM test.person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="person_id">1</field>
<field name="fname">Kapek</field>
<field name="lname">Sainnouine</field>
</row>
<row>
<field name="person_id">2</field>
<field name="fname">Sajon</field>
<field name="lname">Rondela</field>
</row>
<row>
<field name="person_id">3</field>
<field name="fname">Likema</field>
<field name="lname">Örrtmons</field>
</row>
<row>
<field name="person_id">4</field>
<field name="fname">Slar</field>
<field name="lname">Manlanth</field>
</row>
<row>
<field name="person_id">5</field>
<field name="fname">Stoma</field>
<field name="lname">Nilu</field>
</row>
<row>
<field name="person_id">6</field>
<field name="fname">Nirtam</field>
<field name="lname">Sklöd</field>
</row>
<row>
<field name="person_id">7</field>
<field name="fname">Sungam</field>
<field name="lname">Dulbåd</field>
</row>
<row>
<field name="person_id">8</field>
<field name="fname">Sreraf</field>
<field name="lname">Encmelt</field>
</row>
</resultset>
Note
--xml选项使mysqlClient 端对其输出使用 XML 格式。 -e选项使 Client 端在该选项之后立即执行 SQL 语句。参见第 4.5.1 节“ mysql-MySQL 命令行 Client 端”。
您可以通过创建person
table 的副本并将转储文件导入到新 table 中来验证转储是否有效,如下所示:
mysql> USE test;
mysql> CREATE TABLE person2 LIKE person;
Query OK, 0 rows affected (0.00 sec)
mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
-> INTO TABLE person2;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM person2;
+-----------+--------+------------+---------------------+
| person_id | fname | lname | created |
+-----------+--------+------------+---------------------+
| 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 |
| 2 | Sajon | Rondela | 2007-07-13 16:18:47 |
| 3 | Likema | Örrtmons | 2007-07-13 16:18:47 |
| 4 | Slar | Manlanth | 2007-07-13 16:18:47 |
| 5 | Stoma | Nilu | 2007-07-13 16:18:47 |
| 6 | Nirtam | Sklöd | 2007-07-13 16:18:47 |
| 7 | Sungam | Dulbåd | 2007-07-13 16:18:47 |
| 8 | Sreraf | Encmelt | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)
不需要 XML 文件中的每个字段都与对应 table 中的列匹配。没有相应列的字段将被跳过。通过先清空person2
table 并删除created
列,然后使用我们之前使用的相同的LOAD XML语句,您可以看到这一点,如下所示:
mysql> TRUNCATE person2;
Query OK, 8 rows affected (0.26 sec)
mysql> ALTER TABLE person2 DROP COLUMN created;
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE person2\G
*************************** 1. row ***************************
Table: person2
Create Table: CREATE TABLE `person2` (
`person_id` int(11) NOT NULL,
`fname` varchar(40) DEFAULT NULL,
`lname` varchar(40) DEFAULT NULL,
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
-> INTO TABLE person2;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM person2;
+-----------+--------+------------+
| person_id | fname | lname |
+-----------+--------+------------+
| 1 | Kapek | Sainnouine |
| 2 | Sajon | Rondela |
| 3 | Likema | Örrtmons |
| 4 | Slar | Manlanth |
| 5 | Stoma | Nilu |
| 6 | Nirtam | Sklöd |
| 7 | Sungam | Dulbåd |
| 8 | Sreraf | Encmelt |
+-----------+--------+------------+
8 rows in set (0.00 sec)
XML 文件的每一行中字段的 Sequences 不影响LOAD XML的操作;字段 Sequences 在行与行之间可以不同,并且不需要与 table 中相应列的 Sequences 相同。
如前所述,您可以使用一个或多个 XML 字段的(field_name_or_user_var, ...)
列 table(仅选择所需的字段)或用户变量(存储相应的字段值以供以后使用)。当您要将 XML 文件中的数据插入名称与 XML 字段名称不匹配的 table 列中时,用户变量尤其有用。为了了解其工作原理,我们首先创建一个名为individual
的 table,该 table 的结构与person
table 的结构相匹配,但是其列的名称不同:
mysql> CREATE TABLE individual (
-> individual_id INT NOT NULL PRIMARY KEY,
-> name1 VARCHAR(40) NULL,
-> name2 VARCHAR(40) NULL,
-> made TIMESTAMP
-> );
Query OK, 0 rows affected (0.42 sec)
在这种情况下,您不能简单地将 XML 文件直接加载到 table 中,因为字段名和列名不匹配:
mysql> LOAD XML INFILE '../bin/person-dump.xml' INTO TABLE test.individual;
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1
发生这种情况是因为 MySQL 服务器正在寻找与目标 table 的列名匹配的字段名。您可以通过在用户变量中选择字段值,然后使用SET
将目标 table 的列设置为等于这些变量的值来解决此问题。您可以在一个语句中执行这两个操作,如下所示:
mysql> 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;
Query OK, 8 rows affected (0.05 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM individual;
+---------------+--------+------------+---------------------+
| individual_id | name1 | name2 | made |
+---------------+--------+------------+---------------------+
| 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 |
| 2 | Sajon | Rondela | 2007-07-13 16:18:47 |
| 3 | Likema | Örrtmons | 2007-07-13 16:18:47 |
| 4 | Slar | Manlanth | 2007-07-13 16:18:47 |
| 5 | Stoma | Nilu | 2007-07-13 16:18:47 |
| 6 | Nirtam | Sklöd | 2007-07-13 16:18:47 |
| 7 | Sungam | Dulbåd | 2007-07-13 16:18:47 |
| 8 | Srraf | Encmelt | 2007-07-13 16:18:47 |
+---------------+--------+------------+---------------------+
8 rows in set (0.00 sec)
用户变量的名称必须与 XML 文件中相应字段的名称相匹配,并加上必需的@
前缀以指示它们是变量。用户变量无需按照与相应字段相同的 Sequences 列出或分配。
使用ROWS IDENTIFIED BY '<tagname>'
子句,可以将同一 XML 文件中的数据导入具有不同定义的数据库 table 中。对于此示例,假设您有一个名为address.xml
的文件,其中包含以下 XML:
<?xml version="1.0"?>
<list>
<person person_id="1">
<fname>Robert</fname>
<lname>Jones</lname>
<address address_id="1" street="Mill Creek Road" zip="45365" city="Sidney"/>
<address address_id="2" street="Main Street" zip="28681" city="Taylorsville"/>
</person>
<person person_id="2">
<fname>Mary</fname>
<lname>Smith</lname>
<address address_id="3" street="River Road" zip="80239" city="Denver"/>
<!-- <address address_id="4" street="North Street" zip="37920" city="Knoxville"/> -->
</person>
</list>
从 table 中清除所有现有记录,然后显示其结构后,您可以再次使用本节先前定义的test.person
table:
mysql< TRUNCATE person;
Query OK, 0 rows affected (0.04 sec)
mysql< SHOW CREATE TABLE person\G
*************************** 1. row ***************************
Table: person
Create Table: CREATE TABLE `person` (
`person_id` int(11) NOT NULL,
`fname` varchar(40) DEFAULT NULL,
`lname` varchar(40) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
现在,使用以下CREATE TABLE语句在test
数据库中创建address
table:
CREATE TABLE address (
address_id INT NOT NULL PRIMARY KEY,
person_id INT NULL,
street VARCHAR(40) NULL,
zip INT NULL,
city VARCHAR(40) NULL,
created TIMESTAMP
);
要将数据从 XML 文件导入到person
table 中,请执行以下LOAD XML语句,该语句指定要由<person>
元素指定行,如下所示;
mysql> LOAD XML LOCAL INFILE 'address.xml'
-> INTO TABLE person
-> ROWS IDENTIFIED BY '<person>';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
您可以使用SELECT语句验证记录是否已导入:
mysql> SELECT * FROM person;
+-----------+--------+-------+---------------------+
| person_id | fname | lname | created |
+-----------+--------+-------+---------------------+
| 1 | Robert | Jones | 2007-07-24 17:37:06 |
| 2 | Mary | Smith | 2007-07-24 17:37:06 |
+-----------+--------+-------+---------------------+
2 rows in set (0.00 sec)
由于 XML 文件中的<address>
元素在person
table 中没有对应的列,因此将跳过它们。
要将数据从<address>
元素导入到address
table 中,请使用此处显示的LOAD XML语句:
mysql> LOAD XML LOCAL INFILE 'address.xml'
-> INTO TABLE address
-> ROWS IDENTIFIED BY '<address>';
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
您可以看到数据是使用诸如此类的SELECT语句导入的:
mysql> SELECT * FROM address;
+------------+-----------+-----------------+-------+--------------+---------------------+
| address_id | person_id | street | zip | city | created |
+------------+-----------+-----------------+-------+--------------+---------------------+
| 1 | 1 | Mill Creek Road | 45365 | Sidney | 2007-07-24 17:37:37 |
| 2 | 1 | Main Street | 28681 | Taylorsville | 2007-07-24 17:37:37 |
| 3 | 2 | River Road | 80239 | Denver | 2007-07-24 17:37:37 |
+------------+-----------+-----------------+-------+--------------+---------------------+
3 rows in set (0.00 sec)
不会导入 XMLComments 中包含的<address>
元素中的数据。但是,由于address
table 中有person_id
列,因此每个<address>
*的父<person>
元素的person_id
属性的值都被导入address
table 中。
安全考虑. 与LOAD DATA语句一样,XML 文件从 Client 端主机到服务器主机的传输由 MySQL 服务器启动。从理论上讲,可以构建一个打补丁的服务器,该服务器将告诉 Client 端程序传输服务器选择的文件,而不是 Client 端在LOAD XML语句中命名的文件。这样的服务器可以访问 Client 端用户具有读取权限的 Client 端主机上的任何文件。
在 Web 环境中,Client 端通常从 Web 服务器连接到 MySQL。可以对 MySQL 服务器运行任何命令的用户可以使用加载 XML LOCAL来读取 Web 服务器进程具有读取访问权限的任何文件。在这种环境中,关于 MySQL 服务器的 Client 端实际上是 Web 服务器,而不是由连接到 Web 服务器的用户运行的远程程序。
您可以通过使用--local-infile=0或--local-infile=OFF启动服务器来禁用从 Client 端加载 XML 文件。在启动mysqlClient 端以在 Client 端会话期间禁用LOAD XML时,也可以使用此选项。
为了防止 Client 端从服务器加载 XML 文件,请勿将FILE特权授予相应的 MySQL 用户帐户,如果 Client 端用户帐户已经拥有FILE特权,则不要撤消该特权。
Important
撤消FILE特权(或首先不授予它),只会使用户仅执行LOAD XML语句(以及LOAD_FILE()函数;不会禁止用户执行加载 XML LOCAL)。要禁止该语句,您必须使用--local-infile=OFF
启动服务器或 Client 端。
换句话说,FILE特权仅影响 Client 端是否可以读取服务器上的文件;它与 Client 端是否可以读取本地文件系统上的文件无关。
对于使用 table 锁定(例如MyISAM)的存储引擎的分区 table,由LOAD XML引起的任何锁定都会对 table 的所有分区执行锁定。这不适用于使用采用行级锁定(例如InnoDB)的存储引擎的 table。有关更多信息,请参见第 22.6.4 节“分区和锁定”。