65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 C++ 导入和导出 MySQL 数据库

Satish Jagtap
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.68/5 (7投票s)

2015年3月23日

CPOL

3分钟阅读

viewsIcon

31061

downloadIcon

903

本文介绍如何将数据库导入到 MySQL 服务器，以及如何从 MySQL 服务器导出数据库。

引言

本文展示了如何连接到 MySQL 服务器。 它还将向您展示如何使用 MySQL C API 将数据库导入到 MySQL 服务器以及如何从 MySQL 服务器导出数据库。

背景

这是一个关于 MySQL 服务器和数据库导入导出的 C 语言编程教程。它涵盖了使用 C API 导入和导出 MySQL。

MySQL 是一个领先的开源数据库管理系统。 它是一个多用户、多线程的数据库管理系统。 MySQL 的开发始于 1994 年，由瑞典公司 MySQL AB 完成。 Sun Microsystems 于 2008 年收购了 MySQL AB。 Sun 于 2010 年被 Oracle 收购。 因此，如今 Oracle 公司是 MySQL 数据库的所有者。 MySQL 是用 C/C++ 开发的。 除了 C/C++ 之外，还存在用于 PHP、Python、Java、C#、Eiffel、Ruby、Tcl 或 Perl 的 API。

必备组件

  1. 安装 Visual Studio 2013（因为代码和文章一起是在 Visual Studio 2013 中开发的）
  2. 在您的本地机器上安装 MySQL 服务器

设置

在使用 MySQL C API 编写数据库代码之前，我们需要使用以下设置配置我们的 Visual Studio 以用于 MySQL

  1. 首先，从安装 MySQL 的位置复制“libmysql.dll”，在我的电脑上，它位于“C:\Program Files\MySQL\MySQL Server 5.6\lib”位置。 然后将其粘贴到将创建应用程序的 exe/dll 的位置。
  2. 现在是配置 Visual Studio 以使用 MySQL C API 的时候了：为此，请访问项目的项目属性并相应地更新以下设置
    1. C/C++ -> 常规 -> 附加包含目录 - C:\Program Files\MySQL\MySQL Server 5.6\include
    2. 链接器 -> 常规 -> 附加库目录 - C:\Program Files\MySQL\MySQL Server 5.6\lib
    3. 链接器 -> 输入 -> 附加依赖项 - "C:\Program Files\MySQL\MySQL Server 5.6\lib\libmysql.lib"

注意：请根据安装 MySQL 服务器的 PC 设置进行必要的更改。

使用代码

我创建了一个单独的类 (CDatabaseManipulation)，用于处理 MySQL 服务器连接、将数据库导入到 MySQL 服务器以及从 MySQL 服务器导出数据库。 该类还包含一些其他功能来操作 MySQL 数据库。

连接到 MySQL 服务器：要连接到 MySQL 服务器，用户需要提供服务器主机名、用户名和密码。 数据库需要传递为空白。

bool CDatabaseManipulation::Connect(/*[IN]*/const TCHAR *ptchHost,
                                    /*[IN]*/const TCHAR *ptchUserID,
                                    /*[IN]*/const TCHAR *ptchDatabase,
                                    /*[IN]*/const TCHAR *ptchPassword)
/* =====================================================================================================
NAME OF FUNCTION:    CDatabaseManipulation::Connect
CREDIT:              Satish Jagtap
PURPOSE:             This function is used to connect MYSQL database.
PARAMETERS:          [IN] 1) TCHAR *ptchHost: Parameter passed as ptchHost may be either a host name or
                             an IP address. If host is NULL or the string "localhost", a connection to
                             the local host is assumed.
                             For Windows, the client connects using a shared-memory connection,if the
                             server has shared-memory connections enabled. Otherwise, TCP/IP is used.
                     [IN] 2) TCHAR *ptchUserID: Parameter passed as ptchUserID contains the user's
                             MySQL login ID.
                     [IN] 3) TCHAR *ptchDatabase: Parameter passed as ptchDatabase is the database
                             name. If ptchDatabase is not NULL, the connection sets the default
                             database to this value.
                     [IN] 4) TCHAR *ptchPassword: Parameter passed as ptchPassword contains the 
                             password for user.
RETURN VALUE:        None
CALLS TO:            None
CALLED FROM:         None
Added date:          12 March, 2015
Updated date:         
======================================================================================================*/
{
    bool bConnectionSuccess = false;

    if(IsConnected()) 
    {
        m_objLogger.log(_T("Connection has already been established."));
        bConnectionSuccess = false;
    }

    //Allocates or initializes a MYSQL object suitable for mysql_real_connect()
    //returns an initialized MYSQL* handle. 
    //Returns NULL if there was insufficient memory to allocate a new object.
    if(mysql_init(&mysqlInitial) == NULL)
    {
        m_objLogger.log(_T("Failed to initiate MySQL connection"));
        bConnectionSuccess = false;
    }
    //Establishes a connection to a database server. 
    //Returns a MYSQL * handle or NULL if an error occurred.
    mysqlConnection = mysql_real_connect(&mysqlInitial, 
                                          (const char*)ptchHost, 
                                          (const char*)ptchUserID, 
                                          (const char*)ptchPassword, 
                                          (const char*)ptchDatabase, 0, 0, 0);
 
    // Check if connection succeeded.
    if( mysqlConnection == NULL )
    {
        LPTSTR lptstrError = new TCHAR[1024];

        _stprintf_s(lptstrError, 1024, _T("%s %s"), 
                    _T("Couldn't connect to MySQL database server! Error: "), 
                    mysql_error(mysqlConnection));
        m_objLogger.log(lptstrError);

        delete [] lptstrError;

        bConnectionSuccess = false;
    }
    else
    {
        m_objLogger.log(_T("Connect success."), _T("INFO")) ;
        bConnectionSuccess = true;
    }

    if(!IsConnected()) 
    {
        m_objLogger.log(GetError());
        bConnectionSuccess = false;
    }

    return bConnectionSuccess;
}

