13.2.8 LOAD XML Statement语句

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}] ...]

The LOAD XML statement reads data from an XML file into a table. LOAD XML语句将数据从XML文件读入表中。The file_name must be given as a literal string. file_name必须以文本字符串形式给出。The tagname in the optional ROWS IDENTIFIED BY clause must also be given as a literal string, and must be surrounded by angle brackets (< and >).在可选的ROWS IDENTIFIED BY子句中的tagname名也必须以文字字符串形式给出，并且必须用尖括号（<和>）。

LOAD XML acts as the complement of running the mysql client in XML output mode (that is, starting the client with the --xml option). LOAD XML作为在XML输出模式下运行mysql客户机的补充（即，使用--xml选项启动客户机）。To write data from a table to an XML file, you can invoke the mysql client with the --xml and -e options from the system shell, as shown here:要将表中的数据写入XML文件，可以使用系统shell中的--xml和-e选项调用mysql客户端，如下所示：

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

To read the file back into a table, use LOAD XML. 要将文件读回表中，请使用LOAD XML。By default, the <row> element is considered to be the equivalent of a database table row; this can be changed using the ROWS IDENTIFIED BY clause.默认情况下，<row>元素被认为是数据库表行的等价物；这可以使用ROWS IDENTIFIED BY子句来更改。

This statement supports three different XML formats:此语句支持三种不同的XML格式：

All three formats can be used in the same XML file; the import routine automatically detects the format for each row and interprets it correctly. 这三种格式都可以用在同一个XML文件中；导入例程自动检测每一行的格式并正确解释它。Tags are matched based on the tag or attribute name and the column name.根据标记或属性名和列名匹配标记。

Prior to MySQL 8.0.21, LOAD XML did not support CDATA sections in the source XML.在MySQL 8.0.21之前，LOAD XML不支持源XML中的CDATA节。 (Bug #30753708, Bug #98199)

The following clauses work essentially the same way for LOAD XML as they do for LOAD DATA:以下子句对LOAD XML的工作方式与对LOAD DATA的工作方式基本相同：

See Section 13.2.7, “LOAD DATA Statement”, for more information about these clauses.有关这些子句的更多信息，请参阅第13.2.7节，“LOAD DATA语句”。

(field_name_or_user_var, ...) is a list of one or more comma-separated XML fields or user variables. (field_name_or_user_var, ...)是一个或多个逗号分隔的XML字段或用户变量的列表。The name of a user variable used for this purpose must match the name of a field from the XML file, prefixed with @. 用于此目的的用户变量的名称必须与XML文件中以@为前缀的字段的名称匹配。You can use field names to select only desired fields. 可以使用字段名仅选择所需的字段。User variables can be employed to store the corresponding field values for subsequent re-use.可以使用用户变量来存储相应的字段值，以供后续重用。

The IGNORE number LINES or IGNORE number ROWS clause causes the first number rows in the XML file to be skipped. IGNORE number LINES子句或IGNORE number ROWS子句导致跳过XML文件中的第一个数字行。It is analogous to the LOAD DATA statement's IGNORE ... LINES clause.它类似于LOAD DATA语句中的IGNORE ... LINES子句。

Suppose that we have a table named person, created as shown here:假设我们有一个名为person的表，如下所示：

USE test;

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

Suppose further that this table is initially empty.进一步假设这个表最初是空的。

Now suppose that we have a simple XML file person.xml, whose contents are as shown here:现在假设我们有一个简单的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>

Each of the permissible XML formats discussed previously is represented in this example file.前面讨论的每个允许的XML格式都在这个示例文件中表示。

To import the data in person.xml into the person table, you can use this statement:要将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

Here, we assume that person.xml is located in the MySQL data directory. 这里，我们假设person.xml位于MySQL数据目录中。If the file cannot be found, the following error results:如果找不到该文件，将导致以下错误：

ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)

The ROWS IDENTIFIED BY '<person>' clause means that each <person> element in the XML file is considered equivalent to a row in the table into which the data is to be imported. ROWS IDENTIFIED BY '<person>'子句意味着XML文件中的每个<person>元素被认为等效于表中要导入数据的行。In this case, this is the person table in the test database.在本例中，这是test数据库中的person表。

