Windows下MySQL安装配置及libmysql的使用
1、安装配置
请参考：Mysql安装配置
2、API文档
3、示例代码
本示例代码使用了mysql_stmt_xxx接口；
运行本代码需要提前创建好vsdemo数据库以及testsql及testsql2表才可运行；
visual studio 配置
或使用#pragma comment(lib,“libmysql.lib”)添加到代码中
#include <iostream>
#include "mysql.h"
#define STRING_SIZE 50
//UTF-8到GB2312的转换
std::string U2G(const char* utf8)
{
int len = MultiByteToWideChar(CP_UTF8, 0, utf8, -1, NULL, 0);
wchar_t* wstr = new wchar_t[len + 1];
memset(wstr, 0, len + 1);
MultiByteToWideChar(CP_UTF8, 0, utf8, -1, wstr, len);
len = WideCharToMultiByte(CP_ACP, 0, wstr, -1, NULL, 0, NULL, NULL);
char* str = new char[len + 1];
memset(str, 0, len + 1);
WideCharToMultiByte(CP_ACP, 0, wstr, -1, str, len + 1, NULL, NULL);
if (wstr) delete[] wstr;
std::string retStr = str;
if (str) delete[] str;
return retStr;
}
//GB2312到UTF-8的转换
std::string G2U(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;
std::string retStr = str;
if (str) delete[] str;
return retStr;
}
int main()
{
std::cout << "Hello World!\n";
MYSQL *con;
MYSQL_RES *res;
MYSQL_ROW row;
char tmp[400] = {0};
// 用户名
char dbuser[32] = "root";
char dbpasswd[32] = "admin";// 密码
char dbip[32] = "localhost";// IP
char dbname[32] = "vsdemo";// 数据库名称
char tablename[32] = "testsql";// 表名称
int rt;
unsigned int t;
int count = 0;
// 初始化数据库
con = mysql_init((MYSQL*)0);
// 连接数据库
if ((con != nullptr) && mysql_real_connect(con, dbip, dbuser, dbpasswd, dbname, 3306, nullptr, 0))
{
std::cout << "连接成功" << std::endl;
}
else {
std::cout << "连接失败" << std::endl;
return -1;
}
// 使用real_query简单的运行insert语句
//sprintf_s(tmp, "insert into testsql (id,name) values (2,'lisi')");
//rt = mysql_real_query(con, tmp, strlen(tmp));
//if (rt) {
// std::cout << "error making query: " << mysql_error(con) << std::endl;
//}
//else {
// std::cout << "success executed" << std::endl;
// mysql_commit(con);
//}
// 使用STMT方式执行
MYSQL_STMT *stmt;
MYSQL_BIND bind[3];
unsigned long str_length;
unsigned long str_length1;
char name[STRING_SIZE];
char status1[STRING_SIZE];
char status2[STRING_SIZE];
int status3;
MYSQL_STMT *stmt2;
MYSQL_BIND bind2[2];
unsigned long length1;
unsigned long length2;
char info[STRING_SIZE];
char info2[STRING_SIZE];
//std::string info2;
stmt = mysql_stmt_init(con);
stmt2 = mysql_stmt_init(con);
if (!stmt || !stmt2) {
std::cout << "init stmt error" << std::endl;
return -1;
}
char itmp[512] = "insert into testsql (name,status1,status2,status3) VALUES(?,?,'test',?)";
if (mysql_stmt_prepare(stmt, itmp, strlen(itmp))) {
std::cout << "mysql_stmt_prepare error " << mysql_stmt_error(stmt) << std::endl;
return -1;
}
char itmp2[512] = "insert into testsql2 (info,info2) VALUES(?,?)";
if (mysql_stmt_prepare(stmt2, itmp2, strlen(itmp2))) {
std::cout << "mysql_stmt_prepare error " << mysql_stmt_error(stmt2) << std::endl;
return -1;
}
int param_count = mysql_stmt_param_count(stmt);
std::cout << "param_count : " << param_count << std::endl;
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = (char*)name;
bind[0].buffer_length = STRING_SIZE;
bind[0].is_null = 0;
bind[0].length = &str_length;
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = (char*)status1;
bind[1].buffer_length = STRING_SIZE;
bind[1].is_null = 0;
bind[1].length = &str_length1;
bind[2].buffer_type = MYSQL_TYPE_LONG;
bind[2].buffer = (char*)&status3;
bind[2].is_null = 0;
bind[2].length = 0;
memset(bind2, 0, sizeof(bind2));
bind2[0].buffer_type = MYSQL_TYPE_STRING;
bind2[0].buffer = (char*)info;
bind2[0].buffer_length = STRING_SIZE;
bind2[0].is_null = 0;
bind2[0].length = &length1;
bind2[1].buffer_type = MYSQL_TYPE_STRING;
bind2[1].buffer = (char*)info2;
bind2[1].buffer_length = STRING_SIZE;
bind2[1].is_null = 0;
bind2[1].length = &length2;
if (mysql_stmt_bind_param(stmt, bind)) {
std::cout << "mysql_stmt_bind_param error " << mysql_stmt_error(stmt) << std::endl;
}
if (mysql_stmt_bind_param(stmt2, bind2)) {
std::cout << "mysql_stmt_bind_param error " << mysql_stmt_error(stmt) << std::endl;
}
strncpy(name, G2U("张三三").c_str(), STRING_SIZE);
str_length = strlen(name);
strncpy(status1, G2U("这是一个测试字符").c_str(), STRING_SIZE);
str_length1 = strlen(status1);
status3 = 12;
if (mysql_stmt_execute(stmt)) {
std::cout << "mysql_stmt_execute error " << mysql_stmt_error(stmt) << std::endl;
}
strncpy(info, G2U("this is一个测试的info").c_str(), STRING_SIZE);
length1 = strlen(info);
strncpy(info2, G2U("info2也是用来测试的").c_str(), STRING_SIZE);
length2 = strlen(info2);
//info2 = G2U("info2也是用来测试的");
//length2 = info2.size();
if (mysql_stmt_execute(stmt2)) {
std::cout << "mysql_stmt_execute error " << mysql_stmt_error(stmt) << std::endl;
}
strncpy(name, G2U("李世石").c_str(), STRING_SIZE);
str_length = strlen(name);
strncpy(status1, G2U("这还是一个测试文本段").c_str(), STRING_SIZE);
str_length1 = strlen(status1);
status3 = 12;
if (mysql_stmt_execute(stmt)) {
std::cout << "mysql_stmt_execute error " << mysql_stmt_error(stmt) << std::endl;
}
auto affected_rows = mysql_stmt_affected_rows(stmt);
std::cout << "affected_rows: " << std::endl;
if (mysql_stmt_close(stmt)) {
std::cout << "mysql_stmt_close error " << mysql_stmt_error(stmt) << std::endl;
}
memset(tmp, 0, sizeof(tmp));
sprintf_s(tmp, "select * from testsql");
rt = mysql_real_query(con, tmp, strlen(tmp));
if (rt) {
std::cout << "error making query: " << mysql_error(con) << std::endl;
}
else {
std::cout << "success executed " << tmp << std::endl;
}
res = mysql_store_result(con);
while (row = mysql_fetch_row(res)) {
for (t = 0; t < mysql_num_fields(res); ++t) {
std::cout << U2G(row[t]).c_str() << "\t";
}
std::cout << std::endl;
++count;
}
std::cout << "number of rows " << count << std::endl;
mysql_free_result(res);
mysql_close(con);
return 0;
}
4、为什么使用预处理STMT？
上面的示例代码中，我们发现预处理STMT代码又长又易出错；而使用real_query（注释地方）语句简短且方便阅读理解。
解释：
上述的示例代码没法显现出预处理STMT的优势。对于多次执行的语句，预处理执行比直接执行快，主要原因在于，仅对查询执行一次解析操作。在直接执行的情况下，每次执行语句时，均将进行查询。此外，由于每次执行预处理语句时仅需发送参数的数据，从而减少了网络通信量。
预处理机制特点：
故而在我们处理大量数据的时候如批量导入、批量update的时候推荐使用预处理STMT方式。