C++操作mysql

1.首先需要安装mysql连接库，不知道为什么，网上都不说这个，害得我不知道连接库名字，找了半天。

sudo apt-get install libmysql++-dev

不过我这里默认你已经装过了mysql服务器。

2.然后就可以写代码验证一下：

#include <iostream>
#include <mysql/mysql.h>

int main()
{
    return 0;
}

未报错，就说明安装好了。

3.mysql常用函数

(1).MYSQL_RES *mysql_store_result(MYSQL *mysql)

返回一个MYSQL_RES结构，对于成功检索了数据库的每个查询，例如（SELECT，SHOW，DESCRIBE，EXPLAIN，CHECK TABLE等）必须调用mysql_store_result()或mysql_use_result()。对于其他查询，可以调也可以不调。可以调用mysql_fetch_row()函数来获取结果集中的行，一旦完成了对结果集的操作，必须调用mysql_free_result()，防止内存泄漏。

如果查询未返回结果集，mysql_store_result()将返回NULL指针。通过mysql_error()检查错误。

注意：如果查询未返回结果集，mysql_store_result()将返回NULL指针，INSERT语句不会返回结果集，会返回NULL指针。所以不能凭借返回值是否为NULL来判断insert操作是否失败！(今天在这个问题上浪费了半小时，命名query成功了，一直返回NULL，唉)

4.代码

下面是我把mysql的操作封装了一个类，操作mysql用这个类就可以了。

mysql_manager.h

#ifndef _MYSQL_MANAGER_H
#define _MYSQL_MANAGER_H

#include <mysql/mysql.h>

#include <string>
#include <iostream>
#include <vector>
#include <string.h>

#define _DEBUG_

namespace db {
/*
	date: 2016.11.26
	function: manage mysql
*/
class mysql_manager {
public:
	mysql_manager(std::string hosts = "localhost", 
				  std::string user_name = "root", 			 
				  std::string passwd = "vagrant",
				  std::string db_name = "smart_meter", 
				  unsigned int port = 3306);
	~mysql_manager();
public:
	void init_connection();
	void release_connection();
	
	bool is_connected();
	
	bool run_sqlcommand(std::string sql);
	unsigned int insert(std::string sql);   //return the number of affected rows.
	
	std::vector<std::vector<std::string> > get_result() const{
		return result_list_;
	} 
private:
	bool                   is_connected_;
	MYSQL                  mysql_client_;
	char*                  hosts_;
	char*                  user_name_;
	char*                  passwd_;
	char*                  db_name_;
	unsigned int           port_;
	std::vector<std::vector<std::string> > result_list_;
};

}

#endif

下面是.cpp文件：

#include "mysql_manager.h"

#include <assert.h>

using namespace db;

mysql_manager::mysql_manager(std::string hosts, 
							 std::string user_name, 
							 std::string passwd,
				  			 std::string db_name, 
							 unsigned int port)
	: is_connected_(false), port_(port)
{
	hosts_ = new(std::nothrow) char[hosts.length()+1];
	assert(hosts_ != NULL);
	strcpy(hosts_, hosts.c_str());    //use strcpy rather than '='

	user_name_ = new(std::nothrow) char[user_name.length()+1];
	assert(user_name_ != NULL);
	strcpy(user_name_, user_name.c_str());

	passwd_ = new(std::nothrow) char[passwd.length()+1];
	assert(passwd_ != NULL);
	strcpy(passwd_, passwd.c_str());

	db_name_ = new(std::nothrow) char[db_name.length()+1];
	assert(db_name_ != NULL);
	strcpy(db_name_, db_name.c_str());
}

mysql_manager::~mysql_manager()
{
	release_connection();
	delete hosts_;
	delete user_name_;
	delete passwd_;
	delete db_name_;
	hosts_ = user_name_ = passwd_ = db_name_ = NULL;
}

void mysql_manager::release_connection()
{
	mysql_close(&mysql_client_);
/*
	notice: you must call mysql_library_end() when you release the mysql connection, 
			otherwise you will get memory leak
*/
	mysql_library_end();    
	is_connected_ = false;
}

void mysql_manager::init_connection()
{
	assert(!is_connected_);

	mysql_init(&mysql_client_);
	if(!mysql_real_connect(&mysql_client_, hosts_, user_name_, passwd_,   //use mysql_real_connect to check if real connect
						   db_name_, port_, NULL, 0)){
		std::cerr<<"connect to database error."<<mysql_error(&mysql_client_)<<std::endl;  //use mysql_error to print error
	}
#ifdef _DEBUG_
		std::cout<<"mysql conncted."<<std::endl;
#endif
	is_connected_ = true;
}

bool mysql_manager::run_sqlcommand(std::string sql)
{
	assert(is_connected_);
	assert(!sql.empty());

	MYSQL_RES *res;
	MYSQL_ROW row;

	//mysql_real_query
	int retval = mysql_real_query(&mysql_client_, sql.c_str(), (unsigned int)strlen(sql.c_str()));
	if(retval < 0){
		std::cerr<<"error query from database"<<mysql_error(&mysql_client_)<<std::endl;
		return false;
	}

	//mysql_store_result
	res = mysql_store_result(&mysql_client_);   //to store the result
	while((row = mysql_fetch_row(res))){   //fetch row by bow
		std::vector<std::string> vec;   //the first dimension, every time the vec will clear automatically.
		for(int i=0; i<mysql_num_fields(res); ++i)
			vec.push_back(row[i]);
		result_list_.push_back(vec);       //the second dimension, to push_back vec
	}
	/
	mysql_free_result(res);      //!!!!!!!!!!!!!!!
	
	return true;
}

unsigned int mysql_manager::insert(std::string sql)   //return the number of affected rows.
{
	assert(is_connected_);
	assert(!sql.empty());

	int res = mysql_query(&mysql_client_, sql.c_str());
	if(res < 0){
		std::cerr<<"insert to database error."<<mysql_errno(&mysql_client_)<<","
							<<mysql_error(&mysql_client_)<<std::endl;
		return -1;
	}
	else{
		int rows = mysql_affected_rows(&mysql_client_);   //return the number of rows affected
#ifdef _DEBUG_
		std::cout<<"inserted "<<rows<<" rows"<<std::endl;
		return rows;
#endif
	}
}

下面是测试程序：

#include "mysql_manager.h"

#include <iostream>
#include <assert.h>
using namespace std;
using namespace db;

typedef vector<vector<string> >::iterator iterator1;
typedef vector<string>::iterator iterator2;

int main()
{
	vector<vector<string> > res;
	mysql_manager mysql;
	mysql.init_connection();
	mysql.run_sqlcommand("select * from smart_meter.electricity");    //use databaename.tablename
	res = mysql.get_result();
	for(iterator1 it1 = res.begin(); it1!=res.end(); ++it1)
		for(iterator2 it2 = (*it1).begin(); it2!=(*it1).end(); ++it2)
			cout<<*it2<<endl;
	
	int r = mysql.insert("insert into smart_meter.electricity values \
							(06141007, \"HuangWeiYang\", \"123456\", 47)");
	assert(r >= 0);

	mysql.run_sqlcommand("select * from smart_meter.electricity");    //use databaename.tablename
	res = mysql.get_result();
	for(iterator1 it1 = res.begin(); it1!=res.end(); ++it1)
		for(iterator2 it2 = (*it1).begin(); it2!=(*it1).end(); ++it2)
			cout<<*it2<<endl;

	return 0;
}

正如代码中指数所话说的，在调用mysql_store_result()函数完了要调用mysql_free_result()函数，以及在析构时要调用mysql_library_end()函数防止内存泄漏。