From cec6937344e179a6db50c6386b24afcff165561f Mon Sep 17 00:00:00 2001
From: zyf <2786722087@qq.com>
Date: 星期三, 02 四月 2025 09:21:04 +0800
Subject: [PATCH] zyf_0402_log
---
Server/张怡帆/code/ConnectMysql.cpp | 300 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 300 insertions(+), 0 deletions(-)
diff --git "a/Server/\345\274\240\346\200\241\345\270\206/code/ConnectMysql.cpp" "b/Server/\345\274\240\346\200\241\345\270\206/code/ConnectMysql.cpp"
new file mode 100644
index 0000000..16f292a
--- /dev/null
+++ "b/Server/\345\274\240\346\200\241\345\270\206/code/ConnectMysql.cpp"
@@ -0,0 +1,300 @@
+#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();
+}
\ No newline at end of file
--
Gitblit v1.8.0