Collazione

Si applica a: Databricks SQL Databricks Runtime 16.1 e versioni successive

Le regole di confronto sono un set di regole che determinano il modo in cui vengono eseguiti i confronti tra stringhe. Le regole di confronto supportano confronti senza distinzione tra maiuscole e minuscole, senza distinzione tra caratteri accentati e non accentati e senza distinzione tra spazi finali, nonché l'ordinamento delle stringhe compatibile con il linguaggio.

Le stringhe in Azure Databricks sono rappresentate come caratteri UTF-8 codificati Unicode. Per impostazione predefinita, Azure Databricks confronta le stringhe in base alla rappresentazione UTF-8 binaria, nota come regole di confronto UTF8_BINARY. UTF8_BINARY i confronti sono veloci e appropriati in molti casi, ma potrebbero non essere adatti per le applicazioni che richiedono l'ordinamento o i confronti con riconoscimento della lingua.

Oltre ai confronti binari, un caso d'uso comune è la corrispondenza senza distinzione tra maiuscole e minuscole. Le UTF8_LCASE regole di confronto sono progettate per questo scopo. Converte le stringhe in lettere minuscole prima di confrontarle usando UTF8_BINARY.

Per i confronti con riconoscimento del linguaggio, Azure Databricks usa le tecnologie seguenti:

• Libreria International Components for Unicode (ICU) per calcolare le regole di confronto.
• Tabelle CLDR (Common Locale Data Repository) per le regole di confronto che supportano le impostazioni locali.
• Unicode Locale Data Markup Language (LDML) per codificare internamente i confronti.

Queste tecnologie vengono incapsulate in un set di regole di confronto denominate disponibili per l'uso nelle istruzioni SQL.

Nomi delle collazioni

Azure Databricks fornisce regole di confronto di sistema denominate per semplificare l'identificazione. Le specifiche LDML possono essere complesse da leggere e usare direttamente.

Sintassi

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  Regole di confronto binarie che confrontano stringhe di byte byte in base alla relativa rappresentazione UTF-8. UTF8_BINARY è la regola di confronto predefinita e più leggera in Azure Databricks.

  In queste regole di confronto: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). Tuttavia, 'Z' (x'90')'a'<(x'97') e 'A' (x'65') <>'a' (x'97'). I caratteri come 'Ä' (x'C384') sono maggiori di e 'Z''z'.

• UTF8_LCASE

  Regole di confronto leggere senza distinzione tra maiuscole e minuscole che convertono le stringhe in lettere minuscole prima di confrontarle usando UTF8_BINARY.

  UTF8_LCASE è la regola di confronto usata per Identifiers in Azure Databricks.

  Per esempio:

  ORDER BY col COLLATE UTF8_LCASE
  

  Equivale a:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  Impostazioni locali radice dell'ICU, note in CLDR come root impostazioni locali (specifica LDML: und-u). Queste regole di confronto applicano un ordine indipendente dal linguaggio che raggruppa caratteri simili. Ad esempio: 'a''b'<<'A'<'Ä'. Questa regola di confronto fa distinzione tra maiuscole e minuscole e distinzione tra caratteri accentati e accentati per impostazione predefinita.

• locale

  Regole di confronto che supportano le impostazioni locali basate sulle tabelle CLDR. Le impostazioni locali sono specificate come codice linguistico, codice script facoltativo e codice paese facoltativo. I valori delle impostazioni locali non fanno distinzione tra maiuscole e minuscole.

  • language_code: codice di lingua ISO 639-1 a due lettere.
  • script_code: codice di script ISO 15924 a quattro lettere.
  • country_code: codice paese ISO 3166-1 alfa-3 di tre lettere.

• modifier

  Controlla la distinzione tra maiuscole e minuscole, la sensibilità principale e il comportamento dello spazio finale. I modificatori non fanno distinzione tra maiuscole e minuscole e possono essere specificati in qualsiasi ordine.

  • CS:Sensitive. Comportamento predefinito.
  • CI: senza distinzione tra maiuscole e minuscole.
  • AS: distinzione tra caratteri accentati e non accentati. Comportamento predefinito.
  • AI: senza distinzione tra caratteri accentati.

  Si applica a: Databricks SQL Databricks Runtime 16.2 e versioni successive

  • RTRIM: senza distinzione tra spazi finali. Taglia gli spazi finali (u0020) prima del confronto.

  Si applica a: Databricks SQL Databricks Runtime 16.2 e versioni successive

  È possibile specificare RTRIM, al massimo uno di CS o CIe al massimo uno di AS o AI.

Quando Azure Databricks elabora un nome di regole di confronto, normalizza il nome rimuovendo le impostazioni predefinite. Ad esempio, SR_CYR_SRN_CS_AS normalizza in SR.

Per l'elenco delle regole di confronto supportate, vedere Regole di confronto supportate.

Esempi

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

Confronto UTF8_LCASE e regole di confronto UNICODE

