(转载)mysql大数据量导入(LOAD DATA INFILE)

原文地址:LOAD DATA INFILE使用与详解_longzhoufeng的博客-CSDN博客_load data infile

mysql导入数据一般都是用insert into,那么mysql有没有一个高效导入方法呢？答案是肯定的，那就是load data infile,下面我们来详细的讲解一下相关的使用方法

基本语法：

根据官方的: https://dev.mysql.com/doc/refman/5.7/en/load-data.html

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, 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
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

根据官方的资料和网上搜集的资料,对上面的语法格式一一解释一下：如

1、LOW_PRIORITY
如果你指定关键词LOW_PRIORITY,这个只对那些只采用了表级别锁（如MYISAM)的引擎有影响，比如InnoDB使用的是行锁，不受这个影响，具体的来说，使用了LOW_PRIORITY，则本操作会在其它线程完成之后再操作。

2、CONCURRENT
CONCURRENT会和其它线程同时进行，这个对性能是有一些影响。

3、LOCAL
这是个非常重要的关键字，指明了文件的位置，简单的说，如果指定了local，则表示文件位于客户端，如果没有，则表示文件在Server端。同时，这个关键字的使用还会影响到load data命令对于错误数据的处理方式

A) 如果是指定了local，则数据从客户端读取，文档中的说法是会在服务端的临时目录下创建一份文件的copy，但我在测试的时候并没有发现，如果file_name中是绝对路径就不用解释，如果是相对路径，则文件的位置应该是在客户端程序启动的位置，所以为了保险，一般使用绝对路径。由于涉及到数据传输，所以这种方式会相对来说慢一些。

B) 如果未指定local，则文件应该是直接在服务端，这种情况下如果文件名使用的是相对路径，则又分两种情况，一种是文件名前没有相对目录，则直接是在默认数据库的data目录下查找，如果是指定了相对目录，则从server的data目录下寻找。

C) 如果指定了local，则当某条数据处理有误时，系统把这个错误记录为一个warning，不会影响下一条数据的处理，因为涉及到数据传输。而如果没有指定local，则默认情况下，遇到错误后不会继续执行。

所以，如果我们是在客户端执行load data命令，一定记得加上local参数。

4、REPLACE | IGNORE
如果指定了,那么,当前的数据跟表中的数据有惟一性冲突的时候，采用什么样的方式，是替换已有还是忽略当前。特别需要说明的是，当这两种方式都未指定时，如果数据来自于客户端，则重复的数据会忽略，如果来源于服务端，则命令将终止执行。

5、PARTITION
指定具体的分区，由于之前数据库中没用到过分区，个人对这块也不熟悉，所以暂时不解释，等到了解了再补充

6、CHARACTER SET
指定编码集，如果文件的编码跟数据库的编码不一致，可能会出现乱码的问题。所以要注意的是，这里指定的是文件的编码集，而不是数据库的编码集

7、

[{FIELDS | COLUMNS} [TERMINATED BY ‘string’][[OPTIONALLY] ENCLOSED BY ‘char’][ESCAPED BY ‘char’]]
[{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]

这些指定了对于字段的处理方式

FIELDS和COLUMNS指定其中一个即可。

TERMINATED表示字段间的分隔符，

ENCLOSED BY
的意思是字段值由什么符号包围

ESCAPED
表示指定转义字符。

在不指定这个参数的情况下，默认的字段分隔符是\t, 默认字段值无任何值包围，默认转义字符为\.(反斜)

8、 [LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]
指定每一行的起始符与终止符，默认情况下，起始符为空，终止符为’\n’，对于windows产生的文本文件来说，需要指定换行符为’\r\n’.

9、IGNORE number LINES
忽略文件中的前 number 行，通常情况下，我们生成的文件可能有列名，那么要忽略的放在，这儿的值设置为１即可。需要注意的是这里是行的数量，而不是行号。

10、[(col_name_or_user_var,…)]
有的时候我们不需要给所有的字段都填充值，这个时候就可以指定列名，以()将列名括起来，注意这里也可以是用户自定义的用户表达式。

11、[SET col_name = expr,…]
如果在前一步中指定了用户表达式，那么相应就可以使用列名等于用户表达式的方式来指定，这个我没有用过，给出一个官方的示例如下：

LOAD DATA INFILE 'file.txt'　INTO TABLE t1(column1, @var1)　SET column2 = @var1/100;
1
下面我们来做一个案例：
1、创建一个数据库：

CREATE DATABASE `load_test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
1
2、创建表

DROP TABLE IF EXISTS `tb0`;
CREATE TABLE tb0(
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    age VARCHAR(10) NOT NULL,
    description TEXT NOT NULL,
    PRIMARY KEY  (id),
    UNIQUE KEY idx_name(username)
) ENGINE=MYISAM DEFAULT CHARSET=utf8

2、如果,没有开启这个功能时执行LOAD DATA INFILE报错如下：

1148 - The used command is not allowed with this MySQL version

3、我们通过命令查看一下是否开启和关闭

show global variables like 'local_infile';

4、如果是关闭状态的，就把它开启一下（1为开启，0为关闭 ）

set global local_infile=1;

1
5、新一个test.txt文件，通过换行的方式插入数据,内容如下：

"李奎5"
"李奎6"
"李奎7"

上面是换行为一个字段，一行代表一个字段的数据

执行sql的load data infile命令

以换行为一条数据插入到对应的字段
-- 以换行为一条数据插入到对应的字段

LOAD DATA LOCAL INFILE 'D:/app/load_data_mysql/test.txt' INTO TABLE tb0 FIELDS TERMINATED BY '\n' (`username`,`age`,`description`);
SELECT * FROM tb0

6、新一个test2.txt文件，通过逗号分隔的方式插入数据,内容如下：

"111111","22222","33333"

以逗号分隔为一条数据插入对应的字段
-- 以逗号分隔为一条数据插入到对应的字段

LOAD DATA LOCAL INFILE 'D:/app/load_data_mysql/test2.txt' INTO TABLE tb0
FIELDS TERMINATED BY ',' (`username`,`age`,`description`);
SELECT * FROM tb0

7、新一个test3.txt文件，通过逗号分隔，并且以换行为一条数据，插入批量数据,内容如下：

"王二米","20","相貌平常，经常耍流氓！哈哈"
"老三","24","很强"
"老四","34","XXXXX"
"老五","52","***%*￥*￥*￥*￥"
"小猫","45","中间省略。。。"
"小狗","12","就会叫"
"小妹","21","PP的很"
"小坏蛋","52","表里不一"
"上帝他爷","96","非常英俊"
"MM来了","10","。。。"
"歌颂党","20","社会主义好"
"人民好","20","的确是好"
"老高","10","学习很好"
"斜三","60","眼睛斜了"
"中华之子","100","威武的不行了"
"大米","63","我爱吃"
"苹果","15","好吃"

以逗号分隔为一个字段的数据，以换行为一条数据，插入批量数据到相应的字段

LOAD DATA LOCAL INFILE 'D:/app/load_data_mysql/test3.txt' INTO TABLE tb0 CHARACTER SET utf8 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (`username`,`age`,`description`);
SELECT * FROM tb0