How to determine if the data is greater than N when querying data? Is it recursive?

For example, there are 300,000 pieces of data in the database. Now there are 30,000 pieces of data that match the query according to the conditions. Fetching too many at one time may be slow or other problems. I want to fetch 10,000 each time and fetch it three times. This is just an example. How should it be implemented? Use recursion?
Let me know. Thank you! ! !
Also, if the data from two tables are merged together, is there any way to sort them according to a certain field?

Reply content:

For example, there are 300,000 pieces of data in the database. Now there are 30,000 pieces of data that match the query according to the conditions. Fetching too many at one time may be slow or other problems. I want to fetch 10,000 each time and fetch it three times. This is just an example. How should it be implemented? Use recursion?
Let me know. Thank you! ! !
Also, if the data from two tables are merged together, is there any way to sort them according to a certain field?

Thanks for the invitation. For result sets with large amounts of data, I currently generally use the following two methods to process them. The purpose of using these two methods of processing is: first, to reduce the occupation of bandwidth resources when obtaining the data set, and second, to reduce the memory usage of the program when processing the result set.

1. Blocking is the idea of ​​the question

<code>$sql = 'SELECT xxx FROM table WHERE xxx LIMIT limit ：limit offset :offset';
$limit = 10000;
$offset = 0;
do{
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    $stmt->execute(array('：limit' => $limit, ':offset' => $offset));
    $data = $stmt->fetchAll();
    // 处理逻辑
    $offset += $limit;
}while(count($data));</code>

1. Cursor, read and return by row

<code>$sql = 'SELECT xxx FROM table WHERE xxx';
$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
do {
    // 处理逻辑 $row
} while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR));</code>

Personally suggest that when you want to find the subsequent blocks, use the where condition instead of directly limit 100000, 100000:
The difference between the two is as follows:

<code>//这个是第一种，取第20w开始的10w条数据
select * from table limit 100000 , 100000 order by id
//这是第二种取法
select * from table where id > 100000 limit 100000 order by id
因为mysql用limit越往后，排序之后取后面的越慢</code>

It can be done directly through sql statements, no background processing is required; it can be sorted according to the fields you specifyorder by

Joint table query
For example: SELECT a.* from tableA as a, tableB as b where a.id = b.id order by a.id desc limit 0,10000";

You can first select count(1) from table where condition. See how much data there is in total. When there are more than N items,
you can select * from table limit m,n to query
2 tables in paging,
select * from table1 union all select * form table2 order by a certain field. Note that the number of columns in the query must be the same. The same goes for the best type

Assuming that table IDs are evenly distributed, you can use the ID interval to query data in segments

<code class="php">//每次计划读取的数据条数
$max_per_size = 10000;
//找到符合条件的最小ID和最大ID
$sql = "SELECT min(id),max(id) AS max_id AS num FROM `table` WHERE xx='xx'";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$res = $stmt->fetch(PDO::FETCH_ASSOC);
//符合条件的最大ID
$max_id = $res['max_id'];
//符合条件的最小ID
$min_id = $res['min_id'];
$times = ceil(($max_id - $min_id + 1) / $max_per_size);
//第一次的ID区间
$current_min_id = $min_id;
$current_max_id = $min_id + $max_per_page;
for($i = 0; $i<$times; $i++) {
    //生成SQL语句
    $sql = "SELECT * FROM `table` WHERE xx='xx' AND (id>= {$current_min_id} AND id <= {$current_max_id})";
    //得到数据之后的操作
    //.........
    //重新计算下一次的ID区间
    $current_min_id = $current_max_id + 1;
    $current_max_id = $current_max_id + $max_per_page;
    if ($current_max_id > $max_id) {
        $current_max_id = $max_id;
    }
}</code>