調用API對Mysql資料庫實現增刪改查以及事務實現

標籤：mys   sql   res   long   行資料   增刪改   cas   提交   varchar   

使用API實現增刪改查用戶端代碼:

#include <stdio.h>#include <mysql.h>#include <string.h>#include <unistd.h>#define _HOST_NAME_ "127.0.0.1"#define _ACT_NAME_"root"#define _ACT_PWD_"123"#define _DB_NAME_"scott"void showResult(MYSQL_RES * result){ MYSQL_FIELD *field; int numfield = mysql_num_fields(result); while(field = mysql_fetch_field(result)) { printf("%-7s\t",field->name); } printf("\n--------------------------------------------------------------\n");  MYSQL_ROW row; while (row = mysql_fetch_row(result)) { int i; for(i = 0;i < numfield;i++){ printf("%-7s\t",row[i]); }  printf("\n"); } printf("--------------------------------------------------------------\n");}int main(){//初始化MYSQLMYSQL *mysql = mysql_init(NULL);if(mysql == NULL){printf("init err\n");return -1;}//建立串連mysql = mysql_real_connect(mysql,_HOST_NAME_,_ACT_NAME_,_ACT_PWD_,_DB_NAME_,0,NULL,0);if(mysql == NULL){fprintf(stderr, "Failed to connect to database: Error: %s\n",          mysql_error(mysql));return -2;}//設定字元集mysql_set_character_set(mysql, "utf8");printf("connected ok!\n");char rsql[512] ={0};while(1){write(STDOUT_FILENO,"sql>",4);memset(rsql,0,sizeof(rsql));read(STDIN_FILENO,rsql,sizeof(rsql));//判斷是quit則退出if(strncasecmp(rsql,"quit",4) == 0){printf("bye!\n");break;}else if( mysql_query(mysql,rsql) ){fprintf(stderr, "Failed to query to database: Error: %s\n",          mysql_error(mysql));continue;}//擷取記錄集MYSQL_RES *result = mysql_store_result(mysql);if(result != NULL){showResult(result);//釋放記錄集mysql_free_result(result);}else{printf("Query OK, %ld row affected \n",(long) mysql_affected_rows(mysql));}}mysql_close(mysql);return 0;}

　

 

使用API操作事務:

//mysql中的事務#include <stdio.h>#include <stdlib.h>#include <string.h>#include "mysql.h"#define SET_TRAN"SET AUTOCOMMIT=0"  //手動commit————手動commit#define UNSET_TRAN"SET AUTOCOMMIT=1"//自動commit#define _HOST_ "127.0.0.1"#define _USER_ "root"#define _PASSWD_ "123"#define _DBNAME_ "scott"//設定事務為手動提交int mysql_OperationTran(MYSQL *mysql)  {//--開啟事務int ret = mysql_query(mysql, "start transaction");  //開啟一次事務 start transactionif (ret != 0) {printf("mysql_OperationTran query start err: %s\n", mysql_error(mysql));return ret;}//--設定事務為手動提交ret = mysql_query(mysql, SET_TRAN);//set　autocommmit = 0if (ret != 0) {printf("mysql_OperationTran query set err: %s\n", mysql_error(mysql));return ret;}return ret;}//設定事務為自動認可int mysql_AutoTran(MYSQL *mysql){//--開啟事務int ret = mysql_query(mysql, "start transaction");  if (ret != 0) {printf("mysql_AutoTran query start err: %s\n", mysql_error(mysql));return ret;}//--設定事務為自動認可ret = mysql_query(mysql, UNSET_TRAN);  //"set autocommit = 1"if (ret != 0) {printf("mysql_AutoTran query set err: %s\n", mysql_error(mysql));return ret;}return ret;}//執行commit，手動提交事務int mysql_Commit(MYSQL *mysql){int ret = mysql_query(mysql, "COMMIT"); //提交if (ret != 0) {printf("commit err: %s\n", mysql_error(mysql));return ret;}return ret;}//執行rollback，復原事務int mysql_Rollback(MYSQL *mysql){int ret = mysql_query(mysql, "ROLLBACK");if (ret != 0) {printf("rollback err: %s\n", mysql_error(mysql));return ret;}return ret;}#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"#define CREATE_SAMPLE_TABLE "CREATE TABLE test_table(col1 INT,                                                 col2 VARCHAR(10),                                                 col3 VARCHAR(10))"                                                                                       #define sql01 "INSERT INTO test_table(col1,col2,col3) VALUES(10, ‘AAA‘, ‘A1‘)"#define sql02 "INSERT INTO test_table(col1,col2,col3) VALUES(20, ‘BBB‘, ‘B2‘)"#define sql03 "INSERT INTO test_table(col1,col2,col3) VALUES(30, ‘CCC‘, ‘C3‘)"#define sql04 "INSERT INTO test_table(col1,col2,col3) VALUES(40, ‘DDD‘, ‘D4‘)"int main(void){int ret = 0;MYSQL *mysql = mysql_init(NULL);mysql = mysql_real_connect(mysql, _HOST_, _USER_, _PASSWD_, _DBNAME_, 0, NULL, 0);if (mysql == NULL) {ret = mysql_errno(mysql);printf("func mysql_real_connect() err：%d\n", ret);return ret;} printf(" --- connect ok......\n");//執行刪除表if (mysql_query(mysql, DROP_SAMPLE_TABLE)) {  fprintf(stderr, " DROP TABLE failed\n");  fprintf(stderr, " %s\n", mysql_error(mysql));  exit(0);}//執行建立表if (mysql_query(mysql, CREATE_SAMPLE_TABLE)) {  fprintf(stderr, " CREATE TABLE failed\n");  fprintf(stderr, " %s\n", mysql_error(mysql));  exit(0);}ret = mysql_OperationTran(mysql); //開啟事務，並修改事務屬性為手動commit if (ret != 0) {printf("mysql_OperationTran() err:%d\n", ret);return ret;}ret = mysql_query(mysql, sql01);//向表中插入第一行資料 ‘AAA’if (ret != 0) {printf("mysql_query() err:%d\n", ret);return ret;}ret = mysql_query(mysql, sql02);//向表中插入第二行資料 ‘BBB’if (ret != 0) {printf("mysql_query() err:%d\n", ret);return ret;}ret = mysql_Commit(mysql); //手動提交事務if (ret != 0) {printf("mysql_Commit() err:%d\n", ret);return ret;}//////////AAA BBB  進去了。#if 1ret = mysql_AutoTran(mysql); // =再次= 修改事務屬性為【自動】commitif (ret != 0) {printf("mysql_OperationTran() err:%d\n", ret);return ret;}#else ret = mysql_OperationTran(mysql); // =再次= 修改事務屬性為【手動】commitif (ret != 0) {printf("mysql_OperationTran() err:%d\n", ret);return ret;}#endifret = mysql_query(mysql, sql03);//向表中插入第三行資料 ‘CCC’if (ret != 0) {printf("mysql_query() err:%d\n", ret);return ret;}ret = mysql_query(mysql, sql04);//向表中插入第四行資料 ‘DDD’if (ret != 0) {printf("mysql_query() err:%d\n", ret);return ret;}ret = mysql_Rollback(mysql);//直接rollback操作if (ret != 0) {printf("mysql_Rollback() err:%d\n", ret);return ret;}//rollback操作是否能回退掉CCC、DDD的值，取決於事務屬性。mysql_close(mysql);return 0;}

　　

調用API對Mysql資料庫實現增刪改查以及事務實現