MySQL查询结果导出方式总结

叁歲伎倆 2022-03-22 05:42 6982阅读 0赞

*说明：以下示例中使用的MySQL版本为MySQL8.0.13，Python版本为Python3.6.4，数据库第三方客户端Navicat Premium版本为 12.0.18*

### **方法一：利用select ······ into outfile语句** ###

**首先登陆MySQL，然后查看系统变量secure\_file\_priv的值，如下所示：**

![20190126184717772.PNG][]

默认情况下，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语句如下：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70][]

导出结果如下图所示：

![20190126195000144.PNG][]

利用这种方式导出的查询结果不包括列名，可以根据数据库中的原始数据手工给各列添上对应的列名。

**以csv文件形式导出查询结果，SQL语句如下：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 1][]

导出结果如下图所示：

![20190126201711399.PNG][]

从上图可以看到导出结果全部都放入了同一列中，想要对导出的数据做进一步还要进行手工分列，比较麻烦。如有哪位朋友知晓用这种方式如何将查询结果以csv文件形式导出且分列正确，还请不吝赐教。

### 方法二：利用重定向命令 ###

将查询结果以txt文件形式导出，SQL语句如下(默认已为mysql添加了环境变量)：

![20190127104557442.PNG][]

最终导出结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 2][]

从上图可以看到，这种导出方式得到的导出结果分列正确，且带有列名。

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

以将查询结果导出为csv文件为例，具体步骤如下：

1）打开navicat premium(默认已经建立好MySQL数据库连接)，选中相应的数据库，然后建立新的查询。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 3][]

2）先点击右上角的运行按钮，然后点击导出结果图标，出现如下图所示对话窗口，在导出格式选项中选择CSV文件。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 4][]

3）点击右下角的下一步后，跳出如下窗口。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 5][]

4）设置导出文件的保存位置及文件名称。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 6][]

5）点击上图中右下角的高级选项，设置csv文件的字符集编码方式为GB2312(默认是UTF-8)。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 8][]

6）设置好字符集编码方式后，先点击确定按钮，再点击右下角的下一步，跳出如下窗口。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 9][]

7）在上图中，勾选包含列的标题，并将日期排序更改为我们熟悉的YMD(即年月日)格式，其他保持默认值即可。然后点击右下角的下一步，跳出如下窗口。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 10][]

8）在上述窗口中点击右下角的开始，即开始导出查询结果。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 11][]

如上图所示，则表明查询结果已成功导出。

最终的导出结果如下图所示：

![201901262101450.PNG][]

从上图可以看到，这种方式导出的查询结果可以包含查询结果各列对应的列名，且有着正确的分列。上述步骤说的是将查询结果导出为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的数据结构如下图所示：

![20190126225926437.PNG][]

# 将查询结果df写入csv文件：
    
    df.to_csv('E:/student.csv',sep=',',index=False,encoding='gbk')

导出的csv文件student中的数据如下所示：

![20190126230230781.PNG][]

利用这种方式将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_select-into.html]

[https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html\#sysvar\_secure\_file\_priv][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][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][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][https_blog.csdn.net_pengchengliu_article_details_82884046]

[20190126184717772.PNG]: /images/20220322/c5fce3e5b80644ff9938182cb12b0ff9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70]: /images/20220322/8c0a287c88064f8786433a76a054f53f.png
[20190126195000144.PNG]: /images/20220322/c386999f0c96411d8737703a1656edbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 1]: /images/20220322/b38c1af384b24d5faca2872d67297556.png
[20190126201711399.PNG]: /images/20220322/960403c24a994786b14dbdfcaa1610cb.png
[20190127104557442.PNG]: /images/20220322/ec8bfa8d8dcd468aa7fa5f52ae206315.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 2]: /images/20220322/9ffeecd13c5941c0ac04819501bb956c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 3]: /images/20220322/14792c237f2d47e8b6a5d6f653e68554.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 4]: /images/20220322/d18440a4e21643b7a76d4b629ee463f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 5]: /images/20220322/1bc2124b706947dba57eb1a552f9a3c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 6]: /images/20220322/4df1a340e4d34aeb81395410ca9b5bfa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 7]: /images/20220322/3b7e2738369e445dbcf6111e81c96605.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 8]: /images/20220322/19c61b838f144f5fbe93b347682f2d91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 9]: /images/20220322/24706e3ff6224aa2b32542b4de398e34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 10]: /images/20220322/f724f4527b9d4be99274f99c60365be8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 11]: /images/20220322/2675b6f60a2d4301adbc5877bc9ce7d1.png
[201901262101450.PNG]: /images/20220322/0c69e69c460c4049b55bf72913d48262.png
[20190126225926437.PNG]: /images/20220322/321e1875ae9446e280a9a29d4f5954f7.png
[20190126230230781.PNG]: /images/20220322/d1e0cbc91706434596066f678fc76c30.png
[https_dev.mysql.com_doc_refman_8.0_en_select-into.html]: 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://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]: 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]: 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]: https://blog.csdn.net/pengchengliu/article/details/82884046