mysql中limit查詢方法怎麼使用-mysql教程-PHP中文網

mysql中limit查詢方法怎麼使用

WBOY

發布： 2023-05-27 15:05:36

轉載

1817 人瀏覽過

背景

最近專案聯調的時候發現了分頁查詢的一個bug，分頁查詢總有資料查不出來或重複查出。

資料庫總共14筆記錄。

mysql中limit查詢方法怎麼使用

如果依照一頁10條。那麼第一頁和第二頁的查詢SQL和和結果如下。

那麼問題來了，查詢第一頁和第二頁的時候都出現了11,12,13的記錄，而且都沒出現 4 的記錄。總有數據查不到這是為啥？？？

mysql中limit查詢方法怎麼使用

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;);

登入後複製