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++写一些业务会有多痛苦，捂脸！