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, ))

    {

        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() == )

    {

        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(, sql.rfind(',')) + ") VALUES(";

        iter = values.begin();

        while (iter != values.end())

        {

            sql += string("\"") + iter->second + "\",";

            iter++;

        }

        sql = sql.substr(, sql.rfind(',')) + ");";

        ret = mysql_query(&connect, sql.c_str());

        if (ret != )

        {

            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() == )

    {

        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(, sql.rfind("and")) + ";";

        ret = mysql_query(&connect, sql.c_str());

        if (ret != )

        {

            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() == )

    {

        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(, sql.rfind("and")) + ";";

        ret = mysql_query(&connect, sql.c_str());

        if (ret != )

        {

            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() == )

        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(, sql.rfind("and")) + ";";

    }

    ret = mysql_query(&connect, sql.c_str());

    if (ret != )

    {

        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 <  || pos > cmd.size())

    {

        sql = cmd;

        ret = mysql_query(&connect, sql.c_str());

        if (ret != )

        {

            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 != )

        {

            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(-);

    }

    fields = mysql_fetch_fields(res);

    for (int i = ; i < col; ++i)

        cout << setw() << fields[i].name << " | ";

    cout << endl;

    while ((row = mysql_fetch_row(res)))

    {

        for (int i = ; i < col; ++i)

        {

            if (row[i] == NULL)

                cout << setw() << "NULL | ";

            else

                cout << setw() << row[i] << " | ";

        }

        cout << endl;

    }

}

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

巴特西

MySQL增、删、改、查基础操作（C++）

最新文章

热门文章