MySQL load xml載入資料

load xml載入資料
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和-e選項的mysql客戶端，例如:

[mysql@localhost ~]$ mysql -uroot -pabcd$123 cs --xml -e 'select * from cs.t' >t.xml
mysql: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 216 5月 27 16:13 t.xml
[mysql@localhost ~]$ cat t.xml
<?xml version="1.0"?>
<resultset statement="select * from cs.t
" xmlns:xsi="
<row>
<field name="id">3</field>
<field name="val" xsi:nil="true" />
</row>
</resultset>

為了將這個xml檔案中的資料讀回到表中，可以使用load xml infile。預設情況下<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檔案，匯入過程會自動檢測每一行的格式並正確解析它。標記是根據標記或屬性名稱和列名進行匹配的

下面建立一個測試表person，建立語句如下:

mysql> CREATE TABLE person (
-> person_id INT NOT NULL PRIMARY KEY,
-> fname VARCHAR(40) NULL,
-> lname VARCHAR(40) NULL,
-> created TIMESTAMP
-> );
Query OK, 0 rows affected (0.14 sec)

下面假設我們有一個簡單的xml檔案person.xml，其內容如下(這裡使用了三種格式來描述行資料):

<list>
<person person_id="1" fname="Kapek" lname="Sainnouine"/>
<person><person_id>2</person_id><fname>Sajon</fname><lname>Rondela</lname></person>
<person><field name="person_id">3</field><field name="fname">Likame</field><field name="lname">Orrtmons</field></person>
</list>

為了將person.xml中的資料匯入person表，可以執行以下語句:

