100001,胡一,北京,huyi@51enet.com
100002,胡二,上海,huer@51enet.com
100003,胡十,广州,hubai@51enet.com
100004,胡百,深圳,hushi@51enet.com
100005,胡千,珠海,huqian@51enet.com
100006,胡万,杭州,huwan@51enet.com


create table ld_csv1(id int not null default '0',
 username varchar(5) not null,
 city varchar(6) not null,
 email varchar(50) not null) engine=innodb default charset=utf8;

注意mysql5.7要先设置my.cnf的变量:
secure_file_priv=""

然后重启数据库,然后执行mysqlimport命令:
[root@localhost soft]# mysqlimport -uroot -p123456 -h192.168.11.31 test --default-character-set=utf8 --fields-terminated-by=',' /tmp/ld_csv1


--default-character-set= 设置默认字符
--fields-terminated-by 指定每列分隔符
-i,--ignore 如果插入记录有重复键,那么该条记录不处理
-r,--replace:如果插入记录发现重复,则覆盖旧记录
-L,--local 从执行mysqlimport命令的客户端本地读文件。
-l,--lock-tables:导入时锁定表
-S,--silent:静默模式导入
--ignore-lines= 跳过文件的前n行
--use-threads= 以并行方式加载
-d,--delete 导入数据前先删除旧数据。
--fields-enclosed-by=xx指定括住列值的符号
--fields-optionally-enclosed-by=xxx  指定列限定符,如果没有限定符就忽略。

假设下面数据:
100007#“胡#万万”#“杭州”#“huwan@51enet.com”
该数据用户名中间用一个#符号,可以用下面命令进行导入:
mysqlimport -uroot -p123456 -h192.168.11.31 test --default-character-set=utf8 --fields-terminated-by='#'
--fields-enclosed-by=\" /tmp/ld_csv1

当一条记录包含在多行时,以三个#号结束一条记录:
100006#"胡万"#"杭
州"#"huwan@51enet.com"###
100007#"胡#
万万"#"杭州"#"huwan@51enet.com"###

可以使用:
--lines-terminated-by= 指定文中记录行的结束符,默认是换行符号,命令如下:
[root@localhost tmp]# mysqlimport -uroot -p123456 -h192.168.11.31 -d test --default-character-set=utf8 --fields-terminated-by='#' --fields-enclosed-by=\" --lines-terminated-by='###\n' /tmp/ld_csv1

mysqlimport仅仅使用数据比较规范的导入。


load data infile

语法:
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,...]
    
利用之前的数据,创建下面表:


create table ldsql(
id int(11) default 0,
username varchar(5),
city varchar(6),
email varchar(50)) engine=innodb charset=utf8;

[root@localhost tmp]# cat cvs2
100001#"胡一"#"北京"#"huyi@51enet.com"
100002#"胡二"#"上海"#"huer@51enet.com"
100003#"胡十"#"广州"#"hubai@51enet.com"
100004#"胡百"#"深圳"#"hushi@51enet.com"
100005#"胡千"#"珠海"#"huqian@51enet.com"
100006#"胡万"#"杭州"#"huwan@51enet.com"
100007#"胡#万万"#"杭州"#"huwan@51enet.com"

mysql> load data infile '/tmp/cvs2' into table ldsql charset utf8 FIELDS TERMINATED BY '#';

数据成功导入.
FIELDS OPTIONALLY ENCLOSED BY,LINES TERMINATED BY等处理语法都与mysqlimport相同。

处理数据文件前N行记录不导入:
可以使用[IGNORE number LINES]语法。
mysql> truncate ldsql;
Query OK, 0 rows affected (0.01 sec)
mysql> load data LOCAL infile '/tmp/cvs2' into table ldsql charset utf8 FIELDS TERMINATED BY '#' IGNORE 2 LINES;

处理行列问题,有以下数据:

Junsansi"abc",1
WHere is Junsansi"def",2
"ghi",3
导入数据过程中过滤Junsansi字符,创建下面测试表:
create table test_starting(v1 varchar(20),id int);

LOAD DATA INFILE '/tmp/test_starting' INTO TABLE test_starting FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY "Junsansi";
LINES STARTING BY 'prefix_string' 希望跳过包含指定字符的前缀及前缀之前的所有字符。

有一个csv格式文件,内容如下:
1,192.168.11.25,Centos5.4,serverGroup1-2,0.11
2,192.168.11.79,WINDOWS SERVER 2003,serverGroup2-3,0.14
3,192.168.11.31,Centos57,serverGroup3-4,0.15
创建表:

create table ld_t1(id int not null auto_increment primary key,
ip varchar(15),
os varchar(20),
prcname varchar(20),
res varchar(10)) charset utf8;
导入语句:
load data infile '/tmp/ld_t1' into table ld_t1 FIELDS TERMINATED BY ',' IGNORE 1 LINES;
对于FIELDS TERMINATED BY还可以指定非逗号的符号。
ENCLOSED BY用于指定列的包括符号,例子:
load data infile '/tmp/ldtest2' into table ld_t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
如果在ENCLOSED BY指定的包括符存在于列的内容中怎么办?例如:
14,"192.168.11.31","what os:"bsd"","serverGroup3-4",0.15
可以对需要转义的“号增加转义符号,变成下面内容:
14,"192.168.11.31","what os:#"bsd#"","serverGroup3-4",0.15
指定转义符号后将不会再把#”的符号与ENCLOSED BY混淆,然后使用ESCAPED BY 来指定转义字符:
load data infile '/tmp/ldtest3' into table ld_t1 FIELDS TERMINATED BY ',' ENCLOSSED BY '"' ESCAPED BY '#';
LINE TERMINATED BY指定换行符,例如:LINES TERMINATED BY'\r\n'指定换行符是windows的换行符号。

处理表结构与文件数据不符
建立下面的表:

mysql> create table test2 (id int(11),
    -> uname varchar(50),
    -> age tinyint(4),
    -> email varchar(50),
    -> status tinyint(4));
然后处理表中的列比文件多,建立下面的文件数据:
100,"zhangsan","zhangsan@qq.com",23
102,"lisi","lisi@qq.com",26
103,"wangwu","wanwu@qq.com",29
执行下面的load data指定导入的字段:
LOAD DATA INFILE '/tmp/test2' INTO TABLE test2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' (id,uname,email,age);
现在处理表的列比文件少,建立下面的数据文件:
10,"zhangsan",26,"zhangsan@qq.com","男",130000001,0
11,"wangwu",36,"wangwu@qq.com","女",130000002,21
12,"lisi",46,"lisi@qq.com","男",1330000003,23
导入到刚才的表中可以输入下面语句:
load data infile '/tmp/test3' into table test2 FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(id,uname,age,email,@tmp,@tmp,status);
也可以根据条件设置状态,比如表有sex字段为整数,0为男,1为女。
LOAD DATA INFILE '/tmp/test3'
INTO TABLE test2
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(id,name,age,email,@tmp_sex,@tmp_tel,status)
set sex=if(@tmp_sex='男',0,1);

利用刚刚的test2表,增加一列sex:
alter table test2 add sex int(4) unsigned not null;
数据文件内容如下:
210,"zhangsan",26,"zhangsan@qq.com","男",130000001,0
211,"wangwu",36,"wangwu@qq.com","女",130000002,21
212,"lisi",46,"lisi@qq.com","男",1330000003,23

最后是导出语句select into outfile,大部分命令与load data或者mysqlimport一样,所以直接用例子说明:
导出test2表,并且带有字段名:
select "id" ,"uname","age","email","status","sex"
 union all  
 select * from test2
 into outfile '/tmp/outtest2'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';