MySQL - 将表导出到 CSV 文件

MySQL 是一个开源的关系数据库管理系统，允许我们存储和管理大量数据。其关键特性之一是从表中导出数据到各种格式，CSV 就是其中之一。CSV 代表 **“逗号分隔值”** 文件。

将 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/MySQL/MySQL Server 8.0/Uploads/file_name.csv'     
FIELDS TERMINATED BY ','    
OPTIONALLY ENCLOSED BY '"'    
LINES TERMINATED BY '\r\n';

其中：

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

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

• **LINES TERMINATED BY** 是导出 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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

执行上述查询后，将在指定路径创建 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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

以下是获得的错误：

ERROR 1086 (HY000): File 'C:/ProgramData/MySQL/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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

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

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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

输出

获得的输出如下：

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 '\r\n';;  

示例

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

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

输出

生成的結果如下：

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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

输出

获得的结果如下所示：

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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';
' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
$mysqli->query($sql);

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

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/MySQL/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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'FIELDS ENCLOSED BY '"'TERMINATED BY '
' ESCAPED BY '"'LINES TERMINATED BY '\r\n'"
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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!\n");
   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"
});

 //Connecting to 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/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n'`;
 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://127.0.0.1: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/MySQL/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
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Query to export table into csv file
sql = """
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';
"""
cursorObj.execute(sql)
print("Table data expoted successfully")
# Reading and displaying the exported CSV file
with open('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv', 'r') as csvfile:
    for row in csvfile:
        # Use strip() to remove extra newlines
        print(row.strip())  
# Closing the cursor and connection
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"