欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL中通过结构化文本倒腾数据

程序员文章站 2024-01-17 08:19:46
拼接为sql语句使用mysql dump可以对表或者其它对象dump出来,可以以文本或者sql语句的形式,如果针对一张表的结果集还好说,甚至还可以使用where选项,但如果是多表关联,就不能把整个结果集导出为sql语句。传统方式为使用into outfile选项导出为本地文本文件。可以使用sql语句拼接的方式,把多表关联的查询输出为sql insert into语句,适合于数据量比较小的情况下,通过字符串类型字段快速的倒腾。select concat( 'insert into us......

 

拼接为sql语句

使用mysql dump可以对表或者其它对象dump出来,可以以文本或者sql语句的形式,如果针对一张表的结果集还好说,甚至还可以使用where选项,但如果是多表关联,就不能把整个结果集导出为sql语句。传统方式为使用into outfile选项导出为本地文本文件。

可以使用sql语句拼接的方式,把多表关联的查询输出为sql insert into语句,适合于数据量比较小的情况下,通过字符串类型字段快速的倒腾。

select concat(
  'insert into user(user_id,user_name,pswd) values(',
              '''',user_id,''',',
              '''',user_name,''',',
              '''',pswd,'''',
   ');') from user;               

如果字符串中有单引号,就会报错,使用转移符号"\" ,可以使用replace函数替换掉。例如:select replace('fdfdfd''','''','\\''');

如果是Oracle数据库,使用以下拼接方式:

select 
  'insert into user(user_id,user_name,pswd) values('||
              ''''||user_id||''',',
              ''''||user_name||''',',
              ''''||pswd||''');'
       from user;   

转换为结构化文本文件

select into outfile可以把查询结果导入到文本文件中。官方参考:https://dev.mysql.com/doc/refman/5.7/en/select-into.html

 

select into outfile为文本

SELECT ... FROM TABLE_A
INTO OUTFILE "/path/to/file"
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

用法说明:

MySQL中,可以使用SELECT...INTO OUTFILE语句将表的内容导出为一个文本文件。其基本的语法格式如下:

SELECT [列名] FROM table [WHERE 语句] INTO OUTFILE '目标文件' [OPTION];该语句分为两个部分。前半部分是一个普通的SELECT语句,通过这个SELECT语句来查询所需要的数据;后半部分是导出数据的。其中,“目标文件”参数指出将查询的记录导出到哪个文件中;

“OPTION”参数为可选参数选项,其可能的取值有:

FIELDS TERMINATED BY '字符串':设置字符串为字段之间的分隔符,可以为单个或多个字符。默认值是“\t”。

FIELDS ENCLOSED BY '字符':设置字符来括住字段的值,只能为单个字符。默认情况下不使用任何符号。

FIELDS OPTIONALLY ENCLOSED BY '字符':设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号。

FIELDS ESCAPED BY '字符':设置转义字符,只能为单个字符。默认值为“\”。

LINES STARTING BY '字符串':设置每行数据开头的字符,可以为单个或多个字符。默认情况下不使用任何字符。

LINES TERMINATED BY '字符串':设置每行数据结尾的字符,可以为单个或多个字符。默认值是“\n”。

FIELDS和LINES两个子句都是自选的,但是如果两个子句都被指定了,FIELDS必须位于LINES的前面。

 

注意事项:该语法中的“目标文件”被创建到服务器主机上,因此必须拥有文件写入权限(FILE权限,grant file on ...)后,才能使用此语法。同时,“目标文件”不能是一个已经存在的文件。

SELECT...INTO OUTFILE语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件,则不能使用SELECT...INTO OUTFILE语句。

 

结构化文本加载到数据库

load data infile方式

load data可以快速的把文本中的结构化数据插入到表中,是select into outfile 的一个补充,当然也可以使用mysqlimport工具来导入文本数据。

官方参考:https://dev.mysql.com/doc/refman/5.7/en/load-data.html

 

用法:LOAD DATA INFILE "/path/to/file" INTO TABLE table_name;

FIELDS必须位于LINES的前面,多个FIELDS子句排列在一起时,后面的FIELDS必须省略;同样,多个LINES子句排列在一起时,后面的LINES也必须省略。

注意:如果导出时用到了FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'语句,那么LOAD时也要加上同样的分隔限制语句。还要注意编码问题。

 

用法案例:

linux下 load data导入数据:

LOAD DATA INFILE "/tmp/a.txt" INTO TABLE t_tmp 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (a,b,c,d,e,f)

windows下稍微特殊些:

“TERMINATED BY '\r\n'”可以保证每条记录占一行。因为Windows操作系统下“\r\n”才是回车换行。如果不加这个选项,默认情况只是“\n”

导出example数据库下employee表的记录。其中,字段之间用“、”隔开,字符型数据用双引号括起来。每条记录以“>”开头。SQL代码如下:

如果在employee表中包含了中文字符,需要注明编码格式, CHARACTER SET gbk|utf8 语句,SQL代码如下:

SELECT * FROM example.employee INTO OUTFILE 'F:/tmp/emp.txt'
CHARACTER SET utf8
FIELDS
TERMINATED BY '\、'
OPTIONALLY ENCLOSED BY '\"'
LINES
STARTING BY '\>'
TERMINATED BY '\r\n';

 

 

 

 

本文地址:https://blog.csdn.net/u010033674/article/details/107671268

相关标签: MySQL数据库