#ifndef __MYSQL_INTERFACE_H__
#define __MYSQL_INTERFACE_H__
#include "winsock.h"
#include <iostream>
#include <string>
#include "mysql.h"
#include <vector>
#pragma comment(lib, "ws2_32.lib")
#pragma comment(lib, "libmysql.lib")
using namespace std;
class MySQL_Operation
{
public:
MySQL_Operation();
virtual ~MySQL_Operation();
bool connectMySQL(char* server, char* username, char* password, char* database, int port);
bool createDatabase(std::string& dbname);
bool CreateTable(const std::string& query);
void errorIntoMySQL();
bool writeDataToDB(const char * sql);
bool getDatafromDB(string queryStr, std::vector<std::vector<std::string> >& data);
void closeMySQL();
public:
int errorNum; //错误代号
const char* errorInfo; //错误提示
private:
MYSQL m_Mysql; //MySQL对象,必备的一个数据结构
MYSQL *m_pMysql;
MYSQL_RES *result; //用于存放结果 建议用char* 数组将此结果转存
};
#endif
↑为.h ↓为.cpp
#include "stdafx.h"
#include "MySQL_Operation.h"
#include <fstream>
using namespace std;
ofstream fout("C:\\Users\\Bin\\Desktop\\数据库错误提示.txt", ios::app);
//构造函数 初始化各个变量和数据
MySQL_Operation::MySQL_Operation() :
errorNum(0), errorInfo("ok")
{
//1. 调用mysql_library_init 初始化库
if (0 == mysql_library_init(0, nullptr, nullptr))
{
cout << "mysql_library_init succeed" << endl;
}
else
{
cout << "mysql_library_init failed" << endl;
}
//2. 调用mysql_init 初始化MYSQL对象
if (mysql_init(&m_Mysql) != nullptr)
{
cout << "mysql_init succeed" << endl;
}
else
{
cout << "mysql_init failed" << endl;
}
//2.5 调用mysql_options设置mysql 设置字符显示
if (0 == mysql_options(&m_Mysql, MYSQL_SET_CHARSET_NAME, "gbk"))
{
cout << "mysql_options succeed" << endl;
}
else
{
cout << "mysql_options failed" << endl;
}
}
MySQL_Operation::~MySQL_Operation()
{
closeMySQL();
//6.调用mysql_library_end():终止MySQL C API库
mysql_library_end();
}
//连接MySQL
bool MySQL_Operation::connectMySQL(char* server, char* username, char* password, char* database, int port)
{
bool reconnect = true;
while (!FALSE)
{
m_pMysql = mysql_init(&m_Mysql);
if (NULL == m_pMysql)
{
//初始化MySql失败
//g_SimpleLog.LogLess0("数据库连接初始化失败");
AfxMessageBox(_T("数据库连接初始化失败!"));
break;
}
mysql_options(m_pMysql, MYSQL_OPT_RECONNECT, &reconnect);
if (mysql_real_connect(&m_Mysql, server, username, password, database, port, 0, 0) != NULL)
{
AfxMessageBox(_T("连接数据库成功!"));
return true;
}
else
{
AfxMessageBox(_T("连接数据库失败!"));
errorIntoMySQL();
break;
}
}
return false;
}
//判断数据库是否存在,不存在则创建数据库,并打开
bool MySQL_Operation::createDatabase(string& dbname)
{
string queryStr = "create database if not exists ";
queryStr += dbname;
if (0 == mysql_query(&m_Mysql, queryStr.c_str()))
{
queryStr = "use ";
queryStr += dbname;
if (0 == mysql_query(&m_Mysql, queryStr.c_str()))
{
return true;
}
}
errorIntoMySQL();
return false;
}
//判断数据库中是否存在相应表,不存在则创建表
bool MySQL_Operation::CreateTable(const std::string& query)
{
if (0 == mysql_query(&m_Mysql, query.c_str()))
{
return true;
}
errorIntoMySQL();
return false;
}
//写入数据
bool MySQL_Operation::writeDataToDB(const char * sql)
{
if (0 == mysql_real_query(&m_Mysql, sql, strlen(sql)))
{
return true;
}
else
errorIntoMySQL();
return false;
}
//读取数据
bool MySQL_Operation::getDatafromDB(string queryStr, std::vector<std::vector<std::string> >& data)
{
if (0 != mysql_query(&m_Mysql, queryStr.c_str()))
{
errorIntoMySQL();
return false;
}
result = mysql_store_result(&m_Mysql);
int row = mysql_num_rows(result);
int field = mysql_num_fields(result);
MYSQL_ROW line = NULL;
line = mysql_fetch_row(result);
int j = 0;
std::string temp;
while (NULL != line)
{
std::vector<std::string> linedata;
for (int i = 0; i<field; i++)
{
if (line[i])
{
temp = line[i];
linedata.push_back(temp);
}
else
{
temp = "";
linedata.push_back(temp);
}
}
line = mysql_fetch_row(result);
data.push_back(linedata);
}
return true;
}
//错误信息
void MySQL_Operation::errorIntoMySQL()
{
errorNum = mysql_errno(&m_Mysql);
errorInfo = mysql_error(&m_Mysql);
fout << errorNum << "\t" << errorInfo << endl;
fout.clear();
}
//断开连接
void MySQL_Operation::closeMySQL()
{
mysql_close(&m_Mysql);
}
需要加入mysql.h头文件
一定要注意关键字。all属于关键字。表有他就不能做任何事。代码无法些人。也没出现错误。
调用:
//数据库连接跟新建数据库,新建表
void CSiteAanalysisDlg::Mysql_Database()
{
//数据库"本地IP","root","123456","xds",
m_Mysql.connectMySQL("本地IP", "root", "123456", "xds_data_analysis", 3306);
string name = "xds_data_analysis";
m_Mysql.createDatabase(name); //创建数据库
m_Mysql.CreateTable(
"CREATE TABLE `ALL_Entries` ( \
`GET_Page` varchar(255) DEFAULT '', \
`GET_Page_Number` int(255) NOT NULL DEFAULT '0', \
`Site_Browse_Mumber` int(255) NOT NULL DEFAULT '0', \
`Other_Entrance_Mumber` int(255) NOT NULL DEFAULT '0', \
`Directly_Open_Mumber` int(255) NOT NULL DEFAULT '0', \
`Web_Links` varchar(255) DEFAULT '', \
`Web_Links_Mumber` int(255) NOT NULL DEFAULT '0' \
) ENGINE = InnoDB DEFAULT CHARSET = utf8;");//创建表格
m_Mysql.CreateTable(
"CREATE TABLE `ENTER_page` ( \
`GET_Page` varchar(255) DEFAULT '', \
`GET_Page_Number` int(255) NOT NULL DEFAULT '0', \
`Directly_Open_Mumber` int(255) NOT NULL DEFAULT '0', \
`Other_Entrance_Mumber` int(255) NOT NULL DEFAULT '0', \
`Web_Links` varchar(255) DEFAULT '', \
`Web_Links_Mumber` int(255) NOT NULL DEFAULT '0', \
`ID_Time` varchar(255) DEFAULT '' \
) ENGINE = InnoDB DEFAULT CHARSET = utf8; ");//创建表格
MySQL_Insert();
MessageBox(TEXT("键数据库,表,完成"), TEXT("温馨提示"), MB_YESNO | MB_ICONQUESTION);
}
void CSiteAanalysisDlg::MySQL_Insert()
{
//insert into 表名 (列名) values (值);
m_Mysql.writeDataToDB("insert into ALL_Entries (GET_Page,GET_Page_Number,Site_Browse_Mumber,Other_Entrance_Mumber,Directly_Open_Mumber \
,Web_Links,Web_Links_Mumber) values ('dfsgdf','12','18','516','156','www.baidu,com','26')");
m_Mysql.writeDataToDB("insert into ENTER_page (GET_Page,GET_Page_Number,Directly_Open_Mumber,Other_Entrance_Mumber \
,Web_Links,Web_Links_Mumber,ID_Time) values ('dfsgdffsdaf','12','18','516','www.baidu,com','26','100')");
//m_Mysql.writeDataToDB("insert into all(GET_Page, GET_Page_Number) values('123', '123')");
}