MySQL cursor based pagination with multiple columns
<p>I have some table that I want to query using cursor based pagination, but it needs to apply for multiple columns.</p>

<p>Let's take a simplified example of using 2 columns - I fetch the first page like this:</p>



<pre class="lang-sql prettyprint-override"><code>SELECT column_1, column_2
FROM table_name
ORDER BY column_1, column_2
LIMIT 10
</code></pre>

<p>After I get the results, I can fetch the next page based on the last row. Let's say the last row was <code>column_1 = 5, column_2 = 8</code>. I'd like to do something like this:</p>

<pre class="lang-sql prettyprint-override"><code>SELECT column_1, column_2
FROM table_name
WHERE column_1 &gt; 5 AND column_2 &gt; 8
ORDER BY column_1, column_2
LIMIT 10
</code></pre>

<p>But this is obviously wrong. It would filter out a row that has <code>column_1 = 5, column_2 = 9</code> (because of the filter on <code>column_1</code>) or a row that has <code>column_1 = 6, column_2 = 6</code> (because of the filter on <code>column_2</code>)</p>

<p>I can do something like this to avoid the problem:</p>

<pre class="lang-sql prettyprint-override"><code>SELECT column_1, column_2
FROM table_name
WHERE column_1 &gt; 5
OR (column_1 = 5 AND column_2 &gt; 8)
ORDER BY column_1, column_2
LIMIT 10
</code></pre>

<p>But this becomes very cumbersome and error prone for more than 2 columns...</p>

<p>Also, my use case includes columns of multiple types (<code>INT UNSIGNED</code> and <code>BINARY</code>), but all are comparable</p>

<p>Do you have any suggestions?</p>

<p>Thanks!</p>