C++：MySQL数据库的增删改（三）

1、相关API

• 执行所有的sql语句都是mysql_query或者mysql_real_query
  • mysql_query无法处理带有特殊字符的sql语句（如：反斜杠0）
  • mysql_real_query则可以避免，一般使用这个。
• mysql_affected_rows：获取sql语句执行结果影响的行数
• mysql_insert_id：插入数据返回主键id值
• mysql_num_rows：获取select语句查询结果有多少条

my_ulonglong mysql_affected_rows(MYSQL *mysql);

my_ulonglong mysql_insert_id(MYSQL *mysql);

my_ulonglong mysql_num_rows(MYSQL_RES *res);

2、创增删改

2.1、连接MySQL数据库

#include <iostream>
#include <mysql/mysql.h>
#include <cstring>
#include <sstream>
#include <string>
#include <unordered_map>

using namespace std;
int main(int argc, char *argv[])
{
    MYSQL mysql;
    // 初始化mysql结构体并且初始化服务连接环境
    mysql_init(&mysql);
    const char *host = "127.0.0.1";
    const char *user = "root";
    const char *password = "123456";
    const char *db = "cpp";
    int timeout = 3;
    // 连接超时时长设置
    mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);

    // 断开重连设置
    int reconnect = 1;
    mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);

    // MySQL连接建立
    if(!mysql_real_connect(&mysql, host, user, password, db, 3306, 0, 0)){
        std::cout << "mysql connect failed!" << mysql_error(&mysql) << std::endl;
    }
    else{
        std::cout << "mysql connect " << host << " success!" << std::endl;
    }

    mysql_close(&mysql);
    return 0;
}

2.1、创建一张表

void create_table(MYSQL &mysql)
{
    string sql = "CREATE TABLE IF NOT EXISTS `user` (\
                   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\
                   `username` varchar(255) NOT NULL,\
                   `password` varchar(255) NOT NULL,\
                   PRIMARY KEY (`id`)\
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    if(mysql_real_query(&mysql, sql.c_str(), sql.length()) != 0){
        cout << "mysql_query failed!" << endl;
    }
}

2.2、插入数据

void insert_data(MYSQL &mysql)
{
    string sql = "insert into `user`(`username`, `password`) values ('Splay', '123456');";
    if(mysql_real_query(&mysql, sql.c_str(), sql.length()) == 0){
        int affect_count = mysql_affected_rows(&mysql);
        cout << "insert data success, affect count = " << affect_count << ", id = " << mysql_insert_id(&mysql) << endl;
    }
    else{
        cout << "insert data failed! sql = " << sql << ", error msg = " << mysql_error(&mysql) << endl;
    }

    for(int i = 2;i <= 1000;i++){
        stringstream ss;
        ss << "insert into `user`(`username`, `password`) values ('Splay', '" << i << "_123456');";
        sql = ss.str();
        int insert_result = mysql_real_query(&mysql, sql.c_str(), sql.length());
        if(insert_result == 0){
            int affect_count = mysql_affected_rows(&mysql);
            cout << "insert data success, affect count = " << affect_count << ", id = " << mysql_insert_id(&mysql) << endl;
        }
        else{
            cout << "insert data failed! sql = " << sql << ", error msg = " << mysql_error(&mysql) << endl;
        }
    }
}

2.3、更改数据

void update_data(MYSQL &mysql)
{
//    string sql = "update `user` set `username` = 'Admin', `password` = 'admin' where `id` = 1";
//    int update_result = mysql_real_query(&mysql, sql.c_str(), sql.length());
    unordered_map<string, string> update_map;
    update_map["username"] = "Admin";
    update_map.insert(make_pair("password", "hello"));
    string sql = "update `user` set ";
    string condition = "where id < 10";
    for(auto it = update_map.begin();it != update_map.end();it++){
        sql += "`" + it->first + "` = '" + it->second + "', ";
    }
    sql += "`id` = id ";
    sql += condition;
    int update_result = mysql_real_query(&mysql, sql.c_str(), sql.length());
    if(update_result == 0){
        int affect_count = mysql_affected_rows(&mysql);
        cout << "update data success, affect count = " << affect_count << endl;
    }
    else{
        cout << "update data failed! sql = " << sql << ", error msg = " << mysql_error(&mysql) << endl;
    }
}

2.4、删除和清空

