MySQL查詢結果匯出方式總結

說明：以下示例中使用的MySQL版本為MySQL8.0.13，Python版本為Python3.6.4，資料庫第三方客戶端Navicat Premium版本為 12.0.18

方法一：利用select ······ into outfile語句

首先登陸MySQL，然後檢視系統變數secure_file_priv的值，如下所示：

    預設情況下，secure_file_priv的值為NULL，此時如果利用select ······ into outfile語句將查詢結果匯出，會因為許可權問題而報錯，要想解決這個問題可以在MySQL的配置檔案my.ini中新增語句secure_file_priv = 'some directory'，some directory指的是SQL語句查詢結果要儲存的位置。此處，筆者已在配置檔案my.ini中新增了語句secure_file_priv = E:\MySQL Exported Results。

以.xls檔案形式匯出查詢結果，SQL語句如下：

    匯出結果如下圖所示：

    利用這種方式匯出的查詢結果不包括列名，可以根據資料庫中的原始資料手工給各列添上對應的列名。

以csv檔案形式匯出查詢結果，SQL語句如下：

    匯出結果如下圖所示：

    從上圖可以看到匯出結果全部都放入了同一列中，想要對匯出的資料做進一步還要進行手工分列，比較麻煩。如有哪位朋友知曉用這種方式如何將查詢結果以csv檔案形式匯出且分列正確，還請不吝賜教。

方法二：利用重定向命令

將查詢結果以txt檔案形式匯出，SQL語句如下(預設已為mysql新增了環境變數)：

最終匯出結果如下：

    從上圖可以看到，這種匯出方式得到的匯出結果分列正確，且帶有列名。

方法三：利用第三方客戶端工具Navicat Premium

以將查詢結果匯出為csv檔案為例，具體步驟如下：

    1）開啟navicat premium(預設已經建立好MySQL資料庫連線)，選中相應的資料庫，然後建立新的查詢。

    2）先點選右上角的執行按鈕，然後點選匯出結果圖示，出現如下圖所示對話視窗，在匯出格式選項中選擇CSV檔案。

    3）點選右下角的下一步後，跳出如下視窗。

    4）設定匯出檔案的儲存位置及檔名稱。

    5）點選上圖中右下角的高階選項，設定csv檔案的字符集編碼方式為GB2312(預設是UTF-8)。

    6）設定好字符集編碼方式後，先點選確定按鈕，再點選右下角的下一步，跳出如下視窗。

    7）在上圖中，勾選包含列的標題，並將日期排序更改為我們熟悉的YMD(即年月日)格式，其他保持預設值即可。然後點選右下角的下一步，跳出如下視窗。

    8）在上述視窗中點選右下角的開始，即開始匯出查詢結果。

    如上圖所示，則表明查詢結果已成功匯出。

    最終的匯出結果如下圖所示：

    從上圖可以看到，這種方式匯出的查詢結果可以包含查詢結果各列對應的列名，且有著正確的分列。上述步驟說的是將查詢結果匯出為csv檔案，若想將查詢結果匯出為.xls或者.xlsx檔案，步驟與上述類似，且不用更改匯出檔案的字符集編碼方式，使用預設的UTF-8即可。

方法四：利用Python與MySQL的互動

import pandas as pd
import pymysql
 
# 定義一個用於與MySQL資料庫互動的上下文管理器：
class DataBase(object):
    
    def __init__(self,name,password):
        # 建立資料庫連線
        self.conn = pymysql.connect('localhost','root',str(password),str(name),charset='utf8mb4')
        # 建立cursor物件
        self.cursor = self.conn.cursor()
        
    def __enter__(self):
        return self.cursor  # 返回cursor物件，且這個cursor物件會被賦給with語句中as後面的變數
    
    def __exit__(self,exc_type,exc_value,traceback):
        self.cursor.close()    # 關閉遊標物件
        self.conn.close()      # 斷開資料庫連線
        
 
def main():
    with DataBase('sql50',883721) as db:
        db.execute("select * from student where sgender='男'")   # 執行sql語句
        content = db.fetchall()  # 獲取資料(db中儲存著查詢結果集)
        df = pd.DataFrame(list(content))  # 將從資料庫中查詢出的資料放入DataFrame物件中
    return df
    
        
if __name__ == '__main__':
    df = main()
 
new_cols = ['sid','sname','sbirth','sgender']
df.columns = new_cols
df.head()

    查詢結果df的資料結構如下圖所示：

# 將查詢結果df寫入csv檔案：

df.to_csv('E:/student.csv',sep=',',index=False,encoding='gbk')

    匯出的csv檔案student中的資料如下所示：

    利用這種方式將SQL語句的查詢結果匯出時，也需要根據資料庫中的原始資料給匯出資料中的各列新增對應的列名。

    類似的，如果想以.xlsx檔案的形式匯出SQL語句的查詢結果，可以呼叫DataFrame的to_excel方法，此處不再贅述。

 

參考：

https://dev.mysql.com/doc/refman/8.0/en/select-into.html

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv

https://blog.csdn.net/qq_41080850/article/details/85100641

http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html?highlight=dataframe%20to_csv#pandas.DataFrame.to_csv

https://blog.csdn.net/pengchengliu/article/details/82884046