场景描述

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

文本预处理

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

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

数据导入

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

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

• 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表中的字段要多，或者是我们只希望导入每一行数据的某一些字段，我们可以在字段列表中按照导入文本的字段顺序加入一些用户变量，如下所示：
  

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

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

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

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

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

注：

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

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

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

另外

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