C++：Stmt预处理SQL与大文件存取（五）

1、预处理相关API

• mysql_stmt_init：初始化生成一个预编译处理的Stmt对象
• mysql_stmt_prepare：预处理SQL语句，值部分用？进行占位（可以防止SQL注入）
• mysql_stmt_bind_param：给预处理的SQL语句中的？进行值绑定
  • MYSQL_BIND：
    • MYSQL_TYPE_XXX：数据类型，int、long、string、blob…
    • buffer：数据值的指针
    • buffer_length：数据长度，如果是整形数据可以不传入
• mysql_stmt_execute：执行SQL语句
• mysql_stmt_close：关闭Stmt对象

MYSQL_STMT * STDCALL mysql_stmt_init(MYSQL *mysql);
int STDCALL mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length);
my_bool STDCALL mysql_stmt_bind_param(MYSQL_STMT * stmt, MYSQL_BIND * bnd);
int STDCALL mysql_stmt_execute(MYSQL_STMT *stmt);
my_bool STDCALL mysql_stmt_close(MYSQL_STMT * stmt);

2、通过预编译接口插入图片到数据库

• 将一张图片存到数据库中然后再从数据库中读取出来

2.1、建立连接

MYSQL mysql;
    // 初始化mysql结构体并且初始化服务连接环境
    mysql_init(&mysql);
    const char *host = "127.0.0.1";
    const char *user = "root";
    const char *password = "123456";
    const char *db = "cpp";
    int timeout = 3;
    // 连接超时时长设置
    mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);

    // 断开重连设置
    int reconnect = 1;
    mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);

    // MySQL连接建立
    if(!mysql_real_connect(&mysql, host, user, password, db, 3306, 0, CLIENT_MULTI_STATEMENTS)){
        std::cout << "mysql connect failed!" << mysql_error(&mysql) << std::endl;
    }
    else{
        std::cout << "mysql connect " << host << " success!" << std::endl;
    }

	// .....

    mysql_close(&mysql);
    mysql_library_end();

2.2、创建表

MySQL中存取文件可以用blob来表示，表示使用二进制的方式存储

void create_table(MYSQL &mysql)
{
    string sql = "CREATE TABLE IF NOT EXISTS `t_data` (\
                   `id` int(10) unsigned AUTO_INCREMENT,\
                   `name` varchar(1024),\
                   `data` blob,\
                   `size` int,\
                   PRIMARY KEY (`id`)\
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    if(mysql_real_query(&mysql, sql.c_str(), sql.length()) != 0){
        cout << "mysql_query failed!" << endl;
    }
}

2.3、存入图片到MySQL中

这里主要通过ftsream来计算文件的大小，也可以通过其他手段，例如stat.h、lseek函数（Linux环境）

void insert_picture(MYSQL &mysql)
{
    // 1. 初始化stmt预处理对象
    MYSQL_STMT *stmt = mysql_stmt_init(&mysql);
    string sql = "insert into `t_data`(`name`, `data`, `size`) values(?,?,?);";
    if(mysql_stmt_prepare(stmt, sql.c_str(), sql.length()) != 0){
        cerr << "mysql_stmt_prepare failed!" << mysql_stmt_error(stmt) << endl;
    }

    // 2. 绑定数据
    string filename = "touxiang.jpg";
    fstream in(filename, ios::in | ios::binary);
    if (!in.is_open()){
        cerr << "open file error!" << endl;
    }
    in.seekg(0, ios::end);
    int filesize = in.tellg();
    in.seekg(0, ios::beg);
    char *data = new char[filesize];
    int readed = 0, current_filesize = filesize;
    while(!in.eof()){
        in.read(data+readed, current_filesize-readed);
        if(in.gcount() <= 0)        break;
        readed += in.gcount();
    }
    in.close();


    MYSQL_BIND bind[3] = {0};
    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = (char *)filename.c_str();
    bind[0].buffer_length = filename.length();

    bind[1].buffer_type = MYSQL_TYPE_BLOB;
    bind[1].buffer = data;
    bind[1].buffer_length = filesize;

    bind[2].buffer_type = MYSQL_TYPE_LONG;
    bind[2].buffer = &filesize;

    // 3. 将占位符的参数替换成实际数据
    if(mysql_stmt_bind_param(stmt, bind) != 0) {
        cerr << "mysql_stmt_bind_param error!" << endl;
    }

    // 4. 执行实际的mysql语句
    if(mysql_stmt_execute(stmt) != 0){
        cerr << "mysql_stmt_execute error!" << mysql_stmt_error(stmt) << endl;
    }
    else{
        cout << "mysql_stmt_execute success! affect row = " << mysql_stmt_affected_rows(stmt) << ", insert data id = " << mysql_stmt_insert_id(stmt) << endl;
    }

    // 5. 关闭连接
    if(mysql_stmt_close(stmt) != 0){
        cerr << "mysql_stmt_close error!" << endl;
    }

    // 释放所有资源
    delete []data;
}

2.4、将数据库中的图片取出来

void select_data(MYSQL &mysql)
{
    string sql = "select *from `t_data` limit 0, 1";
    if (mysql_real_query(&mysql, sql.c_str(), sql.length()) != 0){
        cerr << "mysql_real_query error" << mysql_error(&mysql) << endl;
    }
    MYSQL_RES *result = mysql_store_result(&mysql);

    MYSQL_ROW  row = mysql_fetch_row(result);
    if(!row){
        cerr << "mysql_fetch_row error" << mysql_error(&mysql) << endl;
    }

    unsigned long* lens = mysql_fetch_lengths(result);
    MYSQL_FIELD *mysqlField = mysql_fetch_fields(result);
    int field_count = mysql_num_fields(result);
    for(int i = 0;i < field_count;i++){
        cout << mysqlField[i].name << " , length = " << lens[i] << endl;
    }
    string filename("out_");
    filename += row[1];
    fstream out(filename, ios::out | ios::binary);
    if(!out.is_open()){
        cerr << "open file failed!" << endl;
    }
    out.write(row[2], lens[2]);
    out.flush();
    out.close();
}