数据文本导入MySQL总结
场景描述
最近工作中遇到需要导一批数据到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语句。如果使用的话，具体可以参考这里：mysqlimport