As can be seen by the response from the server, 8 rows were imported into the test.person table. 从服务器的响应可以看出，8行被导入test.person表。This can be verified by a simple SELECT statement:这可以通过一个简单的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)

This shows, as stated earlier in this section, that any or all of the 3 permitted XML formats may appear in a single file and be read using LOAD XML.这表明，如本节前面所述，3种允许的XML格式中的任何一种或全部可以出现在单个文件中，并可以使用LOAD XML读取。

The inverse of the import operation just shown—that is, dumping MySQL table data into an XML file—can be accomplished using the mysql client from the system shell, as shown here:与刚才的导入操作相反的是，可以使用系统shell中的mysql客户端将MySQL表数据转储到XML文件中，如下所示：

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注意

The --xml option causes the mysql client to use XML formatting for its output; the -e option causes the client to execute the SQL statement immediately following the option. --xml选项使mysql客户机对其输出使用xml格式；-e选项使客户机执行紧跟在选项后面的SQL语句。See Section 4.5.1, “mysql — The MySQL Command-Line Client”.请参阅第4.5.1节，“mysql-mysql命令行客户端”。

You can verify that the dump is valid by creating a copy of the person table and importing the dump file into the new table, like this:您可以通过创建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)

There is no requirement that every field in the XML file be matched with a column in the corresponding table. 不要求XML文件中的每个字段都与相应表中的一列匹配。Fields which have no corresponding columns are skipped. 跳过没有相应列的字段。You can see this by first emptying the person2 table and dropping the created column, then using the same LOAD XML statement we just employed previously, like this:首先清空person2表并删除已创建的列，然后使用前面使用的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)

The order in which the fields are given within each row of the XML file does not affect the operation of LOAD XML; the field order can vary from row to row, and is not required to be in the same order as the corresponding columns in the table.字段在XML文件的每一行中的给定顺序不影响LOAD XML的操作；字段顺序可以因行而异，不要求与表中相应列的顺序相同。

As mentioned previously, you can use a (field_name_or_user_var, ...) list of one or more XML fields (to select desired fields only) or user variables (to store the corresponding field values for later use). 如前所述，您可以使用一个或多个XML字段（仅用于选择所需字段）或用户变量（用于存储相应的字段值以供以后使用）的(field_name_or_user_var, ...)列表。User variables can be especially useful when you want to insert data from an XML file into table columns whose names do not match those of the XML fields. 如果要将XML文件中的数据插入到名称与XML字段不匹配的表列中，则用户变量尤其有用。To see how this works, we first create a table named individual whose structure matches that of the person table, but whose columns are named differently:为了了解其工作原理，我们首先创建一个名为individual的表，该表的结构与person表的结构匹配，但其列的命名不同：

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)

In this case, you cannot simply load the XML file directly into the table, because the field and column names do not match:在这种情况下，不能简单地将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

This happens because the MySQL server looks for field names matching the column names of the target table. 这是因为MySQL服务器查找与目标表的列名匹配的字段名。You can work around this problem by selecting the field values into user variables, then setting the target table's columns equal to the values of those variables using SET. 可以通过将字段值选择到用户变量中，然后使用SET将目标表的列设置为这些变量的值来解决此问题。You can perform both of these operations in a single statement, as shown here:您可以在一条语句中执行这两种操作，如下所示：

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)

The names of the user variables must match those of the corresponding fields from the XML file, with the addition of the required @ prefix to indicate that they are variables. 用户变量的名称必须与XML文件中相应字段的名称匹配，并添加所需的@前缀以指示它们是变量。The user variables need not be listed or assigned in the same order as the corresponding fields.用户变量不需要按照与相应字段相同的顺序列出或分配。

Using a ROWS IDENTIFIED BY '<tagname>' clause, it is possible to import data from the same XML file into database tables with different definitions. 使用ROWS IDENTIFIED BY '<tagname>'子句，可以将同一XML文件中的数据导入具有不同定义的数据库表中。For this example, suppose that you have a file named address.xml which contains the following 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>

