Сопоставление

Применяется к: Databricks SQL Databricks Runtime 16.1 и более поздним версиям

Коллация — это набор правил, определяющих, как выполняются сравнения строк. Параметры сортировки поддерживают нечувствительные, нечувствительные к регистру, нечувствительные и конечные нечувствительные сравнения пробелов, а также упорядочение строк с учетом языка.

Строки в Azure Databricks представлены как UTF-8 закодированные символы Unicode. По умолчанию Azure Databricks сравнивает строки по двоичному представлению UTF-8, известному как UTF8_BINARY сортировки. UTF8_BINARY сравнения являются быстрыми и подходящими во многих случаях, но могут не подходить для приложений, требующих сортировки или сравнения с учетом языка.

Помимо двоичных сравнений распространенный вариант использования не учитывает регистр. Параметры UTF8_LCASE сортировки предназначены для этой цели. Он преобразует строки в строчные регистры, прежде чем сравнивать их с помощью UTF8_BINARY.

Для сравнения с учетом языка Azure Databricks использует следующие технологии:

• Международные компоненты библиотеки Юникода (ICU) для вычисления параметров сортировки.
• Общие таблицы репозитория данных языкового стандарта (CLDR) для параметров сортировки с поддержкой языкового стандарта.
• язык разметки данных Юникода (LDML) для внутреннего кодирования колляций.

Эти технологии инкапсулируются в набор именованных параметров сортировки, доступных для использования в инструкциях SQL.

Замечание

Ограничения при использовании параметров сортировки с таблицами Delta Lake см. в разделе "Ограничения".

Имена параметров сортировки

Azure Databricks предоставляет именованные системные параметры сортировки для упрощения идентификации. Спецификации LDML могут быть сложными для чтения и использования напрямую.

Синтаксис

