深入分析csv数据导入mysql的法子_php才具_脚本之家

2020-01-24 07:53栏目:bob体育平台
TAG:

mysql自己有个csv引擎,可以通过这个引擎来实现将csv中的数据导入到mysql数据库中,并且速度比通过php或是python写的批处理程序快的多。具体的实现代码示例:复制代码 代码如下:load data infile '/tmp/file.csv' into table _tablename fields terminated by ','enclosed by '"'lines terminated by 'rn'; 这段代码中涉及的一些关键字的解释如下:fields terminated by '':这是指出csv文件中字段终止符,也就是数据之间的分隔符;enclosed by '':指出封套符;lines terminated by '':指行终止符在csv文档中详细介绍了csv的格式,其中的要点有:间隔,数据行之间使用rn分隔;字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。通过以上的解释,详细对于数据导入代码应该有更好的理解了。同样的,csv数据能够导入mysql数据库中,mysql中的数据表也能导出csv文件,导出的代码示例:复制代码 代码如下:select * from tablename into outfile '/tmp/data.txt' fields terminated by ','optionally enclosed by '"'lines terminated by 'n'; 当将数据库中的数据导出到文件后,要再将数据导入到数据库中,必须遵守导出时的文件中定义的格式。

MySQL中导出CSV格式数据的SQL语句样本如下:

Sql代码 
select * from test_info   
into outfile '/tmp/test.csv'   
fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by 'rn';  

select * from test_info
into outfile '/tmp/test.csv'
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by 'rn'; MySQL中导入CSV格式数据的SQL语句样本如下:

Sql代码 
load data infile '/tmp/test.csv'   
into table test_info    
fields terminated by ','  optionally enclosed by '"' escaped by '"'   
lines terminated by 'rn';  

load data infile '/tmp/test.csv'
into table test_info 
fields terminated by ','  optionally enclosed by '"' escaped by '"'
lines terminated by 'rn'; 里面最关键的部分就是格式参数

Sql代码 
fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by 'rn'  

fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by 'rn' 这个参数是根据RFC4180文档设置的,该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files,其中详细描述了CSV格式,其要点包括:

(1)字段之间以逗号分隔,数据行之间以rn分隔;

(2)字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。

 

文件:test_csv.sql

Sql代码 
use test;  
 
create table test_info (  
    id  integer not null,  
    content varchar(64) not null,  
    primary key (id)  
);  
 
delete from test_info;  
 
insert into test_info values (2010, 'hello, line  
suped  
seped  
"  
end'  
);  
 
select * from test_info;  
 
select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by 'rn';  
 
delete from test_info;  
 
load data infile '/tmp/test.csv' into table test_info  fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by 'rn';  
 
select * from test_info;       

use test;

create table test_info (
id integer not null,
content varchar(64) not null,
primary key (id)
);

delete from test_info;

insert into test_info values (2010, 'hello, line
suped
seped
"
end'
);

select * from test_info;

select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by 'rn';

delete from test_info;

load data infile '/tmp/test.csv' into table test_info  fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by 'rn';

select * from test_info;

 

文件:test.csv

Text代码 
2010,"hello, line  
suped  
seped  
"" 
end" 

2010,"hello, line
suped
seped
""
end"

版权声明:本文由bob体育app发布于bob体育平台,转载请注明出处:深入分析csv数据导入mysql的法子_php才具_脚本之家