作者:beitianmolang | 来源:互联网 | 2023-10-11 19:46
一、将CSV文件导入MySQL表这里主要用到的是LOADDATAINFILE语句在导入文件操作之前,需要准备以下内容:将要导入文件的数据对应的数据库表。准备好一个CSV文件,其数据
一、将CSV文件导入MySQL表
这里主要用到的是LOAD DATA INFILE
语句
在导入文件操作之前,需要准备以下内容:
- 将要导入文件的数据对应的数据库表。
- 准备好一个CSV文件,其数据与表的列数和每列中的数据类型相匹配。
- 连接到MySQL数据库服务器的帐户具有
FILE
和INSERT
权限。
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数据库服务器的帐户具有FILE
和INSERT
权限。
这里要把csv文件放到有file权限的目录,否则会报错没有权限
根据报错提示找到这个有secure_file_priv权限的目录
show global variables like '%secure_file_priv%';
然后把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;
执行结果:
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';
执行结果:
导出700多万行只用了7s左右;
该sql语句表示,在/data/datas/目录下创建一个名称为train.csv的CSV文件。
CSV文件包含结果集中的行集合。
每行由一个回车序列和由LINES TERMINATED BY ‘\r\n’子句指定的换行字符终止。
文件中的每行包含表的结果集的每一行记录。
每个值由FIELDS ENCLOSED BY ‘”‘子句指示的双引号括起来。 这样可以防止可能包含逗号(,)的值被解释为字段分隔符。 当用双引号括住这些值时,该值中的逗号不会被识别为字段分隔符。
参考链接:
将CSV文件导入MySQL表 – MySQL教程™ www.yiibai.com
MySQL将表导出为CSV – MySQL教程™ www.yiibai.com
mysql导出导入文件问题整理 www.jianshu.com