MySQL LOAD DATA INFILE

LOAD DATA INFILE语句是MySQL中用来批量、快速导入数据的一种方式，和SELECT ... INTO OUTFILE 作用是相反的

 

一、完整语法

Sql代码 

1. LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'  
2.     [REPLACE | IGNORE]  
3.     INTO TABLE tbl_name  
4.     [PARTITION (partition_name,...)]  
5.     [CHARACTER SET charset_name]  
6.     [{FIELDS | COLUMNS}  
7.         [TERMINATED BY 'string']  
8.         [[OPTIONALLY] ENCLOSED BY 'char']  
9.         [ESCAPED BY 'char']  
10.     ]  
11.     [LINES  
12.         [STARTING BY 'string']  
13.         [TERMINATED BY 'string']  
14.     ]  
15.     [IGNORE number {LINES | ROWS}]  
16.     [(col_name_or_user_var,...)]  
17.     [SET col_name = expr,...]  

 关键字解释：

LOW_PRIORITY | CONCURRENT：若指定LOW_PRIORITY，则该LOAD DATA 语句的真正执行将推迟到没有客户端在读取所设计的表时（只对只支持表锁的引擎有效）；

LOCAL：若未指定该关键字，则说明文件在MySQL所在机子上，文件由MySQL服务器去读取，此时如果后面指定为文件路径为相对路径，1.如果路径以./开头，那么路径是相对于MySQL的data目录的，2.如果路径不是以./开头，那么路径是相对于默认数据库的目录的；若指定了该关键字，则说明文件在客户端机子上，文件由客户端去读取并通过网络发送给MySQL服务器

REPLACE | IGNORE ：当插入的行遇到UNIQUE字段重复时，若指定为REPLACE，则用该行替换原来的行；若指定为IGNORE，则忽略改行

PARTITION (partition_name,...)：将数据插入指定分区

CHARACTER SET：若不指定字符集，MySQL默认使用character_set_database变量指定的字符集去读取文件，若文件字符集不同，则应指定该关键字

FIELDS TERMINATED BY：字段值的分隔符，若不指定则默认为 '\t'

FIELDS ENCLOSED BY：字段值的包括符，若不指定则默认为 ''

FIELDS ESCAPED BY：字段值的转义字符，若不指定则默认为'\\'

LINES TERMINATED BY：指定行分隔符，若不指定则默认为为系统的默认行分隔符（‘\r\n‘ on windows，'\n' on linux）

LINES STARTING BY：若指定该值为xxx，则MySQL会自动去掉xxx及其前面的字符，若某行不包含xxx，则改行将被忽略，若不指定默认为''，比如执行以下语句：

 

Sql代码 

1. LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test  
2.   FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';  

 对应的文件内容为：

 

 

Txt代码 

1. xxx"abc",1  
2. something xxx"def",2  
3. "ghi",3  

 则实际插入的行为("abc", 1), ("def", 2)

 

IGNORE LINES | ROWS：忽略文件开头的指定行，比如指定为2，那么MySQL只会解析并插入第三行及后面的数据

 

 二、协议交换细节

1.用户发起LOAD DATA AINFILE语句，客户端工具向MySQL服务器发送一个Query Packet（command type = 0x03），内含LOAD DATA INFILE完整SQL

2.MySQL解析该SQL，发现是LOAD DATA INFILE，此时，若MySQL发现SQL中不含LOCAL关键字，则MySQL直接根据路径去本地取文件，跳至第4步；若发现包含LOCAL关键字，则MySQL发送一个请求文件（命令标识为0xFB）的包到客户端，MySQL进入等待接受文件的状态（当前连接不可进行其他操作）

3.客户端接受到来自MySQL服务器的请求文件的命令，客户端解析出报文中的路径，并根据该路径去本地读取文件，将该文件分段发送给MySQL服务器（至于这个分段的大小是多少应该不是绝对的，调试时发现是16384字节，可能根据客户端不同而不同吧，因为理论上MySQL的报文最大长度远远不止16384，而是2^24 - 1），发送完文件内容后，最后发送一个空包（报文序列为[0, 0, 0, packetId]），以此告诉MySQL服务器文件内容已发送完毕。

4.MySQL服务器获取到完整的文件内容，开始解析并插入数据，完后回复一个OKPacket，告诉客户端affected rows，warnings条数等信息

 

以下是带有LOCAL的报文交换示意图：

 
 

三、示例

表：

Sql代码 

1. CREATE TABLE `department` (  
2.   `id` int(11) NOT NULL,  
3.   `name` varchar(255) DEFAULT NULL,  
4.   PRIMARY KEY (`id`)  
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
6.   
7. CREATE TABLE `employee` (  
8.   `id` int(11) NOT NULL,  
9.   `name` varchar(255) DEFAULT NULL,  
10.   `dept_id` int(11) DEFAULT NULL,  
11.   PRIMARY KEY (`id`),  
12.   KEY `dept_id` (`dept_id`),  
13.   CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)  
14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

 

准备插入employee的文件内容：

Txt代码 

1. "0","a","0"  
2. foo"1","a\"","0"  
3. "2","a","0"  
4. xxxxxxfoo"3","b","0"  

 执行SQL及其结果：

Sql代码 

1. mysql> load data local infile 'C:\\Users\\lixiaohui\\Desktop\\source.sql' into table employee character set utf8 fields terminated by ',' enclosed by '"' escaped by '\\' lines starting by   
2. 'foo' terminated by '\r\n' ignore 1 lines (id, name, dept_id);  
3. Query OK, 2 rows affected (1.19 sec)  
4. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0  
5.   
6. mysql> select * from employee;  
7. +----+------+---------+  
8. | id | name | dept_id |  
9. +----+------+---------+  
10. |  1 | a"   |       0 |  
11. |  3 | b    |       0 |  
12. +----+------+---------+  
13. 2 rows in set (0.22 sec)  

 第一行被ignore 1 lines忽略，第三行不包含'foo'而被忽略