数据库基础Mysql项目实战-CSDN博客

本文链接：https://blog.csdn.net/adagio9z/article/details/148046138

数据库mysql基础

注：请先安装MySQL开发包，Linux安装命令：sudo apt-get isntall libmysqlclient-dev

一、数据库建模

CREATE DATABASE ADAGIO_DB; #创建数据库
SHOW DATABASE; 
USE ADAGIO_DB; #使用数据库
CREATE TABLE TBL_USER (
    U_ID INT PRIMARY KEY AUTO_INCREMENT, #自增
    U_NAME VARCHAR(32),
    U_GENDER VARCHAR(32),
)

首先通过Mysql workbench创建一个张表，包含用户编号（自增），姓名与性别。

二、远程连接数据库

//与mysql服务器通信管道的创建与初始化
MYSQL mysql;

if( mysql_init(&mysql) == NULL ) {
    printf("mysql_init : %s\n",mysql_error(&mysql));
    goto Exit;
}

//连接数据库
if( !mysql_real_connect(&mysql, KING_DB_SERVER_IP, KING_DB_USERNAME, KING_DB_PASSWORD, KING_DB_DEFAULTDB, KING_DB_SERVER_PORT, NULL, 0)){
    printf("mysql_real_connect : %s\n",mysql_error(&mysql));
    goto Exit;
}

mysql是我们对数据库进行操作的句柄，相当于我们把sql语句丢到这个管道里传输到MySQL服务器。然后通过mysql_init()进行初始化，通过mysql_real_connect()连接数据库，其参数有服务器IP，端口号，用户名密码等，若失败则报错退出。

三、向服务器传递指令

//写好要插入的sql语句
#define SQL_INSERT_TBL_USER "insert TBL_USER(U_NAME,U_GENDER) values('adagio','man');"

//传递指令
if(mysql_real_query(&mysql, SQL_INSERT_TBL_USER, strlen(SQL_INSERT_TBL_USER))){
    printf("mysql_real_query : %s\n",mysql_error(&mysql));
    goto Exit;
}

以增加一条数据’adagio’,'man’为例，通过mysql_real_query将sql语句通过mysql管道传到服务器。
注意mysql_real_connect返回值为非0时成功，mysql_real_query返回值为0时成功。

四、查询数据

int king_mysql_select(MYSQL *handle){

    //发送 mysql_real_query
    if(mysql_real_query(handle, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER))){
        printf("mysql_real_query : %s\n",mysql_error(handle));
    }

    //存储
    MYSQL_RES *res = mysql_store_result(handle);
    if( res == NULL){
        printf("mysql_store_result : %s\n",mysql_error(handle));
        return -2;
    }

    //判断结果行数和列数
    int rows = mysql_num_rows(res);
    int fields = mysql_num_fields(res);
    printf("rows: %d,fields: %d\n",rows,fields);

    //抓取数据
    MYSQL_ROW row;
    while( ( row = mysql_fetch_row(res) ) ){
        int i = 0;
        for( i=0; i < fields; i++){
            printf("%s\t", row[i]);
        }
        printf("\n");

    }

    mysql_free_result(res);

    return 0;


}

先通过mysql_real_query向服务器发送查询指令；
mysql_real_query执行完之后，服务器已经执行了SQL语句并把数据传送到了mysql管道里，需要定义一个res通过mysql_store_result来存储查询到的结果；
判断结果的行数和列数
抓取数据。定义一个MySQL_ROW row,row的本质是一个字符串数组(char**)。通过mysql_fetch_row 逐行抓取每一行的数据直至row == NULL，再根据列数fileds把每行的数据逐项输出，row[i]表示该行的某一项数据。

五、删除一个用户的数据

delete from TBL_USER where u_name=UNAME;

若直接执行如上的删除的sql语句会报错，因为数据库里可能有U_NAME相同的用户数据，直接执行将删除所有U_NAME为该值的数据，这是一个非安全的操作，因此要先退出安全模式。

set  sql_safe_updates=0;
delete from TBL_USER where u_name=UNAME;
set  sql_safe_updates=1;

为了避免多次向服务器传送指令，可以引入存储过程把三条指令简化为一条。

delimiter $$
create procedure proc_delete_user(in UNAME varchar(32))
begin
set  sql_safe_updates=0;
delete from TBL_USER where u_name=UNAME;
set  sql_safe_updates=1;
end$$

然后直接传输定义好的过程指令即可。

#define SQL_DELETE_TBL_USER "call proc_delete_user('adagio');"

if(mysql_real_query(&mysql, SQL_DELETE_TBL_USER, strlen(SQL_DELETE_TBL_USER))){
        printf("mysql_real_query : %s\n",mysql_error(&mysql));
        goto Exit;
}

六、图片操作

图片属于大字段二进制数据，操作上与上述简单的字符串等数据有些区别。

从本地读取一张图片

