mysql 存储二进制数据

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

最重要的一点：存储二进制数据的表的类型需要是blob类型（按长度不同分为tiny, media, long）

插入二进制数据时需要利用mysql_real_escape_string函数对数据进行转换

从数据库中读取二进制数据时需要利用mysql_fetch_length函数字段长度,该函数需要在mysql_fetch_row调用后才可以正常获取结果

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

先来一个插入:

复制代码

int

db_insert_object(void *object, unsigned int objsize)

{

    int ret;

    char *escape_object = NULL;

    char sql[];

    unsigned int escape_size =  * objsize + ;

    int sql_len;

    MYSQL *mysql= NULL;

    /* connnect db */

    mysql = mysql_init(NULL);

    if( !mysql_real_connect(mysql, SERVER, USER, PASSWD, DATABASE, , NULL, ) ){

        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 ;

error1:

    printf("error: %s\n", mysql_error(mysql));

    if( mysql ) mysql_close(mysql);

    if( escape_object ) free(escape_object);

    return -;

}

复制代码

然后是读取:

复制代码

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, , NULL, ) ){

        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[];

    object  = (char*)malloc(objsize);

    if( object == NULL ){

        goto error2;

    }

    memcpy(object, row[], 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;

}

好像还有一种直接点mysql api可以存储二进制数据。