mysql版本mysql-5.5.62-winx64;
.exe所在文件中要加入libmysql.dll文件
目录
1、使用步骤
1.1配置参数、连接数据库
/*MYSQL m_mysql;
struct BYDatabaseConfig
{
char host[_MAX_PATH];//服务器IP(mysql)
char port[_MAX_PATH];//数据库端口号(mysql)
char user[_MAX_PATH];//数据库用户名(mysql)
char passwd[_MAX_PATH];//数据库密码(mysql)
char dbname[_MAX_PATH];//数据库名称(mysql)
char charset[_MAX_PATH];//数据库字符集(mysql)
char filename[_MAX_PATH];//数据库文件名(sqlite)
};
BYDatabaseConfig m_config;
*/
mysql_real_connect(&m_mysql, m_config.host, m_config.user, m_config.passwd, m_config.dbname, port, NULL, 0) != NULL
方法解析
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned int client_flag)
• 如果unix_socket不是NULL,字符串指定套接字或应该被使用的命名管道。注意host参数决定连接的类型。
• client_flag值通常是0,但是在很特殊的情况下可以被设置为下列标志的组合:
标志名字 意味着的标志
CLIENT_FOUND_ROWS 返回找到的(匹配的)行数,不是受到影响的行数。
CLIENT_NO_SCHEMA 不允许db_name.tbl_name.col_name语法。这是为了ODBC;如果你使用该语法,导致语法分析器产生一个错误,它是为在一些ODBC程序捕捉错误是有用。
CLIENT_COMPRESS 使用压缩协议。
CLIENT_ODBC 客户是一个ODBC客户。这使mysqld变得对ODBC更友好。
1.2设置数据库字符集
mysql_set_character_set(&m_mysql, m_config.charset)
1.3执行sql语句
int mysql_query(&m_mysql, sql)
int mysql_real_query(MYSQL *mysql, const char *query, unsigned int length)
执行由query指向的SQL查询,它应该是一个length个字节的字符串。查询必须由一个单个的SQL语句组成。你不应该在语句后增加一个终止的分号(“;”)或\g。
对于包含二进制数据的查询,你必须使用mysql_real_query()而不是mysql_query(),因为二进制代码数据可能包含“\0”字符,而且,mysql_real_query()比mysql_query()更快,因为它对查询字符串调用strlen()。
1.4存储执行结果
//MYSQL_RES *mysqlRes=NULL;
mysqlRes = mysql_store_result(&m_mysql);
1.5读取MYSQL_RES
int columns=mysql_num_fields(mysqlRes);
mysqlRow = mysql_fetch_row(mysqlRes)
1.6释放资源
mysql_free_result(mysqlRes);
mysql_close(&m_mysql);
2、使用示例
2.1配置连接
BYDatabaseConfig bYDatabaseConfig;
m_bYMysqlConnector.SetConfig(bYDatabaseConfig);
BYMysqlConnector m_bYMysqlConnector;
int m_connectResult = m_bYMysqlConnector.Connect();
if (m_connectResult != 0)
{
res = ERRORCODE_CONNECT_FAIL;
}
2.2执行语句
std::vector<std::vector<std::string>> data;
char sql[MAXCHAR];
memset(sql, 0, MAXCHAR);
sprintf_s(sql, "select * from t_wrs_user where user_name = '%s'", userName);
int executeResult = MysqlConnectManage::GetInstanse()->m_bYMysqlConnector.ExecuteSql(sql, data);
if (executeResult != 0)
{
res = ERRORCODE_SQLERROR;
}
3.3解析结果
std::vector<std::vector<std::string>> data;
for (auto row : data)
{
if (row.size() != 4)//数据列数
{
res = 1;
goto ExitLine;
}
UserInfo user = { 0 };
strcpy(user.user, row.at(0).c_str());
strcpy(user.id, row.at(2).c_str());
user.role = (UserRole)atoi(row.at(3).c_str());
users.push_back(user);
}
附录
//BYDatabaseConnector.h
#pragma once
#include <vector>
struct BYDatabaseConfig
{
char host[_MAX_PATH];//服务器IP(mysql)
char port[_MAX_PATH];//数据库端口号(mysql)
char user[_MAX_PATH];//数据库用户名(mysql)
char passwd[_MAX_PATH];//数据库密码(mysql)
char dbname[_MAX_PATH];//数据库名称(mysql)
char charset[_MAX_PATH];//数据库字符集(mysql)
char filename[_MAX_PATH];//数据库文件名(sqlite)
};
class BYDatabaseConnector
{
public:
/// <summary>
/// 设置参数
/// </summary>
/// <param name="cfg">参数</param>
virtual void SetConfig(BYDatabaseConfig& cfg)=0;
/// <summary>
/// 连接数据库
/// </summary>
/// <returns>0:成功</returns>
virtual int Connect()=0;
/// <summary>
/// 断开数据库连接
/// </summary>
virtual void Disconnect()=0;
/// <summary>
/// 运行无返回数据的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>0:成功</returns>
virtual int ExecuteSql(const char* sql)=0;
/// <summary>
/// 运行有返回数据的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="data">返回数据</param>
/// <returns>0:成功</returns>
virtual int ExecuteSql(const char* sql,std::vector<std::vector<std::string>> &data) = 0;
/// <summary>
/// 运行有返回数据的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="data">返回数据</param>
/// <param name="columnName">每一列的列名</param>
/// <returns>0:成功</returns>
virtual int ExecuteSql(const char* sql, std::vector<std::vector<std::string>> &data, std::vector<std::string> &columnName) = 0;
};
BYDatabaseConnector* CreateDatabaseConnector();
void ReleaseDatabaseConnector(BYDatabaseConnector* obj);
//BYMysqlConnector.h
#pragma once
#include "BYDatabaseConnector.h"
#include <WinSock2.h>
#include "mysql.h"
class BYMysqlConnector:
public BYDatabaseConnector
{
public:
BYMysqlConnector();
virtual void SetConfig(BYDatabaseConfig& cfg);
virtual int Connect();
virtual void Disconnect();
virtual int ExecuteSql(const char* sql);
virtual int ExecuteSql(const char* sql, std::vector<std::vector<std::string>> &data);
virtual int ExecuteSql(const char* sql, std::vector<std::vector<std::string>> &data, std::vector<std::string> &columnName);
private:
MYSQL m_mysql;
bool m_bConnected;
BYDatabaseConfig m_config;
};
//BYMysqlConnector.cpp
#include "BYMysqlConnector.h"
//
BYDatabaseConnector* CreateDatabaseConnector()
{
return new BYMysqlConnector();
}
void ReleaseDatabaseConnector(BYDatabaseConnector* obj)
{
if (obj != NULL)
{
delete(obj);
}
}
BYMysqlConnector::BYMysqlConnector()
{
m_bConnected = false;
}
void BYMysqlConnector::SetConfig(BYDatabaseConfig& cfg)
{
m_config = cfg;
}
int BYMysqlConnector::Connect()
{
int res = 0;
if (m_bConnected)
{
Disconnect();
}
if (mysql_init(&m_mysql) == NULL)
{
res = 1;
goto ExitLine;
}
int port = atoi(m_config.port);
if (mysql_real_connect(&m_mysql, m_config.host, m_config.user, m_config.passwd, m_config.dbname, port, NULL, 0) == NULL)
{
mysql_close(&m_mysql);
res = 2;
goto ExitLine;
}
if (mysql_set_character_set(&m_mysql, m_config.charset) != 0)
{
mysql_close(&m_mysql);
res = 3;
goto ExitLine;
}
ExitLine:
if (res==0)
{
m_bConnected = true;
}
return res;
}
void BYMysqlConnector::Disconnect()
{
if (m_bConnected)
{
mysql_close(&m_mysql);
m_bConnected = false;
}
}
int BYMysqlConnector::ExecuteSql(const char* sql)
{
int res = 0;
if (!m_bConnected)
{
if (Connect()!=0)
{
res = -1;
goto ExitLine;
}
}
if (mysql_query(&m_mysql, sql) != 0)
{
res = 1;
goto ExitLine;
}
ExitLine:
return res;
}
//data 行-列存储
int BYMysqlConnector::ExecuteSql(const char* sql, std::vector<std::vector<std::string>> &data)
{
int res = 0;
MYSQL_ROW mysqlRow;
MYSQL_RES *mysqlRes=NULL;
if (!m_bConnected)
{
if (Connect() != 0)
{
res = -1;
goto ExitLine;
}
}
res = mysql_query(&m_mysql, sql);
if (res != 0)
{
goto ExitLine;
}
mysqlRes = mysql_store_result(&m_mysql);
if (mysqlRes == NULL)
{
res = 1;
goto ExitLine;
}
int columns=mysql_num_fields(mysqlRes);
while (mysqlRow = mysql_fetch_row(mysqlRes))
{
std::vector<std::string> rowInfo;
for (int i = 0; i < columns; i++)
{
if (NULL == mysqlRow[i])
{
rowInfo.push_back(std::string(""));
}
else
{
rowInfo.push_back(mysqlRow[i]);
}
}
data.push_back(rowInfo);
}
ExitLine:
if (mysqlRes != NULL)
{
mysql_free_result(mysqlRes);
}
return res;
}
int BYMysqlConnector::ExecuteSql(const char* sql, std::vector<std::vector<std::string>> &data, std::vector<std::string> &columnName)
{
int res = 0;
MYSQL_ROW mysqlRow;
MYSQL_RES *mysqlRes = NULL;
if (!m_bConnected)
{
if (Connect() != 0)
{
res = -1;
goto ExitLine;
}
}
data.clear();
columnName.clear();
res = mysql_query(&m_mysql, sql);
if (res != 0)
{
goto ExitLine;
}
mysqlRes = mysql_store_result(&m_mysql);
if (mysqlRes == NULL)
{
res = 1;
goto ExitLine;
}
int columns = mysql_num_fields(mysqlRes);
while (mysqlRow = mysql_fetch_row(mysqlRes))
{
std::vector<std::string> rowInfo;
if (columnName.size() == 0)
{
MYSQL_FIELD* fields = mysql_fetch_fields(mysqlRes);
for (int i = 0; i < columns; i++)
{
std::string str = fields[i].name;
columnName.push_back(str);
}
}
for (int i = 0; i < columns; i++)
{
if (NULL == mysqlRow[i])
{
rowInfo.push_back(std::string(""));
}
else
{
rowInfo.push_back(mysqlRow[i]);
}
}
data.push_back(rowInfo);
}
ExitLine:
if (mysqlRes != NULL)
{
mysql_free_result(mysqlRes);
}
return res;
}