一个让人惊讶的排序导致的死循环问题原创
首先重现问题–表中的数据大概是这样的：
+----------+---------------------+
| image_no | create_time |
+----------+---------------------+
| e5 | 2019-06-07 01:21:57 |
| Q1 | 2019-06-07 01:21:20 |
| Q2 | 2019-06-07 01:21:46 |
| Q3 | 2019-06-07 01:21:49 |
| Q4 | 2019-06-07 01:21:52 |
| v1 | 2019-06-07 01:22:06 |
| v2 | 2019-06-07 01:22:19 |
+----------+---------------------+
备注：由于历史原因，image_no虽然是主键，但是不是int类型，而是varchar类型。当然，不推荐这样的做法，应该为创建的每一张表，都尽可能增加一个int类型的自增ID为主键。
业务需求是需要遍历这个表做一些事情（do something），很明显，笔者选择根据主键排序来遍历这个表，对应的SQL大概是这样的：
select * from test where image_no>=? order by image_no asc limit ?;
需要说明的是，笔者并没有使用limit m,n这种分页，而且使用image > ? limit ?，是因为当遍历的偏移量很大时，后者的性能远比前者要更好。
所以，其对应的实际SQL如下：
mysql> select * from test where image_no>='0' order by image_no asc limit 5;
+----------+---------------------+
| image_no | create_time |
+----------+---------------------+
| e5 | 2019-06-07 01:21:57 |
| Q1 | 2019-06-07 01:21:20 |
| Q2 | 2019-06-07 01:21:46 |
| Q3 | 2019-06-07 01:21:49 |
| Q4 | 2019-06-07 01:21:52 |
+----------+---------------------+
而伪业务代码如下：
public class OrderTest {
public static void main(String[] args) {
String imageNo = "0";
List<String> list;
int pageSize = 5;
do {
list = getListFromDB(imageNo, pageSize);
// do something
Collections.sort(list);
// 更新imageNo，即下一次查询DB时最小imageNo，也是当前这批数据的最大imageNo
imageNo = list.get(list.size()-1);
}while (list.size()==pageSize);
}
// 模拟从数据库中批量获取数据
private static List<String> getListFromDB(String imageNo, int pageSize){
List<String> orders = new ArrayList<>();
// select * from test where image_no>='0' order by image_no asc limit 5;的结果就是这样的顺序
orders.add("e5");orders.add("Q1");orders.add("Q2");orders.add("Q3");orders.add("Q4");
return orders;
}
}
我们把断点放在while的上一行。debug这段代码后，我们发现第一轮遍历后，imageNo变成了e5。请注意，是e5，而不是Q4。但是我们的SQL结果顺序却是：e5，Q1，Q2，Q3，Q4。这时候，第二轮遍历的SQL应该是这样的–image_no变成了e5，我们惊讶的发现，它和第一轮遍历即image_no为0时的结果是完全一样的。如此一来，就会在这里发生死循环：
mysql> select * from test where image_no>='e5' order by image_no asc limit 5;
+----------+---------------------+
| image_no | create_time |
+----------+---------------------+
| e5 | 2019-06-07 01:21:57 |
| Q1 | 2019-06-07 01:21:20 |
| Q2 | 2019-06-07 01:21:46 |
| Q3 | 2019-06-07 01:21:49 |
| Q4 | 2019-06-07 01:21:52 |
+----------+---------------------+
很明显，根据上面的分析，我们预期在第二轮遍历时，image_no应该是Q4才对，而不应该是e5。这是为什么呢？很明显，我们的业务代码中篡改了顺序。debug一下很容易发现，是执行Collections.sort(list)前后，导致顺序发生了变化。我们的SQL中本来就已经order by image_no asc，这里再次对结果集合进行排序，并且排序前后，list的顺序有变，这就说明：数据库中的order by field asc和Collections.sort(list)的排序规则不一样。
- Collections.sort()
我们先来看Java中Collections.sort()这个方法对应的排序规则，通过源码可知，它的排序依赖的是要比较的类实现的compareTo方法。我们这里比较的是字符串，所以，其对应的排序规则应该在String.java中的compareTo中，源码如下：
public int compareTo(String anotherString) {
int len1 = value.length;
int len2 = anotherString.value.length;
int lim = Math.min(len1, len2);
char v1[] = value;
char v2[] = anotherString.value;
int k = 0;
while (k < lim) {
char c1 = v1[k];
char c2 = v2[k];
if (c1 != c2) {
return c1 - c2;
}
k++;
}
return len1 - len2;
}
逻辑很简单，就是从左到右，依次比较每一个字符。所以，本质是ASCII码的比较。而Q的ASCII码是81，e的ASCII码是101，很明显，e比Q要大。
- order by field asc
那数据库中的order by field asc是按照什么排序的呢？很明显，不是按照ASCII码来排序的。事实上，数据库的这个排序默认是大小写不敏感的。我们可以通过如下的例子验证。第一次插入的数据是bb和cc，第二次插入的数据是bb和CC，但是select * from tb_afei order by image_no asc;的结果完全一样：
drop table if exists tb_afei;
CREATE TABLE `tb_afei` (
`image_no` varchar(40) NOT NULL COMMENT '编号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
PRIMARY KEY (`image_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into tb_afei values('bb', now());
insert into tb_afei values('cc', now());
select * from tb_afei order by image_no asc;
+----------+---------------------+
| image_no | create_time |
+----------+---------------------+
| bb | 2019-07-02 10:00:14 |
| cc | 2019-07-02 10:00:14 |
+----------+---------------------+
truncate table tb_afei;
insert into tb_afei values('bb', now());
insert into tb_afei values('CC', now());
select * from tb_afei order by image_no asc;
+----------+---------------------+
| image_no | create_time |
+----------+---------------------+
| bb | 2019-07-02 10:00:14 |
| CC | 2019-07-02 10:00:14 |
+----------+---------------------+
解决问题
既然知道问题所在，最后就是解决问题了。很明显，因为数据库的order by和java提供的Collections.sort()排序规则不一致，所以业务代码中的Collections.sort()是完全多余的，只需要将其删除即可正常遍历整个表，即使主键是varchar也能利用order by顺利的遍历完（多说一句：这就是没有int类型自增主键带来的麻烦，cry…）。
写在最后
这篇文章前提是数据库设定的排序规则是utf8_general_ci（这也是mysql默认的排序规则，你也可以通过show full columns from tb_afei;结果中的Collation的值来获取字段具体使用的排序规则），utf8_general_ci中的ci就是case insensitive的简写，即大小写不敏感。所以设置排序规则是utf8_general_ci时是不会区分大小写的。排序规则命令有一定的规则：如_ci结尾表示大小写不敏感（caseinsensitive），_cs表示大小写敏感（case sensitive），_bin表示二进制的比较（binary），也会区分大小写。
