加载XML [low_priority | concurrent] [local] infile 'file_name[replace | ignore] into table[替换| ignore]db_name.]tbl_name(字符集charset_name[由'< '标识的行tagname> '][忽视数量{line | rows}] [(field_name_or_user_var(,field_name_or_user_var]…)][集col_name= {expr|默认}(,col_name= {expr|默认}]…]
的加载XML
语句将数据从XML文件读入表中。的file_name
必须以字面值字符串的形式给出。的tagname
在可选行了
子句也必须是字符串,并且必须用尖括号括起来(<
和>
).
加载XML
作为运行mysqlXML输出模式下的客户机(也就是说,使用——xml
选项)。要将数据从表写入XML文件,可以调用mysql客户端与——xml
和- e
系统外壳的选项,如下所示:
mysql——xml -e 'SELECT * FROM mydb。mytable ' > file.xml
若要将文件读回表中,请使用加载XML
.默认情况下,行> <
元素被认为等同于数据库表中的行;可以使用行了
条款。
该语句支持三种不同的XML格式:
列名作为属性,列值作为属性值:
<行column1= "value1"column2= "value2“…/ >
列名作为标签,列值作为这些标签的内容:
<行> <column1>value1column1> <column2>value2column2> 行>
列名是
的名字
的属性<字段>
标签和值是这些标签的内容:<行> <字段名称= 'column1' >value1<字段> / <字段名称= 'column2' >value2> < /字段> < /行
这是其他MySQL工具使用的格式,例如, mysqldump.
这三种格式都可以在同一个XML文件中使用;导入例程自动检测每一行的格式并正确解释它。标记是根据标记或属性名称和列名匹配的。
在MySQL 8.0.21之前,加载XML
不支持CDATA
节。(Bug #30753708, Bug #98199)
下面几个从句的工作原理基本上是相同的加载XML
就像他们做的加载数据
:
LOW_PRIORITY
或并发
当地的
取代
或忽略
字符集
集
的忽略
或数量
行忽略
第一个条款数量
行数量
要跳过的XML文件中的行。它类似于加载数据
声明的忽略……行
条款。
假设我们有一个名为人
,如下所示:
使用测试;创建表person (person_id INT NOT NULL PRIMARY KEY, fname VARCHAR(40) NULL, lname VARCHAR(40) NULL, created TIMESTAMP);
进一步假设这个表最初是空的。
现在假设我们有一个简单的XML文件person.xml
,其内容如下:
Likame Örrtmons Slar Manlanth person>< /person> 5 Stoma Milu Nirtam Sklöd Sungam < person person_id="8" fname="Sraref" lname="Encmelt"/>
前面讨论的每种允许的XML格式都在这个示例文件中表示。
导入数据person.xml
到人
表,你可以使用这个语句:
mysql> LOAD XML LOCAL INFILE 'person. XML ' -> INTO TABLE person -> ROWS IDENTIFIED BY '';Query OK, 8 rows affected (0.00 sec) Records: 8 Deleted: 0 skip: 0 Warnings: 0.删除警告
这里,我们假设person.xml
位于MySQL数据目录下。如果无法找到该文件,则出现以下错误结果:
ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)
的由'
条款是指<人>
元素被认为等效于要导入数据的表中的一行。在这种情况下,这是人
表中测试
数据库。
从服务器的响应中可以看到,有8行被导入test.person
表格这可以通过一个简单的选择
声明:
mysql> SELECT * FROM person;+-----------+--------+------------+---------------------+ | person_id |帧| lname |创建 | +-----------+--------+------------+---------------------+ | 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 | | 2 | Sajon | Rondela | 2007-07-13 16:18:47 | | 3 | Likame | Orrtmons | 2007-07-13 16:18:47 | | 4 | Slar |可以Manlanth | 2007-07-13 16:18:47 | | 5 |气孔| Nilu | 2007-07-13 16:18:47 | | 6 | Nirtam | Sklod | 2007-07-13 16:18:47 | | 7 | Sungam | Dulbad | 2007-07-13 16:18:47 | | 8 | Sreraf | Encmelt | 2007-07-13 16:18:47 | +-----------+--------+------------+---------------------+ 8行集(0.00秒)
这表明,如本节前面所述,3种允许的XML格式中的任何一种或所有一种都可以出现在单个文件中,并可以使用它们读取加载XML
.
与刚才演示的导入操作相反——即将MySQL表数据转储到XML文件中——可以使用mysql客户端来自系统外壳,如下所示:
shell> mysql——xml -e "SELECT * FROM test。人">人-dump.xml shell> cat person-dump.xml 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
的——xml
选择原因mysql客户端对其输出使用XML格式;的- e
选项会导致客户端立即执行该选项后面的SQL语句。看到人表并导入转储文件到新表中,如下所示:
mysql >使用测试;mysql> CREATE TABLE person2 LIKE personmysql> LOAD XML LOCAL INFILE 'person . dump. XML ' -> INTO TABLE person2;查询OK, 8行影响(0.01 sec)记录:8删除:0跳过:0警告:0 mysql> SELECT * FROM person2;+-----------+--------+------------+---------------------+ | person_id |帧| lname |创建 | +-----------+--------+------------+---------------------+ | 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 | | 2 | Sajon | Rondela | 2007-07-13 16:18:47 | | 3 | Likema | Orrtmons | 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
表,并删除创建
列,然后使用相同的加载XML
我们之前使用的语句是这样的:
mysql >截断person2;mysql> ALTER TABLE person2 DROP COLUMN created;查询OK, 0行影响(0.52秒)记录:0重复:0警告:0 mysql> SHOW CREATE TABLE person2\G *************************** 1。行 *************************** 表:person2创建表:创建表“person2”(“person_id”int(11)不是零,“帧”varchar(40)默认为空,“lname”varchar(40)默认为空,主键(person_id))引擎= InnoDB默认字符集= utf8 1行组(0.00秒)mysql >加载XML本地INFILE“person-dump.xml”——>表person2;查询OK, 8行影响(0.01 sec)记录:8删除:0跳过:0警告:0 mysql> SELECT * FROM person2;+-----------+--------+------------+ | person_id |帧| lname | +-----------+--------+------------+ | 1 | Kapek | Sainnouine | | 2 | Sajon | Rondela | | 3 | Likema | Orrtmons | | 4 | Slar |可以Manlanth | | 5 |气孔| Nilu | | 6 | Nirtam | Sklod | | 7 | Sungam | Dulbad | | 8 | Sreraf | Encmelt | +-----------+--------+------------+ 8行设置(0.00秒)
在XML文件的每一行中给出字段的顺序并不影响加载XML
;字段顺序可以随行变化,不需要与表中相应列的顺序相同。
如前所述,您可以使用(
一个或多个XML字段(仅选择所需的字段)或用户变量(存储相应的字段值以供以后使用)的列表。当您希望将XML文件中的数据插入到名称与XML字段名称不匹配的表列中时,用户变量可能特别有用。要了解其工作原理,我们首先创建一个名为field_name_or_user_var
,……)个人
谁的结构匹配人
表,但其列的名称不同:
mysql> CREATE TABLE individual (-> individual_id INT NOT NULL PRIMARY KEY, -> name1 VARCHAR(40) NULL, -> name2 VARCHAR(40) NULL, -> made TIMESTAMP ->);查询OK, 0行受影响(0.42秒)
在这种情况下,你不能简单地将XML文件直接加载到表中,因为字段名和列名不匹配:
mysql> LOAD XML INFILE '../bin/person-dump.xml' INTO TABLE test.individual;ERROR 1263(22004):列设置为默认值;NULL提供给NOT NULL列'individual_id'在行1
这是因为MySQL服务器查找与目标表的列名匹配的字段名。您可以通过将字段值选择到用户变量中来解决这个问题,然后将目标表的列设置为这些变量的值集
.您可以在一条语句中执行这两种操作,如下所示:
mysql> LOAD XML INFILE '../bin/person-dump.xml' -> INTO TABLE test。个人(@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文件导入到具有不同定义的数据库表中。对于本例,假设您有一个名为tagname
>”address.xml
包含以下XML:
<?xml version = " 1.0 " ?> Robert Jones Mary Smith
您可以再次使用test.person
清除表中所有现有记录后,显示其结构如下所示:
mysql <截断的人;查询好,0行影响(0.04秒)mysql <显示创建表人\ G *************************** 1。行 *************************** 表:人创建表: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_id INT NOT NULL PRIMARY KEY, person_id INT NULL, street VARCHAR(40) NULL, zip INT NULL, city VARCHAR(40) NULL, created TIMESTAMP);
将数据从XML文件导入到人
表中,执行以下操作加载XML
语句指定要由<人>
元素,如下所示;
mysql> LOAD XML LOCAL INFILE 'address.xml' -> INTO TABLE person -> ROWS IDENTIFIED BY '';Query OK, 2 rows affected (0.00 sec) Records: 2 Deleted: 0 skip: 0 Warnings: 0.删除警告
您可以使用选择
声明:
mysql> SELECT * FROM person;+-----------+--------+-------+---------------------+ | person_id |帧| lname |创建 | +-----------+--------+-------+---------------------+ | 1 |罗伯特琼斯| | 2007-07-24 17:37:06 | | 2 |玛丽史密斯| | 2007-07-24 17:37:06 | +-----------+--------+-------+---------------------+ 2行集(0.00秒)
自<地址>
元素中没有对应的列人
表,他们被跳过。
导入数据<地址>
元素进地址
表,可以使用加载XML
声明所示:
mysql> LOAD XML LOCAL INFILE 'address. XML ' -> INTO TABLE address -> ROWS IDENTIFIED BY '';Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 skip: 0 Warnings: 0.删除警告
您可以看到数据是使用选择
语句如下:
mysql> SELECT * FROM address;+------------+-----------+-----------------+-------+--------------+---------------------+ | 街address_id | person_id | |邮政| |创建城市 | +------------+-----------+-----------------+-------+--------------+---------------------+ | 1 | 1 |磨溪路| 45365 |悉尼| 2007-07-24 17:37:37 | | 2 | 1 |大街| 28681 |泰勒斯威尔|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注释中的元素不会被导入。然而,由于有一个person_id
列地址
表中,值的person_id
属性。<人>
元素为每个<地址>
是导入到地址
表格
安全方面的考虑。与加载数据
语句时,XML文件从客户端主机传输到服务器主机是由MySQL服务器发起的。理论上,可以构建一个打过补丁的服务器,它会告诉客户端程序传输服务器选择的文件,而不是客户端在加载XML
声明。这样的服务器可以访问客户机主机上客户机用户具有读访问权的任何文件。
在Web环境中,客户机通常从Web服务器连接到MySQL。可以对MySQL服务器运行任何命令的用户可以使用加载XML本地
读取Web服务器进程具有读访问权限的任何文件。在这个环境中,与MySQL服务器相关的客户机实际上是Web服务器,而不是由连接到Web服务器的用户运行的远程程序。
通过启动服务器,可以禁用从客户机加载XML文件——local-infile = 0
或——local-infile =了
.控件启动时也可以使用此选项mysql客户禁用加载XML
用于客户端会话期间。
若要防止客户端从服务器加载XML文件,请不要授予文件
如果客户端用户帐户已经拥有该特权,则取消该特权。
撤销的文件
特权(或者一开始就不授予它)只会使用户无法执行加载XML
语句(以及LOAD_FILE ()
函数;它不阻止用户执行加载XML本地
.要禁止此语句,您必须使用——local-infile =了
.
换句话说文件
特权只影响客户端是否可以读取服务器上的文件;它与客户端是否可以读取本地文件系统上的文件没有关系。