排序

项目
02/10/2025

重要说明

此功能目前以公共预览版提供。

适用于：勾选“是” Databricks SQL Databricks Runtime 16.1 及更高版本

排序规则是一组确定如何进行字符串比较的规则。排序规则用于以不区分大小写、重音或尾随空间的方式比较字符串，或按特定语言的顺序对字符串进行排序。

Azure Databricks 中的字符串表示为 UTF-8 编码的 Unicode 字符。默认情况下，Azure Databricks 按二进制 UTF8 表示方式对字符串进行比较。这称为 UTF8_BINARY 排序规则。在许多情况下，UTF8_BINARY 比较快速且适当，但可能不适合所有应用程序，尤其是需要语言感知排序或比较的应用程序。

除了语言感知比较之外，常见的用例是强制不区分大小写。 Azure Databricks 具有专门针对此用途的 UTF8_LCASE 排序规则。在使用快速 UTF8_BINARY 排序规则比较字符串之前，它会将字符串转换为小写。

对于语言感知比较，Azure Databricks 采用以下技术：

用于计算排序规则的 Unicode 国际组件 (ICU) 库
适用于特定区域设置感知排序规则的通用区域设置数据存储库 (CLDR) 表。
用于内部编码排序规则的 Unicode 区域设置数据标记语言 (LDML)。

这些技术封装在一组可在 SQL 语句中使用的命名排序规则中。

排序规则名称

由于通过 LDML 规范识别排序规则可能很复杂且难以读取，因此，Azure Databricks 具有一组更易于使用的命名系统排序规则。

语法

{ UTF8_BINARY |
  UTF8_LCASE |
  { UNICODE | locale } [ _ modifier [...] ] }

locale
  language_code [ _ script_code ] [ _ country_code ]

modifier
  { CS | CI | AS | AI | RTRIM }

UTF8_BINARY

基于 UTF-8 字节表示形式逐字节比较字符串的元本地二进制排序。 UTF8_BINARY 是 Azure Databricks 中用于字符串比较的默认的和最轻量级的排序规则。

在此排序规则中，‘A’ (x’65’) < ‘B’ (x’66’) < … < ‘Z’ (x’90’)。

但是，“Z”（x'90'）<“a”（x'97'）和“A”（x'65'）<>“a”（x'97'）。

此外，此排序规则中的字符（如“Ä”（x'C384）大于“Z”和“z”。
UTF8_LCASE

一种轻量级的元区域设置不区分大小写的排序规则，它在将字符串转换为小写后，使用其 UTF-8 字节表示形式进行字符串比较。

UTF8_LCASE 是用于 Azure Databricks 中的标识符的排序规则。

例如：
```
ORDER BY col COLLATE UTF8_LCASE
```
等效于
```
ORDER BY LOWER(col) COLLATE UTF8_BINARY
```
UNICODE

ICU 根区域设置。

这种排序规则在 CLDR 中称为“root”区域设置（LDML 规范：“und-u”），它施加了一种与语言无关但力求整体直观易懂的排序方式。在此排序规则中，将对“like”字符分组。例如：“a”<“A”<“Ä”<“b”。 “A”不被视为等效于“a”。因此，排序规则区分大小写。 “a”不被视为等效于“ä”。因此，排序规则区分重音。
locale

基于 CLDR 表的区域设置感知的排序规则。

区域设置被指定为语言代码、可选脚本代码和可选国家/地区代码。 locale 不区分大小写。
- language_code：一个双字母 ISO 639-1 语言代码。
- script_code：一个由四个字母组成的 ISO 15924 脚本代码。
- country_code：一个三字母 ISO 3166-1 alpha-3 国家/地区代码。
modifier

指定关于区分大小写和重音的排序规则行为。
- CS：区分大小写。默认行为。
- CI：不区分大小写。
- AS：区分重音。默认行为。
- AI：不区分重音。
适用于： Databricks Runtime 16.2 及更高版本
- RTRIM：不区分尾随空格。去除尾随空格（“u0020”）后再进行比较。
适用于：Databricks Runtime 16.2 及更高版本

可以指定 RTRIM、CS/CI 和 AS/AI，各自最多一次，可采用任意顺序。修饰符本身不区分大小写。

处理排序规则时，Azure Databricks 通过移除默认值来规范化排序规则名称。例如，SR_CYR_SRN_CS_AS 规范化为 SR。

有关支持的排序规则的列表，请参阅支持的排序规则。

示例

-- You can fully qualify collations, and case doesn't matter.
system.builtin.unicode

-- Since all collations are system defined you don't need to qualify them
unicode

-- Using 2-letter language code only for german collation
DE

-- Using 2-letter language code and 3-letter country code for french-canadian collation
-- It is common to use lower case 2-letter language codes and upper case 3-letter country codes
-- But collation names are case insensitive
fr_CAN

-- Using 2-letter language code and 4-letter script code and 3-letter country code for traditional chinese in Macao
zh_Hant_MAC

-- Using a 2 letter german language code and 2 modifiers for case insensitive and accent insensitive
-- So 'Ä', 'A', and 'a' are all considered equal
de_CI_AI

-- Using back ticks is allowed, but unnecessary for builtin collations
`UTF8_BINARY`

默认排序规则