{ 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

  Упрощенная нечувствительная сортировка регистра, которая преобразует строки в строчные регистры, прежде чем сравнивать их с помощью UTF8_BINARY.

  UTF8_LCASE — это параметры сортировки, используемые для Identifiers в 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'. Этот параметр сортировки учитывает регистр и учитывает акцент по умолчанию.

• locale

  Параметры сортировки с поддержкой языкового стандарта на основе таблиц CLDR. Локаль указывается как языковой код, необязательный код сценария и необязательный код страны. Значения языкового стандарта не учитывает регистр.

  • language_code: двухбуквенный код языка ISO 639-1 .
  • script_code: код скрипта ISO 15924 с четырьмя буквами.
  • country_code: трехбуквенный код страны ISO 3166-1 alpha-3 .

• modifier

  Управляет чувствительностью регистра, конфиденциальностью акцента и поведением пробела. Модификаторы не учитывает регистр и могут быть указаны в любом порядке.

  • CS: учитывает регистр. Поведение по умолчанию.
  • CI: без учета регистра.
  • AS: с учетом акцента. Поведение по умолчанию.
  • AI: нечувствительный акцент.

  применяется: Databricks SQL Databricks Runtime 16.2 и выше

  • RTRIM: не учитывается в пространстве. Обрезает конечные пробелы (u0020) перед сравнением.

  применяется: Databricks SQL Databricks Runtime 16.2 и выше

  Можно указать RTRIM, не более одного CS или CI, а не более одного или ASAI.

При Azure Databricks обрабатывает имя сортировки, оно нормализует имя, удаляя значения по умолчанию. Например, SR_CYR_SRN_CS_AS нормализуется в SR.

Список поддерживаемых параметров сортировки см. в разделе Поддерживаемые параметры сортировки.

Примеры

-- Fully qualified collation names are supported; case doesn't matter.
system.builtin.unicode

-- All collations are system-defined and do not require qualification.
unicode

-- Two-letter language code for German collation.
DE

-- Two-letter language code and three-letter country code for French Canadian collation.
fr_CAN

-- Two-letter language code, four-letter script code, and three-letter country code
-- for Traditional Chinese in Macao.
zh_Hant_MAC

-- German collation with case-insensitive and accent-insensitive modifiers.
-- 'Ä', 'A', and 'a' are all considered equal.
de_CI_AI

-- Backticks are allowed but not required for built-in collations.
`UTF8_BINARY`

Сравнение и параметры UTF8_LCASE сортировки ЮНИКОД

Параметры UTF8_LCASE сортировки на основе ЮНИКОДа, такие как UNICODE_CI и UNICODE_CI_AI поддержка сравнения без учета регистра, но отличаются тем, как они обрабатывают акцентированные символы и правила языкового стандарта.

Различия в поведении

UTF8_LCASE преобразует строки в нижний регистр и сравнивает их байт байтов с помощью UTF8_BINARY. Он легкий и быстрый, но рассматривает акцентированные символы как отличные от их непристойных форм.

Параметры сортировки на основе ЮНИКОД используют библиотеку ICU и данные языкового стандарта CLDR. CI Модификаторы AI расширяют это, чтобы рассматривать символы как эквивалентные, даже если они отличаются в случае, акценте или обоих. Эти параметры сортировки более тщательны, но имеют более высокую стоимость вычислений.

Поведение	UTF8_LCASE	UNICODE_CI	UNICODE_CI_AI
'A' = 'a'	true	true	true
'Café' = 'café'	true	true	true
'Cafe' = 'Café'	false	false	true
'ß' = 'ss' (немецкие острые s)	false	false	false
'resume' = 'résumé'	false	false	true
Поддержка LIKE и RLIKE	Да	Нет	Нет
Поддерживает правила, зависящие от языкового стандарта	Нет	Да	Да

Когда следует использовать каждую сортировку

• Используйте UTF8_LCASE для быстрых сравнений без учета регистра, когда акцентированные символы должны оставаться уникальными. Этот параметр сортировки хорошо подходит для миграции хранилища данных из систем, использующих сопоставление строк с простым регистром.

• Используйте UNICODE_CI для сравнения без учета регистра, которые соответствуют правилам Юникода, особенно если данные включают символы из нескольких языков, где простое снижение недостаточно.

• Используйте, UNICODE_CI_AI когда следует игнорировать оба варианта и отличия акцента. Например, если поиск "resume" должен совпадать "résumé". Это часто используется для поиска пользователей и многоязычных приложений.

Примеры

-- UTF8_LCASE lowercases then compares bytes.
-- Accented and unaccented characters are not equivalent.
> SELECT 'Cafe' = 'café' COLLATE UTF8_LCASE;
  false

> SELECT 'Café' = 'café' COLLATE UTF8_LCASE;
  true

-- UNICODE_CI is case-insensitive but accent-sensitive.
-- 'Café' equals 'café' (case differs) but not 'Cafe' (accent differs).
> SELECT 'Café' = 'cafe' COLLATE UNICODE_CI;
  false

> SELECT 'Café' = 'café' COLLATE UNICODE_CI;
  true

-- UNICODE_CI_AI is case-insensitive and accent-insensitive.
-- 'Café' matches 'cafe' because both case and accent differences are ignored.
> SELECT 'Cafe' = 'café' COLLATE UNICODE_CI_AI;
  true

> SELECT 'resume' = 'résumé' COLLATE UNICODE_CI_AI;
  true

-- UTF8_LCASE sorts by lowercase byte order.
-- UNICODE collations sort by linguistic similarity.
> SELECT col FROM VALUES('Banana'), ('apple'), ('Ångström'), ('äpfel') AS t(col)
    ORDER BY col COLLATE UTF8_LCASE;
  apple
  Banana
  Ångström
  äpfel

> SELECT col FROM VALUES('Banana'), ('apple'), ('Ångström'), ('äpfel') AS t(col)
    ORDER BY col COLLATE UNICODE_CI;
  apple
  Ångström
  äpfel
  Banana

Колляция по умолчанию

Параметры сортировки по умолчанию применяются к STRING литералам, маркерам параметров, функциям без STRING параметров, которые создают строки, столбцы, поля или определения типов переменных без COLLATE предложения.

Параметры сортировки по умолчанию являются производными следующим образом:

• Для инструкций DDL, таких как ALTER TABLE, CREATE VIEWCREATE TABLEи CREATE FUNCTION:

  • Параметры сортировки по умолчанию — это параметры сортировки по умолчанию создаваемого или измененного объекта.
  • Если условие DEFAULT COLLATION не указано, используется сортировка по умолчанию UTF8_BINARY.

• Для операторов DML (UPDATE, DELETE FROM, INSERT, MERGE INTO) и Query используется UTF8_BINARYпараметры сортировки по умолчанию.

Приоритет сортировки

Azure Databricks применяет правила приоритета сортировки, чтобы определить, какие параметры сортировки следует использовать для данной строки. Определены четыре уровня приоритета:

1. явные

   Параметры сортировки явно назначаются с помощью collate выражения.

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

2. неявный

   Параметры сортировки неявно назначаются именем столбца, именем поля, псевдонимом столбца, именем переменной или ссылкой на имя параметра , включая результат вложенного запроса, в котором параметры сортировки не равны None.

   -- Use the collation of the column as defined.
   employee.name LIKE 'Mc%'
   
   -- Use the collation of the variable as defined.
   translate(session.tempvar, 'Z', ',')
   

3. по умолчанию

   Литерал STRING , именованные или неназванные маркеры параметров или STRING созданный функцией из другого типа.

   -- A literal string has the default collation.
   'Hello'
   
   -- :parm1 is a parameter marker using the session default collation.
   EXECUTE IMMEDIATE 'SELECT :parm1' USING 'Hello' AS parm1;
   
   -- ? is a parameter marker using the session default collation.
   EXECUTE IMMEDIATE 'SELECT ?' USING 'Hello';
   
   -- The result of casting a non-STRING to STRING uses 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')
   
   -- session_user() returns a STRING with the default collation.
   session_user()
   

   Назначенная параметры сортировки — это параметры сортировки по умолчанию.

4. Нет

   Результат 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 explicit collations results in no collation.
   SELECT 'Voulez vous ' COLLATE FR UNION ALL SELECT 'Kartoffelsupp...' COLLATE DE
   

Производная колляции

При выводе параметров сортировки для STRING результата правила приоритета применяются следующим образом:

1. Если выражение соответствует одному из указанных выше определений, параметры сортировки и приоритеты определяются.

2. Если выражение является функцией или оператором с одним STRING параметром, возвращающим значение, STRINGпараметры сортировки и приоритета являются параметрами STRING .

3. Если выражение является функцией или оператором с двумя или более STRING параметрами:

   1. Если все параметры имеют одинаковые параметры сортировки и приоритета, результат использует этот параметр сортировки и приоритета.

   2. Если параметры имеют разные параметры сортировки или приоритета, пусть 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

-- A function that modifies 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 German collides with implicit French; the result has 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

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

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

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

Ограничения

Многие функции сопоставления шаблонов и регулярных функций поддерживают только UTF8_BINARY параметры сортировки и UTF8_LCASE сортировки. Azure Databricks вызывает ошибку, если эти функции используются с другими параметрами сортировки, такими как UNICODE или языковые параметры, такие как параметры сортировки DE или FR_CI_AI.

Затронутые функции включают LIKE, ILIKERLIKEи regexp_* семейство функций. Для сопоставления строк в столбцах с другими параметрами сортировки используйте contains функцию, startswith функцию или endswith функцию.

Родственный

• collate выражение
• функцияcollation
• collations табличная функция