MySQL 在数据库/表中导入CSV文件

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

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

当我们需要将复杂数据从一个应用程序导出到CSV文件，然后将文件数据导入到另一个应用程序时，这个文件非常有用。我们可以使用各种电子表格程序如MS Excel或Google Spreadsheets来处理CSV文件。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 语句来验证新创建的表是否为空，如下输出所示：

MySQL 在数据库/表中导入CSV文件

接下来，我们将创建一个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"

MySQL 在数据库/表中导入CSV文件

最后，以下语句允许我们将数据从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 ‘”‘: 它用于指定文件的字段由双引号括起来。

LINES TERMINATED BY ‘\r\n’: 它用于终止CSV文件的每一行。

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

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

MySQL 在数据库/表中导入CSV文件

导入时转换数据

有时，在将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服务器。远程 MySQL服务器中也可以使用LOAD DATA INFILE语句导入CSV文件。

当在LOAD DATA INFILE语句中添加 LOCAL 子句时，客户端程序可以读取本地系统上的CSV文件并将其发送到MySQL数据库服务器。然后，该文件将被上传到数据库服务器操作系统的 临时文件夹 （例如C：\ windows \ temp上的 Windows 和/ tmp上的 Linux OS ）。这些文件夹不是由 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语句存在一些安全问题。在这种情况下，我们应该注意避免潜在的安全风险。