csv导入mysql_CSV文件与MySQL表的导入、导出

一、将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:windowstemp或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 'rn';

执行结果：

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

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

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

每行由一个回车序列和由LINES TERMINATED BY 'rn'子句指定的换行字符终止。

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

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

可能会遇到的问题：

1、1261 - Row 4855735 doesn't contain data for all columns

或者

Row 1 doesn't contain data for all columns

解决方法：

show variables like 'sql_mode';
set sql_mode='';

参考链接：

将CSV文件导入MySQL表 - MySQL教程™​www.yiibai.com

MySQL将表导出为CSV - MySQL教程™​www.yiibai.com mysql导出导入文件问题整理​www.jianshu.com