MYSQL导入xml

MYSQL导入xml导出xml

一、导出xml

1、按照SQL语句导出表数据到xml文件

mysql -X -uroot -p123456 -e "use mysql; select * from cs_student;" > ./a.xml

导出的xml格式如下

<resultset statement="select * from cs_student" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="STU_ID">1</field>
	<field name="STU_NAME">BANM
        </field>
	<field name="STU_SEX">WOMAN
   </field>
  </row>

  <row>
	<field name="STU_ID">2</field>
	<field name="STU_NAME">SANPZ
        </field>
	<field name="STU_SEX">MAN
     </field>
  </row>

2、mysqldump导出xml数据文件

导出mysql数据库下cs_student表

mysqldump --xml -uroot -p123456 mysql cs_student

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="mysql">
	<table_structure name="cs_student">
		<field Field="STU_ID" Type="int" Null="YES" Key="" Extra="" Comment="学号" />
		<field Field="STU_NAME" Type="varchar(20)" Null="YES" Key="" Extra="" Comment="姓名" />
		<field Field="STU_SEX" Type="varchar(10)" Null="YES" Key="" Extra="" Comment="性别" />
		<options Name="cs_student" Engine="InnoDB" Version="10" Row_format="Dynamic" Rows="4" Avg_row_length="4096" Data_length="16384" Max_dat
a_length="0" Index_length="0" Data_free="0" Create_time="2022-05-06 07:22:13" Update_time="2022-05-07 06:50:40" Collation="utf8mb4_0900_ai_ci" Create_o
ptions="" Comment="中证学生表" />
	</table_structure>
	<table_data name="cs_student">
	<row>
		<field name="STU_ID">1</field>
		<field name="STU_NAME">BANM
        </field>
		<field name="STU_SEX">WOMAN
   </field>
	</row>
	<row>
		<field name="STU_ID">2</field>
		<field name="STU_NAME">SANPZ
        </field>
		<field name="STU_SEX">MAN
     </field>
	</row>

二、导入xml

1、load_file()函数导入

登录mysql

mysql -uroot -p123456

create table mysql.xmlt(
 id int ,
 doc blob
 )

将xml文件 a.xml导入到mysql.xmlt

insert into mysql.xmlt values(1,load_file('/opt/module/a.xml'));

本方法将xml文件内容导入到数据库，是将xml文件直接内容直接导入，不推荐。因为不方便解析

2、load（推荐）

load xml local infile '/opt/module/insert_01.xml' into table mysql.cs_student rows identified by '<row>';

可能遇到的问题：

Q1：ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

查看是否开启加载本地文件

show variables like 'local_infile';

开启全局本地文件设置

set global local_infile=on;

MySQL 8.0版本， local_infile默认是关闭的，但是MySQL 5.7版本默认是开启的；

Q2：ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

使用如下命令导出（–local-infile=1）

mysql -uroot -p123456 --local-infile=1 -e "load xml local infile '/opt/module/insert_01.xml' into table mysql.cs_student rows identified by '<row>'"