VC++ mysql 操作

本地类：CMysqlHelper  

#if !defined(CMysqlHelper_fdfdkjfdkfdjlkfds1111111)
#define CMysqlHelper_fdfdkjfdkfdjlkfds1111111

#include "mysql.h"
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

struct TDataBaseConfig
{
    CString strIp;
    CString strUser;
    CString strPwd;
    CString strDbNmae;
};

class CMysqlHelper  
{
public:
    CMysqlHelper();
    ~CMysqlHelper();

    bool Connect();
    void Close();
    char * Gbk2UTF8(const char * gb2312);
    bool ExeSqlCmd(CString cmd);
    void MysqlTest();
private:
    MYSQL * m_pConnect;
    TDataBaseConfig m_dbCfg;
    bool m_bConnect;
};

#endif // !defined(AFX_CONTROL_H__01D78FC5_AA56_47DE_A532_73EC71F4C0C6__INCLUDED_)


#include "stdafx.h"
#include "MysqlHelper.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////



CMysqlHelper::CMysqlHelper()
{
    m_dbCfg.strDbNmae = "hpw";
    m_dbCfg.strIp = "127.0.0.1";
    m_dbCfg.strPwd = "colibri";
    m_dbCfg.strUser = "root";
    m_bConnect = false;
}

CMysqlHelper::~CMysqlHelper()
{

}

bool CMysqlHelper::Connect()
{
    __try
    {
        m_pConnect = mysql_init((MYSQL*)0);
        if (m_pConnect != NULL && mysql_real_connect(m_pConnect, m_dbCfg.strIp, m_dbCfg.strUser, m_dbCfg.strPwd, m_dbCfg.strDbNmae, 3306, NULL, 0))
        {
            if (!mysql_select_db(m_pConnect, m_dbCfg.strDbNmae))
            {
                if (!mysql_set_character_set(m_pConnect, "utf8"))  //设置编码为UTF8
                {
                    m_bConnect = true;
                    return true;
                }                
            }
        }
        else
        {
            return false;
        }
    }
    _except(1)
    {
    }
    return false;
}


void CMysqlHelper::Close()
{
    m_bConnect = false;
    mysql_close(m_pConnect);
}


char * CMysqlHelper::Gbk2UTF8(const char* gb2312)
{
    int len = MultiByteToWideChar(CP_ACP, 0, gb2312, -1, NULL, 0);
    wchar_t* wstr = new wchar_t[len + 1];
    memset(wstr, 0, len + 1);
    MultiByteToWideChar(CP_ACP, 0, gb2312, -1, wstr, len);
    len = WideCharToMultiByte(CP_UTF8, 0, wstr, -1, NULL, 0, NULL, NULL);
    char* str = new char[len + 1];
    memset(str, 0, len + 1);
    WideCharToMultiByte(CP_UTF8, 0, wstr, -1, str, len, NULL, NULL);
    if (wstr) delete[] wstr;
    return str;
}


//列名中有空格，需要``  分隔（1左边的键）
bool CMysqlHelper::ExeSqlCmd(CString cmd)
{
    if (!m_bConnect)
    {
        Connect();
    }
    __try
    {
        char * pchar = Gbk2UTF8((LPSTR)(LPCTSTR) cmd);  //列名中有中文，格式转换
        if (mysql_real_query(m_pConnect, pchar, strlen(pchar)) ==0)
        {
            return true;
        }
    }
    _except(1)
    {
    }
    m_bConnect = false;
    return false;
}

void CMysqlHelper::MysqlTest()
{
    //return;
    MYSQL * con; //= mysql_init((MYSQL*) 0); 
    MYSQL_RES *res;
    MYSQL_ROW row;
    char tmp[400];
    //database configuartion
    char dbuser[30] = "root";
    char dbpasswd[30] = "colibri"; // it must be    changed
    char dbip[30] = "localhost";
    char dbname[50] = "localdb";
    char tablename[50] = "shiftinfo";
    char *query = NULL;

    int x;
    int y;
    int rt;//return value  
    unsigned int t;

    int count = 0;

    con = mysql_init((MYSQL*)0);

    if (con != NULL && mysql_real_connect(con, dbip, dbuser, dbpasswd, dbname, 3306, NULL, 0)) {
        if (!mysql_select_db(con, dbname)) {
            TRACE("Select successfully the database!\n");
            con->reconnect = 1;
            query = "set names \'GBK\'";
            rt = mysql_real_query(con, query, strlen(query));
            if (rt) {
                TRACE("Error making query: %s !!!\n", mysql_error(con));
            }
            else {
                TRACE("query %s succeed!\n", query);
            }
        }
    }
    else {
        AfxMessageBox("Unable to connect the database,check your configuration!");
    }

    //sprintf(tmp, "insert into %s values(%s,%d,%d)", tablename, "null", x, y); //注意如何向具有自增字段的数据库中插入记录
    //sprintf(tmp, "insert into shiftinfo values(2,'2019-09-03 M',0,0)");
    sprintf(tmp, "insert into %s (ShiftNo,YieldNum,OKCount) values(%s,%d,%d)", tablename, "'2019-09-03 M'", 0, 0);

    for (int i = 0; i < 10; i++)
    {
        rt = mysql_real_query(con, tmp, strlen(tmp));
        if (rt)
        {
            TRACE("Error making query: %s !!!\n", mysql_error(con));
        }
        else
        {
            TRACE("%s executed!!!\n", tmp);
        }
    }
    sprintf(tmp, "select * from %s", tablename);
    rt = mysql_real_query(con, tmp, strlen(tmp));
    if (rt)
    {
        TRACE("Error making query: %s !!!\n", mysql_error(con));
    }
    else
    {
        TRACE("%s executed!!!\n", tmp);
    }
    res = mysql_store_result(con);//将结果保存在res结构体中

    while (row = mysql_fetch_row(res)) {
        for (t = 0; t<mysql_num_fields(res); t++) {
            TRACE("%s  ", row[t]);
        }
        TRACE(".............\n");
        count++;
    }

    /*sprintf(tmp, "truncate table %s", tablename);
    rt = mysql_real_query(con, tmp, strlen(tmp));
    if (rt)
    {
    TRACE("Error making query: %s !!!\n", mysql_error(con));
    }
    else
    {
    TRACE("%s executed!!!\n", tmp);
    }*/

    sprintf(tmp, "delete from %s where id > 2", tablename);
    rt = mysql_real_query(con, tmp, strlen(tmp));
    if (rt)
    {
        TRACE("Error making query: %s !!!\n", mysql_error(con));
    }
    else
    {
        TRACE("%s executed!!!\n", tmp);
    }

    TRACE("number of rows %d\n", count);
    TRACE("mysql_free_result...\n");
    mysql_free_result(res);
    mysql_close(con);


}

 

if (!m_mySql.Connect())
    {
        return -1;
    }
    CString cmd = "insert into productdata (日期,二维码,`Tab W1结果`,`Tab W1`) values('2020-08-22 17:00:00','1222','OK',123);";
    m_mySql.ExeSqlCmd(cmd);