MySQL :: MySQL 8.4 參考手冊 :: 15.2.10 LOAD XML 語法

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / LOAD XML 語法

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 的作用相當於在 XML 輸出模式下執行 mysql 用戶端 (也就是使用 --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>

欄位名稱是 <field> 標籤的 name 屬性，而值是這些標籤的內容
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```
這是其他 MySQL 工具（例如 mysqldump）使用的格式。

所有三種格式都可以在同一個 XML 檔案中使用；匯入常式會自動偵測每一列的格式並正確地解譯它。標籤會根據標籤或屬性名稱以及欄位名稱進行比對。

以下子句對於 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> 元素都視為與要將資料匯入的表格中的一列相同。在此案例中，這是 test 資料庫中的 person 表格。

從伺服器的回應可以看出，有 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 檔案中）可以使用系統 Shell 中的 mysql 用戶端來完成，如下所示

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

注意

--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 的操作；欄位順序可能會因列而異，且不一定要與表格中對應的欄位順序相同。

如先前所述，您可以使用一個或多個 XML 欄位的 (field_name_or_user_var, ...) 清單（僅選取所需的欄位）或使用者變數（儲存對應的欄位值以供日後使用）。當您想要將 XML 檔案中的資料插入到欄位名稱與 XML 欄位名稱不符的表格欄位時，使用者變數會特別有用。若要查看其運作方式，我們首先建立一個名為 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)

在此情況下，您無法直接將 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)

現在使用下列 CREATE TABLE 陳述式在 test 資料庫中建立 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
);

若要將 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> 元素中的資料。但是，由於 address 表格中有 person_id 欄位，因此會將每個 <address> 的父 <person> 元素的 person_id 屬性值匯入到 address 表格中。

安全性考量。如同 LOAD DATA 陳述式，XML 檔案從用戶端主機到伺服器主機的傳輸是由 MySQL 伺服器啟動的。理論上，可以建構一個修補的伺服器，告訴用戶端程式傳輸伺服器選擇的檔案，而不是用戶端在 LOAD XML 陳述式中命名的檔案。此類伺服器可以存取用戶端主機上用戶端使用者具有讀取存取權限的任何檔案。

在 Web 環境中，用戶端通常從 Web 伺服器連線到 MySQL。可以對 MySQL 伺服器執行任何命令的使用者可以使用 LOAD XML LOCAL 來讀取 Web 伺服器程序具有讀取存取權限的任何檔案。在此環境中，相對於 MySQL 伺服器的用戶端實際上是 Web 伺服器，而不是連線到 Web 伺服器的使用者執行的遠端程式。

您可以藉由使用 --local-infile=0 或 --local-infile=OFF 啟動伺服器來停用從用戶端載入 XML 檔案的功能。啟動 mysql 用戶端時也可以使用此選項，以在用戶端工作階段期間停用 LOAD XML。

若要防止用戶端從伺服器載入 XML 檔案，請勿將 FILE 權限授與對應的 MySQL 使用者帳戶，或在用戶端使用者帳戶已具有此權限時撤銷此權限。

重要

撤銷 FILE 權限（或一開始不授與）只會阻止使用者執行 LOAD XML 陳述式（以及 LOAD_FILE() 函數；它不會阻止使用者執行 LOAD XML LOCAL。若要不允許此陳述式，您必須使用 --local-infile=OFF 啟動伺服器或用戶端。

換句話說，FILE 權限只會影響客戶端是否能讀取伺服器上的檔案；它與客戶端是否能讀取本機檔案系統上的檔案無關。