mysql导入数据load data infile用法整理

有时候我们需要将大量数据批量写入数据库，直接使用程序语言和Sql写入往往很耗时间，其中有一种方案就是使用MySql Load data infile导入文件的形式导入数据，这样可大大缩短数据导入时间。

假如是从MySql客户端调用，将客户端的文件导入，则需要使用 load local data infile.

//load data local infile "E:\\phpStudy\\PHPTutorial\\WWW\\admin\\modules\\admin\\caches/caches_import/audit.csv" into table mix_fidis_sgs_record fields terminated by ',' (num,name,size,file_offset,comment);

//LOAD DATA local INFILE 'E:\\phpStudy\\PHPTutorial\\WWW\\admin\\modules\\admin\\caches/caches_import/audit.csv' INTO TABLE mix_fidis_sgs_record CHARACTER SET gbk FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 LINES (@col0, @col1, @col2, @col3, @col4) set `num`=@col0, `name`=@col1, `size`=@col2, `file_offset`=@col3, `comment`=@col4;
 

LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必须是一个文字字符串。

1，开启load local data infile.

假如是Linux下编译安装，

如果使用源码编译的MySQL，在configure的时候，需要添加参数：--enable-local-infile 客户端和服务器端都需要，否则不能使用local参数。

./configure --prefix=/usr/local/mysql --enable-local-infile

make install

若是其它系统，可在配置文件中配置：

在MySql 配置文件My.ini文件中下面项中加入local-infile=1：

add:

[mysqld]

local-infile=1

[mysql]

local-infile=1

客户端和服务端度需要开启，对于客户端也可以在执行命中加上--local-infile=1 参数：

mysql --local-infile=1 -uroot -pyourpwd yourdbname

如:

如：/usr/local/mysql/bin/mysql -uroot -h192.168.0.2 -proot databaseName --local-infile=1 -e "LOAD DATA LOCAL INFILE 'data.txt' into table test(name,sex) "

2, 编码格式注意：

若包含中文，请保证导入文件、连接字符串、导入表都是UTF-8编码。

 

3，执行

在使用LOAD DATA到MySQL的时候，有2种情况：

（1）在远程客户端（需要添加选项：--local-infile=1）导入远程客户端文本到MySQL，需指定LOCAL（默认就是ignore）,加ignore选项会放弃数据，加replace选项会更新数据，都不会出现唯一性约束问题。

[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \

> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";

（2）在本地服务器导入本地服务器文本到MySQL，不指定LOACL，出现唯一性约束冲突，会失败回滚，数据导入不进去，这个时候就需要加ignore或者replace来导入数据。

mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

 

4，事务分析

步骤是这样的：

1，开启binlog，设置binlog_format=row，执行reset master；

2，load data infile xxxxx；

3，查看binlog。

 

可以看出，总共是一个事务，也通过mysqlbinlog查看了binary log，确认中间是被拆分成了多个insert形式。所以load data infile基本上是这样执行的：

begin

insert into values(),(),(),()...

insert into values(),(),(),()...

insert into values(),(),(),()...

...

...

commit

当然，由于row格式的binlog的语句并不是很明显的记录成多值insert语句，它的格式时

insert into table

set @1=

set @2=

...

set @n=

insert into table

set @1=

set @2=

...

set @n=

insert ...

;注意这里有一个分号‘;’，其实前面这一部分就相当于前面说的多值insert形式

然后接下来就重复上面的那种格式，也就是一个load data infile 拆成了多个多值insert语句。

前面说的是row格式记录的load data infile，那么对于statement是怎么样的呢？statement格式的binlog，它是这样记录的，binlog中还是同样的load data语句，但是在记录load data 语句之前，它会先将你master上这个load data 使用到的csv格式的文件拆分成多个部分，然后传到slave上（在mysql的tmpdir下），当然传这些csv格式的文件也会记录binlog event，然后最后真正的SQL语句形式就是load data local infile '/tmp/SQL_X_Y'这种形式（这里假设mysql的tmpdir是默认的/tmp），实际上这样很危险，比如tmpdir空间不够，那就会报错。不过从效率上来说两者可能差不多，因为statement格式的binlog也是拆分成了多个语句。