MySQL :: MySQL 8.3 Reference Manual :: 15.2.10 LOAD XML Statement

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / LOAD XML Statement

15.2.10 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 文件中读取数据到表中。 file_name 必须作为文字字符串给出。可选的 ROWS IDENTIFIED BY 子句中的 tagname 也必须作为文字字符串给出，并且必须被角括号 (< 和 >) 包围。

LOAD XML 是 mysql 客户端在 XML 输出模式下运行的补充（即，以 --xml 选项启动客户端）。要将表数据写入 XML 文件，可以从系统 shell 中以 --xml 和 -e 选项启动 mysql 客户端，如下所示：

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

要将文件读回表中，请使用 LOAD XML。默认情况下，<row> 元素被认为是数据库表行的等效项；这可以使用 ROWS IDENTIFIED BY 子句更改。

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

列名作为属性，列值作为属性值：

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

列名作为标签，列值作为这些标签的内容：

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

列名是 name 属性的 <field> 标签，值是这些标签的内容：
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```
这是在其他 MySQL 工具中使用的格式，例如 mysqldump。

所有三种格式可以在同一个 XML 文件中使用；导入routine 会自动检测每行的格式并正确地解释它。标签是根据标签或属性名称和列名匹配的。

以下子句对 LOAD XML 的工作方式与 LOAD DATA 相同：

LOW_PRIORITY 或 CONCURRENT
LOCAL
REPLACE 或 IGNORE
CHARACTER SET
SET

请参阅第 15.2.9 节，“LOAD DATA 语句”，以获取更多关于这些子句的信息。

(field_name_or_user_var, ...) 是一个或多个逗号分隔的 XML 字段或用户变量列表。用于此目的的用户变量名称必须与 XML 文件中的字段名称匹配，前缀为 @。您可以使用字段名称来选择所需的字段。用户变量可以用于存储相应的字段值，以便后续重用。

该 IGNORE number LINES 或 IGNORE number ROWS 子句会导致 XML 文件中的前 number 行被跳过。这类似于 LOAD DATA 语句的 IGNORE ... LINES 子句。

假设我们有一个名为 person 的表，创建如下所示：

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>

每种允许的 XML 格式都在这个示例文件中表示。

要将 person.xml 文件中的数据导入 person 表，可以使用以下语句：

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> 元素被视为等同于要导入数据的表中的行。在这种情况下，这是 person 表在 test 数据库中。

如服务器响应所示，8 行被导入到 test.person 表中。这可以通过简单的 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)

这表明，如本节前面所述，任何或所有 3 个允许的 XML 格式都可以出现在单个文件中，并使用 LOAD XML 读取。

刚刚显示的导入操作的逆操作，即将 MySQL 表数据转储到 XML 文件中，可以使用 mysql 客户端从系统 shell 中执行，如下所示：

$> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
$> 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 选项使 mysql 客户端使用 XML 格式化其输出；-e 选项使客户端立即执行 SQL 语句。请参阅第 6.5.1 节，“mysql — MySQL 命令行客户端”。

您可以通过创建 person 表的副本并将转储文件导入新表来验证转储的有效性，如下所示：

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 文件中的每个字段都与对应表中的列匹配。没有对应列的字段将被跳过。您可以通过首先清空 person2 表，然后删除 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 NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 文件中每行字段的顺序不会影响 LOAD XML 的操作；字段顺序可以从行到行变化，并且不需要与表中的列顺序相同。

如前所述，您可以使用一个 (field_name_or_user_var, ...) 列表，其中包含一个或多个 XML 字段（以选择所需字段）或用户变量（以存储对应字段值以供后续使用）。用户变量特别有用，当您想从 XML 文件中插入数据到表列名与 XML 字段名不匹配时。

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 文件加载到表中，因为字段名和列名不匹配：

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 服务器查找与目标表列名匹配的字段名。您可以通过将字段值选择到用户变量中，然后使用 SET 将目标表的列设置为这些变量的值来解决这个问题，如下所示：

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 文件字段名匹配，添加了必需的 @ 前缀以指示它们是变量。用户变量不需要按与对应字段相同的顺序列出或分配。

使用 ROWS IDENTIFIED BY '<tagname>' 子句，可以从同一个 XML 文件中将数据导入到具有不同定义的数据库表中。例如，假设您有一个名为 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>

您可以再次使用本节前面定义的 test.person 表，首先清空表中的所有记录，然后显示表结构，如下所示：

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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

现在，在 test 数据库中创建一个 address 表，使用以下 CREATE 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 表，执行以下 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 表中，因此它们被跳过。

要将数据从 <address> 元素导入到 address 表中，使用以下 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)

XML 文件中的 <address> 元素中的数据，如果被 XML 注释所包围，则不会被导入。然而，由于 address 表中有 person_id 列，因此来自父 <person> 元素的 person_id 属性值将被导入到 address 表中。

安全注意事项。 与 LOAD DATA 语句一样，从客户端主机到服务器主机的 XML 文件传输是由 MySQL 服务器发起的。在理论上，一个修补的服务器可以被构建，以便让客户端程序传输服务器选择的文件，而不是客户端在 LOAD XML 语句中指定的文件。这样的服务器可以访问客户端主机上的任何文件，客户端用户有读取权限。

在 Web 环境中，客户端通常从 Web 服务器连接到 MySQL 服务器。可以运行任何命令的用户可以使用 LOAD XML LOCAL 读取 Web 服务器进程有读取权限的任何文件。在这种环境中，相对于 MySQL 服务器的客户端实际上是 Web 服务器，而不是远程程序的用户。

您可以通过在服务器启动时使用 --local-infile=0 或 --local-infile=OFF 来禁用从客户端加载 XML 文件。此选项也可以在启动 mysql 客户端时使用，以禁用 LOAD XML 语句的整个客户端会话。

要防止客户端从服务器加载 XML 文件，不要授予相应的 MySQL 用户账户 FILE 权限，或者撤销该权限，如果客户端用户账户已经拥有该权限。

Important

撤销 FILE 权限（或不授予该权限）将阻止用户执行 LOAD XML 语句（以及 LOAD_FILE() 函数）；但是，它不会阻止用户执行 LOAD XML LOCAL 语句。要禁用该语句，您必须在服务器或客户端启动时使用 --local-infile=OFF。

换言之，FILE 权限仅影响客户端是否可以在服务器上读取文件；它不影响客户端是否可以在本地文件系统上读取文件。

PREV HOME UP NEXT