一个让人惊讶的排序导致的死循环问题

栏目: 数据库 · 发布时间: 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 |
| 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),也会区分大小写。

【阿飞的博客】 公众号二维码

↓↓↓↓

一个让人惊讶的排序导致的死循环问题


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

马尔可夫链:模型、算法与应用

马尔可夫链:模型、算法与应用

Wai-Ki Ching、Ximin Huang / 陈曦 / 清华大学出版社 / 2015-6 / 39

《马尔可夫链:模型、算法与应用 应用数学译丛》讲述了马尔可夫链模型在排队系统、网页重要性排名、制造系统、再制造系统、库存系统以及金融风险管理等方面的最新应用进展.全书共安排8章内容,第1章介绍马尔可夫链、隐马尔可夫模型和马尔可夫决策过程的基本理论和方法,其余7章分别介绍马尔可夫链模型在不同领域中的应用. 《马尔可夫链:模型、算法与应用 应用数学译丛》可作为自动化、工业工程、统计学、应用数学以及管理......一起来看看 《马尔可夫链:模型、算法与应用》 这本书的介绍吧!

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试