一次使用固定数量的结果流式传输MySql ResultSet [英] Streaming MySql ResultSet with fixed number of results at a time

问题描述

我的MySql表有1600万条记录，由于一些迁移工作，我正在阅读整个Mysql表。

I have MySql table with 16 millions records, because of some migration work I'm reading whole Mysql table.

以下代码用于流式传输大型ResultSet MySql

The following code is used for streaming large ResultSet in MySql

        statement = connection.createStatement(
                java.sql.ResultSet.TYPE_FORWARD_ONLY,
                java.sql.ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(Integer.MIN_VALUE);

但这是一次流式传输一个结果，是否意味着我们每行都要点击MySql服务器

but this is streaming one result at a time , does it mean we are hitting MySql server for each row

使用流媒体时我们可以设置类似这样的语句.setFetchSize（1000）;

while using streaming can we set something like this statement.setFetchSize(1000);

我想减少流式传输大型ResultSet时到服务器的往返次数

I want to reduce number of round trips to server while streaming large ResultSet

推荐答案

我将假设您使用的是MySQL官方提供的JDBC驱动程序连接器/ J。

I will assume that you are using the official MySQL provided JDBC driver Connector/J.

您明确告诉JDBC（和MySQL）使用 statement.setFetchSize（Integer.MIN_VALUE）逐行传输结果;

You are explicitly telling JDBC (and MySQL) to stream the results row-by-row with statement.setFetchSize(Integer.MIN_VALUE);

来自 MYSQL文档：

默认情况下，ResultSet完全是检索并存储在内存中。
在大多数情况下，这是最有效的操作方式，由于
，MySQL网络协议的设计更容易实现。如果
您正在使用具有大量行或
大值的ResultSet，并且无法在JVM中为所需的
内存分配堆空间，则可以告诉驱动程序要流结果一次返回
一行。

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

要启用此功能，您需要以下列方式创建Statement实例
：

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
          java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

只进，只读结果集与fetch
的组合Integer.MIN_VALUE的大小用作驱动程序的信号，以逐行流式传输
结果集。在此之后，将逐行检索使用
语句创建的任何结果集。

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

除<之外的任何值MySQL忽略了获取大小的code> Integer.MIN_VALUE ，并且标准行为适用。整个结果集将由JDBC驱动程序提取。

Any value other than Integer.MIN_VALUE for the fetch size is ignored by MySQL, and the standard behavior applies. The entire result set will be fetched by the JDBC driver.

要么不使用 setFetchSize（），那么JDBC驱动程序将使用默认值（ 0 ），或者将值设置为 0 。使用 0 的值也将确保JDBC不使用MySQL游标，这可能会发生，这取决于您的MySQL和Connector / J版本和配置。

Either don't use setFetchSize(), so the JDBC driver will use the default value (0), or set the value to 0 explicitly. Using the value of 0 will also ensure that JDBC doesn't use use MySQL cursors, which may occur depending on your MySQL and Connector/J versions and configuration.

这篇关于一次使用固定数量的结果流式传输MySql ResultSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！