How to read data in batches by mysql

scenario is that there are a series of post (news category) in the database. Now you want to sort according to the post_date in the post table (time), view (browsing), or the likeCount of the post_like table, and read the data in batches. The limit is 25

.

for example, post_date:

SELECT post.*, user.user_avatar, user.user_name,

        (SELECT COUNT(*) FROM comment
        WHERE comment.post_id = post.post_id) AS commentCount,
        (SELECT COUNT(*) FROM post_like
        WHERE post_like.post_id = post.post_id) AS likeCount
  FROM post, user where post.user_id=user.user_id and post.post_date < "2018-04-05 12:12:35"  order by post.post_date desc limit 25
  

here post_date can grab the post_date of the last entry and use it as the tag value to be obtained next time.

but the problem is: if I want to read the data in batches through view or likeCount sorting, there is no tag value in the obtained data that can be used as a tag for the next comparison.

The

mysql statement is as follows:

SELECT post.*, user.user_avatar, user.user_name,

        (SELECT COUNT(*) FROM comment
        WHERE comment.post_id = post.post_id) AS commentCount,
        (SELECT COUNT(*) FROM post_like
        WHERE post_like.post_id = post.post_id) AS likeCount
  FROM post, user where post.user_id=user.user_id  order by likeCount desc limit 25
  

how do people usually solve the problem? Thank you

Mar.02,2021

limit n code m , indicating that the initial value is n, and then m records are taken out. If batch size is 25, then you can:
limit 25 , limit 25 , limit 50 . Next, by default, the primary key id of the table is sorted in ascending order, and the largest primary key id of the processed record is recorded each time (based on the assumption that this table is a self-increasing primary key)

if there are no new records in this table, the above method is certainly fine, but if the table has multiple transactions writing concurrently, it may cause the large id record to be processed before the small id record (two transactions), resulting in this part of the small id record never being processed.

Using post_date in

problems actually has the same problem. There is no guarantee that the small data records of post_date will enter the database first before the large records of post_date . Insert is early, and records with small id are not necessarily inserted into the database earlier than large records from id. This depends entirely on the commit time of the transaction.

This is how the paging of the

list is implemented. This idea can not be wrong


for example, there are 110 pieces of data, 25 pages per page, that has a total of 5 pages. At the beginning of the page is the data obtained by select from post order by view desc limit 0Phone25. The second page select from post order by view desc limit 25 is loaded as the page slides up. It should be OK to calculate the first parameter of limit when loading more. It's been tested, and it's okay.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b333e9-2be62.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b333e9-2be62.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?