如何通过 JDBC 将数据流式传输到 MariaDB [英] How to stream data to MariaDB over JDBC

问题描述

我想使用 MariaDB 连接器将 BLOB 数据存储到 MariaDB 中，并尝试找到最有效的解决方案.现在我使用 setBlob() 或 setBinaryStream() 方法来存储数据.但是与将数据直接流式传输到数据库相比，它是如此缓慢.我为 Oracle 数据库使用了流式传输，一切都运行得又快又完美.但是由于我将数据库更改为 MariaDB，直接流式传输不起作用.

I want to store BLOB data into MariaDB using MariaDB connector and I try to find the most efficient solution. Now I'm using setBlob() or setBinaryStream() method to store data. But it is so slow comparing to direct streaming of data to database. I used streaming for Oracle database and everything worked fast and perfectly. But since I changed the database to MariaDB, direct streaming doesn't work.

直接流的代码如下:

        Blob localBlob =  lrs.getBlob("MyData");

        try {
            los = localBlob.setBinaryStream(1);
        } catch (Throwable t) {
        }
        int countBytesRead;
        // md5 hash
        InputStream dis = new DigestInputStream(inputStreamArgument, localHash);
        byte[] localBuffer = new byte[BUFFER_SIZE];
        while ((countBytesRead = dis.read(localBuffer)) >= 0) {
            los.write(localBuffer, 0, countBytesRead);
        }
        los.close();
        inputStreamArgument.close();
        lstmt.close();

推荐答案

简答 - 从今天起您无法流式传输 blob.此连接器始终将至少一整行读入内存.此外，MariaDB 服务器(以及 MySQL)不能非常有效地处理 blob，它还会将整个博客加载到内存中，仅在服务器端.如果您想要自己动手流式传输，也许您可​​以将 blob 拆分为更小的块，例如 4K，并将它们存储在专用表中的不同行中.您可以 SELECT * from my_blob 将其读取为多行，并使用例如 setFetchSize(1) 一次读取一个块.

Short answer - you cannot stream blobs as of today. this connector always reads at least one entire row into memory. Moreover, the MariaDB Server( and MySQL as well) does not handle blobs very efficiently, it also would load the entire blog into memory, only on the server side. If you want do-it-yourself streaming, maybe you can split the blob into smaller chunks of say 4K, and store them in different rows in a dedicated table. you can SELECT * from my_blob to read that as multiple rows, and use e.g setFetchSize(1) to read one chunk at a time.

这篇关于如何通过 JDBC 将数据流式传输到 MariaDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！