使用 STRING 文本、参数标记、不包含生成字符串的 STRING 参数的函数时，以及定义没有 COLLATE 子句的列、字段或变量类型时，适用默认排序规则。

默认排序规则在 UTF8_BINARY 中派生。

排序规则优先级

为了确定要用于给定字符串的排序规则，Azure Databricks 会定义排序规则优先规则。

此规则为排序规则分配 4 个优先级级别：

显式

已将字符串显式分配给使用 COLLATE 表达式的排序规则。

示例

-- Force fast binary collation to check whether a vin matches a Ferrari
vin COLLATE UTF8_BINARY LIKE 'ZFF%'

-- Force German collation to order German first names
ORDER BY vorname COLLATE DE

隐式

排序规则按列、字段、列别名、变量或例程参数引用隐式分配。这包括子查询的结果，前提是排序规则不是“无”。

示例

-- Use the collation of the column as it was defined
employee.name LIKE 'Mc%'

-- Use the collation of the variable as it was defined.
translate(session.tempvar, 'Z', ',')

默认

STRING 文本、命名或未命名参数标记，或由另一种类型的函数生成的 STRING。

示例

-- A literal string has the default collation
'Hello'

-- :parm1 is a parameter marker using session default collation
EXECUTE IMMEDIATE 'SELECT :parm1' USING 'Hello' AS parm1;

-- ? is a parameter marker using session default collation
EXECUTE IMMEDIATE 'SELECT ?' USING 'Hello';

-- The result of a cast of a non-STRING to a STRING is a STRING with the default collation
CAST(5 AS STRING)

-- The date is converted to a string using the default collation
to_char(DATE'2016-04-08', 'y')

-- The collation of the session_user STRING is the default collation
session_user()

分配的排序规则为默认排序规则。

无

函数、运算符或集合运算（例如 UNION）的 STRING 结果，该运算采用多个具有不同隐式排序规则的 STRING 参数。

示例

-- Concatenating two strings with different explicit collations results in no collation
SELECT fr || de AS freutsch FROM VALUES('Voulez vous ' COLLATE FR), 'Kartoffelsupp...' COLLATE DE) AS T(fr, de)

-- A union of two strings with different excplicit collations results in no collation
SELECT 'Voulez vous ' COLLATE FR UNION ALL SELECT 'Kartoffelsupp...' COLLATE DE

排序规则派生

为 STRING 结果派生排序规则时，排序规则优先规则按以下方式应用：

如果表达式：

与上述定义匹配

排序规则和优先级如定义所述。
是具有单个 STRING 参数的函数或运算符，则返回 STRING

排序和优先级参照 STRING 参数的设置。

是具有两个或多个 STRING 参数的函数或运算符

具有相同排序规则和优先级

排序和优先级参照 STRING 参数的设置。

具有不同的排序规则或优先级

使 C1 和 C2 成为不同的排序规则，使 D 成为默认排序规则。优先级和排序规则由下表确定：

排序规则和优先级	C1 显式	C1 隐式	D 默认	无
C2 显式	错误	C2 显式	C2 显式	C2 显式
C2 隐式	显式 C1	无	C2 隐式	无
D 默认	C1 显式	C1 隐式	D 默认	无
无	C1 显式	无	无	无

示例

> SELECT 'hello' = 'hello   ' COLLATE UNICODE_RTRIM;
  true

> CREATE TABLE words(fr STRING COLLATE FR, de STRING COLLATE DE, en STRING COLLATE EN);
> INSERT INTO words VALUES ('Salut', 'Hallo', 'Hello');

-- A literal string has the default collation
> SELECT collation('Ciao');
  UTF8_BINARY

-- A function producing a STRING has the default collation
> SELECT collation(user());
  UTF8_BINARY

-- Function modifying a STRING passes the collation through
> SELECT collation(upper('Ciao'));
  UTF8_BINARY

-- Implicit collation (French) wins over default collation
> SELECT collation(fr || 'Ciao') FROM words;
  FR

-- Explicit collation (French) wins over implicit collation (German)
> SELECT collation('Salut' COLLATE FR || de) FROM words;
  FR

-- Implicit collation German collides with implicit collation French
-- The result is no collation
> SELECT collation(de || fr) FROM words;
  null

-- Explicit collation (French) wins over default collation (Italian)
> SELECT collation('Salut' COLLATE FR || 'Ciao');
  FR

-- Explicit collation (French) collides with explicit collation (German)
> SELECT collation('Salut' COLLATE FR || 'Hallo' COLLATE DE);
  COLLATION_MISMATCH.EXPLICIT

-- The explicit collation wins over no collation
> SELECT collation('Ciao' COLLATE IT || (fr || de)) FROM words;
  IT

-- The implict collation (English) does not win over None
> SELECT collation(en || (fr || de)) FROM words;
  null

-- The explicit collation (English) wins over Implicit collation anywhere in the expression
> SELECT collation((fr || ltrim('H' COLLATE EN, fr)) || fr) FROM words;
  EN

通过

排序

排序规则名称

语法

示例

默认排序规则

排序规则优先级

排序规则派生

示例

反馈

其他资源

通过

排序

排序规则名称

语法

示例

默认排序规则

排序规则优先级

排序规则派生

示例

相关内容

反馈

其他资源