mysql中的load data infile可以快速的将文档中的数据插入数据表中,非常方便,快捷,导入300万条数据只需两三分钟。
语法如下:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]]
一、准备数据和表
1、在数据库test中新建表 student:
create table student(id int primary key auto_increment,name varchar(20) );
2、准备数据文件 student.txt,并保存在D盘目录下student.txt内容如下:
1,quejinlong
2,dongyuncheng
3,lixuan
4,zhouwen
5,chenming
二、测试
1、最简单的情况
mysql> use test;
mysql>load data infile 'd:\\student.txt' into table student fields terminated by ',' lines terminated by '\r\n';
以上代码即可实现,将student.txt 文件中的内容快速插入到student表中。
fields terminated by ',' lines terminated by '\r\n' 如果没有这两句,那么默认是 fields terminated by tab lines terminated by '\n'
2、ignore和replace关键字
因为student 表中id 是关键字,唯一的,sdudent.txt 中如果有id与表中数据一致,就会冲突,所以还需要加关键字ignore 或者replace 告诉程序碰到这种情况如何操作
mysql>load data infile 'd:\\student.txt' ignore into table student fields terminated by ',' lines terminated by '\r\n';
mysql>load data infile 'd:\\student.txt' replace into table student fields terminated by ',' lines terminated by '\r\n';
3、中文字符
如果文本中有中文字符,需要将该文本的字符集编码改为utf-8(与数据库编码一致),否则会出现乱码
查看student 表字符集(其实就是查看建表命令)可以用以下命令:
<span style="font-size: 18px;">mysql></span>show create table student;
4、忽略表头前几行
假如student.txt 内容如下:
<pre name="code" class="html" style="font-size: 18px;">第一行
第二行
0,quejinlong,2015-07-25,2015-07-25 16:37:471,dongyuncheng,2015-07-25,2015-07-25 16:37:472,lixuan,2015-07-25,2015-07-25 16:37:473,zhouwen,2015-07-25,2015-07-25 16:37:474,chenming3,2015-07-25,2015-07-25 16:37:47
mysql>load data infile 'd:\\student.txt' replace into table student fields terminated by ',' lines terminated by '\r\n' ignore 2 lines;
5、Enclosed By 和 Escaped By关键字
6、load file into 的反操作:将数据表导出为文件:
select * from student Into OutFile 'd:/studentout.txt';