bool CDatabaseManipulation::CloseConnection(void) 
/* =====================================================================================================
NAME OF FUNCTION:    CDatabaseManipulation::CloseConnection
CREDIT:              Satish Jagtap
PURPOSE:             This function is used to close database connection if exist.
                     It should be non-NULL for MySQL.
PARAMETERS:          None
RETURN VALUE:        Returns connection status of database.
CALLS TO:            1) IsConnected()
CALLED FROM:         2) Destructor i.e. from ~CDatabaseManipulation()
Added date:          12 March, 2015
Updated date:         
======================================================================================================*/
{
    bool bSuccessCloseConnection = false;

    if(IsConnected()) 
    {
        mysql_close(mysqlConnection);
        mysqlConnection = (MYSQL *)NULL;

        bSuccessCloseConnection = true;
    }
    else
    {
        bSuccessCloseConnection = false;
    }

    return bSuccessCloseConnection;
}

将数据库导入到 MySQL 服务器：要导入数据库，用户需要连接到 MySQL 服务器。 除其他详细信息外，用户还需要将数据库名称和导入文件（带有 .sql 扩展名）位置传递给 ImportDatabase 函数。

注意：对于从 ImportDatabase 函数调用的函数，请参阅本文附带的应用程序代码中的类

bool CDatabaseManipulation::ImportDatabase(/*[IN]*/const TCHAR *ptchHost, 
                                           /*[IN]*/const TCHAR *ptchUserID, 
                                           /*[IN]*/const TCHAR *ptchPassword, 
                                           /*[IN]*/const TCHAR *ptchDatabaseNameToImport, 
                                           /*[IN]*/const TCHAR *ptchImportDatabaseFile)
