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必须作为一个字符串。tagname可选ROWS IDENTIFIED BY子句中的in 也必须以文字字符串形式给出，并且必须用尖括号（<和>）包围。
LOAD XML充当在XML输出模式下运行mysql客户端的补充（即，使用--xml选项启动客户端）。要将表中的数据写入XML文件，可以使用系统外壳程序中的和选项调用mysql客户端，如下所示：--xml-e
shell>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>
列名称是标签的
name属性<field>，值是这些标签的内容：<row> <field name='column1'>value1</field> <field name='column2'>value2</field> </row>
这是其他MySQL工具使用的格式，例如mysqldump。
三种格式都可以在同一XML文件中使用。导入例程会自动检测每一行的格式并正确解释。根据标签或属性名称以及列名称来匹配标签。
以下子句的工作方式基本上与LOAD XML它们相同LOAD DATA：
LOW_PRIORITY要么CONCURRENTLOCALREPLACE要么IGNORECHARACTER SETSET
有关这些子句的更多信息，请参见“ LOAD DATA语句”。
(field_name_or_user_var,...)是一个或多个逗号分隔的XML字段或用户变量的列表。用于此目的的用户变量名称必须与XML文件中以开头的字段名称相匹配@。您可以使用字段名称来仅选择所需的字段。可以使用用户变量来存储相应的字段值，以供后续重用。
在或子句使第一XML文件中的行被跳过。它类似于该语句的子句。IGNORE number LINESIGNORE number ROWSnumberLOAD DATAIGNORE ... LINES
假设我们person创建了一个名为的表，如下所示：
USE test;CREATE TABLE person ( person_id INT NOT NULLPRIMARY 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>'子句意味着<person>XML文件中的每个元素都被认为等效于表中要将数据导入到其中的一行。在这种情况下，这是数据库中的person表test。
从服务器的响应可以看出，test.person表中已导入了8行。这可以通过一个简单的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文件）可以使用来自系统外壳的mysql客户端来完成，如下所示：
shell>mysql --xml -e "SELECT * FROM test.person" > person-dump.xml shell>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语句。请参见“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 person2DROP 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(11) NOT NULL, `fname` varchar(40) DEFAULT NULL, `lname` varchar(40) DEFAULT NULL, PRIMARY KEY (`person_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 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字段的列表（仅选择所需的字段）或用户变量（存储相应的字段值以供以后使用）。当您要将XML文件中的数据插入名称与XML字段名称不匹配的表列中时，用户变量尤其有用。为了了解其工作原理，我们首先创建一个表，该表的结构与表的结构相匹配，但是其列的名称不同：(field_name_or_user_var,...)individualperson
mysql>CREATE TABLE individual ( -> individual_id INT NOT NULLPRIMARY 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文件中相应字段的名称匹配，并添加必需的@前缀以指示它们是变量。用户变量无需按照与相应字段相同的顺序列出或分配。
使用子句，可以将同一XML文件中的数据导入具有不同定义的数据库表中。对于此示例，假设您有一个名为的文件，其中包含以下XML：ROWS IDENTIFIED BY '<tagname>'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>
从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=MyISAM DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
现在address，test使用以下CREATE TABLE语句在数据库中创建一个表：
CREATE TABLE address ( address_id INT NOT NULLPRIMARY 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)
由于<address>XML文件中的元素在表中没有对应的列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)
<address>XML注释中包含的来自元素的数据不会导入。但是，由于表中有一person_id列，因此每个父元素address的person_id属性值<person>都<address>将导入address表中。
安全注意事项。与该LOAD DATA语句一样，MySQL服务器将XML文件从客户端主机传输到服务器主机。从理论上讲，可以构建一个打补丁的服务器，该服务器将告诉客户端程序传输服务器选择的文件，而不是客户端在LOAD XML语句中命名的文件。这样的服务器可以访问客户端用户具有读取访问权限的客户端主机上的任何文件。
在Web环境中，客户端通常从Web服务器连接到MySQL。可以对MySQL服务器运行任何命令的用户可以LOAD XML LOCAL用来读取Web服务器进程具有读取权限的任何文件。在这种环境中，相对于MySQL服务器的客户端实际上是Web服务器，而不是由连接到Web服务器的用户运行的远程程序。
您可以通过使用--local-infile=0或启动服务器来禁用从客户端加载XML文件--local-infile=OFF。在启动mysql客户端时，也可以LOAD XML在客户端会话期间禁用该选项。
为了防止客户端从服务器加载XML文件，请勿将FILE特权授予相应的MySQL用户帐户，如果客户端用户帐户已经拥有该特权，则不要撤消该特权。
撤销FILE只能从执行权限（或没有在第一时间给予它）让用户LOAD XML声明（还有LOAD_FILE()功能；它并不能阻止用户执行LOAD XML LOCAL。要禁止这种说法，你必须启动服务器或客户端与--local-infile=OFF。
换句话说，FILE特权仅影响客户端是否可以读取服务器上的文件；否则，将影响客户端。它与客户端是否可以读取本地文件系统上的文件无关。