mysql load xml_MySQL的SQL语句 - 数据操作语句（10）- LOAD XML 语句

最新推荐文章于 2024-04-09 22:52:47 发布

Mustafa先生

最新推荐文章于 2024-04-09 22:52:47 发布

阅读量723

点赞数

文章标签： mysql load xml

本文链接：https://blog.csdn.net/weixin_32136817/article/details/113261749

版权

LOAD XML语句用于将数据从XML文件导入到MySQL表中，支持三种不同的XML格式。文件中的数据行可以通过ROWS IDENTIFIED BY子句指定。在导入过程中，可以设置LOW_PRIORITY、CONCURRENT、REPLACE或IGNORE等选项。LOAD XML与LOAD DATA语句的部分子句作用相似，如IGNORE、CHARACTER SET和SET。示例展示了如何将XML文件的数据导入到person表中，以及如何从表中导出数据到XML文件。

摘要由CSDN通过智能技术生成

LOAD XML 语句

1. LOAD XML

2. [LOW_PRIORITY | CONCURRENT] [LOCAL]

3. INFILE 'file_name'

4. [REPLACE | IGNORE]

5. INTO TABLE [db_name.]tbl_name

6. [CHARACTER SET charset_name]

7. [ROWS IDENTIFIED BY '']

8. [IGNORE number {LINES | ROWS}]

9. [(field_name_or_user_var

10. [, field_name_or_user_var] ...)]

11. [SET col_name={expr | DEFAULT},

12. [, col_name={expr | DEFAULT}] ...]

LOAD XML 语句将数据从 XML 文件读入表中。file_name 必须以文本字符串的形式给出。可选的 ROWS IDENTIFIED BY 子句中的 tagname 也必须作为文本字符串给定，并且必须用尖括号(< 和 >)包围。

LOAD XML 作为在 XML 输出模式(用 --xml 选项启动客户端)下运行 mysql 客户端的补充。要将表中的数据写入XML文件，可以在系统 shell 中使用 --xml 和 -e 选项调用 mysql 客户端，如下所示：

shell> mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

要将文件读回表中，请使用 LOAD XML。默认情况下，元素被认为是数据库表行的等价物；这可以使用 ROWS IDENTIFIED BY 子句更改。

此语句支持三种不同的 XML 格式：

● 列名作为属性，列值作为属性值：

● 列名作为标签，列值作为这些标签的内容：

2. value1

3. value2

● 列名是标签的 name 属性，值是这些标签的内容：

2. value1

3. value2

这是 mysqldump 等其他 MySQL 工具使用的格式。

这三种格式都可以在同一个 XML 文件中使用；导入程序会自动检测每一行的格式并正确解析它。根据标签或属性名和列名匹配所有的标签。

在 MySQL 8.0.21 之前，LOAD XML 不支持源 XML 中的 CDATA 部分。

以下子句对 LOAD XML 的作用与对 LOAD DATA 的作用基本相同：

● LOW_PRIORITY 或 CONCURRENT

● LOCAL

● REPLACE or IGNORE

● CHARACTER SET

● SET

(field_name_or_user_var, ...) 是逗号分隔的 XML 字段或用户变量的列表。这里的用户变量名称必须与XML文件中前缀为 @ 的字段名称匹配。可以使用字段名称仅选择所需的字段。可以使用用户变量来存储相应的字段值，以便以后重用。

IGNORE number LINES 或 IGNORE number ROWS 子句将跳过 XML 文件中的前 number 行。它类似于 LOAD DATA 语句的 IGNORE ... LINES 子句。

假设我们有一个名为 person 的表，创建如下：

1. USE test;

3. CREATE TABLE person (

4. person_id INT NOT NULL PRIMARY KEY,

5. fname VARCHAR(40) NULL,

6. lname VARCHAR(40) NULL,

7. created TIMESTAMP

8. );

进一步假设这个表最初是空的。

现在假设我们有一个简单的 XML 文件 person.xml，其内容如下：

4. LikameÖrrtmons

5. SlarManlanth

6. 5Stoma

7. Milu

8. 6Nirtam

9. Sklöd

10. SungamDulbåd

11.

12.

