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://: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"