mysql> load xml local infile 'person.xml' into table person rows identified by '<person>';
Query OK, 3 rows affected (0.02 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
rows identified by '<person>'子句意味著xml檔案中的每個<person>元屬就相當於表中的一行資料。

上面載入語句返回資訊顯示有3行資料被匯入person表，執行簡單的select語句來進行驗證:

mysql> select * from person;
+-----------+--------+------------+---------+
| person_id | fname | lname | created |
+-----------+--------+------------+---------+
| 1 | Kapek | Sainnouine | NULL |
| 2 | Sajon | Rondela | NULL |
| 3 | Likame | Orrtmons | NULL |
+-----------+--------+------------+---------+
3 rows in set (0.01 sec)

這表明，如前面所述，3種允許的XML格式中的任何一種或所有三種都可以出現在單個檔案中，並使用LOAD XML讀取。

與剛才所示的匯入操作相反--即將MySQL表資料轉儲到XML檔案中--可以使用系統shell中的MySQL客戶機來完成，如下所示:

[mysql@localhost ~]$ mysql -uroot -pabcd$123 --xml -e 'select * from cs.person' >person_dump.xml
mysql: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 641 5月 27 16:00 person.xml
-rw-r--r--. 1 mysql mysql 641 5月 27 16:52 person_dump.xml
[mysql@localhost ~]$ cat person_dump.xml
<?xml version="1.0"?>
<resultset statement="select * from cs.person
" xmlns:xsi="
<row>
<field name="person_id">1</field>
<field name="fname">Kapek</field>
<field name="lname">Sainnouine</field>
<field name="created" xsi:nil="true" />
</row>
<row>
<field name="person_id">2</field>
<field name="fname">Sajon</field>
<field name="lname">Rondela</field>
<field name="created" xsi:nil="true" />
</row>
<row>
<field name="person_id">3</field>
<field name="fname">Likame</field>
<field name="lname">Orrtmons</field>
<field name="created" xsi:nil="true" />
</row>

可以透過建立一個person表的副本並匯入這個dump檔案到新表中來驗證這個dump的有效性，例如:

mysql> create table person1 like person;
Query OK, 0 rows affected (0.22 sec)
mysql> select * from person1;
Empty set (0.00 sec)
mysql> load xml local infile 'person_dump.xml' into table person1;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from person1;
+-----------+--------+------------+---------+
| person_id | fname | lname | created |
+-----------+--------+------------+---------+
| 1 | Kapek | Sainnouine | NULL |
| 2 | Sajon | Rondela | NULL |
| 3 | Likame | Orrtmons | NULL |
+-----------+--------+------------+---------+
3 rows in set (0.00 sec)

不要求XML檔案中的每個欄位都與對應表中的列相匹配。沒有相應列的欄位將被跳過。可以首先清空person1表並刪除created列，然後使用我們剛才使用的LOAD XML語句，如下所示:

mysql> truncate table person1;
Query OK, 0 rows affected (0.08 sec)
mysql> alter table person1 drop column created;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table person1\G
*************************** 1. row ***************************
Table: person1
Create Table: CREATE TABLE `person1` (
`person_id` int(11) NOT NULL,
`fname` varchar(40) DEFAULT NULL,
`lname` varchar(40) DEFAULT NULL,
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> load xml local infile 'person_dump.xml' into table person1;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from person1;
+-----------+--------+------------+
| person_id | fname | lname |
+-----------+--------+------------+
| 1 | Kapek | Sainnouine |
| 2 | Sajon | Rondela |
| 3 | Likame | Orrtmons |
+-----------+--------+------------+
3 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.13 sec)

在這種情況下，不能簡單地將XML檔案直接載入到表中，因為欄位名和列名不匹配:

mysql> load xml local infile 'person_dump.xml' into table cs.individual;
Query OK, 1 row affected, 15 warnings (0.04 sec)
Records: 8 Deleted: 0 Skipped: 7 Warnings: 15
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1
*************************** 2. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 3. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 2
*************************** 4. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 5. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 3
*************************** 6. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 7. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 4
*************************** 8. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 9. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 5
*************************** 10. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 11. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 6
*************************** 12. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 13. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 7
*************************** 14. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
*************************** 15. row ***************************
Level: Warning
Code: 1263
Message: Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 8
*************************** 16. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry '0' for key 'PRIMARY'
16 rows in set (0.00 sec)
mysql> select * from individual;
+---------------+-------+-------+------+
| individual_id | name1 | name2 | made |
+---------------+-------+-------+------+
| 0 | NULL | NULL | NULL |
+---------------+-------+-------+------+
1 row in set (0.00 sec)

這是因為MySQL伺服器查詢與目標表的列名匹配的欄位名。您可以透過將欄位值選擇到使用者變數中來解決這個問題，然後使用SET將目標表的列設定為這些變數的值。您可以在一個語句中執行這兩個操作，如下所示:

mysql> load xml local infile 'person_dump.xml' into table cs.individual (@person_id,@fname,@lname,@created)
-> set individual_id=@person_id,name1=@fname,name2=@lname,made=@created;
Query OK, 8 rows affected (0.03 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from individual;
+---------------+--------+------------+------+
| individual_id | name1 | name2 | made |
+---------------+--------+------------+------+
| 1 | Kapek | Sainnouine | NULL |
| 2 | Sajon | Rondela | NULL |
| 3 | Likame | ?rrtmons | NULL |
| 4 | Slar | Manlanth | NULL |
| 5 | Stoma | Milu | NULL |
| 6 | Nirtam | Skl?d | NULL |
| 7 | Sungam | Dulb?d | NULL |
| 8 | Sraref | Encmelt | NULL |
+---------------+--------+------------+------+
8 rows in set (0.00 sec)

使用者變數的名稱必須與XML檔案中相應欄位的名稱匹配，並新增必要的@字首，表示它們是變數。使用者變數不需要按照與相應欄位相同的順序列出或分配。

使用rows identified by '<tagname>'子句，它可以從相同的XML檔案中將資料匯入有不同定義的表。例如，假設有一個名叫address.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>

可以再次使用之前使用的person表，在刪除表中記錄後顯示錶結構資訊:

mysql> truncate table person;
Query OK, 0 rows affected (0.18 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 NULL DEFAULT NULL,
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

現在使用下面的create table語句來建立一個名叫address的表:

mysql> 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
-> );
Query OK, 0 rows affected (0.13 sec)

為了將資料從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.13 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

現在來驗證被匯入的資料:

mysql> select * from person;
+-----------+--------+-------+---------+
| person_id | fname | lname | created |
+-----------+--------+-------+---------+
| 1 | Robert | Jones | NULL |
| 2 | Mary | Smith | NULL |
+-----------+--------+-------+---------+
2 rows in set (0.00 sec)

因為address.xml檔案中的<address>元素在表person中沒有相關聯的列，因此會被跳過。

為了將address.xml檔案中的<address>元素匯入到address表中，執行下面的語句:

mysql> load xml local infile 'address.xml' into table address rows identified by '<address>';
Query OK, 3 rows affected (0.06 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

現在可以看到<address>元素表示的資料被匯入address表了

mysql> select * from address;
+------------+-----------+-----------------+-------+--------------+---------+
| address_id | person_id | street | zip | city | created |
+------------+-----------+-----------------+-------+--------------+---------+
| 1 | 1 | Mill Creek Road | 45365 | Sidney | NULL |
| 2 | 1 | Main Street | 28681 | Taylorsville | NULL |
| 3 | 2 | River Road | 80239 | Denver | NULL |
+------------+-----------+-----------------+-------+--------------+---------+
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使用者帳戶，如果客戶端使用者帳戶已經擁有該特權，則取消該特權。