將CSV檔案匯入MySQL表

2019-10-16 22:58:51

本教學您學習如何使用LOAD DATA INFILE語句將CSV檔案匯入到MySQL表中。
LOAD DATA INFILE語句允許您從文字檔案讀取資料,並將檔案的資料快速匯入資料庫的表中。

在匯入檔案操作之前,需要準備以下內容:

  • 將要匯入檔案的資料對應的資料庫表。
  • 準備好一個CSV檔案,其資料與表的列數和每列中的資料型別相匹配。
  • 連線到MySQL資料庫伺服器的帳戶具有FILEINSERT許可權。

假設我們有一個名為discounts表,具有以下結構:

接下來,使用CREATE TABLE語句建立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選項來忽略第一行。

現在,我們可以檢視discounts表中的資料,檢視是否成功匯入了資料。

SELECT * FROM discounts;

執行上面查詢語句,得到以下結果 -

mysql> SELECT * FROM discounts;
+----+---------------------+--------------+--------+
| id | title               | expired_date | amount |
+----+---------------------+--------------+--------+
|  1 | Spring Break 2018   | 2018-04-01   | 20     |
|  2 | Back to Scholl 2017 | 2017-09-01   | 29     |
|  3 | Summer 2018         | 2018-08-20   | 100    |
+----+---------------------+--------------+--------+
3 rows in set

匯入時轉換資料

有時,資料格式與表中的目標列不匹配。在簡單的情況下,可以使用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> SELECT * FROM discounts;
+----+---------------------+--------------+--------+
| id | title               | expired_date | amount |
+----+---------------------+--------------+--------+
|  1 | Spring Break 2018   | 2018-04-01   | 20     |
|  2 | Back to Scholl 2017 | 2017-09-01   | 29     |
|  3 | Summer 2018         | 2018-08-20   | 100    |
|  4 | Item-4              | 2018-01-04   | 200    |
|  5 | Item-5              | 2017-01-09   | 290    |
|  6 | Item-6              | 2018-12-08   | 122    |
+----+---------------------+--------------+--------+
6 rows in set

將檔案從用戶端匯入遠端MySQL資料庫伺服器

可以使用LOAD DATA INFILE語句將資料從用戶端(本地計算機)匯入遠端MySQL資料庫伺服器。

當您在LOAD DATA INFILE中使用LOCAL選項時,用戶端程式會讀取用戶端上的檔案並將其傳送到MySQL伺服器。該檔案將被上傳到資料庫伺服器作業系統的臨時檔案夾,例如Windows上的C:\windows\temp或Linux上為/tmp目錄。 此檔案夾不可由MySQL組態或確定。

我們來看看下面的例子:

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

唯一的區別是語句中多了個LOCAL選項。如果載入一個大的CSV檔案,將會看到使用LOCAL選項來載入該檔案將會稍微慢些,因為需要時間將檔案傳輸到資料庫伺服器。

使用LOCAL選項時,連線到MySQL伺服器的帳戶不需要具有FILE許可權來匯入檔案。

使用LOAD DATA LOCAL將檔案從用戶端匯入到遠端資料庫伺服器時,有一些安全問題應該要注意,以避免潛在的安全風險。