MySQL中导出CSV格式数据的SQL

来源:网络 责任编辑:栏目编辑 发表时间:2013-07-01 14:34 点击:

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

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

select * from test_info
into outfile '/tmp/test.csv'
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'; 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 '\r\n';  

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

Sql代码 
fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n'  

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

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

(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 '\r\n';  
 
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 '\r\n';  
 
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 '\r\n';

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 '\r\n';

select * from test_info;

 


文件:test.csv

Text代码 

    相关新闻>>

      发表评论
      请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
      用户名: 验证码:点击我更换图片
      最新评论 更多>>

      推荐热点

      • mysql-mmm
      • mysqldump命令——MySQL数据库备份还原
      • Oracle数据导入MySQL的快捷工具:MySQL Migration Toolkit
      • 简简单单储存过程——循环一个select结果集
      • MySQL数据库十大优化技巧
      • Mysql安装笔记
      • Mysql主主复制架构配置
      • Mysql的Procedure 参数为NULL问题分析
      • MySQL Stmt预处理提高效率问题的小研究
      网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
      Copyright © 2008-2015 计算机技术学习交流网. 版权所有

      豫ICP备11007008号-1