MySQL-导入与导出

CSV文件导入MySQL

LOAD DATA INFILE语句允许您从文本文件读取数据，并将文件的数据快速导入数据库的表中。

导入文件操作之前，需要准备以下内容：

　　一、将要导入文件的数据对应的数据库表。

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

　　三、连接到MySQL数据库服务器的帐户具有FILE和INSERT权限。

首先，创建discounts表：

use testdb;
CREATE TABLE discounts (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL,
    amount DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);

discounts.csv文件的内容，第一行作为列标题和其他三行则为数据。

id,title,expired date,amout
1,"Spring Break 2018",20180401,20
2,"Back to Scholl 2017",20170901,29
3,"Summer 2018",20180820,100

接下来，将数据从F:/worksp/mysql/discounts.csv文件导入到discounts表。

LOAD DATA INFILE 'F:/worksp/mysql/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

文件的字段由FIELD TERMINATED BY ','指示的逗号终止，并由ENCLOSED BY '"'指定的双引号括起来。

因为文件第一行包含列标题，列标题不需要导入到表中，因此通过指定IGNORE 1 ROWS选项来忽略第一行。

导入时转换数据

数据格式与表中的目标列不匹配，用LOAD DATA INFILE语句中的SET子句进行转换。

有一个discount_2.csv文件中，它存储的过期日期列是mm/dd/yyyy格式。内容如下：

id,title,expired date,amout
4,"Item-4","01/04/2018",200
5,"Item-5","01/09/2017",290
6,"Item-6","12/08/2018",122

将数据导入discounts表时，必须使用str_to_date()函数将其转换为MySQL日期格式

LOAD DATA INFILE 'F:/worksp/mysql/discounts_2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id,title,@expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');

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

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

LOAD DATA INFILE中使用LOCAL选项时，客户端程序会读取客户端上的文件并将其发送到MySQL服务器。该文件将被上传到数据库服务器操作系统的临时文件夹，

Windows上的C:\windows\temp,此文件夹不可由MySQL配置或确定。

示例：
LOAD DATA LOCAL INFILE  'c:/tmp/discounts.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

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

使用LOCAL选项时，连接到MySQL服务器的帐户不需要具有FILE权限来导入文件。

 

MySQL导出CSV文件

导出数据之前，必须确保：

一、MySQL服务器的进程对包含目标CSV文件的目标文件夹具有写访问权限。

二、要导出的目标CSV文件不能存在。

示例：

查询从orders表中查询选择已取消的订单，将此结果集导出为CSV文件 
SELECT
    orderNumber, status, orderDate, requiredDate, comments 
FROM 
    orders 
WHERE
    status = 'Cancelled' 

INTO OUTFILE 'F:/worksp/mysql/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

  该语句在F:/worksp/mysql/目录下创建一个包含结果集，名称为cancelled_orders.csv的CSV文件。

  CSV文件包含结果集中的行集合。每行由一个回车序列和由LINES TERMINATED BY '\r\n'子句指定的换行字符终止。文件中的每行包含表的结果集的每一行记录。

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

将数据导出到文件名包含时间戳的CSV文件

将数据导出到CSV文件中，该文件的名称包含创建文件的时间戳。

将整个orders表导出为将时间戳作为文件名的一部分的CSV文件。

SET @TS = DATE_FORMAT(NOW(),'_%Y%m%d_%H%i%s');

SET @FOLDER = 'F:/worksp/mysql/';
SET @PREFIX = 'orders';
SET @EXT    = '.csv';

SET @CMD = CONCAT("SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
    "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
    "  LINES TERMINATED BY '\r\n';");

PREPARE statement FROM @CMD;

EXECUTE statement;

首先，构造了一个具有当前时间戳的查询作为文件名的一部分。

其次，使用PREPARE语句FROM命令准备执行语句。

最后，使用EXECUTE命令执行语句。

使用列标题导出数据

要添加列标题，需要使用UNION语句如下：

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'F:/worksp/mysql/orders_union_title.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

处理NULL值

如果结果集中的值包含NULL值，则目标文件将使用“N/A”来代替数据中的NULL值。要解决此问题，您需要将NULL

SELECT 
    orderNumber, orderDate, IFNULL(shippedDate, 'N/A')
FROM
    orders INTO OUTFILE 'F:/worksp/mysql/orders_null2na.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n';

用N/A字符串替换了shippingDate列中的NULL值。 CSV文件将显示N/A而不是NULL值。

 

值替换为另一个值，例如不适用(N/A)，方法是使用IFNULL函数，如下：