mysql 5.7库文件和测试代码

#include <stdio.h> #include <stdlib.h> #include <string> #include <iostream> #include <sstream> #include <vector> #include <map> #include "winsock2.h" #include "mysql.h" using namespace std; MYSQL* myConn = NULL; MYSQL_RES* myRes = NULL; std::vector<std::string> m_vecFiled;//字段列表数据 std::map<std::string, std::string> m_mapFiled;//字段数据 void GetMysqlFields() { m_vecFiled.clear(); m_mapFiled.clear(); //取得结果集中字段的数目 int totalFields = mysql_num_fields(myRes); for (int i = 0; i < totalFields; ++i) { //返回结果集的列，重复调用以检索结果集所有列的信息，没有剩余字段时，返回NULL // 默认提取下一个尚未被取得的字段 //每次执行新的select查询时将复位 const char* name = mysql_fetch_field(myRes)->name; std::string fieldName = name; m_vecFiled.push_back(fieldName); } MYSQL_ROW mysqlRow; //从结果集取得一行作为枚举数组 while ((mysqlRow = mysql_fetch_row(myRes)) != NULL) { for (int i = 0; i < totalFields ; ++i) { //cout << m_vecFiled[i] << " = " << mysqlRow[i] << "\n"; m_mapFiled.insert(std::make_pair(m_vecFiled[i], mysqlRow[i])); } } } int RunMysqlStr(const std::string& str) { if (str.size() < 1) { return -1; } int ret = mysql_real_query(myConn, str.c_str(), str.size()); if (ret != 0) { return ret; } //非查询语句直接返回 auto result = str.find("select"); if (str.find("select") == -1 && str.find("SELECT") == -1) { return ret; } if (myRes) { mysql_free_result(myRes); myRes = NULL; } myRes = mysql_store_result(myConn); if (myRes == nullptr) { return -3; } GetMysqlFields(); return 0; } void CreateTable() { std::string sql = ""; sql = R"( CREATE TABLE `player` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `coin` bigint(20) NOT NULL, `power` int(11) NOT NULL, `otherdata` blob NOT NULL, PRIMARY KEY (`id`) ); )"; auto err = RunMysqlStr(sql); if (err != 0) { printf("CREATE TABLE SUC !\n"); } else { printf("CREATE TABLE FAIL !\n"); } } bool RollbackTransaction() { std::string str = "ROLLBACK;"; int err = RunMysqlStr(str); if (err != 0) { printf("ROLLBACK FAIL !\n"); return false; } printf("ROLLBACK SUC !\n"); return true; } bool CommitTransaction() { std::string str = "COMMIT;"; int err = RunMysqlStr(str); if (err != 0) { printf("COMMIT FAIL !\n"); return false; } printf("COMMIT SUC !\n"); return true; } bool StartTransaction() { std::string str = "START TRANSACTION;"; int err = RunMysqlStr(str); if (err != 0) { RollbackTransaction(); printf("TRANSACTION FAIL !\n"); return false; } printf("TRANSACTION SUC !\n"); return true; } std::string EscapeString(const std::string& strSrc) { //规定至少开辟str长度*2 + 1的空间存储转换后的字符串 size_t iDstSize = strSrc.size() * 2 + 1; char* pcDst = new char[iDstSize]; //转换mysql字符串，防止sql注入 int len = mysql_real_escape_string(myConn, pcDst, strSrc.c_str(), strSrc.size()); if (len <= 0) { return ""; } std::string str(pcDst, len); delete[] pcDst; pcDst = nullptr; return "\"" + str + "\""; } int EscapeString(const int& value) { return value; } int WriteMysql() { if (!StartTransaction()) { return -1; } const int id = 8; const std::string name = "jack"; const int coin = 999; const int power = 2222; const std::string otherdata = "other"; std::stringstream str; str.str(""); str << "INSERT INTO player ("; str << "id, name, coin, power, otherdata"; str << ") VALUES ("; str << EscapeString(id) << ","; str << EscapeString(name) << ","; str << EscapeString(coin) << ","; str << EscapeString(power) << ","; str << EscapeString(otherdata); str << ") ON DUPLICATE KEY UPDATE "; str << "name = " << EscapeString(name) << ","; str << "coin = " << EscapeString(coin) << ","; str << "power = " << EscapeString(power) << ","; str << "otherdata = " << EscapeString(otherdata) << ";"; if (RunMysqlStr(str.str()) != 0) { RollbackTransaction(); return -2; } // 事务提交 if (!CommitTransaction()) return -3; return 0; } int ReadMysql() { std::stringstream str; const int id = 6; str << "SELECT * FROM player WHERE id = " << EscapeString(id) << ";"; //str << "SELECT * FROM player " << ";"; string name = ""; if (RunMysqlStr(str.str()) != 0) { printf("ReadMysql err! \n"); return -1; } printf("ReadMysql suc! \n"); for (auto& it : m_mapFiled) { cout << it.first << " = " << it.second.data() << "\n"; } cout << m_mapFiled.find("name")->second.data() << "\n"; return 0; } void ConnectMysql() { const char* ip = "127.0.0.1"; const char* username = "root"; const char* password = "123456"; const char* dbname = "mytest"; const int port = 3306; myConn = mysql_init(NULL); char value = 1; int time = 5; mysql_options(myConn, MYSQL_OPT_RECONNECT, &value); mysql_options(myConn, MYSQL_OPT_CONNECT_TIMEOUT, &time); mysql_options(myConn, MYSQL_SET_CHARSET_NAME, "utf8"); if (mysql_real_connect(myConn, ip, username, password, dbname, port, NULL, 0)) { printf("connect success !\n"); CreateTable(); } } int main() { mysql_library_init(0, NULL, NULL); ConnectMysql(); WriteMysql(); ReadMysql(); //释放结果资源 if (myRes) { mysql_free_result(myRes); } mysql_close(myConn);//关闭MySQL连接 mysql_library_end();//关闭MySQL库 system("pause"); return 0; }