C语言操作mysql数据库

从mysql数据库访问安全性考虑,stmt预处理更加方便,然而C语言与Java语言不同,数据绑定时需要大量重复冗余的操作.而且多表查询会变的更加繁琐,所以从解决数据绑定与简化多表查询的复杂逻辑入手,作了封装处理.
一.那么首先需要了解下mysql stmt操作数据库的几个重要步骤.
1.引入mysql的头文件,并声明需要的变量.这里不再赘述.
2.连接mysql数据库.部分代码如下
#define HOST “localhost” //主机
#define USER “root” //mysql用户,非主机
#define PASSWD “12345678” //密码
#define DBNAME “mydb” //库名
/初始化mysql句柄/
MYSQL* init_mysql()
{
return mysql_init(NULL);
}

/连接mysql数据库/
MYSQL* connect_db(MYSQL conn_prt,char host,char* user,char* pwd,char* db)
{
/假设我的云服务器IP为118.89.20.60，密码为123456,进入的数据库名字为zje/
conn_prt = mysql_real_connect(conn_prt,host,user,
pwd,db,0,NULL,0);
if(conn_prt==NULL)
{
printf(“failed to connect:%s\n”,mysql_error(conn_prt));
return NULL;
}
printf(“connect success!\n”);
return conn_prt;
}
3.stmt预处理变量声明以及准备工作
MYSQL_STMT *stmt;
MYSQL_BIND bind[4];
MYSQL_RES *prepare_meta_result;
MYSQL_TIME ts;
unsigned long length[4];
int param_count, column_count, row_count;
short small_data;
int int_data;
char str_data[STRING_SIZE];
bool is_null[4];
bool error[4];

