首先,环境是windows + vs2008,Mysql数据库已经安装好,在使用之前,需要配置工程属性,附加包含目录添加
D:\Program Files\MySQL\MySQL Server 5.6\include (Mysql安装目录),附加库目录添加 D:\Program Files\MySQL\MySQL Server 5.6\lib ,附加依赖项添加 mysqlib.lib,当然mysqllib.lib 只是包含符号而已,可执行文件运行的时候需要mysqllib.dll(lib目录下), 将其拷贝到exe同目录下。
一、常用Mysql C API 介绍和使用
1.mysql_init
MYSQL结构代表一个连接句柄
MYSQL*mysql_init(MYSQL*mysql);
如果mysql是NULL指针,该函数将分配、初始化、并返回新对象。否则,将初始化对象,并返回对象的地址。如果mysql_init()分配了新的对象,当调用mysql_close()来关闭连接时。将释放该对象。
2.mysql_real_connect
// 连接数据库
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 long client_flag)
//设置数据库
my_bool reconnect = true;
mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);
mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "gbk");
3.mysql_query
int mysql_query(MYSQL *mysql, const char *query)
mysql_affected_rows
mysql_store_result
mysql_num_fields
mysql_num_rows
mysql_fetch_field
mysql_fetch_row
mysql_free_result
示例代码如下:
#include <Windows.h>
#include <mysql.h>
#include <stdio.h>
int main(void)
{
//初始化一个连接句柄
MYSQL* mysql = mysql_init(NULL);
if (mysql == NULL)
{
printf("error:%s", mysql_error(mysql));
return 1;
}
my_bool reconnect = true;
mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);
mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "gbk");
if (!mysql_real_connect(mysql, "localhost", "root", "123456",
"scott", 0, NULL, 0))
{
printf("error:%s", mysql_error(mysql));
return 1;
}
// 没有返回结果集的操作
int result;
result = mysql_query(mysql, "insert into emp values(8888, 'YYYY', 'CLERK', 7782, '1990-04-10', 1500, NULL, 50);");
if (result != 0)
{
printf("error:%s", mysql_error(mysql));
return 1;
}
printf("%llu 行受影响\n", mysql_affected_rows(mysql));
// 有返回结果集的操作
result = mysql_query(mysql, "select * from emp where deptno=30;");
if (result != 0)
{
printf("error:%s", mysql_error(mysql));
return 1;
}
MYSQL_RES* mysql_res;
MYSQL_FIELD* mysql_field;
MYSQL_ROW mysql_row;
unsigned int cols;
mysql_res = mysql_store_result(mysql);
cols = mysql_num_fields(mysql_res);
if (mysql_res != NULL)
{
printf("返回%llu行\n", mysql_num_rows(mysql_res));
while((mysql_field = mysql_fetch_field(mysql_res)))
{
printf("%s\t", mysql_field->name);
}
printf("\n");
while((mysql_row = mysql_fetch_row(mysql_res)))
{
for (unsigned int i = 0; i < cols; i++)
{
printf("%s\t", mysql_row[i]? mysql_row[i]: "NULL");
}
printf("\n");
}
mysql_free_result(mysql_res);
}
mysql_close(mysql);
return 0;
}
输出结果如下,因为各字段值长短不一,虽然加了tab,输出还是有点别扭:
二、下面封装MysqlDB类
使用的基本是上面演示过的函数,就不多解释了,直接看代码吧。
MysqlDB.h:
#ifndef _MYSQL_DB_H_
#define _MYSQL_DB_H_
//#define WIN32_LEAN_AND_MEAN
#include <winsock2.h>
#include <mysql.h>
#include <vector>
#include <string>
using namespace std;
namespace DAL
{
class MysqlDB;
class MysqlRecordset
{
friend class MysqlDB;
public:
const string& GetItem(unsigned int nRow, unsigned int nCol) const
{
return rows_[nRow][nCol];
}
const string& GetItem(unsigned int nRow, const string& name) const
{
unsigned int index = GetFieldIndex(name);
return rows_[nRow][index];
}
unsigned int GetRows() const
{
return rows_.size();
}
unsigned int GetCols() const
{
return fields_.size();
}
unsigned int GetFieldIndex(const std::string &name) const
{
unsigned int index = -1;
for(unsigned int i = 0; i < fields_.size(); ++i)
{
if (fields_[i].name == name)
index = fields_[i].index;
}
return index;
}
void Clear()
{
rows_.clear();
fields_.clear();
}
typedef struct Field
{
string name; //列的字段名
unsigned int index; //字段名对应的下标
} FIELD;
typedef vector<FIELD> FIELDS; //所有列的字段结构体集合
typedef vector<string> ROW; //每一行存储值
private:
vector<ROW> rows_; // 总共存储多个行
FIELDS fields_;
};
class MysqlDB
{
public:
MysqlDB();
~MysqlDB();
void Open(const char* host,
const char* user,
const char* passwd,
const char* db,
unsigned int port);
void Close();
unsigned long long ExecSQL(const char* sql);
MysqlRecordset QuerySQL(const char* sql);
unsigned long long GetInsertId() const;
void StartTransaction();
void Commit();
void Rollback();
private:
MYSQL* mysql_;
};
}
#endif // _MYSQL_DB_H_
MysqlDB.cpp:
#include <exception>
#include "MysqlDB.h"
using namespace std;
using namespace DAL;
MysqlDB::MysqlDB() : mysql_(NULL)
{
}
MysqlDB::~MysqlDB()
{
if (mysql_)
{
Close();
}
}
void MysqlDB::Open(const char* host,
const char* user,
const char* passwd,
const char* db,
unsigned int port)
{
mysql_ = mysql_init(NULL);
if (mysql_ == NULL)
{
string errmsg = mysql_error(mysql_);
throw Exception("DB ERROR:"+errmsg);
}
my_bool reconnect = true;
mysql_options(mysql_, MYSQL_OPT_RECONNECT, &reconnect);
mysql_options(mysql_, MYSQL_SET_CHARSET_NAME, "gbk");
if (!mysql_real_connect(mysql_, host, user,
passwd, db, 0, NULL, 0))
{
string errmsg = mysql_error(mysql_);
Close();
throw Exception("DB ERROR:"+errmsg);
}
}
void MysqlDB::Close()
{
if (NULL != mysql_)
{
mysql_close(mysql_);
mysql_ = NULL;
}
}
MysqlRecordset MysqlDB::QuerySQL(const char* sql)
{
if (mysql_query(mysql_, sql) != 0)
{
//int errno = mysql_errno(mysql_);
string errmsg = mysql_error(mysql_);
throw Exception("DB ERROR:"+errmsg);
}
MYSQL_RES* mysql_res;
mysql_res = mysql_store_result(mysql_);
//得到查询返回的行数
//unsigned long n = mysql_affected_rows(mysql_);
//指向 mysql 的查询字段集
MYSQL_FIELD* mysql_field = NULL;
MysqlRecordset rs;
unsigned int i = 0;
unsigned int nCols = mysql_num_fields(mysql_res);
while ((mysql_field = mysql_fetch_field(mysql_res)) != NULL)
{
MysqlRecordset::FIELD field;
field.name = mysql_field->name;
field.index = i;
++i;
rs.fields_.push_back(field); //压入某个列字段的结构体
}
MYSQL_ROW mysql_row;
while ((mysql_row = mysql_fetch_row(mysql_res)))
{
MysqlRecordset::ROW row(nCols);
for (unsigned int i = 0; i< nCols; ++i)
{
row[i] = mysql_row[i] ? mysql_row[i] : "";
}
rs.rows_.push_back(row); //压入某一行的存储值
}
mysql_free_result(mysql_res);
return rs;
}
unsigned long long MysqlDB::ExecSQL(const char* sql)
{
if (mysql_query(mysql_, sql) != 0)
{
//int errno = mysql_errno(mysql_);
string errmsg = mysql_error(mysql_);
throw Exception("DB ERROR:"+errmsg);
}
return mysql_affected_rows(mysql_);
}
void MysqlDB::StartTransaction()
{
if (mysql_query(mysql_, "START TRANSACTION") != 0)
{
//int errno = mysql_errno(mysql_);
string errmsg = mysql_error(mysql_);
throw Exception("DB ERROR:"+errmsg);
}
}
void MysqlDB::Commit()
{
if (mysql_query( mysql_, "COMMIT") != 0)
{
//int errno = mysql_errno(mysql_);
string errmsg = mysql_error(mysql_);
throw Exception("DB ERROR:"+errmsg);
}
}
void MysqlDB::Rollback()
{
if (mysql_query(mysql_, "ROLLBACK") == 0)
{
//int errno = mysql_errno(mysql_);
string errmsg = mysql_error(mysql_);
throw Exception("DB ERROR:"+errmsg);
}
}
unsigned long long MysqlDB::GetInsertId() const
{
return mysql_insert_id(mysql_); //auto_increment字段
}
具体使用方法就很简单了,包含MysqlDB.h,比如
MysqlDB mysqldb;
mysqldb.open(...);
mysqldb.QuerySQL(...);
...
参考:
《数据库系统概论》
mysql 5.1 参考手册