將CSV檔案匯入MySQL表

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

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

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

假設我們有一個名為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將檔案從用戶端匯入到遠端資料庫伺服器時，有一些安全問題應該要注意，以避免潛在的安全風險。