int read_image(char *filename, char *buffer){

    if( filename == NULL || buffer == NULL) return -1;
    FILE *fp = fopen(filename, "rb");
    if ( fp == NULL){
        printf("fopen failed\n");
        return -2;
    }

    //将指针置于文件最末尾，偏移量即为文件大小
    fseek(fp, 0, SEEK_END);
    int length = ftell(fp);//file size
    fseek(fp, 0, SEEK_SET);

    //读取
    int size = fread(buffer, 1, length, fp);
    if( size != length){
        printf("fread failed: %d\n", size);
        return -3;
    }

    fclose(fp);
    return size;
}

将图片存储到buffer里。

将图片存储到数据库

#define SQL_INSERT_IMG_USER "insert TBL_USER(U_NAME,U_GENDER,U_IMG) values('adagio', 'man', ?);"

//将图片传输到mysql服务器
int mysql_write(MYSQL *handle, char *buffer, int length){

    if( handle == NULL || buffer == NULL || length < 1 ) return -1;

    MYSQL_STMT *stmt = mysql_stmt_init(handle); 
    int ret = mysql_stmt_prepare(stmt, SQL_INSERT_IMG_USER,strlen(SQL_INSERT_IMG_USER));

    if( ret ){
        printf("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 = NULL;
    

    //将设置好的参数与stmt绑定
    ret = mysql_stmt_bind_param(stmt, &param);
    if( ret ){
        printf("mysql_stmt_bind_param: %s\n", mysql_error(handle));
        return -3;
    }


    //分段发送
    ret = mysql_stmt_send_long_data(stmt, 0, buffer, length);
    if( ret ){
        printf("mysql_stmt_send_long_data: %s\n", mysql_error(handle));
        return -4;
    }
    

    //执行预编译的sql语句
    ret = mysql_stmt_execute(stmt);
    if( ret ){
        printf("mysql_stmt_excute: %s\n", mysql_error(handle));
        return -5;
    }

    ret = mysql_stmt_close(stmt);
    if( ret ){
        printf("mysql_stmt_close: %s\n", mysql_error(handle));
        return -6;
    }

    return 0;

}

MYSQL_STMT 的作用是对要发送的内容做一个声明，相当于待发送内容的缓冲区，与MYSQL_BIND联合使用以说明待发送的数据的类型长度以及要执行的sql语句等，并通过mysql_stmt_bind_param将这两者绑定，再将图片发送出去。
注意对于大字段数据为了避免内存溢出一般把与stmt绑定的buffer设置为NULL，表示参数将通过mysql_stmt_send_long_data分段发送。

从数据库中接受图片

#define SQL_SELECT_IMG_USER "select U_IMG from TBL_USER where U_NAME='adagio';"

//从mysql服务器接收图片
int mysql_read(MYSQL *handle, char *buffer, int length){

    if( handle == NULL || buffer == NULL || length < 1 ) return -1;

    MYSQL_STMT *stmt = mysql_stmt_init(handle); 

    //准备一个 SQL 语句进行执行，将 SQL 语句发送到服务器进行编译
    int ret = mysql_stmt_prepare(stmt, SQL_SELECT_IMG_USER, strlen(SQL_SELECT_IMG_USER));
    if( ret ){
        printf("mysql_stmt_prepare: %s\n", mysql_error(handle));
        return -2;
    }
    

    MYSQL_BIND result= {0};
    unsigned long total_length = 0;
    result.length = &total_length;
    

    //将参数绑定到预编译的 SQL 语句中
    ret = mysql_stmt_bind_result(stmt, &result);
    if( ret ){
        printf("mysql_stmt_bind_param: %s\n", mysql_error(handle));
        return -3;
    }
    

    //执行预编译的 SQL 语句
    ret = mysql_stmt_execute(stmt);
    if(ret){
        printf("mysq_stmt_execute: %s\n", mysql_error(handle));
        return -4;
    }
    


    //将结果集绑定到 MYSQL_BIND 结构体数组，用于接收查询结果
    ret = mysql_stmt_store_result(stmt);
    if(ret){
        printf("mysq_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;
        }
    }


    ret = mysql_stmt_close(stmt);
    if( ret ){
        printf("mysql_stmt_close: %s\n", mysql_error(handle));
        return -6;
    }

    return total_length;
    
}

从服务器接收图片的过程与发送类似，也需要先设定一个缓冲区并设置参数再接收数据。在接收数据时先通过 mysql_stmt_fetch定位到某一行，并把buffer的地址赋值给结果集里的result.buffer，然后再用result.buffer分段读取接收的数据，当没有可读取的数据时退出循环。

将图片存储到本地

int write_image(char *filename, char *buffer, int length){

    if(filename == NULL || buffer == NULL || length < 1 ) return -1;

    FILE *fp = fopen(filename, "wb+");
    if ( fp == NULL){
        printf("fopen failed\n");
        return -2;
    }
    
    int size = fwrite(buffer, 1, length, fp);
    if( size != length){
        printf("fwrite failed: %d\n", size);
        return -3;
    }

    fclose(fp);
    return size;

}