排序规则

项目
2025/01/13

重要

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

适用于： 勾选“是” 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 }

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”）强加了与语言无关的顺序，该顺序试图整体直观展示。在此排序规则中，相似字符将分组。例如：'a' < 'A' < 'Ä' < 'b'。 “A”不被视为等效于“a”。因此，排序规则区分大小写。 “a”不被视为等效于“ä”。因此，排序规则区分重音。
区域设置

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

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

指定区分大小写和区分重音的排序规则行为。
- CS：区分大小写。默认行为。
- CI：不区分大小写。
- AS：区分重音。默认行为。
- AI：不区分重音。
可以指定任一 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, China
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 个优先级级别分配给排序规则：

Explicit

已使用 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

隐式

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

示例

-- 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()

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

无

具有不同隐式排序规则的多个 STRING 参数的函数、运算符或设置操作（例如 UNION）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 显式	无	None	无

示例

> 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;
  UTF8_BINARY

-- 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

AI 技能盛会

通过

排序规则

排序规则名称

语法

示例

默认排序规则

排序规则优先级

排序规则派生

示例

其他资源

AI 技能盛会

通过

排序规则

排序规则名称

语法

示例

默认排序规则

排序规则优先级

排序规则派生

示例

相关内容

其他资源