数据文本导入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

参考

• LOAD DATA INFILE Syntax