#include "ConnectMysql.h"
|
|
// ¹¹Ô캯ÊýʵÏÖ£¬³õʼ»¯³ÉÔ±±äÁ¿
|
ConnectMysql::ConnectMysql()
|
{
|
m_result = nullptr;
|
m_mysqlRow = nullptr;
|
|
m_conn = mysql_init(nullptr); // ³õʼ»¯MySQLÁ¬½Ó¶ÔÏ󣬴«Èënullptr»á×Ô¶¯·ÖÅäÒ»¸öMYSQL¶ÔÏó
|
if (m_conn == nullptr)
|
{
|
cout << "³õʼ»¯Ê§°Ü" << endl;
|
return;
|
}
|
cout << "³õʼ»¯³É¹¦" << endl;
|
}
|
|
// Îö¹¹º¯ÊýʵÏÖ£¬ÊͷŽá¹û¼¯ºÍÊý¾Ý¿âÁ¬½Ó×ÊÔ´
|
ConnectMysql::~ConnectMysql()
|
{
|
freeRes();// ÊͷŽá¹û¼¯
|
if (m_conn != nullptr) {
|
mysql_close(m_conn);
|
m_conn = nullptr;
|
cout << "Êý¾Ý¿âÁ¬½Ó¹Ø±Õ" << endl;
|
}
|
}
|
|
// ÊͷŽá¹û¼¯×ÊÔ´µÄº¯ÊýʵÏÖ
|
void ConnectMysql::freeRes()
|
{
|
if (m_result) {
|
mysql_free_result(m_result);
|
m_result = nullptr;
|
}
|
}
|
|
// ʹÓà Shell ½Å±¾±¸·ÝÊý¾Ý¿â
|
void ConnectMysql::backupCurrentDBUsingShell(const string path)
|
{
|
string user = "root";
|
string passwd = "1";
|
string dbName = "AI_datas";
|
string command = "sh backup_script.sh " + user + " " + passwd + " " + dbName + " " + path;
|
int result = system(command.c_str());
|
if (result != 0) {
|
cerr << "±¸·Ýʧ°Ü!" << endl;//cerr: ÕâÊÇÒ»¸ö±ê×¼Êä³öÁ÷£¬ÓÃÓÚÊä³ö´íÎóÐÅÏ¢.
|
}
|
}
|
|
// Á¬½ÓÊý¾Ý¿âµÄº¯ÊýʵÏÖ
|
bool ConnectMysql::connect(const string user, const string passwd,
|
const string dbName, string ip, const unsigned short port)
|
{
|
|
MYSQL* res = mysql_real_connect(m_conn, ip.c_str(), user.c_str(),
|
passwd.c_str(), dbName.c_str(), port, nullptr, 0); // ʹÓÃmysql_real_connectÁ¬½ÓÊý¾Ý¿â
|
|
if (res == nullptr)
|
{
|
cout << "Êý¾Ý¿âÁ¬½Óʧ°Ü" << endl;
|
return false;
|
}
|
cout << "Êý¾Ý¿âÁ¬½Ó³É¹¦" << endl;
|
|
if (mysql_set_character_set(m_conn, "GBK")) {// ÉèÖÃ×Ö·û¼¯,·ÀÖ¹ÂÒÂë
|
puts("×Ö·û¼¯ÉèÖôíÎó£¡");
|
}
|
else {
|
puts("×Ö·û¼¯ÉèÖóɹ¦£¡");
|
}
|
|
return res != nullptr;
|
}
|
|
// Ö´ÐиüвÙ×÷£¨Ôöɾ¸Ä£©µÄº¯ÊýʵÏÖ
|
bool ConnectMysql::update(const string sql) const
|
{
|
// Ö´ÐÐSQLÓï¾ä£¬³É¹¦·µ»Ø0£¬Ê§°Ü·µ»Ø·Ç0Öµ
|
int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned int>(sql.size()));
|
if (res != 0) {
|
return false;
|
}
|
return true;
|
}
|
|
// Ö´Ðвéѯ²Ù×÷µÄº¯ÊýʵÏÖ
|
bool ConnectMysql::query(const string sql)
|
{
|
freeRes();// ÊͷŽá¹û¼¯
|
|
int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned int>(sql.size())); // Ö´ÐвéѯSQLÓï¾ä
|
if (res != 0) {
|
cout << "²éѯʧ°Ü" << endl;
|
return false;
|
}
|
// »ñÈ¡²éѯ½á¹û¼¯
|
m_result = mysql_store_result(m_conn);
|
if (m_result == nullptr) {
|
cout << "²éѯʧ°Ü" << endl;
|
return false;
|
}
|
return true;
|
}
|
|
// ±éÀú²éѯ½á¹û¼¯µÄº¯ÊýʵÏÖ
|
bool ConnectMysql::getRes()
|
{
|
if (m_result != nullptr) {
|
// »ñÈ¡½á¹û¼¯ÖеÄÏÂÒ»ÐÐÊý¾Ý
|
m_mysqlRow = mysql_fetch_row(m_result);
|
if (m_mysqlRow != nullptr) {
|
return true;
|
}
|
freeRes();
|
}
|
return false;
|
}
|
|
// »ñÈ¡½á¹û¼¯ÖÐÖ¸¶¨×Ö¶ÎÖµµÄº¯ÊýʵÏÖ
|
string ConnectMysql::getValue(const int fieldIndex) const//fieldIndex£º×Ö¶ÎË÷Òý
|
{
|
// »ñÈ¡½á¹û¼¯ÖеÄ×Ö¶ÎÊýÁ¿
|
int fieldCount = mysql_num_fields(m_result);
|
if (fieldIndex >= fieldCount || fieldIndex < 0) {
|
return string();// ·µ»Ø¿Õ×Ö·û´®
|
}
|
// »ñÈ¡Ö¸¶¨×ֶεÄÖµ
|
char* value = m_mysqlRow[fieldIndex];
|
|
// »ñÈ¡¸÷×Ö¶ÎÖµµÄ³¤¶ÈÊý×é
|
unsigned long* len = mysql_fetch_lengths(m_result);// mysql_fetch_lengths:»ñÈ¡ËùÓÐ×Ö¶ÎÖµµÄ³¤¶È
|
unsigned long length = len[fieldIndex];
|
// ·ÀÖ¹Êý¾ÝÖдæÔÚ\0µ¼ÖÂÊý¾Ý¶ªÊ§£¬Ê¹ÓÃÖ¸¶¨³¤¶È¹¹Ôì×Ö·û´®
|
return string(value, length);
|
}
|
|
// »ñÈ¡²éѯ½á¹û¼¯µÄº¯ÊýʵÏÖ
|
vector<vector<string>> ConnectMysql::getQueryResults()
|
{
|
vector<vector<string>> results;
|
if (m_result != nullptr)
|
{
|
int num_fields = mysql_num_fields(m_result);
|
while ((m_mysqlRow = mysql_fetch_row(m_result)))
|
{
|
vector<string> row;//vector<string> ´ú±íÒ»Ðвéѯ½á¹û
|
for (int i = 0; i < num_fields; ++i)
|
{
|
if (m_mysqlRow[i] != nullptr)
|
{
|
row.push_back(m_mysqlRow[i]);
|
}
|
else
|
{
|
row.push_back(""); // ´¦Àí NULL Öµ
|
}
|
}
|
results.push_back(row);
|
}
|
}
|
return results;
|
}
|
|
//// Çл»Êý¾Ý¿âµÄº¯ÊýʵÏÖ
|
//bool ConnectMysql::selectDB(const string dbName) const
|
//{
|
// // Ö´ÐÐÇл»Êý¾Ý¿âµÄ²Ù×÷
|
// int res = mysql_select_db(m_conn, dbName.c_str());
|
// if (res != 0) {
|
// return false;
|
// }
|
// return true;
|
//}
|
|
//// ´´½¨Êý¾Ý¿âµÄº¯ÊýʵÏÖ
|
//bool ConnectMysql::createDB(const string dbName) const
|
//{
|
// // Æ´½Ó´´½¨Êý¾Ý¿âµÄSQLÓï¾ä
|
// string sql = "create database " + dbName + ";";
|
// // Ö´Ðд´½¨Êý¾Ý¿âµÄSQLÓï¾ä
|
// int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned long>(sql.size()));
|
// if (res != 0) {
|
// return false;
|
// }
|
// return true;
|
//}
|
|
// ±¸·Ýµ±Ç°Êý¾Ý¿âµÄº¯ÊýʵÏÖ
|
void ConnectMysql::backupCurrentDB(const string path)
|
{
|
// ²éѯµ±Ç°Êý¾Ý¿âÖеÄËùÓбí
|
string sql = "show tables";
|
int r = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned long>(sql.size()));
|
if (r != 0) {
|
return;
|
}
|
// »ñÈ¡²éѯ½á¹û¼¯
|
MYSQL_RES* tableRes = mysql_store_result(m_conn);
|
for (int i = 0; i < mysql_num_rows(tableRes); ++i) {
|
// »ñÈ¡±íÃû
|
MYSQL_ROW tableName = mysql_fetch_row(tableRes);
|
// ±¸·Ýÿ¸ö±í
|
backupCurrentTable(path, tableName[0]);
|
}
|
}
|
|
// ±¸·Ýµ±Ç°±íµÄº¯ÊýʵÏÖ
|
void ConnectMysql::backupCurrentTable(const string path, const string tableName)
|
{
|
// Æ´½Ó±¸·Ý±íµÄÎļþÃû
|
string file = path + tableName + ".sql";
|
// ´ò¿ªÎļþÓÃÓÚдÈë
|
ofstream ofs(file);
|
if (!ofs.is_open()) {
|
cout << "Îļþ´ò¿ªÊ§°Ü!" << endl;
|
return;
|
}
|
// дÈë±í½á¹¹
|
string showCreate = "show create table " + tableName + ";";//show create table: ²é¿´±í½á¹¹
|
bool res = query(showCreate);
|
if (!res) {
|
return;
|
}
|
if (getRes()) {
|
string writeSQL = getValue(1) + ";\n";
|
ofs.write(writeSQL.c_str(), writeSQL.size());
|
}
|
// дÈë±íÊý¾Ý
|
string sql = "select * from " + tableName + ";";
|
res = query(sql);
|
if (!res) {
|
return;
|
}
|
while (getRes()) {
|
string writeSQL = "insert into `" + tableName + "` values(";
|
for (int i = 0; !getValue(i).empty(); ++i) {
|
if (i != 0) {
|
writeSQL += ",";
|
}
|
// »ñÈ¡×Ö¶ÎÀàÐÍ
|
MYSQL_FIELD* valueType = mysql_fetch_field_direct(m_result, i);//»ñÈ¡µ±Ç°ÁеÄ×Ö¶ÎÐÅÏ¢¡£¸ù¾Ý×Ö¶ÎÀàÐÍÅжÏÊÇ·ñÐèÒª¼ÓÒýºÅ
|
if (valueType->type == MYSQL_TYPE_DECIMAL
|
|| valueType->type == MYSQL_TYPE_TINY
|
|| valueType->type == MYSQL_TYPE_SHORT
|
|| valueType->type == MYSQL_TYPE_LONG
|
|| valueType->type == MYSQL_TYPE_FLOAT
|
|| valueType->type == MYSQL_TYPE_DOUBLE
|
|| valueType->type == MYSQL_TYPE_TIMESTAMP
|
|| valueType->type == MYSQL_TYPE_LONGLONG
|
|| valueType->type == MYSQL_TYPE_INT24
|
|| valueType->type == MYSQL_TYPE_BOOL) {//СÊý£¬ÕûÊý£¬¸¡µãÊý£¬Ê±¼ä£¬³¤ÕûÊý£¬¶ÌÕûÊý£¬²¼¶ûÖµ
|
writeSQL += getValue(i);
|
}
|
else {
|
writeSQL += "'" + getValue(i) + "'";
|
}
|
}
|
writeSQL += ");\n";
|
ofs.write(writeSQL.c_str(), writeSQL.size());
|
}
|
ofs.close();
|
}
|
|
// ¿ªÆôÊÂÎñµÄº¯ÊýʵÏÖ
|
bool ConnectMysql::transaction() const
|
{
|
// ½«ÊÂÎñÌá½»ÉèÖÃΪÊÖ¶¯Ìá½»£¬¿ªÆôÊÂÎñ
|
return mysql_autocommit(m_conn, false);
|
}
|
|
// Ìá½»ÊÂÎñµÄº¯ÊýʵÏÖ
|
bool ConnectMysql::commit() const
|
{
|
// Ìá½»ÊÂÎñ
|
return mysql_commit(m_conn);
|
}
|
|
// »Ø¹öÊÂÎñµÄº¯ÊýʵÏÖ
|
bool ConnectMysql::rollback() const
|
{
|
// »Ø¹öÊÂÎñ
|
return mysql_rollback(m_conn);
|
}
|
|
// Ë¢ÐÂÁ¬½Ó´æ»îÆðʼʱ¼äµãµÄº¯ÊýʵÏÖ
|
void ConnectMysql::refreashAliveTime()
|
{
|
// ¸üÐÂÁ¬½Ó´æ»îÆðʼʱ¼äµã
|
m_alivetime = std::chrono::steady_clock::now();// »ñÈ¡µ±Ç°Ê±¼äµã
|
}
|
|
// »ñÈ¡Á¬½Ó´æ»îʱ³¤µÄº¯ÊýʵÏÖ
|
ll ConnectMysql::getAliveTime()
|
{
|
// ¼ÆËãÁ¬½Ó´æ»îʱ³¤£¬×ª»»ÎªºÁÃë
|
auto now = std::chrono::steady_clock::now();
|
std::chrono::duration<long long, std::milli> res = std::chrono::duration_cast<std::chrono::milliseconds>(now - m_alivetime);
|
return res.count();
|
}
|