void delete_data_or_table(MYSQL& mysql)
{
    string sql = "delete from `user` where id = 1000";              // 删除id = 1000的数据
//    string sql = "truncate table `user`";                           // 清空整张表，主键从0开始
//    string sql = "drop table `user`";                               // 删除所有数据和整张表
    int delete_result = mysql_real_query(&mysql, sql.c_str(), sql.length());
    if(delete_result == 0){
        int affect_count = mysql_affected_rows(&mysql);
        cout << "delete data success, affect count = " << affect_count << endl;
    }
    else{
        cout << "delete data failed! sql = " << sql << ", error msg = " << mysql_error(&mysql) << endl;
    }
}

3、同时执行多条sql语句

• C/C++也提供了通知允许多条语句的执行，查询、执行、结果集等

• 需要再创建连接是在最后的clientflag参数指定，默认并不支持

#define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt support */
#define CLIENT_MULTI_RESULTS    (1UL << 17) /* Enable/disable multi-results */
#define CLIENT_PS_MULTI_RESULTS (1UL << 18) /* Multi-results in PS-protocol */
#define CLIENT_MULTI_QUERIES    CLIENT_MULTI_STATEMENTS    

mysql_real_connect(&mysql, host, user, password, db, 3306, 0, CLIENT_MULTI_QUERIES);

3.1、案例使用代码

• mysql_field_count：可以获取select查询到的字段数目，通过查询到的字段数目可以判断是否是select操作
• mysql_next_result：传入结果集MYSQL_RES *指针，返回-1表示没有下一条的执行结果反馈了，返回0表示有。

// MySQL连接建立
    if(!mysql_real_connect(&mysql, host, user, password, db, 3306, 0, CLIENT_MULTI_QUERIES)){
        std::cout << "mysql connect failed!" << mysql_error(&mysql) << std::endl;
    }
    else{
        std::cout << "mysql connect " << host << " success!" << std::endl;
    }

    // 1. 删除表、创建表
    string sql = "DROP TABLE IF EXISTS `user`;\
                   CREATE TABLE IF NOT EXISTS `user` (\
                   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\
                   `username` varchar(255) NOT NULL,\
                   `password` varchar(255) NOT NULL,\
                   PRIMARY KEY (`id`)\
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    // 2. 增加1000条数据
    for(int i = 1;i <= 1000;i++){
        stringstream ss;
        ss << "insert into `user`(`username`, `password`) values ('Splay', '" << i << "_123456');";
        sql += ss.str();
    }

    // 3. 更改数据
    unordered_map<string, string> update_map;
    update_map["username"] = "Admin";
    update_map.insert(make_pair("password", "hello"));
    sql += "update `user` set ";
    for(auto it = update_map.begin();it != update_map.end();it++){
        sql += "`" + it->first + "` = '" + it->second + "', ";
    }
    sql += "`id` = id where id < 10;";

    // 4. 删除数据
    sql += "delete from `user` where id < 10;";              // 删除id = 1000的数据


    // 5. 查询数据
    sql += "select *from `user`;";

    int execute_result = mysql_real_query(&mysql, sql.c_str(), sql.length());
    if(execute_result != 0){
        cout << "execute multi statement error!" << mysql_error(&mysql) << endl;
    }
    cout << "mysql_next_result(&mysql) = " << mysql_next_result(&mysql) << endl;
    do{
        MYSQL_RES *result = mysql_store_result(&mysql);
        if(result){							// 结果集不为空表示select语句获得了查询结果
            cout << "select get result rows = " << mysql_num_rows(result) << endl;
            mysql_free_result(result);
        }
        else{
            if(mysql_field_count(&mysql)){			 // select 有字段但是没有结果 ===> 查询出错
                cout << "Not Retrieve Result!" << endl;
            }
            else{				// update、delete、insert、truncate、drop、create...
                cout << "execute affect rows = " << mysql_affected_rows(&mysql) << endl;
            }
        }
    }while(mysql_next_result(&mysql) == 0);
    cout << "mysql_next_result(&mysql) = " << mysql_next_result(&mysql) << endl;

4、总结

C/C++操纵数据库的方式便捷性太差，虽然JDBC的也很烂，但是JDBC有开源的ORM框架（mybatis、jooq、hiberate、springdata…），不敢想象如果全裸使用C/C++写一些业务会有多痛苦，捂脸！