Shulou(Shulou.com)05/31 Report--

This article shows you how to write an one-to-multi-page SQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Preface

MySQL one-to-many data paging is a very common requirement, for example, we need to query goods and product picture information. But many people will encounter the misunderstanding of paging here and get incorrect results. Today we are going to analyze and solve this problem.

two。 Analysis of problems

Let's first create a simple merchandise table and a corresponding commodity picture relation table, which have an one-to-many relationship:

Then I wrote some goods and their corresponding pictures, and we can see that there is an obvious one-to-many relationship between them through the left link query below:

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

According to the traditional way of thinking, our paging sentence would say:

SELECT P.PRODUCT_ID, P.PRODNAMEMagna FROM PRODUCT_INFO P LEFT JOIN PRODUCT_IMAGE PI ON P.PRODUCT_ID = PI.PRODUCT_ID LIMIT # {size}, # {IMAGEN URL

When I tried to get the data of the first two products as expected, the result was not what I expected:

2020-06-21 23 DEBUG 35 DEBUG 10980-[main] c.f.m.mappers.ProductInfoMapper.page: = = > Preparing: SELECT P.PRODUCT_ID, P.PRODNAMEMagazine PI.IMAGEe URL FROM PRODUCT_INFO P LEFT JOIN PRODUCT_IMAGE PI ON P.PRODUCT_ID = PI.PRODUCT_ID limit?,? 2020-06-21 2323 351 DEBUG 10980-[main] c.f.m.mappers.ProductInfoMapper.page: = = > Parameters: 0 (Long) 2 (Long) 2020-06-21 23 main 3515 DEBUG 10980-[main] c.f.m.mappers.ProductInfoMapper.page:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.