/* =====================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::ImportDatabase
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to import database using import file into MySql database.
                    This function create vector of strings containing commands to import database.
                    This function then creates batch file.
                    This function then writes vector of commands into batch file.
                    This function then execute batch file using cmd.exe.
                    At the end after import of database, function removes batch file.
PARAMETERS:         [IN] 1) TCHAR *ptchHost: Host or server name to connect and import database.
                    [IN] 2) TCHAR *ptchUserID: User name to connect and import database.
                    [IN] 3) TCHAR *ptchPassword: Password to connect and import database.
                    [IN] 4) TCHAR *ptchDatabaseNameToImport: MySql database name to import.
                    [IN] 5) TCHAR *ptchImportDatabaseFile: Database file to import into MySql database.
RETURN VALUE:       None
CALLS TO:           1) WriteVectorInFile
                    2) GetExecutablePath
CALLED FROM:        None
Added date:         17 March, 2015
Updated date:
======================================================================================================*/
{
    bool bImportDBSuccess = false;

    //Database connection
    //Connect(ptchHost, ptchUserID, _T(""), ptchPassword);
    if(!IsConnected()) 
    {
        m_objLogger.log(_T("MySql server is not connected."));
        bImportDBSuccess = false;
    }
    else
    {
        TCHAR *strCreateDatabaseCommand = new TCHAR[MAX_PATH];
        _tcscpy_s(strCreateDatabaseCommand, MAX_PATH, _T("CREATE DATABASE "));
        _tcscat_s(strCreateDatabaseCommand, MAX_PATH, ptchDatabaseNameToImport);
        mysql_query(mysqlConnection, (const char*)strCreateDatabaseCommand);

        //Creating batch file data to execute
        TCHAR strProgramFilePath[MAX_PATH];
        SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE);

        TCHAR * strReturnSQLFilePath = new TCHAR[MAX_PATH];
        _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));

        SearchForFilePath(strProgramFilePath, _T("mysql.exe"), strReturnSQLFilePath);

        if(!_tcscmp(strReturnSQLFilePath, _T("")))
        {
            return false;
        }

        //populate vector with import database command to write into batch file for import database
        vector<TCHAR *> vecToWriteInFile;
        vecToWriteInFile.push_back(_T("@echooff"));
        vecToWriteInFile.push_back(_T("set MainDir = %CD%"));
        vecToWriteInFile.push_back(_T("CD %MainDir%"));

        TCHAR strSQLDrive[3];
        strSQLDrive[0] = strReturnSQLFilePath[0];
        strSQLDrive[1] = strReturnSQLFilePath[1];
        strSQLDrive[2] = _T('\0');
        vecToWriteInFile.push_back(strSQLDrive);

        TCHAR * strTempPath = new TCHAR[MAX_PATH];
        _tcscpy_s(strTempPath, MAX_PATH, _T("CD "));
        _tcscat_s(strTempPath, MAX_PATH, strReturnSQLFilePath);
        vecToWriteInFile.push_back(strTempPath);

        TCHAR strImportCommand[1024];
        _tcscpy_s(strImportCommand, MAX_PATH, _T("mysql --user="));
        _tcscat_s(strImportCommand, ptchUserID);
        _tcscat_s(strImportCommand, _T(" --password="));
        _tcscat_s(strImportCommand, ptchPassword);
        _tcscat_s(strImportCommand, _T(" -D"));
        _tcscat_s(strImportCommand, ptchDatabaseNameToImport);
        _tcscat_s(strImportCommand, _T(" < \""));
        _tcscat_s(strImportCommand, ptchImportDatabaseFile);
        _tcscat_s(strImportCommand, _T("\""));
        vecToWriteInFile.push_back(strImportCommand);
        vecToWriteInFile.push_back(_T("exit"));

        //Create temporary import batch file
        CExecutablePathInfo objExecutablePathInfo;
        LPTSTR lptstrExecutableDirectory = new TCHAR[1024];
        objExecutablePathInfo.GetExecutableDirectory(lptstrExecutableDirectory, 1024);
        _tcscat_s(lptstrExecutableDirectory, MAX_PATH, _T("\\TempDatabaseManipulationImport.bat"));

        //Write into temporary created import batch file
        WriteVectorInFile(vecToWriteInFile, lptstrExecutableDirectory);

        vecToWriteInFile.clear(); //clears the vector
        vecToWriteInFile.shrink_to_fit(); //It requests the removal of unused capacity of vector

        TCHAR strSystemDirPath[MAX_PATH] = _T("");
        GetSystemDirectory(strSystemDirPath, sizeof(strSystemDirPath) / sizeof(_TCHAR));

        // path to cmd.exe, path to batch file, plus some space for quotes, spaces, etc.
        TCHAR strCommandLine[2 * MAX_PATH + 16] = _T("");

        _sntprintf_s(strCommandLine, sizeof(strCommandLine) / sizeof(_TCHAR), 
                     _T("\"%s\\cmd.exe\" /C \"%s\""), strSystemDirPath, lptstrExecutableDirectory);

        delete[] strTempPath;
        strTempPath = NULL;
        delete[] strReturnSQLFilePath;
        strReturnSQLFilePath = NULL;

        STARTUPINFO si = { 0 }; // alternative way to zero array
        si.cb = sizeof(si);
        PROCESS_INFORMATION pi = { 0 };

        if (!CreateProcess(NULL,
                            strCommandLine,
                            NULL,
                            NULL,
                            FALSE,
                            0,
                            NULL,
                            NULL,
                            &si,
                            &pi)
            )
        {
            LPTSTR lpstrError = new TCHAR[1024];

            _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)\n"), GetLastError());
                        m_objLogger.log(lpstrError);

            delete[] lpstrError;

            bImportDBSuccess = false;
        }
        else
        {
            bImportDBSuccess = true;
        }

        WaitForSingleObject(pi.hProcess, INFINITE);
        CloseHandle(pi.hProcess);
        CloseHandle(pi.hThread);

        //Remove batch file
        remove((const char*)lptstrExecutableDirectory);

        delete[] lptstrExecutableDirectory;
        lptstrExecutableDirectory = NULL;
    }

    return bImportDBSuccess;
}

