MySQL增、删、改、查基础操作(C++)

系统平台:Centos7

MySQL版本:5.7.19

连接MySQL数据库

复制代码
MySQL::MySQL(string host, string user, string passwd, string db, unsigned port)
{
    m_host = host;
    m_user = user;
    m_passwd = passwd;
    m_dbname = db;
    m_port = port;

    mysql_init(&connect);
}

bool MySQL::connectDB()
{
    if (!mysql_real_connect(&connect, m_host.c_str(), m_user.c_str(), m_passwd.c_str(), m_dbname.c_str(), m_port, NULL, 0))
    {
        cout << "mysql connect error with " << mysql_errno(&connect) << endl;
        return false;
    }
    return true;
}
复制代码

增:

复制代码
bool MySQL::Insert(string table, map<string, string> values)
{
    sql.clear();
    if (values.size() == 0)
    {
        cout << "Input Error!!!" << endl;
        return false;
    }
    else
    {
        sql = "INSERT INTO " + table + "(";
        auto iter = values.begin();
        while (iter != values.end())
        {
            sql += iter->first + ',';
            iter++;
        }
        sql = sql.substr(0, sql.rfind(',')) + ") VALUES(";

        iter = values.begin();
        while (iter != values.end())
        {
            sql += string("\"") + iter->second + "\",";
            iter++;
        }
        sql = sql.substr(0, sql.rfind(',')) + ");";
        ret = mysql_query(&connect, sql.c_str());
        if (ret != 0)
        {
            cout << "mysql query error with " << ret << " Reason: " << mysql_error(&connect) << endl;
            return false;
        }
        ret = mysql_query(&connect, "commit");
        return true;
    }
}
复制代码

删:

复制代码
bool MySQL::Delete(string table, map<string, string> values)
{
    sql.clear();
    if (values.size() == 0)
    {
        cout << "Input Error!!!" << endl;
        return false;
    }
    else
    {
        sql = "DELETE FROM " + table + " WHERE ";
        auto iter = values.begin();
        while (iter != values.end())
        {
            sql += iter->first + " = " + iter->second + " and ";
            iter++;
        }
        sql = sql.substr(0, sql.rfind("and")) + ";";
        ret = mysql_query(&connect, sql.c_str());
        if (ret != 0)
        {
            cout << "mysql query error with " << ret << " Reason: " << mysql_error(&connect) << endl;
            return false;
        }
        ret = mysql_query(&connect, "commit");
        return true;
    }
}
复制代码

改:

复制代码
bool MySQL::Update(string table, map<string, string> values)
{
    sql.clear();
    if (values.size() == 0)
    {
        cout << "Input Error!!!" << endl;
        return false;
    }
    else
    {
        sql = "UPDATE " + table + " SET ";
        auto iter = values.begin();
        while (iter != values.end())
        {
            sql += iter->first + " = \"" + iter->second + "\" and ";
            iter++;
        }
        sql = sql.substr(0, sql.rfind("and")) + ";";
        ret = mysql_query(&connect, sql.c_str());
        if (ret != 0)
        {
            cout << "mysql query error with " << ret << " Reason: " << mysql_error(&connect) << endl;
            return false;
        }
        ret = mysql_query(&connect, "commit");
        return true;
    }
}
复制代码

查:

复制代码
bool MySQL::Select(string table, map<string, string> values)
{
    sql.clear();

    if(values.size() == 0)
        sql = "SELECT * FROM " + table + " ;";
    else
    {
        sql = "SELECT * FROM " + table + " where ";
        auto iter = values.begin();
        while (iter != values.end())
        {
            sql += iter->first + " = \"" + iter->second + "\" and ";
            iter++;
        }
        sql = sql.substr(0, sql.rfind("and")) + ";";
    }

    ret = mysql_query(&connect, sql.c_str());
    if (ret != 0)
    {
        cout << "mysql query error with " << ret << " Reason: " << mysql_error(&connect) << endl;
        return false;
    }

    showDetails(connect);

    return true;
}
复制代码

自己编写sql语句查询:

复制代码
bool MySQL::Query(string cmd)
{
    size_t pos = cmd.find("select");
    if (pos < 0 || pos > cmd.size())
    {
        sql = cmd;
        ret = mysql_query(&connect, sql.c_str());
        if (ret != 0)
        {
            cout << "mysql query error with " << ret << " Reason: " << mysql_error(&connect) << endl;
            return false;
        }
        return true;
    }
    else
    {
        ret = mysql_query(&connect, sql.c_str());
        if (ret != 0)
        {
            cout << "mysql query error with " << ret << " Reason: " << mysql_error(&connect) << endl;
            return false;
        }
        showDetails(connect);
        return true;
    }
}
复制代码

MySQL执行完查询语句后会返回一个结果集,使用showDetails()函数输出结果集:

复制代码
void MySQL::showDetails(MYSQL connect)
{
    int col = mysql_field_count(&connect);

    res = mysql_store_result(&connect);
    if (res == NULL)
    {
        cout << "mysql store result error with " << mysql_error(&connect) << endl;
        exit(-1);
    }

    fields = mysql_fetch_fields(res);
    for (int i = 0; i < col; ++i)
        cout << setw(10) << fields[i].name << " | ";
    cout << endl;

    while ((row = mysql_fetch_row(res)))
    {
        for (int i = 0; i < col; ++i)
        {
            if (row[i] == NULL)
                cout << setw(10) << "NULL | ";
            else
                cout << setw(10) << row[i] << " | ";
        }
        cout << endl;
    }
}
复制代码

完整代码详见GitHub:https://github.com/MasterMeng/MySQLOperate

posted @   落雷  阅读(404)  评论(0编辑  收藏  举报
编辑推荐:
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行:
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· AI技术革命,工作效率10个最佳AI工具
点击右上角即可分享
微信分享提示