当我们在使用order by的时候,有一个很经典的坑。不知道大家是否踩过。
当我们使用order
by子句对结果进行排序时,如果有相同值的情况,MySQL会在排序时随机地对它们进行排列。这意味着,如果我们不注意在分页时正确地处理这些相同值,就会导致同一条数据出现在多个页面上.
1. order by 坑的复现
我先创建一个表用户表:
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`signup_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
然后我们执行批量数据插入:
-- 插入大量数据
DELIMITER $$
CREATE PROCEDURE InsertUsers()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000 DO
INSERT INTO users (name, age, signup_date) VALUES
(CONCAT('User', i), FLOOR(RAND() * 30) + 20, DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 1000) DAY));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL InsertUsers();
假设现在20岁的用户有这么多,其中20是年纪最小的。
假设需求是这样的:要求分页查询用户表,按照年龄,从小到大,每一页10个
于是有第一页:
SELECT * FROM users ORDER BY age LIMIT 10 OFFSET 0;
第二页:
SELECT * FROM users ORDER BY age LIMIT 10 OFFSET 10;
大家可以发现,id=315的记录,即在第一页出现,又在第二页出现,这是不是很坑。
2. 如何解决
如果在某些业务需求,我们确实需要对一个非唯一属性约束的字段排序分页的话,我们可以通过某个唯一属性的辅助字段实现。
比如我们以上的例子,如果年龄相同的话,我们就按照id来排序就好。
SQL如下:
SELECT * FROM users ORDER BY age,id LIMIT 10 OFFSET 0;
我们再来第二页,就不会同个记录在不同的页码种都出现啦。
SELECT * FROM users ORDER BY age,id LIMIT 10 OFFSET 10;