mysql 儲存位元據

標籤：

晚上小研究了下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;}

好像還有一種直接點mysql api可以儲存位元據。

mysql 儲存位元據