Mysql 简明教程

MySQL - Import CSV into database

Import MySQL CSV into Database

在将 CSV 文件导入到数据库服务器之前，我们必须确保以下事项 -

Before importing the CSV file into the database server, we must ensure the following things −

Database Table − Ensure you have a database table already set up to receive the incoming data.
CSV File − You need a CSV file containing the data to be imported.
User Privileges − Ensure your account has the necessary privileges, specifically FILE and INSERT, to perform this operation.
*Matching Columns − *The target table and the CSV file should have matching columns with the same data types.
CSV Format − The CSV file should be in a comma-separated format, with each row representing a record.

Syntax

以下是 MySQL 中 LOAD DATA INFILE 语句的语法 -

Following is the syntax of the LOAD DATA INFILE statement in MySQL −

LOAD DATA INFILE file_path
INTO TABLE table_name
FIELDS TERMINATED BY delimiter
ENCLOSED BY enclosure
LINES TERMINATED BY line_separator
IGNORE number_of_lines_to_skip;

其中，

Where,

file_path is the path to the CSV file that contains the data to be imported.
table_name is the name of the target table, where the data will be imported.
delimiter is a character that separates each record in the CSV file.
Enclosure is a character that encloses string records in the CSV file.
line_seperator is a character that marks the end of a line in the CSV file.
number_of_lines_to_skip is the number of lines to ignore at the beginning of the CSV file.

Example

首先，让我们使用以下查询创建一个名为 EMPLOYEES 的表 -

First of all, let us create a table with the name EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

现在，我们向上面创建的表格插入行 -

Now, let us insert rows into the above created table −

INSERT INTO EMPLOYEES VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

得到的 EMPLOYESS 表如下所示 -

The EMPLOYEES table obtained is as shown below −

Export Data to CSV −

现在，我们使用以下查询将 EMPLOYEES 表中的数据导出到名为 “EMPLOYEES_BACKUP” 的 CSV 文件 -

Now, we export the data from the EMPLOYEES table into a CSV file named "EMPLOYEES_BACKUP" using the following query −

SELECT * FROM EMPLOYEES
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

执行上述查询后，CSV 格式文件将在指定路径处创建。以下是执行上述查询后获得的输出 -

After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query −

Query OK, 7 rows affected (0.00 sec)

以下是我们在打开 “EMPLOYEES_BACKUP.csv” 文件时得到的图片 -

Following is the image of "EMPLOYEES_BACKUP.csv" file when we opened it −

Create Another Table −

现在，让我们使用与 EMPLOYEES 表相同的列和数据类型创建另一个名为 "CUSTOMERS" 的表 -

Now, let us create another table named "CUSTOMERS" with the same columns and data types as EMPLOYEES table −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

以下是上面代码的输出： -

Following is the output of the above code −

Query OK, 0 rows affected (0.03 sec)

Import Data from CSV −

现在，我们使用以下查询从 “EMPLOYEES_BACKUP.csv” 文件中导入所有数据到 CUSTOMERS 表 -

Now, we import all the data from "EMPLOYEES_BACKUP.csv" file into the CUSTOMERS table using the following query −

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'
INTO TABLE CUSTOMERS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

获得的结果如下 −

The result obtained is as follows −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

Verify Data Import −

要验证是否已成功将数据导入到 CUSTOMERS 表，我们可以使用以下 SELECT 语句 -

To verify that the data has been successfully imported into the CUSTOMERS table, we can use the following SELECT statement −

SELECT * FROM CUSTOMERS;

正如我们在下面看到的输出，CUSTOMERS 表包含与 EMPLOYEES 表相同的数据，因为 CSV 数据已成功导入 -

As we can see the output below, the CUSTOMERS table contains the same data as the EMPLOYEES table, as the CSV data has been imported successfully −

Importing a CSV File Using Client Program

我们还可以使用客户端程序将 CSV 文件导入数据库。

We can also import CSV file into database using Client Program.

Syntax

Example

以下是这些程序 −

Following are the programs −