前面讨论的每个允许的XML格式都在这个示例文件中表示出来了。

将 person.xml 的数据导入 person 表中，可以使用以下语句：

1. mysql> LOAD XML LOCAL INFILE 'person.xml'

2. -> INTO TABLE person

3. -> ROWS IDENTIFIED BY '';

5. Query OK, 8 rows affected (0.00 sec)

6. Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

我们假设 person.xml 位于 MySQL 数据目录中。如果找不到该文件，将导致以下错误：

1. ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)

ROWS IDENTIFIED BY '' 子句意味着 XML 文件中的每个元素都被视为是表中要导入数据的一行。在本例中，这是 test 数据库中的 person 表。

从服务器的响应可以看出，8行数据被导入到 test.person 表。可以通过简单的 SELECT 语句进行验证：

1. mysql> SELECT * FROM person;

2. +-----------+--------+------------+---------------------+

4. +-----------+--------+------------+---------------------+

5. | 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 |

6. | 2 | Sajon | Rondela | 2007-07-13 16:18:47 |

7. | 3 | Likame | Örrtmons | 2007-07-13 16:18:47 |

8. | 4 | Slar | Manlanth | 2007-07-13 16:18:47 |

9. | 5 | Stoma | Nilu | 2007-07-13 16:18:47 |

10. | 6 | Nirtam | Sklöd | 2007-07-13 16:18:47 |

11. | 7 | Sungam | Dulbåd | 2007-07-13 16:18:47 |

12. | 8 | Sreraf | Encmelt | 2007-07-13 16:18:47 |

13. +-----------+--------+------------+---------------------+

14. 8 rows in set (0.00 sec)

这表明，如本节前面所述，这3种允许的XML格式中的都可以出现在单个文件中，可以使用 LOAD XML 进行读取。

与导入操作相反的是，可以从系统 shell 使用 mysql 客户端将 MySQL 表数据转储到XML文件中，如下所示：

1. shell> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml

2. shell> cat person-dump.xml

3. <?xml version="1.0"?>

7. 1

8. Kapek

9. Sainnouine

10.

11.

12.

13. 2

14. Sajon

15. Rondela

16.

17.

18.

19. 3

20. Likema

21. Örrtmons

22.

23.

24.

25. 4

26. Slar

27. Manlanth

28.

29.

30.

31. 5

32. Stoma

33. Nilu

34.

35.

36.

37. 6

38. Nirtam

39. Sklöd

40.

41.

42.

43. 7

44. Sungam

45. Dulbåd

46.

47.

48.

49. 8

50. Sreraf

51. Encmelt

52.

53.

注意

--xml 选项使 mysql 客户端对其输出使用 xml 格式；-e 选项使客户端执行在其后设置的 SQL 语句。

可以通过创建 person 表的副本并将转储文件导入新表来验证转储是否有效，如下所示：

1. mysql> USE test;

2. mysql> CREATE TABLE person2 LIKE person;

3. Query OK, 0 rows affected (0.00 sec)

5. mysql> LOAD XML LOCAL INFILE 'person-dump.xml'

6. -> INTO TABLE person2;

7. Query OK, 8 rows affected (0.01 sec)

8. Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

10. mysql> SELECT * FROM person2;

11. +-----------+--------+------------+---------------------+

13. +-----------+--------+------------+---------------------+

14. | 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 |

15. | 2 | Sajon | Rondela | 2007-07-13 16:18:47 |

16. | 3 | Likema | Örrtmons | 2007-07-13 16:18:47 |

17. | 4 | Slar | Manlanth | 2007-07-13 16:18:47 |

18. | 5 | Stoma | Nilu | 2007-07-13 16:18:47 |

19. | 6 | Nirtam | Sklöd | 2007-07-13 16:18:47 |

20. | 7 | Sungam | Dulbåd | 2007-07-13 16:18:47 |

21. | 8 | Sreraf | Encmelt | 2007-07-13 16:18:47 |

22. +-----------+--------+------------+---------------------+

23. 8 rows in set (0.00 sec)

没有要求 XML 文件中的每个字段都与相应表中的列相匹配。没有对应列的字段会被跳过。首先清空 person2 表并删除 created 列，然后使用我们刚才使用的 LOAD XML 语句，如下所示：

