导入 CSV 文件到 MySQL Table

---

本教程介绍如何使用  LOAD DATA INFILE语句将CSV文件导入MySQL表。 LOAD DATA INFILE语句允许您从文本文件中读取数据，并将文件的数据快速导入数据库表。 在导入文件之前，您需要准备以下内容：

• 准备好要导入文件数据的数据库表。
• CSV文件中的数据与表的列数和每列中的数据类型相匹配。
• 帐户具有FILE和INSERT权限。

假设我们有一个以discounts下列结构命名的表： 我们使用CREATE TABLE语句创建discounts表，如下所示：

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,amount
1,"Spring Break 2014",20140410,20
2,"Back to School 2014",20140901,25
3,"Summer 2014",20140825,10

以下语句将c:\tmp\discounts.csv文件中的数据   导入discounts表中。

LOAD DATA INFILE '/Users/rick/Downloads/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

文件的字段由逗号表示，  FIELD TERMINATED BY ','并用ENCLOSED BY '"'' 指定的双引号括起来。 CSV文件的每一行都由指示的换行符终止LINES TERMINATED BY '\n'。 因为文件具有包含列标题的第一行，不应将其导入表中，因此我们通过指定IGNORE 1 ROWS选项忽略它  。 如果在执行的过程中出现以下错误：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

我们可以使用如下命令查看配置：

mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

我这里这个参数的值是null，说明mysql中就没有设置secure_file_priv这个选项。如果这个参数是个特定的文件路径，就说明文件只有在这个路径下才能将它导入导出到mysql。参数secure_file_priv的值及含义如下：

secure_file_priv值	含义
NULL	禁止文件的导入导出
‘’	（空字符串）允许所有文件的导入导出
一个特定的路径地址	只有该路径地址下的文件可以导入导出到mysql

解决此问题我们可以通过修改 my.cnf 配置文件：

#写在 my.ini 配置文件中
#允许导入文件
secure_file_priv=''

然后重启数据库

mysql> SHOW GLOBAL VARIABLES LIKE 'secure_%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_auth      | ON    |
| secure_file_priv |       |
+------------------+-------+
2 rows in set (0.00 sec)

现在我们再导入一次：

现在，我们可以检查discounts表以查看是否导入了数据。

SELECT * FROM discounts;

+----+---------------------+--------------+--------+
| id | title               | expired_date | amount |
+----+---------------------+--------------+--------+
|  1 | Spring Break 2014   | 2014-04-10   |  20.00 |
|  2 | Back to School 2014 | 2014-09-01   |  25.00 |
|  3 | Summer 2014         | 2014-08-25   |  10.00 |
+----+---------------------+--------------+--------+
3 rows in set (0.00 sec)

导入时转换数据

有时，数据的格式与表中的目标列不匹配。在简单的情况下，您可以使用SET语句中的子句  对其进行转换LOAD DATA INFILE。 假设  discount_2.csv文件的到期日期列是  mm/dd/yyyy格式。

id,title,expired_date,amount
1,"Spring Break 2014",1/4/2014,20
2,"Back to School 2014",1/9/2014,25
3,"Summer 2014",25/8/2014,10

将数据导入discounts表时，我们使用str_to_date() 函数将其转换为MySQL日期格式，如下所示：

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

---

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

可以使用LOAD DATA INFILE语句将数据从客户端（本地计算机）导入到远程MySQL数据库服务器。 当您使用  LOCAL选项时  LOAD DATA INFILE，客户端程序将读取客户端上的文件并将其发送到MySQL服务器。文件将上载到数据库服务器操作系统的临时文件夹中，例如，  C:\windows\temp在Windows或  /tmpLinux上。此文件夹不可由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一些安全问题将文件从客户端导入到远程数据库服务器  以避免潜在的安全风险。 我们已经向您展示了如何 LOAD DATA LOCAL使用MySQL Workbench 将CSV导入MySQL表。使用这些技术，您可以从其他文本文件格式（如制表符分隔）加载数据。

---