Le UTF8_LCASE regole di confronto basate su UNICODE, ad UNICODE_CI esempio e supportano confronti senza distinzione tra maiuscole e UNICODE_CI_AI minuscole, ma differiscono per la gestione dei caratteri accentati e delle regole specifiche delle impostazioni locali.

Differenze di comportamento

UTF8_LCASE converte le stringhe in lettere minuscole e le confronta in byte di byte tramite UTF8_BINARY. È leggero e veloce, ma tratta i caratteri accentati come distinti dalle loro forme non allineate.

Le regole di confronto basate su UNICODE usano la libreria ICU e i dati delle impostazioni locali CLDR. I CI modificatori e AI estendono questo valore per trattare i caratteri come equivalenti anche quando differiscono in maiuscole e minuscole, accento o entrambi. Queste regole di confronto sono più approfondite, ma hanno costi di calcolo più elevati.

Quando usare ogni regola di confronto

• Usare UTF8_LCASE per confronti rapidi senza distinzione tra maiuscole e minuscole quando i caratteri accentati devono rimanere distinti. Queste regole di confronto sono particolarmente adatte per le migrazioni di data warehouse dai sistemi che usano semplici corrispondenze di stringhe senza distinzione tra maiuscole e minuscole.

• Usare UNICODE_CI per confronti senza distinzione tra maiuscole e minuscole che seguono le regole Unicode, in particolare quando i dati includono caratteri di più lingue in cui la minuscola semplice non è sufficiente.

• Usare UNICODE_CI_AI quando le differenze tra maiuscole e minuscole e accentate devono essere ignorate. Ad esempio, se una ricerca "resume" deve corrispondere a "résumé". Questo è comune nelle applicazioni di ricerca e multilingue rivolte agli utenti.

Esempi

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

Regole di confronto predefinite

Le regole di confronto predefinite si applicano a STRING valori letterali, marcatori di parametro, funzioni senza STRING parametri che producono stringhe e definizioni di tipo di colonna, campo o variabile senza una COLLATE clausola .

Le regole di confronto predefinite sono derivate come segue:

• Per istruzioni DDL come ALTER TABLE, CREATE VIEW, CREATE TABLE e CREATE FUNCTION:

  • Le regole di confronto predefinite sono le regole di confronto predefinite dell'oggetto da creare o modificare.
  • Se non viene specificata alcuna clausola DEFAULT COLLATION, le regole di confronto predefinite sono UTF8_BINARY.

• Per le istruzioni DML (UPDATE, DELETE FROM, INSERT, MERGE INTO) e Query, le regole di confronto predefinite sono UTF8_BINARY.

Precedenza di confronto

Azure Databricks applica regole di precedenza delle regole di confronto per determinare le regole di confronto da usare per una determinata stringa. Vengono definiti quattro livelli di precedenza:

1. Esplicito

   Le regole di confronto vengono assegnate in modo esplicito tramite collate expression.

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

   Le regole di confronto vengono assegnate in modo implicito da un nome di colonna, un nomecampo, un alias di colonna, un nome variabile o un riferimento al nome del parametro , incluso il risultato di una sottoquery in cui le regole di confronto non sono Nessuna.

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

   Un STRING valore letterale, denominato o senza nome marcatori di parametro o un oggetto STRING prodotto da una funzione da un altro tipo.

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

   Le regole di confronto assegnate sono le regole di confronto predefinite.

4. Nessuna

   Risultato STRING di un'operazione di funzione, operatore o set, ad esempio UNION che accetta più argomenti con regole di STRING confronto implicite diverse.

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

Derivazione della collazione

Quando si derivano le regole di confronto per un STRING risultato, le regole di precedenza si applicano come segue:

1. Se l'espressione corrisponde a una delle definizioni precedenti, le regole di confronto e la precedenza vengono definite.

2. Se l'espressione è una funzione o un operatore con un singolo STRING parametro che restituisce , STRINGle regole di confronto e la precedenza sono quelle del STRING parametro .

3. Se l'espressione è una funzione o un operatore con due o più STRING parametri:

   1. Se tutti i parametri hanno le stesse regole di confronto e precedenza, il risultato usa le regole di confronto e la precedenza.

   2. Se i parametri hanno regole di confronto o precedenza diverse, lasciare C1 e C2 essere regole di confronto distinte e D essere le regole di confronto predefinite. Il risultato è determinato dalla tabella seguente:

Esempi

> 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

Limitazioni

Molte funzioni di ricerca ed espressione regolare supportano solo UTF8_BINARY e UTF8_LCASE regole di confronto. Azure Databricks genera un errore quando queste funzioni vengono usate con altre regole di confronto, ad esempio UNICODE o regole di confronto specifiche delle impostazioni locali, ad esempio DE o FR_CI_AI.

Le funzioni interessate includono LIKE, ILIKE, RLIKEe la famiglia di regexp_* funzioni. Per la corrispondenza di stringhe su colonne con altre regole di confronto, usare contains funzione, startswith funzione o endswith funzione.

Correlato

• collate l'espressione
• collation Funzione
• collations funzione tabella