小心避坑：MySQL分页时使用 limit+order by 会出现数据排序错误的问题
0 问题描述
在MySQL中我们通常会采用limit来进行翻页查询，比如limit(0,10)表示列出第一页的10条数据，limit(10,10)表示列出第二页。但是，当limit遇到order by的时候，可能会出现翻到第二页的时候，竟然又出现了第一页的记录或者错乱的情况。
准备脚本
CREATE TABLE `game_record_detail` (
`id` bigint NOT NULL COMMENT '主键',
`uid` bigint NOT NULL COMMENT '用户id',
`game_id` bigint NOT NULL COMMENT '游戏id',
`is_win` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '是否获胜，1获胜，-1失败',
`ranking` int DEFAULT NULL COMMENT '排名',
`game_player` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '本局对战玩家',
`create_time` timestamp NOT NULL COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='游戏记录细节表';
INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1, 10000, 106, '1', NULL, '1,2,3,4,5,6', '2021-08-31 15:27:53'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432626071458680832, 867317442, 1122334455, '2', NULL, '[867317442, 556239852]', '2021-08-31 16:47:27'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432630666478096384, 867317442, 1122334455, '2', NULL, '867317442, 556239852', '2021-08-31 17:05:43'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432630666515845120, 556239852, 1122334455, '2', NULL, '867317442, 556239852', '2021-08-31 17:05:43'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432631182637535232, 556239852, 106, '2', NULL, '556239852, 728436287', '2021-08-31 17:07:46'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432631182658506752, 728436287, 106, '2', NULL, '556239852, 728436287', '2021-08-31 17:07:46'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432631541284081664, 867317442, 106, '2', NULL, '867317442, 556239852', '2021-08-31 17:09:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432631541338607616, 556239852, 106, '1', NULL, '867317442, 556239852', '2021-08-31 17:09:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432637865803780096, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 17:34:19'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432637865824751616, 825247454, 106, '2', NULL, '762845945, 825247454', '2021-08-31 17:34:19'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432641744742256640, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 17:49:44'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432641744754839552, 825247454, 106, '2', NULL, '762845945, 825247454', '2021-08-31 17:49:44'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432645076089180160, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:02:58'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432645076110151680, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:02:58'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432645838835945472, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:06:00'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432645838861111296, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:06:00'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432646135582953472, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:07:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432646135700393984, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:07:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432646346938126336, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:08:01'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432646346950709248, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:08:01'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432646854088200192, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-08-31 18:10:02'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432646919573868544, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:10:18'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432647150810042368, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:11:13'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432647782753243136, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-08-31 18:13:43'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432647817255587840, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:13:52'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432648007643435008, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:14:37'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432648250476859392, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-08-31 18:15:35'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432648627293130752, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:17:05'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432648809455947776, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:17:48'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432649127237390336, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:19:04'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432649846799601664, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:21:55'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432650340574040064, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:23:53'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432650924689592320, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-08-31 18:26:12'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432651362570735616, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-08-31 18:27:57'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432652250681053184, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:31:29'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432652475067928576, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:32:22'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432653130742501376, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:34:58'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432653619911593984, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:36:55'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432656053711015936, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 18:46:35'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432657373293580288, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 18:51:50'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432661361346875392, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:07:41'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432661426236952576, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:07:56'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432662102811742208, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:10:38'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432662815595958272, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-08-31 19:13:27'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432662815650484224, 556239852, 106, '1', NULL, '867317442, 556239852', '2021-08-31 19:13:27'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432663113047609344, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:14:38'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432663113072775168, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:14:38'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432663166898278400, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:14:51'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432663166919249920, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:14:51'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432669472111595520, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:39:54'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432669472166121472, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:39:55'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432669528520790016, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-08-31 19:40:08'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432669528541761536, 556239852, 106, '1', NULL, '867317442, 556239852', '2021-08-31 19:40:08'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432669540969484288, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:40:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432669540990455808, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:40:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432670732017602560, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:44:55'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432670732034379776, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:44:55'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432670789701865472, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:45:09'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432670789718642688, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:45:09'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432670857146273792, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:45:25'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432670857163051008, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:45:25'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432671098658492416, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:46:22'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432671098671075328, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:46:22'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432671167449272320, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:46:39'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432671167470243840, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:46:39'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432671239444500480, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-08-31 19:46:56'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432671239461277696, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-08-31 19:46:56'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432890467757985792, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 10:18:04'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432890467862843392, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 10:18:04'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432890522975997952, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 10:18:17'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432890522992775168, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 10:18:17'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432893462138720256, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-09-01 10:29:58'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432893462184857600, 556239852, 106, '1', NULL, '867317442, 556239852', '2021-09-01 10:29:58'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432894257265512448, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-09-01 10:33:07'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432894257273901056, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-09-01 10:33:07'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432898078196764672, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 10:48:18'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432898078230319104, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 10:48:18'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432906012158988288, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-09-01 11:19:50'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432906012192542720, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-09-01 11:19:50'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432908499972657152, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 11:29:43'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432908500081709056, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 11:29:43'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432909920373706752, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-09-01 11:35:22'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432909920424038400, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-09-01 11:35:22'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432910158123634688, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-09-01 11:36:19'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432910158165577728, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-09-01 11:36:19'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432910394623660032, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 11:37:15'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432910394653020160, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 11:37:15'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432910761033863168, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-09-01 11:38:42'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432910761155497984, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-09-01 11:38:42'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432911072305745920, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 11:39:56'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432911072351883264, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 11:39:57'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432911375365181440, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-09-01 11:41:09'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432911375390347264, 556239852, 106, '1', NULL, '867317442, 556239852', '2021-09-01 11:41:09'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432924753034874880, 867317442, 106, '0', NULL, '867317442, 556239852', '2021-09-01 12:34:18'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432924753127149568, 556239852, 106, '1', NULL, '867317442, 556239852', '2021-09-01 12:34:18'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432926709270843392, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-09-01 12:42:05'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432926709472169984, 556239852, 106, '0', NULL, '867317442, 556239852', '2021-09-01 12:42:05'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432942600259768320, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 13:45:13'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432942600335265792, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 13:45:13'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432942744472522752, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-09-01 13:45:48'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432942744501882880, 556239852, 106, '0', NULL, '867317442, 556239852', '2021-09-01 13:45:48'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432942957287313408, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-09-01 13:46:38'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432942957316673536, 556239852, 106, '0', NULL, '867317442, 556239852', '2021-09-01 13:46:38'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432949440989237248, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-09-01 14:12:24'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432949441064734720, 556239852, 106, '0', NULL, '867317442, 556239852', '2021-09-01 14:12:24'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432966751393550336, 762845945, 106, '0', NULL, '762845945, 825247454', '2021-09-01 15:21:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432966751456464896, 825247454, 106, '1', NULL, '762845945, 825247454', '2021-09-01 15:21:11'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432972096736202752, 527324658, 106, '1', NULL, '527324658, 762845945', '2021-09-01 15:42:26'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432972096786534400, 762845945, 106, '0', NULL, '527324658, 762845945', '2021-09-01 15:42:26'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432985265403727872, 527324658, 106, '1', NULL, '527324658, 762845945', '2021-09-01 16:34:46'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432985265504391168, 762845945, 106, '0', NULL, '527324658, 762845945', '2021-09-01 16:34:46'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432987267663794176, 527324658, 106, '1', NULL, '527324658, 762845945', '2021-09-01 16:42:43'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1432987267680571392, 762845945, 106, '0', NULL, '527324658, 762845945', '2021-09-01 16:42:43'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433000963173650432, 762845945, 106, '1', NULL, '762845945, 825247454', '2021-09-01 17:37:08'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433000963194621952, 825247454, 106, '0', NULL, '762845945, 825247454', '2021-09-01 17:37:08'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433005055392485376, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-09-01 17:53:24'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433005055451205632, 556239852, 106, '0', NULL, '867317442, 556239852', '2021-09-01 17:53:24'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433011029247397888, 527324658, 106, '0', NULL, '527324658, 762845945', '2021-09-01 18:17:08'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433011029339672576, 762845945, 106, '1', NULL, '527324658, 762845945', '2021-09-01 18:17:08'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433038173008695296, 867317442, 106, '1', NULL, '867317442, 556239852', '2021-09-01 20:05:00'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433038173046444032, 556239852, 106, '0', NULL, '867317442, 556239852', '2021-09-01 20:05:00'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433038331830210560, 556239852, 106, '1', NULL, '556239852, 728436287', '2021-09-01 20:05:38'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433038331851182080, 728436287, 106, '0', NULL, '556239852, 728436287', '2021-09-01 20:05:38'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433387895233843200, 527324658, 106, '0', NULL, '527324658, 762845945', '2021-09-02 19:14:40'); INSERT INTO `game_record_detail`(`id`, `uid`, `game_id`, `is_win`, `ranking`, `game_player`, `create_time`) VALUES (1433387895489695744, 762845945, 106, '1', NULL, '527324658, 762845945', '2021-09-02 19:14:40');
具体如下：
全部查询
SELECT uid,game_player FROM `game_record_detail` where is_win=0 order by game_id
查询前五条数据
SELECT uid,game_player FROM `game_record_detail` where is_win=0 order by game_id LIMIT 0,5;
我们可以发现当前数据是乱序的和order所有数据进行对比。
使用上述SQL查询的时候，出现乱序的情况。而如果使用如下方式，则不会出现重复的情况：
SELECT * FROM `game_record_detail` where is_win=0
SELECT * FROM `game_record_detail` where is_win=0 order by game_id LIMIT 0,5;
但是，由于表的字段很多，仅仅希望用这两个字段。为了解决这个情况，在ORDER BY后面使用了两个排序条件来解决这个问题，如下：
SELECT `post_title`, `post_date`FROM postWHERE `post_status` = 'publish'ORDER BY view_count desc, ID ascLIMIT 5, 5按理来说，MySQL的排序默认情况下是以主键ID作为排序条件的，也就是说，如果在view_count相等的情况下，主键ID作为默认的排序条件，不需要我们多此一举加ID asc。但是事实就是，MySQL再order by和limit混用的时候，出现了排序的混乱情况。
1 分析问题
当排序字段存在重复值的时候分页程序查出来的数据有错误
2 解决方法
1.索引排序字段
如果在字段添加上索引，就直接按照索引的有序性进行读取并分页，从而可以规避遇到的这个问题。
实际测试发现：没屌用
2.order by增加id字段
SELECT uid,game_player FROM `game_record_detail` where is_win=0 order by game_id,id
SELECT uid,game_player FROM `game_record_detail` where is_win=0 order by game_id,id LIMIT 0,5;
本文来自博客园，作者：小陈子博客，转载请注明原文链接：https://www.cnblogs.com/cj8357475/p/16539577.html
