用C语言往MySQL中存取二进制数据

by falcon <wuzhangjin@gmail.com>
2008-08-02

    MySQL有一个二进制数据类型longblob，可以用来存放二进制数据。那么如何对MySQL进行该类数据的存取呢？
    通过参考资料[2]（非常感谢该文作者），发现可以直接通过C语言往MySQL中存取二进制数据。
    下面先创建一个数据表test，该表的data字段为longblog类型。

$ mysql -u USER -pPASSWD
mysql> create database test
mysql> use test
mysql> create table test(id int, file char(255), data longblob);
mysql> quit

接着，我们来写一个C语言程序，通过该程序往该表的data字段存入一个二进制文件，紧接着从数据库中取出该字段的内容写入另外一个文件中，从而演示longblob数据的存和取操作。

Code:

/**
 * mysql_binary.c -- store/fetch binary data to/from Mysql
 *
 * author: falcon <wuzhangjin@gmail.com>
 * update: 2008-08-02
 * ref   : [url]http://wl-zz.com.cn/Art/show.asp?id=6420[/url]
 *         [url]http://www.2ed.cn/Article-119-21440.shtml[/url]
 *
 * compile(in ubuntu 8.10): 
 *        $ sudo apt-get install libmysqlclient15-dev
 *        $ gcc -o mysql_binary mysql_binary.c -lmysqlclient
 * usage:
 *        at first, you should create a mysql table like this.
 *
 *        $ mysql -u USER -pPASSWD 
 *        mysql> create database test
 *        mysql> use test
 *        mysql> create table test(id int, file char(255), data longblob);
 *        mysql> quit
 *      
 *      or just execute the following command.
 *      $ echo "create database test;use test;create table test(id int, file char(255), data longblob);" \
 *        mysql -u USER -pPASSWD
 *     
 *        and then, store a binary file(input) to mysql and fetch it from mysql to output
 *        $ ./mysql_binary input_binary_file output_binary_file
 * note: 
 *       this source code is based on the source code in [url]http://wl-zz.com.cn/Art/show.asp?id=6420[/url]
 *       here just fix some bugs and make some modification.
 */

#include <stdio.h>
#include <mysql/mysql.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>

#define host          "localhost"
#define username      "<a href="javascript:;" onClick="javascript:tagshow(event, 'root');" target="_self"><u><strong>root</strong></u></a>"
#define password      ""    /* set the password of the root user here */
#define database      "test"

