將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將檔從客戶端導入到遠程資料庫伺服器時，有一些安全問題應該要注意，以避免潛在的安全風險。