How to write one-to-many paged SQL

This article introduces you how to write a pair of multi-page SQL, the content is very detailed, interested friends can refer to reference, I hope to help you.

MySQL one-to-many data pagination is a very common requirement, such as we want to query the image information of goods and commodities. But many people will encounter pagination errors here and get incorrect results.

MySQL one-to-many data pagination is a very common requirement, such as we want to query product and product image information. But many people will encounter pagination errors here and get incorrect results. Today we will analyze and solve this problem.

problem analysis

Let's create a simple commodity table and the corresponding commodity picture relationship table, and there is a one-to-many relationship between them:

Then I wrote some products and corresponding pictures of these products separately. The following left link query shows that there is an obvious one-to-many relationship between them:

SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL FROM PRODUCT_INFO P LEFT JOIN PRODUCT_IMAGE PI ON P.PRODUCT_ID = PI.PRODUCT_ID

Traditionally, our pagination statement would read:

When I pass in (0, 2) as expected to get the data for the first two products, the result is not what I expected:

2020-06-21 23:35:54.515 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page : ==> Preparing: SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL FROM PRODUCT_INFO P LEFT JOIN PRODUCT_IMAGE PI ON P.PRODUCT_ID = PI.PRODUCT_ID limit ?,? 2020-06-21 23:35:54.541 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page : ==> Parameters: 0(Long), 2(Long) 2020-06-21 23:35:54.565 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page :