CSV文件導(dǎo)入MySQL

LOAD DATA INFILE語(yǔ)句允許您從文本文件讀取數(shù)據(jù)，并將文件的數(shù)據(jù)快速導(dǎo)入數(shù)據(jù)庫(kù)的表中。

導(dǎo)入文件操作之前，需要準(zhǔn)備以下內(nèi)容：

一、將要導(dǎo)入文件的數(shù)據(jù)對(duì)應(yīng)的數(shù)據(jù)庫(kù)表。

二、準(zhǔn)備好一個(gè)CSV文件，其數(shù)據(jù)與表的列數(shù)和每列中的數(shù)據(jù)類(lèi)型相匹配。

三、連接到MySQL數(shù)據(jù)庫(kù)服務(wù)器的帳戶具有FILE和INSERT權(quán)限。

首先，創(chuàng)建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文件的內(nèi)容，第一行作為列標(biāo)題和其他三行則為數(shù)據(jù)。

id,title,expired date,amout1,"Spring Break 2018",20180401,20

2,"Back to Scholl 2017",20170901,29

3,"Summer 2018",20180820,100

接下來(lái)，將數(shù)據(jù)從F:/worksp/mysql/discounts.csv文件導(dǎo)入到discounts表。

LOAD DATA INFILE 'F:/worksp/mysql/discounts.csv'INTO TABLE discounts

FIELDS TERMINATED BY','ENCLOSED BY'"'LINES TERMINATED BY'\n'IGNORE1 ROWS;

文件的字段由FIELD TERMINATED BY ','指示的逗號(hào)終止，并由ENCLOSED BY '"'指定的雙引號(hào)括起來(lái)。

因?yàn)槲募谝恍邪袠?biāo)題，列標(biāo)題不需要導(dǎo)入到表中，因此通過(guò)指定IGNORE 1 ROWS選項(xiàng)來(lái)忽略第一行。

導(dǎo)入時(shí)轉(zhuǎn)換數(shù)據(jù)

數(shù)據(jù)格式與表中的目標(biāo)列不匹配，用LOAD DATA INFILE語(yǔ)句中的SET子句進(jìn)行轉(zhuǎn)換。

有一個(gè)discount_2.csv文件中，它存儲(chǔ)的過(guò)期日期列是mm/dd/yyyy格式。內(nèi)容如下：

id,title,expired date,amout4,"Item-4","01/04/2018",200

5,"Item-5","01/09/2017",290

6,"Item-6","12/08/2018",122

將數(shù)據(jù)導(dǎo)入discounts表時(shí)，必須使用str_to_date()函數(shù)將其轉(zhuǎn)換為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');

將文件從客戶端導(dǎo)入遠(yuǎn)程MySQL數(shù)據(jù)庫(kù)服務(wù)器

使用LOAD DATA INFILE語(yǔ)句將數(shù)據(jù)從客戶端(本地計(jì)算機(jī))導(dǎo)入遠(yuǎn)程MySQL數(shù)據(jù)庫(kù)服務(wù)器。

LOAD DATA INFILE中使用LOCAL選項(xiàng)時(shí)，客戶端程序會(huì)讀取客戶端上的文件并將其發(fā)送到MySQL服務(wù)器。該文件將被上傳到數(shù)據(jù)庫(kù)服務(wù)器操作系統(tǒng)的臨時(shí)文件夾，

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'IGNORE1 ROWS;

如果加載一個(gè)大的CSV文件，將會(huì)看到使用LOCAL選項(xiàng)來(lái)加載該文件將會(huì)稍微慢些，因?yàn)樾枰獣r(shí)間將文件傳輸?shù)綌?shù)據(jù)庫(kù)服務(wù)器。

使用LOCAL選項(xiàng)時(shí)，連接到MySQL服務(wù)器的帳戶不需要具有FILE權(quán)限來(lái)導(dǎo)入文件。

MySQL導(dǎo)出CSV文件

導(dǎo)出數(shù)據(jù)之前，必須確保：

一、MySQL服務(wù)器的進(jìn)程對(duì)包含目標(biāo)CSV文件的目標(biāo)文件夾具有寫(xiě)訪問(wèn)權(quán)限。

二、要導(dǎo)出的目標(biāo)CSV文件不能存在。

示例：

查詢(xún)從orders表中查詢(xún)選擇已取消的訂單，將此結(jié)果集導(dǎo)出為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';

該語(yǔ)句在F:/worksp/mysql/目錄下創(chuàng)建一個(gè)包含結(jié)果集，名稱(chēng)為cancelled_orders.csv的CSV文件。

CSV文件包含結(jié)果集中的行集合。每行由一個(gè)回車(chē)序列和由LINES TERMINATED BY '\r\n'子句指定的換行字符終止。文件中的每行包含表的結(jié)果集的每一行記錄。

每個(gè)值由FIELDS ENCLOSED BY '"'子句指示的雙引號(hào)括起來(lái)。 這樣可以防止可能包含逗號(hào)(，)的值被解釋為字段分隔符。 當(dāng)用雙引號(hào)括住這些值時(shí)，該值中的逗號(hào)不會(huì)被識(shí)別為字段分隔符。

將數(shù)據(jù)導(dǎo)出到文件名包含時(shí)間戳的CSV文件

將數(shù)據(jù)導(dǎo)出到CSV文件中，該文件的名稱(chēng)包含創(chuàng)建文件的時(shí)間戳。

將整個(gè)orders表導(dǎo)出為將時(shí)間戳作為文件名的一部分的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;

首先，構(gòu)造了一個(gè)具有當(dāng)前時(shí)間戳的查詢(xún)作為文件名的一部分。其次，使用PREPARE語(yǔ)句FROM命令準(zhǔn)備執(zhí)行語(yǔ)句。

最后，使用EXECUTE命令執(zhí)行語(yǔ)句。

使用列標(biāo)題導(dǎo)出數(shù)據(jù)

要添加列標(biāo)題，需要使用UNION語(yǔ)句如下：

(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值

如果結(jié)果集中的值包含NULL值，則目標(biāo)文件將使用“N/A”來(lái)代替數(shù)據(jù)中的NULL值。要解決此問(wèn)題，您需要將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值。

值替換為另一個(gè)值，例如不適用(N/A)，方法是使用IFNULL函數(shù)，如下：

總結(jié)

以上是生活随笔為你收集整理的mysql 导入文件夹_MySQL-导入与导出的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。