mysql超过100w怎么分多个sheet导出sql

原创

mob649e815e6170 2024-12-21 04:44:14 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e815e6170的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 数据导出方案：分多个 Sheet 导出超过 100 万条记录

背景

在进行数据分析或者迁移时，我们通常希望将 MySQL 数据库中的数据导出为 Excel 文件。然而，当数据量超过 100 万条记录时，Excel 会遇到行数限制的问题，使得直接导出整张表变得不切实际。因此，合理分割数据并导出为多个 Sheet，是一种有效的解决方案。

方案设计

整体思路

我们的方案分为以下几个步骤：

连接数据库：使用适合的数据库驱动连接到 MySQL 数据库。
查询数据：根据特定的条件分批次查询数据，每次查询不超过 100 万条记录。
导出为 Excel：将查询结果写入不同的 Sheet 中。
优化与异常处理：确保能够处理查询异常和写入异常。

技术选型

MySQL: 数据库
Python: 脚本语言
Pandas: 数据处理库
OpenPyXL: Excel 文件处理库

类图设计

以下是我们的类图设计，展示了主要类及其关系：

classDiagram
    class DatabaseConnector {
        +connect()
        +fetchData(query)
    }
    class ExcelExporter {
        +writeToExcel(data, sheetName)
    }
    class DataExporter {
        +exportData(batchSize)
    }
    
    DatabaseConnector --> ExcelExporter : uses
    DataExporter --> DatabaseConnector : uses
    DataExporter --> ExcelExporter : uses

代码示例

以下是导出数据的代码示例：

import mysql.connector
import pandas as pd
from openpyxl import Workbook

class DatabaseConnector:
    def __init__(self, config):
        self.config = config
        self.connection = None

    def connect(self):
        self.connection = mysql.connector.connect(**self.config)

    def fetch_data(self, query):
        cursor = self.connection.cursor()
        cursor.execute(query)
        return cursor.fetchall()

class ExcelExporter:
    def __init__(self, filename):
        self.workbook = Workbook()
        self.filename = filename

    def write_to_excel(self, data, sheet_name):
        sheet = self.workbook.create_sheet(sheet_name)
        for row in data:
            sheet.append(row)
        self.workbook.save(self.filename)

class DataExporter:
    def __init__(self, db_config, excel_filename):
        self.db_connector = DatabaseConnector(db_config)
        self.excel_exporter = ExcelExporter(excel_filename)

    def export_data(self, batch_size=1000000):
        self.db_connector.connect()
        
        total_rows = self.db_connector.fetch_data("SELECT COUNT(*) FROM your_table")[0][0]
        for offset in range(0, total_rows, batch_size):
            query = f"SELECT * FROM your_table LIMIT {batch_size} OFFSET {offset}"
            data = self.db_connector.fetch_data(query)
            sheet_name = f"Sheet{offset // batch_size + 1}"
            self.excel_exporter.write_to_excel(data, sheet_name)

# 使用示例
if __name__ == "__main__":
    db_config = {
        'user': 'your_username',
        'password': 'your_password',
        'host': 'localhost',
        'database': 'your_database',
    }
    
    exporter = DataExporter(db_config, "exported_data.xlsx")
    exporter.export_data()