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