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

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 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 權限僅影響用戶端是否可以讀取伺服器上的檔案；它與用戶端是否可以讀取本機檔案系統上的檔案無關。

上一步首頁上一層下一步