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 statement reads data from an XML file into a table. The file_name must be given as a literal string. The tagname in the optional ROWS IDENTIFIED BY clause must also be given as a literal string, and must be surrounded by angle brackets (<以及>).The LOAD XML statement reads data from an XML file into a table. The file_name must be given as a literal string. 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 >).

LOAD XML 作为运行 mysql客户端在 XML 输出模式下（即以--xml选项启动客户端）。要将表中的数据写入 XML 文件，你可以调用 mysql 客户端，--xml 和 -e 选项，如图所示这里：LOAD XML acts as the complement of running the mysql client in XML output mode (that is, starting the client with the --xml option). 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:

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

要将文件读回表中，请使用LOAD XML。默认情况下，<row> element is considered to be the equivalent of a database table row; this can be changed using the ROWS IDENTIFIED BY子句创建的表空间。To read the file back into a table, use 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.

该语句支持三种不同的 XML 格式：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. Tags are matched based on the tag or attribute name and the column name.

以下子句对 LOAD XML“的工作方式与 LOAD DATAThe following clauses work essentially the same way for LOAD XML as they do for LOAD DATA:

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

(field_name_or_user_var, ...)是一个或多个逗号分隔的 XML 字段或用户变量的列表。用于此目的的用户变量名称必须与 XML 文件中的字段名称一致，前缀为@。您可以使用字段名称只选择所需的字段。用户变量可用于存储相应的字段值以便后续重复使用。(field_name_or_user_var, ...) is a list of one or more comma-separated XML fields or user variables. The name of a user variable used for this purpose must match the name of a field from the XML file, prefixed with @. 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.

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

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

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.

现在假设我们有一个简单的 XML 文件 person.xml，其内容如下：Now suppose that we have a simple XML file person.xml, whose contents are as shown here:

<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 格式都包含在这个示例文件中。Each of the permissible XML formats discussed previously is represented in this example file.

这是其他 MySQL 工具使用的格式，例如person.xml要将数据导入person可以使用以下语句：To import the data in person.xml into the person table, you can use this statement:

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 数据目录中。如果找不到该文件，会出现以下错误：Here, we assume that person.xml is located in the MySQL data directory. If the file cannot be found, the following error results:

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

该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. In this case, this is the person table in the test database.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. In this case, this is the person table in the test database.

正如服务器的响应所示，表格中导入了 8 行test.person。这可以通过一个简单的SELECT语句验证：As can be seen by the response from the server, 8 rows were imported into the test.person table. This can be verified by a simple SELECT statement:

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)

这表明，正如本节前述所述，允许的三种 XML 格式中的任意或全部都可以出现在单一文件中，并可通过LOAD XML。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.

刚才展示的导入操作的反向——即将 MySQL 表数据转储到 XML 文件——可以通过 mysql 客户端实现，如图所示：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:

$> 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>

您可以通过创建 person 表的副本，并将转储文件导入新表，这样验证转储文件的有效性：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:

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)

文件中的每个字段都必须与对应表中的一列匹配。没有对应列的字段会被跳过。你可以通过先清空 person2 表，并丢弃 created 列，然后使用相同的 LOAD XML 语句来实现，比如这样：There is no requirement that every field in the XML file be matched with a column in the corresponding table. 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:

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;字段顺序可以因行而异，不必与表中对应列的顺序相同。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.

如前所述，您可以使用 (field_name_or_user_var, ...)列表，包含一个或多个 XML 字段（仅用于选择目标字段）或用户变量（存储相应字段值以便后续使用）。用户变量在想将 XML 文件中的数据插入与 XML 字段名称不一致的表列时尤其有用。为了了解其工作原理，我们首先创建一个名为 individual，其结构与 person，但列名不同：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). 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. 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:

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 文件加载到表中，因为字段和列名不匹配：In this case, you cannot simply load the XML file directly into the table, because the field and column names do not match:

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这个问题。你可以在一个语句中完成这两个操作，如下所示：This happens because the MySQL server looks for field names matching the column names of the target table. 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. 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)

用户变量的名称必须与 XML 文件中对应字段的名称一致，并添加所需的@ prefix to indicate that they are variables. The user variables need not be listed or assigned in the same order as the corresponding fields.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. The user variables need not be listed or assigned in the same order as the corresponding fields.

使用ROWS IDENTIFIED BY '<tagname>'子句，可以将相同的 XML 文件的数据导入具有不同定义的表。对于这个例子，假设你有一个名为address.xml的文件，其中包含以下 XML：Using a ROWS IDENTIFIED BY '<tagname>' clause, it is possible to import data from the same XML file into database tables with different definitions. For this example, suppose that you have a file named address.xml which contains the following 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 表，如本节之前定义的，在清除所有现有记录后，再展示其结构，如图所示：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:

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)

Now create an address table in the test database using the following CREATE TABLE语句验证：Now create an address table in the test database using the following CREATE TABLE statement:

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>元素表示，如下所示；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;

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语句验证：You can verify that the records were imported using a SELECT statement:

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表格，它们会被跳过。Since the <address> elements in the XML file have no corresponding columns in the person table, they are skipped.

要从<address>元素导入数据到address表中，请使用LOAD XML下列语句：To import the data from the <address> elements into the address table, use the LOAD XML statement shown here:

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语句导入的：You can see that the data was imported using a SELECT statement such as this one:

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. 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权限。The data from the <address> element that is enclosed in XML comments is not imported. 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.

安全注意事项。与LOAD DATA语句一样，XML 文件从客户端主机到服务器主机的传输是由 MySQL 服务器发起的。理论上，可以构建一个打了补丁的服务器，它会告诉客户端程序传输服务器选择的文件，而不是客户端在LOAD XML语句中指定的文件。这样的服务器可以访问客户端主机上客户端用户有读取权限的任何文件。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. 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. Such a server could access any file on the client host to which the client user has read access.

在 Web 环境中，客户端通常通过 Web 服务器连接到 MySQL。可以对 MySQL 服务器运行任何命令的用户可以使用LOAD XML LOCAL读取 Web 服务器进程具有读取权限的任何文件。在这种环境下，相对于 MySQL 服务器的客户端实际上是 Web 服务器，而不是连接到 Web 服务器的用户运行的远程程序。In a Web environment, clients usually connect to MySQL from a Web server. 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. 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.

你可以通过启动服务器时设置 --local-infile=0 或 --local-infile=OFF。该选项也可用于启动 mysql client to disable LOAD XMLYou can disable loading of XML files from clients by starting the server with --local-infile=0 or --local-infile=OFF. This option can also be used when starting the mysql client to disable LOAD XML for the duration of the client session.

。为了防止客户端从服务器加载 XML 文件，请不要将 FILE 权限授予对应的 MySQL 用户账户，或者如果客户端用户账户已有该权限，则撤销该权限。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.