c++ - 将 jpeg 放入 MySQL 表所需的准系统 C++ 代码是什么？

Question

我创建了一个 MySQL 表，其中一列存储 BLOB 类型。 (互联网告诉我 BLOB 是图像的正确数据类型。)

我几乎是 C++ 和 MySQL 的初学者。我想做的是编写一个带有 main() 的小程序，将 jpeg 放入该表。为了本练习，我不想存储对包含图像的目录的引用。

我是否认为它就像填写下面 BLOCK 2 中的部分一样简单？

#include <iostream>
#include <string>
#include <mysql.h>

using namespace std;

int main(int argc, char **argv)
{

    //BLOCK 1: INIT
    MYSQL *connection, mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;

    int query_state;

    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "root", "secret", "beginner_db",0,0,0);

    //BLOCK 2: SEND QUERY
    /* do something to insert image to table */

    //BLOCK 3: DISPLAY QUERY RESULTS
    result = mysql_store_result(connection);
    /* do something with result */

    //BLOCK 4: FREE
    mysql_free_result(result);
    mysql_close(connection);

    return 0;
}

Answer 1

对于这种情况，一个好的解决方案是使用 mysql_stmt_send_long_data()功能。

我链接到的 MySQL 手册页上有一个示例，但这里有一个更相关的发送文件内容的示例:

#ifdef _WIN32
#include <windows.h>
#endif

#include <cstddef>
#include <cstdio>
#include <cstdlib>
#include <cstring>
#include <iostream>

#include <boost/scope_exit.hpp>

#include <mysql.h>

#define ARR_LEN(arr_id) ((sizeof (arr_id))/(sizeof (arr_id)[0]))

int main()
{
    using namespace std;

    MYSQL *pconn = mysql_init(NULL);
    BOOST_SCOPE_EXIT( (pconn) ) {
        mysql_close(pconn);
    } BOOST_SCOPE_EXIT_END

    const char *db_name = "test";
    if (!mysql_real_connect(pconn, "localhost", "test", "********", db_name, 0, NULL, CLIENT_COMPRESS)) {
        cerr << "Error: mysql_real_connect() failed to connect to `" << db_name << "`." << endl;
        return EXIT_FAILURE;
    }

    MYSQL_STMT *pinsert_into_images_stmt = mysql_stmt_init(pconn);
    BOOST_SCOPE_EXIT( (pinsert_into_images_stmt) ) {
        mysql_stmt_close(pinsert_into_images_stmt);
    } BOOST_SCOPE_EXIT_END

    const char sql1[] = "INSERT INTO images(data) VALUES (?)";
    if (mysql_stmt_prepare(pinsert_into_images_stmt, sql1, strlen(sql1)) != 0) {
        cerr << "Error: mysql_stmt_prepare() failed to prepare `" << sql1 << "`." << endl;
        return EXIT_FAILURE;
    }

    MYSQL_BIND bind_structs[] = {
        { 0 } // One for each ?-placeholder
    };

    unsigned long length0;
    bind_structs[0].length = &length0;
    bind_structs[0].buffer_type = MYSQL_TYPE_BLOB;
    bind_structs[0].is_null_value = 0;

    if (mysql_stmt_bind_param(pinsert_into_images_stmt, bind_structs) != 0) {
        cerr << "Error: mysql_stmt_bind_param() failed." << endl;
        return EXIT_FAILURE;
    }

    const char *file_name = "image.jpg";
    FILE *fp = fopen(file_name, "rb");
    BOOST_SCOPE_EXIT( (fp) ) {
        fclose(fp);
    } BOOST_SCOPE_EXIT_END

    // Use mysql_stmt_send_long_data() to send the file data in chunks.
    char buf[10*1024];
    while (!ferror(fp) && !feof(fp)) {
        size_t res = fread(buf, 1, ARR_LEN(buf), fp);
        if (mysql_stmt_send_long_data(pinsert_into_images_stmt, 0, buf, res) != 0) {
            cerr << "Error: mysql_stmt_send_long_data() failed." << endl;
            return EXIT_FAILURE;
        }
    }

    if (!feof(fp)) {
        cerr << "Error: Failed to read `" << file_name << "` in its entirety." << endl;
        return EXIT_FAILURE;
    }

    if (mysql_stmt_execute(pinsert_into_images_stmt) != 0) {
        cerr << "Error: mysql_stmt_execute() failed." << endl;
        return EXIT_FAILURE;
    }

    cout << "Inserted record #" << mysql_insert_id(pconn) << endl;
    return EXIT_SUCCESS;
}

我正在使用表 `images` 的以下定义:

CREATE TABLE images (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    data MEDIUMBLOB NOT NULL,

    PRIMARY KEY (id)
);

运行该程序后，它成功将 38,339 字节的 JPEG image.jpg 发送到服务器并输出“Inserted record #1”。

您可以验证发送的字节数是否正确:

mysql> SELECT octet_length(data) FROM images WHERE id=1;+--------------------+| octet_length(data) |+--------------------+|              38339 |+--------------------+1 row in set (0.00 sec)