MySQL中导出CSV格式数据的SQL语句样本如下:
FIELDS TERMINATED BY ---- 字段终止字符
FIELDS OPTIONALLY ENCLOSED BY ---- 封套符
LINES TERMINATED BY ---- 行终止符
MYSQL LOAD DATA INFILE命令可以把csv平面文件中的数据导入到数据库中。
linux下:
-
LOAD DATA INFILE '/home/test/dump/ip_location.csv'
-
INTO TABLE ip_location
-
CHARACTER SET utf8
- FIELDS TERMINATED BY ',' ENCLOSED BY '"';
--CHARACTER SET :mysql字符集,一定要加上,免去乱码麻烦
--INTO TABLE :导入到哪个表
--FIELDS TERMINATED BY :以什么作为分割符
-- ENCLOSED BY :被什么包围
windows:
-
LOAD DATA INFILE "d:/insert_data.csv"
-
REPLACE INTO TABLE DEMO
-
CHARACTER SET gb2312
-
FIELDS TERMINATED BY "," ENCLOSED BY ""
- LINES TERMINATED BY "\r\n";
--LINES TERMINATED BY:这个与linux不同,以什么作为一行的结尾。
- 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语句样本如下:
- 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';
里面最关键的部分就是格式参数
- 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
- 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