MySQL LOAD DATA INFILE

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

 

一、完整语法

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,...]

 关键字解释：

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，则改行将被忽略，若不指定默认为''，比如执行以下语句：

 

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

 对应的文件内容为：

 

 

xxx"abc",1
something xxx"def",2
"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的报文交换示意图：

 
 

三、示例

表：

CREATE TABLE `department` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

准备插入employee的文件内容：

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

 执行SQL及其结果：

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 
'foo' terminated by '\r\n' ignore 1 lines (id, name, dept_id);
Query OK, 2 rows affected (1.19 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from employee;
+----+------+---------+
| id | name | dept_id |
+----+------+---------+
|  1 | a"   |       0 |
|  3 | b    |       0 |
+----+------+---------+
2 rows in set (0.22 sec)

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