SQL Server与Elasticsearch排序结果差异原因及规则咨询

我在SQL Server 2019数据库中有一个仅含单列的表，数据如下：

T-RT-001
T-RT-002
T-RT-003
TESTDOC-000001-B
TESTDOC-000001-C
TESTDOC-000001-D

执行带ORDER BY的SQL语句后，得到排序结果：

TESTDOC-000001-B
TESTDOC-000001-C
TESTDOC-000001-D
T-RT-001
T-RT-002
T-RT-003

随后在Elasticsearch 8.1.13中创建了包含小写过滤器的自定义分析器索引，配置如下：

{
  "settings": {
    "analysis": {
      "analyzer": {
        "CustomTokenizer": {
          "filter": [
            "lowercase"
          ],
          "tokenizer": "keyword",
          "type": "custom"
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "Name": {
        "type": "text",
        "analyzer": "CustomTokenizer",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      }
    }
  }
}

将相同数据索引后，执行以下查询：

GET default_index001/_search
{
  "query": {
    "Match_all": {
    }
  }, 
  "sort": [
    {
      "Name.keyword": {
        "order": "asc"
      }
    }
  ],
  "size": 100
}

得到排序结果：

T-RT-001
T-RT-002
T-RT-003
TESTDOC-000001-B
TESTDOC-000001-C
TESTDOC-000001-D

请问为何两种排序结果存在差异？SQL Server和Elasticsearch分别采用何种排序规则？（SQL Server排序规则为Latin1_General_CI_AS）

差异根源 #

两者排序结果不同的核心原因是采用的字符排序规则完全不同，对字符的权重判定逻辑存在本质差异，导致相同字符串的排序顺序出现反转。

SQL Server 2019（Latin1_General_CI_AS）排序规则 #

• Latin1_General_CI_AS是基于Windows文化排序标准实现的规则：
  • 后缀CI表示大小写不敏感，AS表示重音敏感。
  • 排序时优先处理字母数字字符，非字母数字字符（如连字符-）权重极低，比较时会被忽略，仅以字母数字部分参与排序。例如T-RT-001会被当作TRT001，TESTDOC-000001-B被当作TESTDOC000001B。
  • 按照字母字典序比较，TE开头的字符串优先级高于TR开头的字符串，因此TESTDOC系列排在T-RT系列前面。

Elasticsearch 8.1.13排序规则 #

你查询时使用的Name.keyword属于keyword类型，该类型排序采用严格的Unicode码点字典序：

• 直接比较字符串中每个字符的Unicode码点值，码点值越小的字符排序优先级越高。例如：
  • 连字符-的Unicode码点是U+002D（十进制45），大写字母E是U+0045（十进制69）。
  • 对比T-RT-001和TESTDOC-000001-B时，第二个字符分别是-和E，因为45 < 69，所以T-RT-001排序优先级更高，排在前面。

内容的提问来源于stack exchange，提问作者Guru Office