Mysql 简明教程

MySQL - Export Table into CSV File

MySQL 是一个开放源代码关系数据库管理系统，允许我们存储和管理大量数据。它的主要一项功能是从表格导出各种格式的数据，CSV 就是其中一种格式。CSV 是 "Comma Separated Values" 文件的缩写。

MySQL is an open-source relational database management system that allows us to store and manage large volume of data. One of its key feature is to export data from a table into various formats and CSV is one of it. CSV stands for "Comma Separated Values" file.

Export MySQL Table into CSV File

要将 MySQL 表格数据导出成 CSV 文件，可以使用 MySQL "SELECT INTO … OUTFILE" 语句。在将任何表格数据导出成数据库服务器中的 CSV 文件之前，我们必须确保以下事项：

To export the MySQL table data into a CSV file, we can use the MySQL "SELECT INTO … OUTFILE" statement. Before exporting any table data into CSV files in the database server, we must ensure the following things −

The MySQL server’s process must have the read/write privileges to the specified target folder, where CSV file will be created.
The specified CSV file should be already present in the system (No duplicate file).

导出的 CSV 文件可以包含来自一个或多个表格的数据，并且可以修改成仅包含特定列或行。

The exported CSV file can contain data from one or more tables, and it can be modified to include only particular columns or rows.

Syntax

以下是 SELECT INTO … OUTFILE 语句的语法：

Following is the syntax of SELECT INTO … OUTFILE statement −

SELECT column_name1, column_name2,...
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file_name.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

其中，

Where,

INTO OUTFILE is the path and name of the CSV file that we want to export the table data to.
FIELDS TERMINATED BY is the delimiter that separates the fields in the exported CSV file.
*LINES TERMINATED *is the line terminator character for exported CSV file.

Storage Location of Exported .csv File

在 MySQL 中，导出文件（例如 .csv 文件）时，导出文件的默认存储位置由 “secure_file_priv” 变量决定。

In MySQL, when you export a file, such as a .csv file, the default storage location for the exported file is determined by the "secure_file_priv" variable.

要找出导出文件的默认路径，可以使用以下 SQL 查询：

To find out the default path for exported files, you can use the following SQL query −

SHOW VARIABLES LIKE "secure_file_priv";

我们获得以下输出 −

We get the following output −

在将数据导出到 .csv 文件之前，需要在 MySQL 数据库中至少有一个表。我们使用以下 SQL 查询创建一个名为 “CUSTOMERS” 的表：

Before exporting data to a .csv file, you will need to have at least one table in your MySQL database. Let us create a table named "CUSTOMERS" using the following SQL query −

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

现在，我们将数据插入上面创建的表中，如下所示：

Now, we are inserting data into the above created table as shown below −

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

获得的 CUSTOMERS 表如下所示：

The CUSTOMERS table obtained is as shown below −

Exporting MySQL Data in CSV Format

可以使用 SELECT INTO … OUTFILE 语句将 MySQL 数据导出到 CSV 文件。在此，我们使用以下查询将 CUSTOMERS 表的数据导出到名为 “CUSTOMERS_BACKUP” 的 CSV 文件：

You can export MySQL data in CSV file using the SELECT INTO … OUTFILE statement. Here, we are exporting the data of CUSTOMERS table into a CSV file named "CUSTOMERS_BACKUP" using the following query −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED 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.01 sec)

打开 "CUSTOMERS_BACKUP.csv" 文件时的图像如下：

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

Handling File Already Exists Error −

如果尝试将数据导出到已存在的文件中，MySQL 将生成错误：

If you attempt to export data into a file that already exists, MySQL will generate an error −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

以下是获得的错误 −

Following is the error obtained −

ERROR 1086 (HY000): File 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv' already exists

为避免此错误，在执行导出查询之前，可以要么选择其他文件名，要么删除现有文件。

To avoid this error, you can either choose a different filename or delete the existing file before executing the export query.

Removing Enclosing Quotes for Numeric Records −

默认情况下，CSV 文件中的所有记录都用双引号括起来，包括数字值。如果要为数字记录删除引号，可以在 ENCLOSED BY 子句前使用 OPTIONAL 子句，如下所示：

By default, all records in the CSV file will be enclosed in double quotes, including numeric values. If you want to remove the quotes for numeric records, you can use the OPTIONALLY clause before the ENCLOSED BY clause, as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

以下是执行上述查询后获得的输出：

Following is the output obtained after executing the above query −

Query OK, 7 rows affected (0.00 sec)

如我们在下方的 CSV 文件图像中看到的，已为数字记录删除了双引号 (“”)。

As we can see the image of CSV file below, the double quotes ("") are removed for the numeric records.

Exporting Table Data Along with Column Headings

若要导出表数据及其各自的列标题，可以使用 UNION ALL 语句。这允许你创建带有列名称的行，然后追加数据行。以下是一个示例查询：

To export table data along with their respective column headings, you can use the UNION ALL statement. This allows you to create a row with column names and then append the data rows. Here is an example query −

SELECT 'ID', 'NAME', 'EMAIL', 'PHONE', 'CITY'
UNION ALL
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Output

获得的输出如下 −

The output obtained is as follows −

Query OK, 8 rows affected (0.01 sec)

Verification

如果验证 .csv 文件，我们可以看到添加了各自的列名称：

If we verify the .csv file, we can see that the respective column names are added −

Exporting Table Data Without Specifying Column Names

你还可以将表数据导出到 CSV 文件中，而不指定列名称。

You can also export table data into a CSV file without specifying column names.

Syntax

以下是在不指定列名称的情况下将表数据导出到 CSV 文件中的语法：

Following is the syntax to export table data into a CSV file without specifying column names −

TABLE table_name ORDER BY column_name LIMIT 100
INTO OUTFILE '/path/filename.txt'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';;

Example

在以下查询中，我们将 CUSTOMERS 表数据导出到 “CUSTOMER_BACKUP.csv” 文件，不指定它们的列名称：

In the following query, we are exporting the CUSTOMERS table data into "CUSTOMERS_BACKUP.csv" file without specifying their column names −

TABLE CUSTOMERS ORDER BY NAME LIMIT 100
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Output

产生的结果如下 −

The result produced is as follows −

Query OK, 7 rows affected (0.01 sec)

Verification

如我们可以在 “CUSTOMERS_BACKUP.csv” 文件中看到的，表数据已导出：

As we can see in the "CUSTOMERS_BACKUP.csv" file, the table data got exported −

Replacing NULL Values

如果你的表包含 NULL 值，可以在将数据导出到 CSV 文件之前使用 IFNULL() 函数将它们替换为特定值。

If your table contains NULL values, you can use the IFNULL() function to replace them with specific values before exporting the data to a CSV file.

Example

在以下查询中，IFNULL()\ 函数用于用“NULL_VALUE”替换“ADDRESS”列中的空值，如下所示：

In the following query, the IFNULL() function is used to replace NULL values in the "ADDRESS" column with "NULL_VALUE" before exporting the data as shown below −

SELECT ID, NAME, AGE, IFNULL(ADDRESS, 'NULL_VALUE') FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Output

获得的结果如下所示 −

The result obtained is as shown below −

Query OK, 7 rows affected (0.00 sec)

Verification

空值 (N) 用“NULL_VALUE”替换

The NULL values (N) are replaced with "NULL_VALUE" −

Using Client Program

我们还可以使用客户端程序将表导出到 CSV 文件中。

We can also export table into CSV file using Client Program.

Syntax

Example

以下是这些程序 −

Following are the programs −