XML (Extensible Markup Language) 是一種可延伸標示語言.
相較於 HTML 是定義好的國際通用的標記式語言,我們是無法自訂標記(標籤 Tag),只能變更其屬性值,這時候可以使用 XML 來自定,讓電腦之間可以處理自定的資訊,這邊來看在 MariaDB 資料庫如何處理這些自定的標記(標籤 Tag).
測試環境為 CentOS 8 x86_64 (虛擬機)
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE testdb; Database changed
LOAD XML INFILE
第一種處理 XML 模式,是將 XML 的欄位轉換到相對應的資料庫欄位,MySQL (Mariadb) 支援以下這幾種 XML 格式.
<row column1="value1" column2="value2" .../>
<row> <column1>value1</column1> <column2>value2</column2> </row>
<row> <field name='column1'>value1</field> <field name='column2'>value2</field> </row>
建立要使用的 XML 格式檔案(同一個檔案可以混合不同格式).
[root@localhost ~]# vi /var/ftp/file.xml <Computer> <row Id="1" Owner="Ben1" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" MEM="16 GB" /> <row> <Id>2</Id> <Owner>Ben2</Owner> <CreationDate>2020-05-03 19:54:30</CreationDate> <DisplayName>Ben Mac</DisplayName> <Model>MacBookPro15</Model> <CPU>Quad-Core Intel Core i5</CPU> <MEM>16 GB</Memory> </row> <row> <field name='Id'>3</field> <field name='Owner'>Ben3</field> <field name='CreationDate'>2020-05-03 19:54:30</field> <field name='DisplayName'>Ben Mac</field> <field name='Model'>MacBookPro13.3</field> <field name='CPU'>Quad-Core Intel Core i5</field> </row> </Computer>
先定義我們所需的資料表,看你要儲存什麼欄位 ( Column ) ,就定義相對應的欄位 (通常建議使用 TEXT 資料型態欄位),下面我定義了全部資料欄位.
MariaDB [testdb]> CREATE TABLE xmltbl (ID VARCHAR(100), Owner VARCHAR(100), CreationDate TIMESTAMP, DisplayName VARCHAR(100), Model VARCHAR(100), CPU VARCHAR(100) , MEM VARCHAR(100) ); Query OK, 0 rows affected (0.009 sec)
- ROWS IDENTIFIED BY
剛剛建立資料表欄位與 XML 格式是一對一,可以用下面的指令來做.MariaDB [testdb]> LOAD XML LOCAL INFILE '/var/ftp/file.xml' INTO TABLE xmltbl ROWS IDENTIFIED BY '<row>'; Query OK, 3 rows affected (0.003 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
可以注意到 ID 欄位為 NULL ,是因為資料表欄位名稱為 ID 而 XML 檔案為 Id.另外一個是第三筆沒有 Memory 資料,所以當找不到相對應的 XML 欄位時會填為 NULL .
MariaDB [testdb]> SELECT * FROM xmltbl; +------+-------+---------------------+-------------+----------------+-------------------------+-------+ | ID | Owner | CreationDate | DisplayName | Model | CPU | MEM | +------+-------+---------------------+-------------+----------------+-------------------------+-------+ | NULL | Ben1 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro15 | Quad-Core Intel Core i5 | 16 GB | | NULL | Ben2 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro15 | Quad-Core Intel Core i5 | 16 GB | | NULL | Ben3 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro13.3 | Quad-Core Intel Core i5 | NULL | +------+-------+---------------------+-------------+----------------+-------------------------+-------+ 3 rows in set (0.001 sec)
針對上面遇到的問題修改一下 XML 檔案.
[root@localhost ~]# vi /var/ftp/file1.xml <Computer> <row ID="1" Owner="Ben1" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" MEM="16 GB" /> <row> <ID>2</ID> <Owner>Ben2</Owner> <CreationDate>2020-05-03 19:54:30</CreationDate> <DisplayName>Ben Mac</DisplayName> <Model>MacBookPro15</Model> <CPU>Quad-Core Intel Core i5</CPU> <MEM>16 GB</Memory> </row> <row> <field name='ID'>3</field> <field name='Owner'>Ben3</field> <field name='CreationDate'>2020-05-03 19:54:30</field> <field name='DisplayName'>Ben Mac</field> <field name='Model'>MacBookPro13.3</field> <field name='CPU'>Quad-Core Intel Core i5</field> <field name='MEM'>16 GB</field> </row> </Computer>
先清除剛剛的內容,在匯入一次.
MariaDB [testdb]> TRUNCATE TABLE xmltbl; Query OK, 0 rows affected (0.012 sec) MariaDB [testdb]> LOAD XML LOCAL INFILE '/var/ftp/file1.xml' INTO TABLE xmltbl ROWS IDENTIFIED BY '<row>'; Query OK, 3 rows affected (0.002 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 MariaDB [testdb]> SELECT * FROM xmltbl; +------+-------+---------------------+-------------+----------------+-------------------------+-------+ | ID | Owner | CreationDate | DisplayName | Model | CPU | MEM | +------+-------+---------------------+-------------+----------------+-------------------------+-------+ | 1 | Ben1 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro15 | Quad-Core Intel Core i5 | 16 GB | | 2 | Ben2 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro15 | Quad-Core Intel Core i5 | 16 GB | | 3 | Ben3 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro13.3 | Quad-Core Intel Core i5 | 16 GB | +------+-------+---------------------+-------------+----------------+-------------------------+-------+ 3 rows in set (0.001 sec)
- SET
先清除剛剛的內容,利用不同的方式再匯入一次.MariaDB [testdb]> TRUNCATE TABLE xmltbl; Query OK, 0 rows affected (0.012 sec)
這方式是將相對應的欄位透過相對應的變數儲存起來.
LOAD XML LOCAL INFILE '/var/ftp/file.xml' INTO TABLE xmltbl (@Id, @Owner, @CreationDate, @DisplayName, @Model, @CPU, @MEM ) SET ID=@Id, Owner=@Owner, CreationDate=@CreationDate, DisplayName=@DisplayName, Model=@Model, CPU=@CPU, MEM =@MEM;
MariaDB [testdb]> LOAD XML LOCAL INFILE '/var/ftp/file.xml' INTO TABLE xmltbl (@Id, @Owner, @CreationDate, @DisplayName, @Model, @CPU, @MEM ) SET ID=@Id, Owner=@Owner, CreationDate=@CreationDate, DisplayName=@DisplayName, Model=@Model, CPU=@CPU, MEM =@MEM; Query OK, 3 rows affected (0.003 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
在儲存時指定 ID=@Id (因為資料表欄位名稱為 ID 而 XML 檔案為 Id).另外一個是第三筆沒有 Memory 資料,所以當找不到相對應的 XML 欄位時會填為 NULL .
MariaDB [testdb]> SELECT * FROM xmltbl; +------+-------+---------------------+-------------+----------------+-------------------------+-------+ | ID | Owner | CreationDate | DisplayName | Model | CPU | MEM | +------+-------+---------------------+-------------+----------------+-------------------------+-------+ | 1 | Ben1 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro15 | Quad-Core Intel Core i5 | 16 GB | | 2 | Ben2 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro15 | Quad-Core Intel Core i5 | 16 GB | | 3 | Ben3 | 2020-05-03 19:54:30 | Ben Mac | MacBookPro13.3 | Quad-Core Intel Core i5 | NULL | +------+-------+---------------------+-------------+----------------+-------------------------+-------+ 3 rows in set (0.001 sec)
LOAD_FILE + ExtractValue
第二種方式是把整個 XML 儲存在一個欄位 (建議使用 TEXT 資料型態欄位),再透過函數 ExtractValue 取出相對應的資料.
建立要使用的 XML(file2.xml) 格式檔案.
[root@localhost ~]# vi /var/ftp/file2.xml <Computer> <row Id="1" Owner="Ben" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" Memory="16 GB" /> <row Id="2" Owner="Ben" CreationDate="2020-05-01 19:54:30" DisplayName="Ben OLD Mac" Model="MacBook Air" CPU="Intel Ivy Bridge Core i5" Memory="4 GB" /> </Computer>
建立資料表並把 file2.xml 整個儲存起來.
MariaDB [testdb]> CREATE TABLE xmlextract (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, xml_data TEXT NOT NULL); Query OK, 0 rows affected (0.008 sec)
MariaDB [testdb]> SET @xml = LOAD_FILE("/var/ftp/file2.xml");
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> INSERT INTO xmlextract (xml_data) VALUES (@xml);
Query OK, 1 row affected (0.003 sec)
檢視一下資料.
MariaDB [testdb]> SELECT * FROM xmlextract\G
*************************** 1. row ***************************
id: 1
xml_data: <Computer>
<row Id="1" Owner="Ben" CreationDate="2020-05-03 19:54:30" DisplayName="Ben Mac" Model="MacBookPro15" CPU="Quad-Core Intel Core i5" Memory="16 GB" />
<row Id="2" Owner="Ben" CreationDate="2020-05-01 19:54:30" DisplayName="Ben OLD Mac" Model="MacBook Air" CPU="Intel Ivy Bridge Core i5" Memory="4 GB" />
</Computer>
1 row in set (0.001 sec)
函數說明:
LOAD_FILE 函數指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案),需注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.
另外一個可能是當檔案大小超出 MYSQL 的預設時 max_allowed_packet 也無法存取,可以使用SQL 指令 SHOW VARIABLES LIKE ‘max_allowed_packet’; 來檢視.
要怎麼透過函數 ExtractValue 把 XML 格式的資料擷取出來呢!函數 ExtractValue 參數需指定 XPath 其格式如下.
- /tag
- 位於最上(外)層 (Root Element , 根元素 , 父元素) 且名稱匹配 <tag/> <tag/> 內的資料.
- /tag1/tag2
- 除了要匹配 <tag2> <tag2/> 外,還必須為 <tag1> <tag1/> 最上(外)層 (Root Element , 根元素 , 父元素) 的子元素.
MariaDB [testdb]> SET @xml = '<a><b>X</b><b>Y</b></a>'; Query OK, 0 rows affected (0.001 sec) MariaDB [testdb]> SELECT ExtractValue(@xml, '/a/b[1]'); +-------------------------------+ | ExtractValue(@xml, '/a/b[1]') | +-------------------------------+ | X | +-------------------------------+ 1 row in set (0.000 sec)
- //tag
- 匹配任何為 <tag> 的資料.
MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[1]'); +------------------------------+ | ExtractValue(@xml, '//b[1]') | +------------------------------+ | X | +------------------------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[2]'); +------------------------------+ | ExtractValue(@xml, '//b[2]') | +------------------------------+ | Y | +------------------------------+ 1 row in set (0.000 sec)
- No Matching Element
- 如果沒符合的資料時,並不是回傳 NULL 而是 “”(空字串),要怎麼判斷是否沒資料(回傳空字串)還是真的是得到的是空字串.
MariaDB> SELECT ExtractValue('<a><b/></a>', '/a/b'); +-------------------------------------+ | ExtractValue('<a><b/></a>', '/a/b') | +-------------------------------------+ | | +-------------------------------------+ 1 row in set (0.00 sec) MariaDB> SELECT ExtractValue('<a><c/></a>', '/a/b'); +-------------------------------------+ | ExtractValue('<a><c/></a>', '/a/b') | +-------------------------------------+ | | +-------------------------------------+ 1 row in set (0.00 sec)- 上面範例一回傳的是有資料(內容物為空字串),範例二是沒有資料(一樣回傳空字串),我們要怎麼判斷,可以利用 COUNT() 函數.
MariaDB> SELECT ExtractValue('<a><b/></a>', 'count(/a/b)'); +-------------------------------------+ | ExtractValue('<a><b/></a>', 'count(/a/b)') | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.00 sec) MariaDB> SELECT ExtractValue('<a><c/></a>', 'count(/a/b)'); +-------------------------------------+ | ExtractValue('<a><c/></a>', 'count(/a/b)') | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set (0.01 sec)- 有資料的 COUNT() 為 1 ,沒資料的是 0 .
- 上面範例一回傳的是有資料(內容物為空字串),範例二是沒有資料(一樣回傳空字串),我們要怎麼判斷,可以利用 COUNT() 函數.
透過剛剛的語法把資料擷取出來.
MariaDB [testdb]> SELECT xml_data FROM xmlextract WHERE id = 1 INTO @xml; Query OK, 1 row affected (0.001 sec) MariaDB [testdb]> SELECT ExtractValue(@xml, '//Computer/row[1]/@DisplayName') , ExtractValue(@xml, '//Computer/row[2]/@DisplayName'); +------------------------------------------------------+------------------------------------------------------+ | ExtractValue(@xml, '//Computer/row[1]/@DisplayName') | ExtractValue(@xml, '//Computer/row[2]/@DisplayName') | +------------------------------------------------------+------------------------------------------------------+ | Ben Mac | Ben OLD Mac | +------------------------------------------------------+------------------------------------------------------+ 1 row in set (0.001 sec)
遇過的問題
使用 LOAD_FILE 匯入 XML(編碼 UTF-16) 但資料表欄位變成亂碼 (欄位已選擇 UTF-16),後來是先將檔但從 UTF-16 轉換成為 UTF-8 再匯入才沒產生亂碼.
Linux 下可以使用者令來轉換
iconv -f UTF-16LE -t UTF-8 <filename> -o <new-filename>
Windows 下的 PowerShell 可以使用指令來轉換 (沒試過)
WideCharToMultiByte
沒有解決問題,試試搜尋本站其他內容