LOAD DATA INFILE使用与详解

叁歲伎倆 2022-11-19 13:56 241阅读 0赞

## mysql LOAD DATA INFILE使用与详解 ##

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

基本语法：

根据官方的: [https://dev.mysql.com/doc/refman/5.7/en/load-data.html][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、创建一个数据库：

CREATE DATABASE `load_test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

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;

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

[https_dev.mysql.com_doc_refman_5.7_en_load-data.html]: https://dev.mysql.com/doc/refman/5.7/en/load-data.html