MySQL 导入CSV文件到表

MySQL 具有允许我们将 CSV 文件导入数据库或表的功能。CSV 代表逗号分隔值，它是一个纯文本文件，其中包含数据列表并允许我们以表格格式保存它。它主要用于在不同应用程序之间交换数据。有时也称为字符分隔值。通常，它与逗号字符一起使用来分隔（分隔）数据，但也可以使用其他字符，例如分号。

当我们需要将复杂数据从一个应用程序导出为 CSV 文件，然后将文件数据导入另一个应用程序时，此文件非常有用。我们可以将 CSV 文件与各种电子表格程序（如MS Excel或 Google 电子表格）一起使用。CSV 文件不允许以这种格式保存公式。

一、CSV 文件的结构

CSV 文件的结构包含由逗号分隔的数据列表。例如，下面的数据结构更清楚地解释了它：

Name, Email, Mobile, Address  
  
Jorge Stephen, jorge@javatpoint.com, 546-329-9876, 123 Park Street  
  
Peter, peter@javatpoint.com, 123-786-5678, 321 Fake Avenue

要导入 CSV 文件，我们将使用 LOAD DATA INFILE 语句。该语句用于读取文本文件并将其快速导入数据库表。

在将文件导入数据库表之前，我们必须确保以下事项：

将导入文件中数据的空表。
一个 CSV 文件，它与表的每一列中的顺序、列数和数据类型相匹配。
MySQL 中与数据库服务器连接的用户帐户具有 FILE 和 INSERT 权限。

假设我们有一个名为mytestdb的数据库。现在，我们将使用以下查询在此数据库中创建一个名为Address_Book的空表：

CREATE TABLE Address_Book (  
    ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,   
    Name varchar(35) NOT NULL,   
    Email varchar(35),  
    Mobile varchar(15),   
    Address varchar(45)  
);

现在，我们可以使用下面输出中显示的SELECT语句来验证新创建的表是否为空：

接下来，我们将创建一个 CSV 文件。下面的address_book.csv文件包含第一行作为列标题，其他行是要插入到表中的数据。

ID, Name, Email, Mobile, Address  
1, "Jorge Stephen", "jorge@javatpoint.com", "546-329-9876", "123 Park Street"  
2, "Peter", "peter@javatpoint.com", "123-786-5678", "321 Fake Avenue"  
3, "Michael Clark", "clark@javatpoint.com", "543-346-5988", "321 Park Avenue"  
4, "James Franklin", "james@javatpoint.com", "890-798-5448", "321 Fake Avenue"

最后，以下语句允许我们将数据从 address_book.csv 导入到 address_book 表。

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/address_book.csv'   
INTO TABLE address_book   
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\r\n'   
IGNORE 1 ROWS;

参数说明

File Location：它指定 CSV 文件在系统中的位置。

FIELD TERMINATED BY ','：用于表示文件的字段，以逗号结束。

ENCLOSED BY '"'：用于指定用双引号括起来的文件的字段。

LINES TERMINATED BY '\r\n'：用于终止 CSV 文件的每一行。

IGNORE 1 ROWS：用于忽略 CSV 文件的第一行，其中包含不需要导入表的列标题。

我们现在可以使用下面输出中显示的 SELECT 语句来验证新创建的表是否填充了导入的数据。

二、导入时转换数据

有时，将 CSV 文件导入数据库表时，数据格式与表中的对应列不匹配。在这种情况下，我们可以使用带有 LOAD DATA INFILE 语句的SET 子句来转换该列。

假设 CSV 文件的日期列格式为"dd/mm/yy"，并且在导入时与目标表的对应列不匹配。为了克服这个问题，我们需要使用STR_TO_DATE()函数将其转换为 MySQL 日期格式，如下所示：

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/address_book.csv'   
INTO TABLE address_book   
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\r\n'   
IGNORE 1 ROWS  
(Mobile,@Date,Address)  
SET Date = STR_TO_DATE(@Date, '%m/%d/%Y');

三、将 CSV 文件从客户端导入 MySQL 服务器

LOAD DATA INFILE 语句还允许我们将 CSV 文件从客户端（本地系统）导入到远程MySQL 服务器。

当我们在 LOAD DATA INFILE 语句中添加LOCAL子句时，客户端程序可以读取本地系统上的 CSV 文件并将其发送到 MySQL 数据库服务器。然后，此文件将上传到数据库服务器操作系统的临时文件夹，例如 Windows 上的 C:\windows \temp和 /tmp 在Linux 操作系统上。这些文件夹不是由MySQL确定或配置的。

以下语句更清楚地解释了它：

LOAD DATA LOCAL INFILE 'C:/windows/temp/address_book.csv'   
INTO TABLE address_book   
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\r\n'   
IGNORE 1 ROWS;

在上面，我们可以看到我们只在语句中添加了 LOCAL 选项。但是在使用 LOCAL 选项导入大型 CSV 文件时，会使文件加载速度稍慢一些。这是因为此选项在数据库服务器上传输文件需要时间。

当我们使用 LOCAL 选项时，MySQL 用户帐户不需要具有 FILE 权限来导入它。

将文件从本地导入到远程服务器时，LOAD DATA LOCAL INFILE 语句存在一些安全问题。在这种情况下，我们应该注意避免潜在的安全风险。