/* Prepare a SELECT query to fetch data from test_table */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
{
fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
4.stmt绑定结果或者绑定参数
①绑定结果
if (mysql_stmt_execute(stmt))
{ fprintf(stderr, " mysql_stmt_execute(), failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Fetch result set meta information */
prepare_meta_result = mysql_stmt_result_metadata(stmt);if (!prepare_meta_result){ fprintf(stderr, " mysql_stmt_result_metadata(), \ returned no meta information\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

memset(bind, 0, sizeof(bind));
/* INTEGER COLUMN */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= &is_null[0];
bind[0].length= &length[0];
bind[0].error= &error[0];

/* STRING COLUMN */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= &is_null[1];
bind[1].length= &length[1];
bind[1].error= &error[1];

/* SMALLINT COLUMN */
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer= (char *)&small_data;
bind[2].is_null= &is_null[2];
bind[2].length= &length[2];
bind[2].error= &error[2];

/* TIMESTAMP COLUMN */
bind[3].buffer_type= MYSQL_TYPE_TIMESTAMP;
bind[3].buffer= (char *)&ts;
bind[3].is_null= &is_null[3];
bind[3].length= &length[3];
bind[3].error= &error[3];

/* Bind the result buffers /
if (mysql_stmt_bind_result(stmt, bind))
{
fprintf(stderr, " mysql_stmt_bind_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/ Now buffer all results to client (optional step) /
if (mysql_stmt_store_result(stmt)){ fprintf(stderr, " mysql_stmt_store_result() failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}
②绑定参数
memset(bind, 0, sizeof(bind));
/ INTEGER PARAM /
/ This is a number type, so there is no need
to specify buffer_length */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= 0;
bind[0].length= 0;

/* STRING PARAM */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= 0;
bind[1].length= &str_length;

/* SMALLINT PARAM */
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer= (char *)&small_data;
bind[2].is_null= &is_null;
bind[2].length= 0;

/* Bind the buffers /
if (mysql_stmt_bind_param(stmt, bind))
{
fprintf(stderr, " mysql_stmt_bind_param() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
if (mysql_stmt_execute(stmt))
{ fprintf(stderr, " mysql_stmt_execute(), failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}
注意这两者的区别,绑定结果先执行后绑定,绑定参数先绑定后执行.
5.stmt销毁以及关闭连接
mysql_free_result(prepare_meta_result);
/ Close the statement /if (mysql_stmt_close(stmt)){ / mysql_stmt_close() invalidates stmt, so call / / mysql_error(mysql) rather than mysql_stmt_error(stmt) */ fprintf(stderr, " failed while closing the statement\n"); fprintf(stderr, " %s\n", mysql_error(mysql)); exit(0);

6.细心的读者已经发现了代码的问题,绑定部分重复冗余的太多.这样写出的代码不太容易维护.解决的思路可以是这样,把关联绑定的代码能否抽出再封装.
于是有了以下代码:
.h文件
#pragma once
#include “dataType.h”#include “mysql.h”#include “FlexArr.h”#include "common.h"typedef struct model{ enum_field_types sqltype; dataType dtype; my_bool isnull; my_bool isincr; unsigned long plength; unsigned long col_length; FlexArr* value; int retSelected; int parSelected; struct tm* ts; struct tm* dt; MYSQL_TIME* mts; MYSQL_TIME* mdt; void* addr; int valueint; double valuedouble; long valuelong; float valuefloat; char name[NAME_SIZE]; char alianame[ALIA_NAME_SIZE]; char fullname[NAME_SIZE];}model;void fillData(model* pobject);//void initData(model* pobject,enum_field_types type,long col_length,void* addr,char* name,my_bool isnull);void fillAllData(model** pblist,int nCount);void fillBindValFromModel(model** pblist,MYSQL_BIND* bind,int nCount);void freeVal(FlexArr* p);void freeValue(model** pblist,int nCount);
.c文件
#include “model.h”

void fillData(model* pobject)
{
unsigned long len;
if(pobject->sqltypeMYSQL_TYPE_STRING||pobject->sqltypeMYSQL_TYPE_VAR_STRING)
{
CopyString(pobject->value->data,(char*)pobject->addr,pobject->col_length);
len = strlen(pobject->value->data);
}
else
{
if(pobject->sqltypeMYSQL_TYPE_LONG||pobject->sqltypeMYSQL_TYPE_SHORT)
{
pobject->valueint = (int)pobject->addr;
}
else
if(pobject->sqltypeMYSQL_TYPE_FLOAT)
{
pobject->valuefloat = (float)pobject->addr;
}
else
if(pobject->sqltypeMYSQL_TYPE_LONGLONG)
{
pobject->valuelong = (long)pobject->addr;
}
else
if(pobject->sqltypeMYSQL_TYPE_DOUBLE)
{
pobject->valuedouble = (double)pobject->addr;
}
else
if(pobject->sqltypeMYSQL_TYPE_DATETIME)
{
pobject->dt = (struct tm*)pobject->addr;
pobject->mdt = (MYSQL_TIME*)(intptr_t)tm_to_mysql_time(pobject->dt);
}
else
if(pobject->sqltype==MYSQL_TYPE_TIMESTAMP)
{
pobject->ts = (struct tm*)pobject->addr;
pobject->mts = (MYSQL_TIME*)(intptr_t)tm_to_mysql_time(pobject->ts);
}
len = 0;
}
pobject->plength = len;
}

//void initData(model* pobject,enum_field_types type,long col_length,void* addr,char* name,my_bool isnull)
//{
// pobject->parSelected = 0;
// pobject->retSelected = 0;
// pobject->type = type;
// pobject->col_length = col_length;
// pobject->addr = addr;
// pobject->isnull = isnull;
// CopyString(pobject->name,name,NAME_SIZE);
// CopyString(pobject->fullname,"",NAME_SIZE);
// CopyString(pobject->alianame,"",ALIA_NAME_SIZE);
// pobject->value = (FlexArr*)malloc(sizeof(FlexArr) + sizeof(char)*pobject->col_length);
// pobject->value->iLen = pobject->col_length;
//}

void fillBindValFromModel(model** pblist,MYSQL_BIND* bind,int nCount)
{
int iCnt = 0;
model* pObject = NULL;
if(nCount>0){
for(iCnt=0; iCnt<nCount; iCnt++)
{
pObject = (pblist+iCnt);
bind[iCnt].buffer_type = pObject->sqltype;
printf("\npObject->alianame = %s\n",pObject->alianame);
printf("\npObject->name = %s\n",pObject->name);
printf("\npObject->fullname = %s\n",pObject->fullname);
if(pObject->sqltypeMYSQL_TYPE_STRING||pObject->sqltypeMYSQL_TYPE_VAR_STRING)
{
bind[iCnt].buffer_length=pObject->col_length;
bind[iCnt].buffer=(void)pObject->value->data; //
printf("\npObject->col_length = %d\n",pObject->col_length);
printf("\npObject->buffer = %s\n",(char*)pObject->value->data);
}
else
{
printf("\npObject->col_length = %d\n",pObject->col_length);
if(pObject->sqltypeMYSQL_TYPE_TIMESTAMP)
{
bind[iCnt].buffer=(void*)pObject->mts; //
}
else
if(pObject->sqltypeMYSQL_TYPE_DATETIME)
{
bind[iCnt].buffer=(void*)pObject->mdt; //
}
else
if(pObject->sqltypeMYSQL_TYPE_LONG)
{
bind[iCnt].buffer=(void*)&pObject->valueint;
}
else
if(pObject->sqltypeMYSQL_TYPE_SHORT)
{
bind[iCnt].buffer=(void*)&pObject->valueint;
}
else
if(pObject->sqltypeMYSQL_TYPE_FLOAT)
{
bind[iCnt].buffer=(void*)&pObject->valuefloat;// ? ? ?
}
else
if(pObject->sqltypeMYSQL_TYPE_DOUBLE)
{
bind[iCnt].buffer=(void*)&pObject->valuedouble;
}
}
bind[iCnt].length=&pObject->plength;
bind[iCnt].is_null=&pObject->isnull;
}
}
}

void fillAllData(model** pblist,int nCount)
{
int iCnt = 0;
model* pObject = NULL;
if(nCount>0){
for(iCnt=0; iCnt<nCount; iCnt++)
{
pObject = *(pblist+iCnt);
fillData(pObject);
}
}
}

void freeVal(FlexArr* p)
{
if(p!=NULL)
{
free§;
p = NULL;
}
}

void freeValue(model** pblist,int nCount)
{
if(nCount>0)
{
int iCnt = 0;
model* pObject = NULL;
for(iCnt=0; iCnt<nCount; iCnt++)
{
pObject = *(pblist+iCnt);
if(pObject->sqltypeMYSQL_TYPE_STRING||pObject->sqltypeMYSQL_TYPE_VAR_STRING)
freeVal(pObject->value);
iCnt++;
}
}
}
另外为了方便多表查询这个C语言版本的mysql框架还作了许多处理.全部代码参见
https://gitee.com/solider12/lazy-cats-code-warehouse.git.记得给我加星哦