MySQL - 将表导出为 CSV 文件

• 将 MySQL 表导出为 CSV 文件
• 导出的 .csv 文件的存储位置
• 以 CSV 格式导出 MySQL 数据
• 导出表数据及列标题
• 导出表数据但不指定列名
• 客户端程序

MySQL 是一个开源关系数据库管理系统，可用于存储和管理海量数据。其主要功能之一是将表中的数据导出为各种格式，CSV 就是其中一种。CSV 代表"逗号分隔值"文件。 这允许用户以结构化的格式从表中提取数据，以便使用其他工具(例如 Microsoft Excel、Google 文档、Open Office 等)轻松操作和分析。

将 MySQL 表导出为 CSV 文件

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

• MySQL 服务器进程必须对指定目标文件夹具有读/写权限，CSV 文件将在该目标文件夹创建。

• 指定的 CSV 文件必须已存在于系统中(无重复文件)。

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

语法

以下是 SELECT INTO ... OUTFILE 语句的语法 -

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

其中:

• INTO OUTFILE 是要将表数据导出到的 CSV 文件的路径和名称。

• FIELDS TERMINATED BY 是导出的 CSV 文件中字段的分隔符。

• LINES TERMINATED 是导出的 CSV 文件的行终止符。

导出的 .csv 文件的存储位置

在 MySQL 中，导出文件(例如 .csv 文件)时，导出文件的默认存储位置由"secure_file_priv"变量决定。

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

SHOW VARIABLES LIKE "secure_file_priv";

我们得到以下输出 -

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

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)
);

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

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表如下所示 -

以 CSV 格式导出 MySQL 数据

您可以使用 SELECT INTO ... OUTFILE 语句将 MySQL 数据导出到 CSV 文件中。此处，我们使用以下查询将 CUSTOMERS 表的数据导出到名为"CUSTOMERS_BACKUP"的 CSV 文件中 -

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

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

Query OK, 7 rows affected (0.01 sec)

以下是我们打开"CUSTOMERS_BACKUP.csv"文件时的图像 -

处理文件已存在错误 -

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

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

以下是获得的错误 -

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

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

删除数字记录的引号 -

默认情况下，CSV 文件中的所有记录(包括数字值)都会用双引号括起来。如果您想删除数字记录的引号，可以在 ENCLOSED BY 子句之前使用 OPTIONALLY 子句，如下所示 -

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

以下是执行上述查询后的输出 -

Query OK, 7 rows affected (0.00 sec)

如下图 CSV 文件所示，数字记录的双引号 ("") 已被删除。

导出表数据及其列标题

要导出表数据及其各自的列标题，您可以使用 UNION ALL 语句。这允许您创建一个包含列名的行，然后附加数据行。以下是一个示例查询 -

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

输出

获得的输出如下 -

Query OK, 8 rows affected (0.01 sec)

验证

如果我们验证 .csv 文件，我们可以看到相应的列名已添加 -

导出表数据而不指定列名

您也可以在不指定列名的情况下将表数据导出到 CSV 文件中。

语法

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

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

示例

在以下查询中，我们将 CUSTOMERS 表数据导出到"CUSTOMERS_BACKUP.csv"文件中，但不指定其列名 -

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

输出

生成的结果如下 -

Query OK, 7 rows affected (0.01 sec)

验证

正如我们在"CUSTOMERS_BACKUP.csv"文件中看到的，表数据已导出 -

替换 NULL 值

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

示例

在以下查询中，IFNULL() 函数用于在导出数据之前将"ADDRESS"列中的 NULL 值替换为"NULL_VALUE"，如下所示 -

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

输出

得到的结果如下所示 −

Query OK, 7 rows affected (0.00 sec)

验证

NULL 值 (N) 将被替换为"NULL_VALUE"-

使用客户端程序

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

语法

$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';
' ESCAPED BY '' LINES TERMINATED BY'
'";
$mysqli->query($sql);

sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';
' ESCAPED BY '"' LINES TERMINATED BY '
'`;
con.query(sql);

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';
' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
statement.execute(sql);

sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'FIELDS ENCLOSED BY '"'TERMINATED BY '
' ESCAPED BY '"'LINES TERMINATED BY '
'"
cursorObj.execute(sql)

示例

以下是程序 -

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '
'";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!
");
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table data exported successfully....!
1     

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
//连接到 MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '
'`;
 con.query(sql, function(err, result){
    console.log("Table data exported successfully...!");
    console.log("Data: ")
    if (err) throw err;
    console.log(result);
    });
});  

Table data exported successfully...!
Data: 
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 7,
  insertId: 0,
  info: '',
  serverStatus: 34,
  warningStatus: 0,
  changedRows: 0
}  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ExportTableToCSVFile {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
            st.execute(sql);
            System.out.println("Successfully...! table exported into CSV file..");
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Successfully...! table exported into CSV file..

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# 查询将表导出到 csv 文件
sql = """
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '
';
"""
cursorObj.execute(sql)
print("Table data expoted successfully")
# 读取并显示导出的 CSV 文件
with open('C:/ProgramData/mysql8/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv', 'r') as csvfile:
    for row in csvfile:
        # 使用 strip() 删除多余的换行符
        print(row.strip())  
# 关闭游标和连接
cursorObj.close()
connection.close()  

Table data expoted successfully
"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"