从当前运行的 MySqlServer 检索数据库列表：要从 MySQL 服务器导出数据库，我们需要知道当前正在运行的 MySQL 服务器中当前可用的数据库。 本节从 MySQL 服务器检索数据库列表。

void CDatabaseManipulation::RetrieveDatabasesListInMySqlServer(vector<TCHAR*> &vecMySqlDatabasesList)
/* =====================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::RetrieveDatabasesListInMySqlServer
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to receives databases list in currently running MySql server 
                    instance
PARAMETERS:         1) vector<TCHAR*> &vecMySqlDatabasesList - Receives databases list in currently 
                                                               running MySql server instance
RETURN VALUE:       None
CALLS TO:           None
CALLED FROM:        None
Added date:         12 March, 2015
Updated date:
======================================================================================================*/
{
    MYSQL_RES *myqlResult = mysql_list_dbs(mysqlConnection, _T("%") /* fetch all */);

    if (!myqlResult) 
    {
        LPTSTR lptstrError = new TCHAR[1024];

        _stprintf_s(lptstrError, 1024, _T("Couldn't get db list: %s"), GetError());
        m_objLogger.log(lptstrError);

        delete [] lptstrError;
    }
    else 
    {
        MYSQL_ROW mysqlRow;

        while(mysqlRow = mysql_fetch_row(myqlResult)) 
        {
            if((_tcscmp(mysqlRow[0], "information_schema")) && 
               (_tcscmp(mysqlRow[0], "performance_schema")) && 
               (_tcscmp(mysqlRow[0], "mysql")))
            {
                vecMySqlDatabasesList.push_back(mysqlRow[0]);
            }
        }
    }
}

从 MySQL 服务器导出数据库：要导出数据库，用户需要连接到 MySQL 服务器。 除其他详细信息外，用户还需要将数据库名称和导出文件（带有 .sql 扩展名）位置传递给 ExportDatabase 函数。

注意：对于从 ExportDatabase 函数调用的函数，请参阅本文附带的应用程序代码中的类

bool CDatabaseManipulation::ExportDatabase(/*[IN]*/const TCHAR *ptchHost,
                                           /*[IN]*/const TCHAR *ptchUserID, 
                                           /*[IN]*/const TCHAR *ptchPassword, 
                                           /*[IN]*/const TCHAR *ptchDatabaseNameToExport, 
                                           /*[IN]*/const TCHAR *ptchExportDatabaseFileWithPath)
