使用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()
{
//初始化MYSQL
MYSQL *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 transaction
if (ret != 0) {
printf("mysql_OperationTran query start err: %s\n", mysql_error(mysql));
return ret;
}
//--设置事务为手动提交
ret = mysql_query(mysql, SET_TRAN); //set autocommmit = 0
if (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 1
ret = mysql_AutoTran(mysql); // =再次= 修改事务属性为【自动】commit
if (ret != 0) {
printf("mysql_OperationTran() err:%d\n", ret);
return ret;
}
#else
ret = mysql_OperationTran(mysql); // =再次= 修改事务属性为【手动】commit
if (ret != 0) {
printf("mysql_OperationTran() err:%d\n", ret);
return ret;
}
#endif
ret = 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;
}