You can again use the test.person table as defined previously in this section, after clearing all the existing records from the table and then showing its structure as shown here:在清除表中的所有现有记录并显示其结构后，您可以再次使用本节前面定义的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=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Now create an address table in the test database using the following CREATE TABLE statement:现在使用以下CREATE TABLE语句在测试数据库中创建一个address表：

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
);

To import the data from the XML file into the person table, execute the following LOAD XML statement, which specifies that rows are to be specified by the <person> element, as shown here;要将数据从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

You can verify that the records were imported using a SELECT statement:您可以使用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)

Since the <address> elements in the XML file have no corresponding columns in the person table, they are skipped.由于XML文件中的<address>元素在person表中没有相应的列，它们将被跳过。

To import the data from the <address> elements into the address table, use the LOAD XML statement shown here:若要将数据从<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

You can see that the data was imported using a SELECT statement such as this one:您可以看到，数据是使用如下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)

The data from the <address> element that is enclosed in XML comments is not imported. 来自<address>元素的数据不导入包含在XML注释中的元素。However, since there is a person_id column in the address table, the value of the person_id attribute from the parent <person> element for each <address> is imported into the address table.但是，由于address表中有person_id列，因此来自父级<person>元素的person_id属性的值针对每个<address>被导入到address表中。

Security Considerations.安全考虑。  As with the LOAD DATA statement, the transfer of the XML file from the client host to the server host is initiated by the MySQL server. 与LOAD DATA语句一样，XML文件从客户机主机传输到服务器主机是由MySQL服务器发起的。In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD XML statement. 理论上，可以构建一个修补过的服务器，告诉客户机程序传输服务器选择的文件，而不是LOAD XML语句中客户机指定的文件。Such a server could access any file on the client host to which the client user has read access.这样的服务器可以访问客户机主机上客户机用户具有读取权限的任何文件。

In a Web environment, clients usually connect to MySQL from a Web server. 在Web环境中，客户机通常从Web服务器连接到MySQL。A user that can run any command against the MySQL server can use LOAD XML LOCAL to read any files to which the Web server process has read access. 可以对MySQL服务器运行任何命令的用户可以使用LOAD XML LOCAL来读取Web服务器进程具有读取权限的任何文件。In this environment, the client with respect to the MySQL server is actually the Web server, not the remote program being run by the user who connects to the Web server.在这种环境中，MySQL服务器的客户机实际上是Web服务器，而不是由连接到Web服务器的用户运行的远程程序。

You can disable loading of XML files from clients by starting the server with --local-infile=0 or --local-infile=OFF. 通过使用--local infle=0或--local infle=OFF启动服务器，可以禁用从客户端加载XML文件。This option can also be used when starting the mysql client to disable LOAD XML for the duration of the client session.在启动mysql客户机时，也可以使用此选项在客户机会话期间禁用加载XML。

To prevent a client from loading XML files from the server, do not grant the FILE privilege to the corresponding MySQL user account, or revoke this privilege if the client user account already has it.为了防止客户机从服务器加载XML文件，请不要将FILE权限授予相应的MySQL用户帐户，或者如果客户机用户帐户已经拥有该权限，请撤消该权限。

Important重要

Revoking the FILE privilege (or not granting it in the first place) keeps the user only from executing the LOAD XML statement (as well as the LOAD_FILE() function; it does not prevent the user from executing LOAD XML LOCAL. 取消FILE权限（或者不首先授予它）只会让用户不执行LOAD XML语句（以及LOAD_FILE()函数）；它不会阻止用户在本地执行LOAD XML。To disallow this statement, you must start the server or the client with --local-infile=OFF.若要禁止此语句，必须使用--local-infile=OFF启动服务器或客户端。

In other words, the FILE privilege affects only whether the client can read files on the server; it has no bearing on whether the client can read files on the local file system.换句话说，FILE权限只影响客户端是否可以读取服务器上的文件；它与客户端能否读取本地文件系统上的文件无关。