热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

CSV文件与MySQL表的导入、导出

一、将CSV文件导入MySQL表这里主要用到的是LOADDATAINFILE语句在导入文件操作之前,需要准备以下内容:将要导入文件的数据对应的数据库表。准备好一个CSV文件,其数据

一、将CSV文件导入MySQL表

这里主要用到的是LOAD DATA INFILE语句

在导入文件操作之前,需要准备以下内容:

  • 将要导入文件的数据对应的数据库表。
  • 准备好一个CSV文件,其数据与表的列数和每列中的数据类型相匹配。
  • 连接到MySQL数据库服务器的帐户具有FILEINSERT权限。

1、本地导入(这里是centos7)

1)将要导入文件的数据对应的数据库表。

新建一个名为train的表;

use tmp;
CREATE TABLE `train` (
`user_id` varchar(255) DEFAULT NULL,
`age_range` varchar(255) DEFAULT NULL,
`gender` varchar(255) DEFAULT NULL,
`merchant_id` varchar(255) DEFAULT NULL,
`label` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2)准备好一个CSV文件,其数据与表的列数和每列中的数据类型相匹配。

以下是train.csv文件的内容,第一行作为列标题和后面四行则为数据。

user_id,age_range,gender,merchant_id,label
34176,6,0,944,-1
34176,6,0,412,-1
34176,6,0,1945,-1
34176,6,0,4752,-1

3)连接到MySQL数据库服务器的帐户具有FILEINSERT权限。

这里要把csv文件放到有file权限的目录,否则会报错没有权限

《CSV文件与MySQL表的导入、导出》
《CSV文件与MySQL表的导入、导出》

根据报错提示找到这个有secure_file_priv权限的目录

show global variables like '%secure_file_priv%';

《CSV文件与MySQL表的导入、导出》
《CSV文件与MySQL表的导入、导出》

然后把csv文件复制到这个目录下:

cp train.csv /var/lib/mysql-files/

然后就可以执行导入:

LOAD DATA INFILE '/var/lib/mysql-files/train.csv'
INTO TABLE train
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

执行结果:

《CSV文件与MySQL表的导入、导出》
《CSV文件与MySQL表的导入、导出》

700多万行的数据只用了41s,效果还是可以接受;

2、将文件从客户端导入远程MySQL数据库服务器

可以使用LOAD DATA INFILE语句将数据从客户端(本地计算机)导入远程MySQL数据库服务器。

当您在LOAD DATA INFILE中使用LOCAL选项时,客户端程序会读取客户端上的文件并将其发送到MySQL服务器。该文件将被上传到数据库服务器操作系统的临时文件夹,例如Windows上的C:\windows\temp或Linux上为/tmp目录。 此文件夹不可由MySQL配置或确定。

导入代码:

LOAD DATA LOCAL INFILE '/var/lib/mysql-files/train.csv'
INTO TABLE train
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

唯一的区别是语句中多了个LOCAL选项。

如果加载一个大的CSV文件,将会看到使用LOCAL选项来加载该文件将会稍微慢些,因为需要时间将文件传输到数据库服务器。

使用LOCAL选项时,连接到MySQL服务器的帐户不需要具有FILE权限来导入文件。但是使用LOAD DATA LOCAL将文件从客户端导入到远程数据库服务器时,有一些安全问题应该要注意,以避免潜在的安全风险。

二、从MySQL表导出csv文件

1、

在导出数据之前,必须确保:

  • MySQL服务器的进程对包含目标CSV文件的目标文件夹具有写访问权限。
  • 要导出的目标CSV文件不能存在。

1)要导出的数据查询结果:

SELECT * FROM train;

我这里是取了该表所有的字段;

2)将上述查询结果集导出为CSV文件

SELECT * FROM train
INTO OUTFILE '/var/lib/mysql-files/train.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

执行结果:

《CSV文件与MySQL表的导入、导出》
《CSV文件与MySQL表的导入、导出》

导出700多万行只用了7s左右;

该sql语句表示,在/data/datas/目录下创建一个名称为train.csv的CSV文件。

CSV文件包含结果集中的行集合。

每行由一个回车序列和由LINES TERMINATED BY ‘\r\n’子句指定的换行字符终止。

文件中的每行包含表的结果集的每一行记录。

每个值由FIELDS ENCLOSED BY ‘”‘子句指示的双引号括起来。 这样可以防止可能包含逗号(,)的值被解释为字段分隔符。 当用双引号括住这些值时,该值中的逗号不会被识别为字段分隔符。

参考链接:

将CSV文件导入MySQL表 – MySQL教程™www.yiibai.com《CSV文件与MySQL表的导入、导出》
MySQL将表导出为CSV – MySQL教程™www.yiibai.com
mysql导出导入文件问题整理www.jianshu.com《CSV文件与MySQL表的导入、导出》


推荐阅读
author-avatar
beitianmolang
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有