/* ======================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::ExportDatabase
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to export database to the specified path with specified file 
                    name.
PARAMETERS:         [IN] 1) TCHAR *ptchHost: Host or server name to connect and import database.
                    [IN] 2) TCHAR *ptchUserID: User name to connect and import database.
                    [IN] 3) TCHAR *ptchPassword: Password to connect and import database.
                    [IN] 4) TCHAR *ptchDatabaseNameToExport: MySql database name to export.
                    [IN] 5) TCHAR *ptchExportDatabaseFileWithPath: Database filename with path to 
                            export MySql database.
RETURN VALUE:       Returns true on success.
CALLS TO:           1) WriteVectorInFile
                    2) GetExecutablePath
CALLED FROM:        None
Added date:         17 March, 2015
Updated date:
=======================================================================================================*/
{
    bool bExportDBSuccess = false;

    //Database connection
    //Connect(ptchHost, ptchUserID, ptchDatabaseNameToExport, ptchPassword);
    if(!IsConnected()) 
    {
        m_objLogger.log(_T("MySql server is not connected."));
        bExportDBSuccess = false;
    }
    else
    {
        //Creating batch file data to execute
        TCHAR strProgramFilePath[MAX_PATH];
        SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE);

        TCHAR * strReturnSQLFilePath = new TCHAR[MAX_PATH];
        _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));

        SearchForFilePath(strProgramFilePath, _T("mysqldump.exe"), strReturnSQLFilePath);

        if(!_tcscmp(strReturnSQLFilePath, _T("")))
        {
            return false;
        }

        //populate vector with export database command to write into batch file for export database
        vector<TCHAR *> vecToWriteInFile;
        vecToWriteInFile.push_back(_T("@echooff"));
        vecToWriteInFile.push_back(_T("set MainDir = %CD%"));
        vecToWriteInFile.push_back(_T("CD %MainDir%"));

        TCHAR strSQLDrive[3];
        strSQLDrive[0] = strReturnSQLFilePath[0];
        strSQLDrive[1] = strReturnSQLFilePath[1];
        strSQLDrive[2] = _T('\0');
        vecToWriteInFile.push_back(strSQLDrive);

        TCHAR * strTempPath = new TCHAR[MAX_PATH];
        _tcscpy_s(strTempPath, MAX_PATH, _T("CD "));
        _tcscat_s(strTempPath, MAX_PATH, strReturnSQLFilePath);
        vecToWriteInFile.push_back(strTempPath);

        TCHAR strImportCommand[1024];
        _tcscpy_s(strImportCommand, MAX_PATH, _T("mysqldump --user="));
        _tcscat_s(strImportCommand, ptchUserID);
        _tcscat_s(strImportCommand, _T(" --password="));
        _tcscat_s(strImportCommand, ptchPassword);
        _tcscat_s(strImportCommand, _T(" --result-file="));
        _tcscat_s(strImportCommand, _T("\""));
        _tcscat_s(strImportCommand, ptchExportDatabaseFileWithPath);
        _tcscat_s(strImportCommand, _T("\""));
        _tcscat_s(strImportCommand, _T(" "));
        _tcscat_s(strImportCommand, ptchDatabaseNameToExport);
        vecToWriteInFile.push_back(strImportCommand);
        vecToWriteInFile.push_back(_T("exit"));

        //Create temporary import batch file
        CExecutablePathInfo objExecutablePathInfo;
        LPTSTR lptstrExecutableDirectory = new TCHAR[1024];
        objExecutablePathInfo.GetExecutableDirectory(lptstrExecutableDirectory, 1024);
        _tcscat_s(lptstrExecutableDirectory, MAX_PATH, _T("\\TempDatabaseManipulationExport.bat"));

        //Write into temporary created import batch file
        WriteVectorInFile(vecToWriteInFile, lptstrExecutableDirectory);
    
        vecToWriteInFile.clear(); //clears the vector
        vecToWriteInFile.shrink_to_fit(); //It requests the removal of unused capacity of vector

        TCHAR strSystemDirPath[MAX_PATH] = _T("");
        GetSystemDirectory(strSystemDirPath, sizeof(strSystemDirPath) / sizeof(_TCHAR));

        // path to cmd.exe, path to batch file, plus some space for quotes, spaces, etc.
        TCHAR strCommandLine[2 * MAX_PATH + 16] = _T("");

        _sntprintf_s(strCommandLine, sizeof(strCommandLine) / sizeof(_TCHAR), 
                     _T("\"%s\\cmd.exe\" /C \"%s\""), strSystemDirPath, lptstrExecutableDirectory);

        delete [] strTempPath;
        strTempPath = NULL;
        delete [] strReturnSQLFilePath;
        strReturnSQLFilePath = NULL;

        STARTUPINFO si = { 0 }; // alternative way to zero array
        si.cb = sizeof(si);
        PROCESS_INFORMATION pi = { 0 };

        if (!CreateProcess(NULL,
                            strCommandLine,
                            NULL,
                            NULL,
                            FALSE,
                            0,
                            NULL,
                            NULL,
                            &si,
                            &pi)
            )
        {
            LPTSTR lpstrError = new TCHAR[1024];

            _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)\n"), GetLastError());
            m_objLogger.log(lpstrError);

            delete [] lpstrError;

            bExportDBSuccess = false;
        }
        else
        {
            bExportDBSuccess = true;
        }

        WaitForSingleObject(pi.hProcess, INFINITE);
        CloseHandle(pi.hProcess);
        CloseHandle(pi.hThread);

        //Remove batch file
        remove((const char*)lptstrExecutableDirectory);

        delete [] lptstrExecutableDirectory;
        lptstrExecutableDirectory = NULL;
    }

    return bExportDBSuccess;
}

本文附带的应用程序代码是使用 MFC 对话框应用程序开发的整个源代码。 这是应用程序窗口的样子

使用应用程序的步骤

  1. 首先使用“连接到 MySql 服务器实例”部分连接到 MySQL 服务器。
  2. 使用“导入数据库”部分导入数据库。
  3. 使用“导出数据库”部分导出数据库。

关注点

为了导入和导出数据库，我创建了相应的（用于导入/导出数据库）批处理文件，并在相应的事件上运行这些批处理文件。 我在完成事件（导入/导出）后删除了这些批处理文件。

备注

  1. 请记住更改您的项目设置，使其指向 MySQL 的包含文件和 MySQL 的 lib 文件。
  2. 从安装 MySQL 的位置复制“libmysql.dll”，在我的电脑上，它位于“C:\Program Files\MySQL\MySQL Server 5.6\lib”位置。 然后将其粘贴到将创建应用程序的 exe/dll 的位置。
© . All rights reserved.