int main(int argc, char *argv[])
{
    MYSQL *conn;
    MYSQL_RES *res_set;
    MYSQL_ROW row;
    MYSQL_FIELD *field;

char ibf[255] = "", obf[255] = "";    /*input binary file, output binary file */
    struct stat file_stats;
    off_t size;
    char *buf;
    int i, flag;
    char *sql;
    FILE *fp;
    int n = 0;
    char *end;
    unsigned long *length;

/* get user's input */
    if (argc != 3) {
        printf("Usage: %s input_binary_file output_binary_file\n",
               argv[0]);
        return -1;
    }
    sprintf(ibf, "%s", argv[1]);
    sprintf(obf, "%s", argv[2]);

/* get the size of the input binary file */
    if (stat(ibf, &file_stats))
        return -1;
    size = file_stats.st_size;

/* allocate a "size" of space to a buffer: buf */
    if ((buf = (char *) malloc(sizeof(char) * (size + 1))) == NULL) {
        perror("malloc buf");
        exit(1);
    }

/* open the input binary file and copy it to the buffer */
    if ((fp = fopen(ibf, "rb")) == NULL) {
        perror("fopen file");
        exit(1);
    }

if ((n = fread(buf, 1, size, fp)) < 0) {    //n=size
        perror("fread file");
        exit(1);
    }

fclose(fp);
    fp = NULL;

/* allocate a memory space to the SQL statement, it need to big enough to store the whole input binary file */
    sql = (char *) malloc(sizeof(char) * n * 2 + 256);    //2n+1+strlen(other sql)
    if (sql == NULL) {
        perror("malloc sql");
        exit(1);
    }

/* initilize connection to mysql */
    conn = mysql_init(NULL);
    if (conn == NULL) {
        printf("init mysql, %s\n", mysql_error(conn));
        exit(1);
    }

/* build the SQL statement for storing the input binary file to mysql */
    sprintf(sql, "insert into test(id, file, data) values(5, '%s',",
        ibf);
    end = sql;
    end += strlen(sql);    //point sql tail

//convert NUL(ASCII 0)、'\n'、'\r'、'\'’、'''、'"', Control-Z and so on
    *end++ = '\'';
    end += mysql_real_escape_string(conn, end, buf, n);
    *end++ = '\'';
    *end++ = ')';

free(buf);
    buf = NULL;

/* connect to mysql with indicated host, username, password and database */
    if ((mysql_real_connect
         (conn, host, username, password, database, 0, NULL,
          0)) == NULL) {
        printf("connect mysql, %s\n", mysql_error(conn));
        exit(1);
    }

/* execute the built SQL statement to store the input binary file to MySQL */
    flag = mysql_real_query(conn, sql, (unsigned int) (end - sql));
    if (flag != 0) {
        printf("insert failed, %s\n", mysql_error(conn));
        exit(1);
    }

/* fetch the binary data from MySQL to a file */
    if ((mysql_real_query
         (conn, "select data from test where id=5",
          strlen("select data from test where id=5")))
        != 0) {
        printf("select failed, %s\n", mysql_error(conn));
        exit(1);
    }

res_set = mysql_store_result(conn);

fp = fopen(obf, "wb");
    while ((row = mysql_fetch_row(res_set)) != NULL) {
        length = mysql_fetch_lengths(res_set);
        for (i = 0; i < mysql_num_fields(res_set); i++) {
            fwrite(row[0], 1, length[0], fp);
        }
    }

fclose(fp);
    mysql_close(conn);
    free(sql);
    sql = NULL;

return 0;
}

[Ctrl+A Select All]

    通过分析源代码不难发现，这里跟存取普通的数据差不多，只是通过mysql_real_escape_string函数在二进制数据外面加了一层“东西”。
    okay，开始编译和运行。
    记得编译之前需要安装对应的mysql函数库，在ubuntu 8.10下可以这么安装。

$ sudo apt-get install libmysqlclient15-dev

编译前还需要设置数据库名、表名、用户名和密码，否则编译后无法正常连接数据库。
下面开始编译。

$ gcc -o mysql_binary mysql_binary.c -lmysqlclient

运行一下，

//先删除之前的数据
$ echo "delete from test;" | mysql -u USER -pPASSWD test
// 复制一份mysql_binary为mysql_binary_copy
$ ./mysql_binary mysql_binary mysql_binary_copy
//可以看到两个大小是一样的
$ ls -l mysql_binary mysql_binary_copy
-rwxr-xr-x 1 falcon falcon 10831 2008-08-02 20:55 mysql_binary
-rw-r--r-- 1 falcon falcon 10831 2008-08-02 20:57 mysql_binary_copy

实际上在postgresql中也可以进行二进制文件的操作，不过不是很方便，如果感兴趣可以参考一下资料[3,4,5,6,7]。

参考和推荐资料

[1] MYSQL中如何存取二进制文件(PHP版)
http://net.stuun.com/database/MySQL/jiqiao/45070.html
[2] C语言实现myql中存取二进制文件
http://linux.chinaitlab.com/MYSQL/383265.html
[3] Storing Binary Data
http://jdbc.postgresql.org/documentation/80/binary-data.html
[4] Chapter 31. Large Objects
http://www.postgresql.org/docs/8.3/interactive/largeobjects.html
[5] PostgreSQL libpq Programming Example
http://www.hungryhacker.com/articles/code/libpq_example.html
[6] ECPG - Embedded SQL in C
http://www.postgresql.org/docs/7.4/static/ecpg.html
[7] Linux下免费数据库PostgreSQL开发入门
http://www-128.ibm.com/developerworks/cn/linux/l-pgsql/index.html

最新更新

月度热点

相关阅读

网络推荐

推荐给好友 上一篇 | 下一篇

我来说两句