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="http://www.w3.org/2001/XMLSchema-instance"> <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="http://www.w3.org/2001/XMLSchema-instance"> <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用户帐户，如果客户端用户帐户已经拥有该特权，则取消该特权。