Re: How to process very large result set from a dynamic cursor

[Rick James]

Is your PRIMARY KEY a single column? If so, this should work (assuming `id` is the PK; does not matter whether it is numeric or string):

SELECT @a := MIN(id) FROM tbl;
Loop...
SELECT @z := id FROM tbl WHERE id >= @a LIMIT 1000,1; # find end of this batch
if @z is NULL, then you are in the last part of the table; exit the Loop.
SELECT * FROM tbl WHERE id >= @a AND id < @z;
Act on the rows from that query
SET @a = @z;
Repeat.
End Loop.
SELECT * FROM tbl WHERE id >= @a; # The last batch
Act on the rows from that query

Caveat 1: If any rows are INSERTed or DELETEd during the process, you will miss them.
Caveat 2: Wrapping the whole thing in an InnoDB transaction might defeat the purpose of avoiding locks, etc.

The principle here is independent of Stored Procedures, Java, etc.