数据库的基本操作

数据库的基本操作

数据库的基本业务逻辑

这里以登录操作为例，客户端通过网络连接到业务服务器，待其解析后在执行其想要进行的操作，这里是查询，网络连接到数据库进行查询。

1. 数据库连接与初始化操作

    MYSQL mysql;
    //初始化mysql
    if(NULL == mysql_init(&mysql)){
        INFO("mysql init:%s\n",mysql_error(&mysql));
        return -1;
    }
	//连接数据库服务器
    if(!mysql_real_connect(&mysql,WANG_DB_SERVER_IP,WANG_DB_USERNAME,
                          WANG_DB_PASSWORD,WANG_DEFAULT_DB_NAME,
                          WANG_DB_SERVER_PORT,NULL,0)){
        INFO("mysql connect:%s\n",mysql_error(&mysql));
        goto Exit;
    }

2. 增删

    //mysql --> insert
    if(mysql_real_query(&mysql,SQL_INSERT_TBL_USER,strlen(SQL_INSERT_TBL_USER))){
        INFO("mysql query:%s\n",mysql_error(&mysql));
        goto Exit;
    }
    //mysql --> select
    wang_mysql_select(&mysql);
    //mysql --> delete
    if(mysql_real_query(&mysql,SQL_DELETE_TBL_USER,strlen(SQL_DELETE_TBL_USER))){
        INFO("mysql query:%s\n",mysql_error(&mysql));
        goto Exit;
    }

3. 查询

int wang_mysql_select(MYSQL *handle){

    //mysql_real_query --> mysql;
    //select语句
    if(mysql_real_query(handle,SQL_SELECT_TBL_USER,strlen(SQL_SELECT_TBL_USER))){
        INFO("mysql query:%s\n",mysql_error(handle));
        return -1;
    }
    //查询到的语句存到res
    // store -->
    MYSQL_RES *res = mysql_store_result(handle);
    if(res == NULL){
        INFO("mysql store result:%s\n",mysql_error(handle));
        return -2;
    }
    
    // rows, fields：获取行列
    int rows = mysql_num_rows(res);
    INFO("rows:%d\n",rows);
    int fields = mysql_num_fields(res);
    INFO("fields:%d\n",fields);

    MYSQL_ROW row;
    while((row = mysql_fetch_row(res))){
        int i = 0;
        for(i = 0; i < fields; i++){
            INFO("%s\t",row[i]);
        }
        INFO("\n");
    }
    mysql_free_result(res);
    return 0;
}

数据库存储一张图片

有了以上的数据库基本操作，那么我们怎么将一张图片插入到数据库里呢？（并且能够从数据库中读取图片。）

steps:

1. 将图片读到内存中。read_image()
2. 将图片写入数据库。mysql_read_image()
3. 从数据库读取图片。mysql_write_image()
4. 写入磁盘。write_image()

   1.读取到内存

   首先将jpg文件以字符串的形式存到内存里，代码中表示为buffer。

   int read_image(char *filename, char *buffer){
    if(filename == NULL || buffer == NULL){
        return -1;
    }
    FILE *fp = fopen(filename,"rb");
    if(fp == NULL){
        INFO("fopen %s failed\n",filename);
        return -2;
    }
    // file size
    // fseek是将文件指针指向末尾。
    fseek(fp,0,SEEK_END);
    //求偏移量，即文件大小
    int length = ftell(fp);
    // 指针指向开头
    fseek(fp,0,SEEK_SET);
    int size = fread(buffer,1,length,fp);
    if(size != length){
        INFO("fread %s failed\n",filename);
        return -3;
    }
    fclose(fp);
    return size;
   }
   

   2.写入磁盘

   int write_image(char *filename, char *buffer, int length){
    if(filename == NULL || buffer == NULL || length <= 0){
        return -1;
    }
    FILE *fp = fopen(filename,"wb+");
    if(fp == NULL){
        INFO("fopen %s failed\n",filename);
        return -2;
    }
   
    int size = fwrite(buffer,1,length,fp);
    if(size != length){
        INFO("fwrite %s failed\n",filename);
        return -3;
    }
    fclose(fp);
    return size;
   }
   

   3.将buffer存入数据库

   int mysql_write(MYSQL *handle, char *buffer,int length){
    if(handle == NULL || buffer == NULL || length <= 0){
        return -1;
    }
    //这里理解为创建了一个存储空间，来存放要访问服务器的句柄，即sql语句。
    MYSQL_STMT *stmt = mysql_stmt_init(handle);
    //将sql语句放入stmt。
    int ret = mysql_stmt_prepare(stmt, SQL_INSERT_IMG_USER, strlen(SQL_INSERT_IMG_USER));
    if(ret){
        INFO("mysql stmt prepare:%s\n",mysql_error(handle));
        return -2;
    }
    //与问号绑定
    MYSQL_BIND param = {0};
    param.buffer_type = MYSQL_TYPE_LONG_BLOB;
    param.buffer = NULL;
    param.is_null = 0;
    param.length = 0;
    //绑定的一些参数
    ret = mysql_stmt_bind_param(stmt, &param);
    if(ret){
        INFO("mysql stmt bind param:%s\n",mysql_error(handle));
        return -3;
    }
    //分块发送数据到服务器
    ret = mysql_stmt_send_long_data(stmt,0,buffer,length);
    if(ret) {
        INFO("mysql stmt send long data:%s\n",mysql_error(handle));
        return -4;
    }
    //执行刚刚发的stmt中的句柄
    ret = mysql_stmt_execute(stmt);
    if(ret){
        INFO("mysql stmt execute:%s\n",mysql_error(handle));
        return -5;
    }
    ret = mysql_stmt_close(stmt);
    if(ret){
        INFO("mysql stmt close:%s\n",mysql_error(handle));
        return -6;
    }
    return ret;
   }
   

   4.将图片内容从数据库中取出

   int mysql_read(MYSQL *handle, char *buffer, int length){
    if(handle == NULL || buffer == NULL || length <= 0){
        return -1;
    }
    MYSQL_STMT *stmt = mysql_stmt_init(handle);
    int ret = mysql_stmt_prepare(stmt, SQL_SELECT_IMG_USER, strlen(SQL_SELECT_IMG_USER));
    if(ret){
        INFO("mysql stmt prepare:%s\n",mysql_error(handle));
        return -2;
    }
    //读取数据库这里bind就叫result
    MYSQL_BIND result = {0};
    result.buffer_type = MYSQL_TYPE_LONG_BLOB;
    unsigned long total_length = 0;
    result.length = &total_length;
   
    ret = mysql_stmt_bind_result(stmt, &result);
    if(ret){
        INFO("mysql stmt bind result:%s\n",mysql_error(handle));
        return -3;
    }
   
    ret = mysql_stmt_execute(stmt);
    if(ret){
        INFO("mysql stmt execute:%s\n",mysql_error(handle));
        return -4;
    }
   
    ret = mysql_stmt_store_result(stmt);
    if(ret){
        INFO("mysql stmt store result:%s\n",mysql_error(handle));
        return -5;  
    }
    while(1){
    	//抓取数据
        ret = mysql_stmt_fetch(stmt);
        //这里的数据如果是残缺一半
        if(ret != 0 && ret != MYSQL_DATA_TRUNCATED){
            break;
        }
        int start = 0;
        while(start < (int)total_length){
            result.buffer = buffer + start;
            result.buffer_length = 1;
            //抓取一列数据
            mysql_stmt_fetch_column(stmt, &result, 0, start);
            start += result.buffer_length;
        }
    }
    mysql_stmt_close(stmt);
    return total_length;