csv导入mysql
load data infile 'C:\\Users\\UserName\\Desktop\\test.csv'
into table `table`
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n'
mysql导入csv
select * from `table`
load data infile 'C:\\Users\\UserName\\Desktop\\test.csv'
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n'
如果乱码,可用相关编辑器打开.csv文件,另存为utf-8的csv
导入语法如下:[plain] view plain copy print?
LOAD DATA INFILE '[FILE]'
INTO TABLE [TABLE];
或者
LOAD DATA INFILE '[FILE]'
INTO TABLE [TABLE]
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
下边是一个具体的导入的例子:
[plain] view plain copy print?
LOAD DATA INFILE '/tmp/mytable.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
导出语法如下:
[plain] view plain copy print?
SELECT * FROM [TABLE]
INTO OUTFILE '[FILE]';
或者
SELECT * FROM [TABLE]
INTO OUTFILE '[FILE]'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
下边是一个具体的导出的例子:
[plain] view plain copy print?
SELECT * FROM mytable
INTO OUTFILE '/tmp/mytable.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
mysql导出数据库到csv文件的方法:1、通过mysql客户端shell连接到服务器,选择使用的数据库,输入sql代码:
select * from test_info
into outfile '/tmp/test.csv'
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)字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。
通过执行上述代码,便可以将需要的数据以csv格式导出到执行的文件中。
2、另外,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'
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)