MySQL 导入CSV文件

我们如何在MySQL数据库或表中导入CSV文件？

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

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

CSV文件的结构

CSV文件的结构包含以逗号分隔的数据列表逗号运算符。例如，以下数据结构对其进行了更清晰的说明:

Name, Email, Mobile, Address
Jorge Stephen, jorge@lidihuo.com, 546-329-9876, 123 Park Street
Peter, peter@lidihuo.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@lidihuo.com", "546-329-9876", "123 Park Street"
2, "Peter", "peter@lidihuo.com", "123-786-5678", "321 Fake Avenue"
3, "Michael Clark", "clark@lidihuo.com", "543-346-5988", "321 Park Avenue"
4, "James Franklin", "james@lidihuo.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;

从上面开始，

文件位置: 它指定CSV文件在系统中的位置。

FIELD TERMINATED BY '，': 用于指示文件的字段，以逗号结尾。

ENCLOSED BY'"':

以'\ r \ n'终止的行: ，用于终止CSV文件的每一行。

IGNORE 1 ROWS: 用于忽略CSV文件的第一行，该行的列标题不需要导入到表中。

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

导入时转换数据

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

假定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数据库服务器。然后，该文件将被上传到数据库服务器操作系统的 临时文件夹，例如上的C: \ windows \ temp。 Windows 和/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语句存在一些安全问题。在这种情况下，我们应该注意避免潜在的安全风险。