[转帖]数据文本导入MySQL总结_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2092 | 回复: 0   主题: [转帖]数据文本导入MySQL总结        上一篇   下一篇 
ad222888
注册用户
等级:新兵
经验:66
发帖:134
精华:0
注册:2016-9-25
状态:离线
发送短消息息给ad222888 加好友    发送短消息息给ad222888 发消息
发表于: IP:您无权察看 2019-9-3 16:40:15 | [全部帖] [楼主帖] 楼主

场景描述

最近工作中遇到需要导一批数据到mysql中。数据以txt文件形式给出,大概有80G大小,统计后有三千多万行,也即会往表里导入三千多万条数据。具体该怎么办呢?写程序一行一行读文本,然后拼接SQL语句往数据库里插入(批量插入),太耗时了也太麻烦了,否定了这种想法。有没有工具能够直接从文本到数据到mysql呢?经查询,mysql客户端自带了导入工具mysqlimport同时mysql本身就支持LOAD DATA SQL语句。最终我选用了mysql的LOAD DATA命令来导入这些数据。下面我们就通过这个场景,一起了解一下mysql文本导入的使用方法。

文本预处理

数据文本是由以'\01'分隔的一行行字符串构成的。我们必须把这里的'\01'替换成mysql文本导入中支持的分隔符,我们这里选择替换成'\t\t',在文本目录下执行命令:

sed -i 's/\x01/\t\t/g' ./*

sed的执行速度还是很快的,一千六百万行文本五分钟左右就替换好了。替换后的文本如下所示:
现在我们的数据经过预处理,已经可以用来导入了。

数据导入

前面也说过,我们选择mysql的命令来导入文本数据。所以我们先一起来看一下mysql的数据导入语法:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
 [REPLACE | IGNORE]
 INTO TABLE tbl_name
 [PARTITION (partition_name,...)]
 [CHARACTER SET charset_name]
 [{FIELDS | COLUMNS}
 [TERMINATED BY 'string']
 [[OPTIONALLY] ENCLOSED BY 'char']
 [ESCAPED BY 'char']
 ]
 [LINES
 [STARTING BY 'string']
 [TERMINATED BY 'string']
 ]
 [IGNORE number {LINES | ROWS}]
 [(col_name_or_user_var,...)]
 [SET col_name = expr,...]

我们一个个参数具体解释一下:

  • file_name
    file_name必须是本地的要导入的文本数据

  • CHARACTER SET
    CHARACTER SET指定导入的文件的字符格式

  • FIELDS | COLUMNS
    如果需要在导入数据时对导入的文本进行分割,我们需要添加FIELDS TERMINATED BY ','语法,LINES TERMINATED BY 语句是以指定字符分隔文本为行,默认是以换行符进行行分隔。

  • IGNORE number [LINES | ROWS]
    IGNORE 后面加数字表示导入数据时忽略文本的前多少行.

  • (col_name1,col_name2...)字段列表
    每一行文本被分隔为一个个字段,但是我们导入这些数据的时候,每个字段对应到表中哪一个字段,是在此处指定的。数据文本中的字段按顺序导入到该字段列表指定的字段中。如果出现文本中的字段比我们mysql表中的字段要多,或者是我们只希望导入每一行数据的某一些字段,我们可以在字段列表中按照导入文本的字段顺序加入一些用户变量,如下所示:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

这样做就相当于我们直接丢弃了导入文本中的某些字段值。

  • SET col_name = expr
    如果表中某些字段我们希望是一个特定值,我们可以在这里直接设置。

我们的具体导入sql语句如下:

LOAD DATA  INFILE '/opt/data/backup/xx_01_xx  
INTO TABLE db_abc.t_info_xxx CHARACTER SET utf8  FIELDS TERMINATED BY '\t\t'  IGNORE 1  LINES
(@dummycola,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,
@dummycolb,@dummycolc,col12,col13,@dummycold,col14,col15,
@dummycole,col16,col17,col18,col19,@dummycolf,col19,col20,
col21,col22,col23,col24,col25,col26,col27);

先拿一部分数据测试,一百六十万行数据,导入大概花了2min左右,速度还是可以接受的。
执行结束后,我们可以通过sql命令SHOW WARNINGS查看数据导入中的一些告警,我的导入中就发现了一些字段的宽度不够的情况,通过调整、测试,保证表结构无误,就可以正式来导入这些数据了。

由于我们的这些数据是由十多个文本组成的,一条一条语句的在mysql客户端命令行去执行每个文件的导入太麻烦了,写个脚本来执行方便多了,我们的脚本如下:

#!/bin/bash

mysql=/usr/bin/mysql
host="192.168.0.1"
port=1234
username=xxx
passwd="xxx"
dbname=db_abc
tbname=t_info_xxx

time0=`date +%s`
echo "------------------start to import data into mysql...-----------------------"
echo -e "\n"
for i in `seq 1 12`
do
 num=`printf "%.2d" $i`
 filename="xxx_${num}_xxx"
 echo "import file:${filename}..."
 time1=`date +%s`
 mysql -h${host} -P${port} -u${username} -p${passwd} -e "
use ${dbname};
LOAD DATA LOCAL INFILE '/opt/data/backup/${filename}' INTO TABLE ${dbname}.${tbname} CHARACTER SET utf8 
FIELDS TERMINATED BY '\t\t' IGNORE 1 LINES
(@dummycola,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,@dummycolb,
@dummycolc,col12,col13,@dummycold,col14,col15,@dummycole,col16,col17,col18,col19,@dummycolf,col19,col20,col21,col22,col23,col24,col25,col26,col27);
quit
"
 echo "import file:${filename} success!"
 time2=`date +%s`
 comp1=`expr $time2 - $time1`
 echo "import file:${filename} has cost ${comp1} seconds!"
 echo -e "\n"
done
timen=`date +%s`
comp2=`expr $timen - $time0`
echo "The whole data import cost ${comp2} seconds!"
echo -e "\n"
echo "-----------------import data into mysql end!-----------------------"

注:

  • 因为是公司内部数据,所以具体的字段名称、数据库名称等都做了处理,但是这个脚本的整体结构是一样的。

  • 最后sql语句中的字段列表中有一些@dummycolx列,这些用户变量是为了解决文本中多出来的一些字段,而我们数据库表中没有对应字段,或者我们不需要一些文本中的字段,把这些@dummycolx列放在这里就相当于丢弃了这些字段,只导入我们需要的那些字段。

  • 最终我们一共导入了2000万条数据,总共耗时10多分钟。

另外

mysqlimport工具使用起来也是十分方便,实际上mysqlimport工具内部就是包装了LOAD DATA INFILE语句。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论