Apache局域网网站制作,长沙网站免费建站,网站服务器使用,上海装修公司排名榜十大品牌1、相关API
执行所有的sql语句都是mysql_query或者mysql_real_query mysql_query无法处理带有特殊字符的sql语句#xff08;如#xff1a;反斜杠0#xff09;mysql_real_query则可以避免#xff0c;一般使用这个。 mysql_affected_rows#xff1a;获取sql语句执行结果影响…1、相关API
执行所有的sql语句都是mysql_query或者mysql_real_query mysql_query无法处理带有特殊字符的sql语句如反斜杠0mysql_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_mapusing 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)\) ENGINEInnoDB DEFAULT CHARSETutf8;;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_mapstring, 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)\) ENGINEInnoDB DEFAULT CHARSETutf8;;// 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_mapstring, 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写一些业务会有多痛苦捂脸
