MySQL存储二进制数据

来源：互联网发布：统计贸易数据自查报告编辑：程序博客网时间：2024/05/01 02:13

原文：http://www.cnblogs.com/aLittleBitCool/archive/2012/08/14/2638831.html

晚上小研究了下MySQL存储于读取二进制数据的功能。关键步骤为以下三点:

最重要的一点：存储二进制数据的表的类型需要是blob类型（按长度不同分为tiny, media, long）
插入二进制数据时需要利用mysql_real_escape_string函数对数据进行转换
从数据库中读取二进制数据时需要利用mysql_fetch_length函数字段长度,该函数需要在mysql_fetch_row调用后才可以正常获取结果

给出一个用C写的一个存储与读取字段的小例子，写的比较粗犷，见谅哈~

先来一个插入:

intdb_insert_object(void *object, unsigned int objsize){    int ret;    char *escape_object = NULL;    char sql[1024];    unsigned int escape_size = 2 * objsize + 2;    int sql_len;    MYSQL *mysql= NULL;    /* connnect db */    mysql = mysql_init(NULL);    if( !mysql_real_connect(mysql, SERVER, USER, PASSWD, DATABASE, 0, NULL, 0) ){        goto error1;    }    /* convert binary string */    escape_object = (char *)malloc(escape_size);    if( escape_object == NULL ){        goto error1;    }    escape_size = mysql_real_escape_string(mysql, escape_object, (char *)object, objsize);    sql_len = sprintf(sql, "insert into task(object) values('%s')", escape_object);    ret = mysql_real_query(mysql, sql, sql_len);    if( ret ){        goto error1;    }    free(escape_object);    mysql_close(mysql);    return 0;error1:    printf("error: %s\n", mysql_error(mysql));    if( mysql ) mysql_close(mysql);    if( escape_object ) free(escape_object);    return -1;}

然后是读取:

void *db_fetch_object(){    MYSQL *mysql= NULL;    MYSQL_RES *res = NULL;    MYSQL_ROW row;    unsigned long *row_len;    char *object = NULL;    const char *sql = "select object from task limit 1";    unsigned long objsize;    int ret;    /* connnect db */    mysql = mysql_init(NULL);    if( !mysql_real_connect(mysql, SERVER, USER, PASSWD, DATABASE, 0, NULL, 0) ){        goto error2;    }    /* get object */    ret = mysql_real_query(mysql, sql, strlen(sql));    if( ret ){        goto error2;    }    res = mysql_store_result(mysql);    if( res == NULL ){        goto error2;    }    /* important */    row = mysql_fetch_row(res);    row_len = mysql_fetch_lengths(res);  /* get the object's length */    if( row_len == NULL ){        goto error2;    }    objsize = row_len[0];    object  = (char*)malloc(objsize);    if( object == NULL ){        goto error2;    }    memcpy(object, row[0], objsize);    mysql_close(mysql);    mysql_free_result(res);    return (void*)object;error2:    printf("error: %s\n", mysql_error(mysql));    if( res ) mysql_free_result(res);    if( mysql ) mysql_close(mysql);    if( object ) free(object);    return NULL;}

PHP以及perl中的操作也是大同小异~

OVER~

0 0