How to use limit query method in mysql-Mysql Tutorial-php.cn

How to use limit query method in mysql

WBOY

Release： 2023-05-27 15:05:36

forward

1816 people have browsed it

Background

Recently, during project joint debugging, a bug in paging query was discovered. Page query always has data that cannot be found or is found repeatedly.

The database has a total of 14 records.

How to use limit query method in mysql

If you follow 10 items per page. Then the query SQL sum results of the first page and the second page are as follows.

Then here comes the problem. When querying the first and second pages, records of 11, 12, and 13 appear , and does not appear 4 Record. Why can’t I always find data? ? ?

How to use limit query method in mysql

SQL

DROP TABLE IF EXISTS `creative_index`;
CREATE TABLE `creative_index` (
  `id` bigint(20) NOT NULL COMMENT &#39;id&#39;,
  `creative_id` bigint(20) NOT NULL COMMENT &#39;creative_id&#39;,
  `name` varchar(256) DEFAULT NULL COMMENT &#39;name&#39;,
  `member_id` bigint(20) NOT NULL COMMENT &#39;member_id&#39;,
  `product_id` int(11) NOT NULL COMMENT &#39;product_id&#39;,
  `template_id` int(11) DEFAULT NULL COMMENT &#39;template_id&#39;,
  `resource_type` int(11) NOT NULL COMMENT &#39;resource_type&#39;,
  `target_type` int(11) NOT NULL COMMENT &#39;target_type&#39;,
  `show_audit_status` tinyint(4) NOT NULL COMMENT &#39;show_audit_status&#39;,
  `bound_adgroup_status` int(11) NOT NULL COMMENT &#39;bound_adgroup_status&#39;,
  `gmt_create` datetime NOT NULL COMMENT &#39;gmt_create&#39;,
  `gmt_modified` datetime NOT NULL COMMENT &#39;gmt_modified&#39;,
  PRIMARY KEY (`id`),
  KEY `idx_member_id_product_id_template_id` (`member_id`,`product_id`,`template_id`),
  KEY `idx_member_id_product_id_show_audit_status` (`member_id`,`product_id`,`show_audit_status`),
  KEY `idx_creative_id` (`creative_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=&#39;测试表&#39;;

-- ----------------------------
-- Records of creative_index
-- ----------------------------
INSERT INTO `creative_index` VALUES (&#39;1349348501&#39;, &#39;511037002&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000695&#39;, &#39;26&#39;, &#39;1&#39;, &#39;7&#39;, &#39;0&#39;, &#39;2023-03-16 22:12:56&#39;, &#39;2023-03-24 23:38:49&#39;);
INSERT INTO `creative_index` VALUES (&#39;1349348502&#39;, &#39;511037003&#39;, &#39;2&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000695&#39;, &#39;26&#39;, &#39;1&#39;, &#39;7&#39;, &#39;1&#39;, &#39;2023-03-16 22:15:29&#39;, &#39;2023-03-24 21:23:33&#39;);
INSERT INTO `creative_index` VALUES (&#39;1391561502&#39;, &#39;512066002&#39;, &#39;3&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000695&#39;, &#39;26&#39;, &#39;1&#39;, &#39;7&#39;, &#39;0&#39;, &#39;2023-03-23 23:37:34&#39;, &#39;2023-03-24 21:24:04&#39;);
INSERT INTO `creative_index` VALUES (&#39;1394049501&#39;, &#39;511937501&#39;, &#39;4&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000942&#39;, &#39;2&#39;, &#39;1&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2023-03-24 14:00:46&#39;, &#39;2023-03-25 15:19:37&#39;);
INSERT INTO `creative_index` VALUES (&#39;1394221002&#39;, &#39;511815502&#39;, &#39;5&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;7&#39;, &#39;0&#39;, &#39;2023-03-23 17:00:41&#39;, &#39;2023-03-24 21:23:39&#39;);
INSERT INTO `creative_index` VALUES (&#39;1394221003&#39;, &#39;511815503&#39;, &#39;6&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;3&#39;, &#39;0&#39;, &#39;2023-03-23 17:22:00&#39;, &#39;2023-03-24 21:23:44&#39;);
INSERT INTO `creative_index` VALUES (&#39;1394257004&#39;, &#39;512091004&#39;, &#39;7&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;7&#39;, &#39;0&#39;, &#39;2023-03-23 17:23:21&#39;, &#39;2023-03-24 21:24:11&#39;);
INSERT INTO `creative_index` VALUES (&#39;1394257005&#39;, &#39;512091005&#39;, &#39;8&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;3&#39;, &#39;0&#39;, &#39;2023-03-23 17:31:05&#39;, &#39;2023-03-25 01:10:58&#39;);
INSERT INTO `creative_index` VALUES (&#39;1403455006&#39;, &#39;512170006&#39;, &#39;9&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2023-03-25 15:31:02&#39;, &#39;2023-03-25 15:31:25&#39;);
INSERT INTO `creative_index` VALUES (&#39;1403455007&#39;, &#39;512170007&#39;, &#39;10&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000695&#39;, &#39;26&#39;, &#39;1&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2023-03-25 15:31:04&#39;, &#39;2023-03-25 15:31:28&#39;);
INSERT INTO `creative_index` VALUES (&#39;1406244001&#39;, &#39;512058001&#39;, &#39;11&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;3&#39;, &#39;0&#39;, &#39;2023-03-23 21:28:11&#39;, &#39;2023-03-24 21:23:56&#39;);
INSERT INTO `creative_index` VALUES (&#39;1411498502&#39;, &#39;512233003&#39;, &#39;12&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2023-03-25 14:34:37&#39;, &#39;2023-03-25 17:00:24&#39;);
INSERT INTO `creative_index` VALUES (&#39;1412288501&#39;, &#39;512174007&#39;, &#39;13&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000694&#39;, &#39;26&#39;, &#39;1&#39;, &#39;7&#39;, &#39;0&#39;, &#39;2023-03-25 01:11:53&#39;, &#39;2023-03-25 01:12:34&#39;);
INSERT INTO `creative_index` VALUES (&#39;1412288502&#39;, &#39;512174008&#39;, &#39;14&#39;, &#39;1&#39;, &#39;1&#39;, &#39;1000942&#39;, &#39;2&#39;, &#39;1&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2023-03-25 11:46:44&#39;, &#39;2023-03-25 15:20:58&#39;);

Copy after login

Solution to the problem

As can be seen from the query results, the query results are obviously not sorted according to a certain column (verychaos).

Then is it enough to add a sorting rule?? With the attitude of giving it a try, it really worked out.

How to use limit query method in mysql

Analysis problem

Why limit query will appear without adding order byPaging query always has data that cannot be found or is found repeatedly? Is there an implicit order sorting?

At this timeexplain appears.

How to use limit query method in mysql

The index has two functions: retrieval and sorting

Because the two SQLs use different indexes (sorting rules) , the above problem will occur when the index limit is released, and the problem is solved.

The above is the detailed content of How to use limit query method in mysql. For more information, please follow other related articles on the PHP Chinese website!