mysql中limit查询踩坑实战记录

吾爱主题阅读：164 2023-03-30 15:59:00 评论：0

背景

最近项目联调的时候发现了分页查询的一个bug，分页查询总有数据查不出来或者重复查出。

数据库一共14条记录。

如果按照一页10条。那么第一页和第二页的查询SQL和和结果如下。

那么问题来了，查询第一页和第二页的时候都出现了11,12,13的记录，而且都没出现 4 的记录。总有数据查不到这是为啥？？？

SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 DROP TABLE IF EXISTS `creative_index`; CREATE TABLE `creative_index` ( `id` bigint (20) NOT NULL COMMENT 'id' , `creative_id` bigint (20) NOT NULL COMMENT 'creative_id' , ` name ` varchar (256) DEFAULT NULL COMMENT 'name' , `member_id` bigint (20) NOT NULL COMMENT 'member_id' , `product_id` int (11) NOT NULL COMMENT 'product_id' , `template_id` int (11) DEFAULT NULL COMMENT 'template_id' , `resource_type` int (11) NOT NULL COMMENT 'resource_type' , `target_type` int (11) NOT NULL COMMENT 'target_type' , `show_audit_status` tinyint(4) NOT NULL COMMENT 'show_audit_status' , `bound_adgroup_status` int (11) NOT NULL COMMENT 'bound_adgroup_status' , `gmt_create` datetime NOT NULL COMMENT 'gmt_create' , `gmt_modified` datetime NOT NULL COMMENT 'gmt_modified' , 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= '测试表' ; -- ---------------------------- -- Records of creative_index -- ---------------------------- INSERT INTO `creative_index` VALUES ( '1349348501' , '511037002' , '1' , '1' , '1' , '1000695' , '26' , '1' , '7' , '0' , '2023-03-16 22:12:56' , '2023-03-24 23:38:49' ); INSERT INTO `creative_index` VALUES ( '1349348502' , '511037003' , '2' , '1' , '1' , '1000695' , '26' , '1' , '7' , '1' , '2023-03-16 22:15:29' , '2023-03-24 21:23:33' ); INSERT INTO `creative_index` VALUES ( '1391561502' , '512066002' , '3' , '1' , '1' , '1000695' , '26' , '1' , '7' , '0' , '2023-03-23 23:37:34' , '2023-03-24 21:24:04' ); INSERT INTO `creative_index` VALUES ( '1394049501' , '511937501' , '4' , '1' , '1' , '1000942' , '2' , '1' , '0' , '0' , '2023-03-24 14:00:46' , '2023-03-25 15:19:37' ); INSERT INTO `creative_index` VALUES ( '1394221002' , '511815502' , '5' , '1' , '1' , '1000694' , '26' , '1' , '7' , '0' , '2023-03-23 17:00:41' , '2023-03-24 21:23:39' ); INSERT INTO `creative_index` VALUES ( '1394221003' , '511815503' , '6' , '1' , '1' , '1000694' , '26' , '1' , '3' , '0' , '2023-03-23 17:22:00' , '2023-03-24 21:23:44' ); INSERT INTO `creative_index` VALUES ( '1394257004' , '512091004' , '7' , '1' , '1' , '1000694' , '26' , '1' , '7' , '0' , '2023-03-23 17:23:21' , '2023-03-24 21:24:11' ); INSERT INTO `creative_index` VALUES ( '1394257005' , '512091005' , '8' , '1' , '1' , '1000694' , '26' , '1' , '3' , '0' , '2023-03-23 17:31:05' , '2023-03-25 01:10:58' ); INSERT INTO `creative_index` VALUES ( '1403455006' , '512170006' , '9' , '1' , '1' , '1000694' , '26' , '1' , '0' , '0' , '2023-03-25 15:31:02' , '2023-03-25 15:31:25' ); INSERT INTO `creative_index` VALUES ( '1403455007' , '512170007' , '10' , '1' , '1' , '1000695' , '26' , '1' , '0' , '0' , '2023-03-25 15:31:04' , '2023-03-25 15:31:28' ); INSERT INTO `creative_index` VALUES ( '1406244001' , '512058001' , '11' , '1' , '1' , '1000694' , '26' , '1' , '3' , '0' , '2023-03-23 21:28:11' , '2023-03-24 21:23:56' ); INSERT INTO `creative_index` VALUES ( '1411498502' , '512233003' , '12' , '1' , '1' , '1000694' , '26' , '1' , '0' , '0' , '2023-03-25 14:34:37' , '2023-03-25 17:00:24' ); INSERT INTO `creative_index` VALUES ( '1412288501' , '512174007' , '13' , '1' , '1' , '1000694' , '26' , '1' , '7' , '0' , '2023-03-25 01:11:53' , '2023-03-25 01:12:34' ); INSERT INTO `creative_index` VALUES ( '1412288502' , '512174008' , '14' , '1' , '1' , '1000942' , '2' , '1' , '0' , '0' , '2023-03-25 11:46:44' , '2023-03-25 15:20:58' );