1. mysql> TRUNCATE person2;

2. Query OK, 8 rows affected (0.26 sec)

4. mysql> ALTER TABLE person2 DROP COLUMN created;

5. Query OK, 0 rows affected (0.52 sec)

6. Records: 0 Duplicates: 0 Warnings: 0

8. mysql> SHOW CREATE TABLE person2\G

9. *************************** 1. row ***************************

10. Table: person2

11. Create Table: CREATE TABLE `person2` (

12. `person_id` int(11) NOT NULL,

13. `fname` varchar(40) DEFAULT NULL,

14. `lname` varchar(40) DEFAULT NULL,

15. PRIMARY KEY (`person_id`)

16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

17. 1 row in set (0.00 sec)

18.

19. mysql> LOAD XML LOCAL INFILE 'person-dump.xml'

20. -> INTO TABLE person2;

21. Query OK, 8 rows affected (0.01 sec)

22. Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

23.

24. mysql> SELECT * FROM person2;

25. +-----------+--------+------------+

26. | person_id | fname | lname |

27. +-----------+--------+------------+

28. | 1 | Kapek | Sainnouine |

29. | 2 | Sajon | Rondela |

30. | 3 | Likema | Örrtmons |

31. | 4 | Slar | Manlanth |

32. | 5 | Stoma | Nilu |

33. | 6 | Nirtam | Sklöd |

34. | 7 | Sungam | Dulbåd |

35. | 8 | Sreraf | Encmelt |

36. +-----------+--------+------------+

37. 8 rows in set (0.00 sec)

字段在 XML 文件的每一行中的给定顺序不会影响 LOAD XML 的操作；字段顺序可能会因行而异，不需要与表中相应列的顺序相同。

如前所述，可以使用一个或多个 XML 字段(仅用于选择所需的字段)或用户变量(存储相应的字段值以供以后使用)的 (field_name_or_user_var, ...) 列表。当希望将 XML 文件中的数据插入到名称与 XML 字段名称不匹配的表列时，用户变量尤其有用。为了了解其工作原理，我们首先创建一个名为 individual 的表，其结构与 person 表的结构匹配，但其列的名称不同：

1. mysql> CREATE TABLE individual (

2. -> individual_id INT NOT NULL PRIMARY KEY,

3. -> name1 VARCHAR(40) NULL,

4. -> name2 VARCHAR(40) NULL,

5. -> made TIMESTAMP

6. -> );

7. Query OK, 0 rows affected (0.42 sec)

在这种情况下，不能简单地将 XML 文件直接加载到表中，因为字段名和列名不匹配：

1. mysql> LOAD XML INFILE '../bin/person-dump.xml' INTO TABLE test.individual;

2. ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1

这是因为 MySQL 服务器查找与目标表的列名匹配的字段名。可以通过在用户变量中选择字段值来解决此问题，然后使用 SET 语句将目标表的列设置为这些变量的值。可以在一条语句中执行这两个操作，如下所示：

1. mysql> LOAD XML INFILE '../bin/person-dump.xml'

2. -> INTO TABLE test.individual (@person_id, @fname, @lname, @created)

3. -> SET individual_id=@person_id, name1=@fname, name2=@lname, made=@created;

4. Query OK, 8 rows affected (0.05 sec)

5. Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

7. mysql> SELECT * FROM individual;

8. +---------------+--------+------------+---------------------+

10. +---------------+--------+------------+---------------------+

11. | 1 | Kapek | Sainnouine | 2007-07-13 16:18:47 |

12. | 2 | Sajon | Rondela | 2007-07-13 16:18:47 |

13. | 3 | Likema | Örrtmons | 2007-07-13 16:18:47 |

14. | 4 | Slar | Manlanth | 2007-07-13 16:18:47 |

15. | 5 | Stoma | Nilu | 2007-07-13 16:18:47 |

16. | 6 | Nirtam | Sklöd | 2007-07-13 16:18:47 |

17. | 7 | Sungam | Dulbåd | 2007-07-13 16:18:47 |

18. | 8 | Srraf | Encmelt | 2007-07-13 16:18:47 |

19. +---------------+--------+------------+---------------------+

20.8 rows in set (0.00 sec)

用户变量的名称必须与 XML 文件中相应字段匹配，并添加@前缀，以表明它们是变量。用户变量不需要按照与相应字段相同的顺序列出或分配。

使用 ROWS IDENTIFIED BY '' 子句，可以将同一 XML 文件中的数据导入到具有不同定义的数据库表中。对于本例，假设有一个名为 address.xml 的文件，包含以下 XML：

1. <?xml version="1.0"?>

5. Robert

6. Jones

10.

11.

12. Mary

13. Smith

14.

15.

16.

17.

18.

可以再次使用本节前面定义的 test.person 表，清除表中的所有记录，显示其结构，如下所示：

1. mysql< TRUNCATE person;

2. Query OK, 0 rows affected (0.04 sec)

4. mysql< SHOW CREATE TABLE person\G

5. *************************** 1. row ***************************

6. Table: person

7. Create Table: CREATE TABLE `person` (

8. `person_id` int(11) NOT NULL,

9. `fname` varchar(40) DEFAULT NULL,

10. `lname` varchar(40) DEFAULT NULL,

11. `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

12. PRIMARY KEY (`person_id`)

13. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

14.1 row in set (0.00 sec)

现在使用以下 CREATE TABLE 语句在 test 数据库中创建一个 address 表：

1. CREATE TABLE address (

2. address_id INT NOT NULL PRIMARY KEY,

3. person_id INT NULL,

4. street VARCHAR(40) NULL,

5. zip INT NULL,

6. city VARCHAR(40) NULL,

7. created TIMESTAMP

8. );

要将数据从 XML 文件导入 person 表，请执行以下 LOAD XML 语句，该语句说明行由元素指定，如下所示；

1. mysql> LOAD XML LOCAL INFILE 'address.xml'

2. -> INTO TABLE person

3. -> ROWS IDENTIFIED BY '';

4. Query OK, 2 rows affected (0.00 sec)

5. Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

可以使用 SELECT 语句验证是否导入了记录：

1. mysql> SELECT * FROM person;

2. +-----------+--------+-------+---------------------+

4. +-----------+--------+-------+---------------------+

5. | 1 | Robert | Jones | 2007-07-24 17:37:06 |

6. | 2 | Mary | Smith | 2007-07-24 17:37:06 |

7. +-----------+--------+-------+---------------------+

8. 2 rows in set (0.00 sec)

由于 XML 文件中的

元素在 person 表中没有对应的列，因此将跳过它们。

要将数据从

元素导入 address 表，请使用如下所示的 LOAD XML 语句：

1. mysql> LOAD XML LOCAL INFILE 'address.xml'

2. -> INTO TABLE address

3. -> ROWS IDENTIFIED BY '

4. Query OK, 3 rows affected (0.00 sec)

5. Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

可以看到数据是使用 SELECT 语句导入的，例如：

1. mysql> SELECT * FROM address;

2. +------------+-----------+-----------------+-------+--------------+---------------------+

4. +------------+-----------+-----------------+-------+--------------+---------------------+

5. | 1 | 1 | Mill Creek Road | 45365 | Sidney | 2007-07-24 17:37:37 |

6. | 2 | 1 | Main Street | 28681 | Taylorsville | 2007-07-24 17:37:37 |

7. | 3 | 2 | River Road | 80239 | Denver | 2007-07-24 17:37:37 |

8. +------------+-----------+-----------------+-------+--------------+---------------------+

9. 3 rows in set (0.00 sec)

不导入

元素中包含在 XML 注释中的数据。但是，由于 address 表中有一个 person_id 列，因此每个

的父元素的 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用户，或者如果客户端用户帐户已经拥有该权限，则取消该权限。

重要

撤销 FILE 权限(或者首先不授予它)只会使用户无法执行 LOAD XML 语句(以及 LOAD_FILE() 函数)；它不会阻止用户执行 LOAD XML LOCAL。若要禁止此语句，启动服务器或客户端时必须使用 --local-infile=OFF。

换句话说，FILE 权限只影响客户端是否可以读取服务器上的文件；它与客户端是否可以读取本地文件系统上的文件没有关系。