Pengurutan

Berlaku untuk: Databricks SQL Databricks Runtime 16.1 ke atas

Kolase adalah sekumpulan aturan yang menentukan bagaimana perbandingan string dilakukan. Kolase mendukung perbandingan yang tidak peka huruf besar/kecil, tidak peka huruf besar/kecil, dan tidak peka terhadap spasi, serta pengurutan string sadar bahasa.

String dalam Azure Databricks diwakili sebagai UTF-8 dikodekan Kode karakter. Secara default, Azure Databricks membandingkan string dengan representasi UTF-8 biner mereka, yang dikenal sebagai kolase UTF8_BINARY. UTF8_BINARY perbandingan cepat dan tepat dalam banyak kasus, tetapi mungkin tidak cocok untuk aplikasi yang memerlukan pengurutan atau perbandingan sadar bahasa.

Di luar perbandingan biner, kasus penggunaan umum adalah pencocokan yang tidak peka huruf besar/kecil. Koladasi UTF8_LCASE dirancang untuk tujuan ini. Ini mengonversi string menjadi huruf kecil sebelum membandingkannya menggunakan UTF8_BINARY.

Untuk perbandingan sadar bahasa, Azure Databricks menggunakan teknologi berikut:

• Pustaka Komponen Internasional untuk Unicode (ICU) untuk menghitung kolaborasi.
• Tabel Common Locale Data Repository (CLDR) untuk kolase sadar lokal.
• Unicode Locale Data Markup Language (LDML) untuk mengodekan kolase secara internal.

Teknologi ini dienkapsulasi dalam satu set kolase bernama yang tersedia untuk digunakan dalam pernyataan SQL.

Nota

Untuk batasan saat menggunakan kolate dengan tabel Delta Lake, lihat Batasan.

Nama kolasi

Azure Databricks menyediakan kolaborasi sistem bernama untuk menyederhanakan identifikasi. Spesifikasi LDML bisa rumit untuk dibaca dan digunakan secara langsung.

Sintaksis

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  Kolatasi biner yang membandingkan string byte byte berdasarkan representasi UTF-8 mereka. UTF8_BINARY adalah kolate default dan paling ringan dalam Azure Databricks.

  Dalam kolatasi ini: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). Namun, 'Z' (x'90') <'a' (x'97'), dan 'A' (x'65') <>'a' (x'97'). Karakter seperti 'Ä' (x'C384') lebih besar dari dan 'Z''z'.

• UTF8_LCASE

  Kolase ringan dan tidak peka huruf besar/kecil yang mengonversi string menjadi huruf kecil sebelum membandingkannya menggunakan UTF8_BINARY.

  UTF8_LCASE adalah kolase yang digunakan untuk Identifiers di Azure Databricks.

  Misalnya:

  ORDER BY col COLLATE UTF8_LCASE
  

  setara dengan:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  Lokal akar ICU, yang dikenal di CLDR sebagai root lokal (spesifikasi LDML: und-u). Kolasasi ini menerapkan urutan bahasa-agnostik yang mengelompokkan karakter serupa bersama-sama. Misalnya: 'a'<'A'<'Ä'<'b'. Kolatasi ini peka huruf besar/kecil dan sensitif terhadap aksen secara default.

• locale

  Kolaborasi sadar lokal berdasarkan tabel CLDR. Lokal ditentukan sebagai kode bahasa, kode skrip opsional, dan kode negara opsional. Nilai lokal tidak peka huruf besar/kecil.

  • language_code: Kode bahasa ISO 639-1 dua huruf.
  • script_code: Kode skrip ISO 15924 empat huruf.
  • country_code: Kode negara ISO 3166-1 alpha-3 tiga huruf.

• modifier

  Mengontrol sensitivitas kasus, sensitivitas aksen, dan perilaku ruang berikutnya. Pengubah tidak peka huruf besar/kecil dan dapat ditentukan dalam urutan apa pun.

  • CS:Case-sensitive. Perilaku bawaan.
  • CI: Tidak peka huruf besar/kecil.
  • AS: Sensitif terhadap aksen. Perilaku bawaan.
  • AI: Aksen-tidak sensitif.

  Berlaku untuk: Databricks SQL Databricks Runtime 16.2 dan versi lebih baru

  • RTRIM: Trailing-space tidak peka. Memangkas spasi berikutnya (u0020) sebelum perbandingan.

  Berlaku untuk: Databricks SQL Databricks Runtime 16.2 dan versi lebih baru

  Anda dapat menentukan RTRIM, paling banyak dari CS atau CI, dan paling banyak salah satu dari AS atau AI.

Saat Azure Databricks memproses nama kolake, nama tersebut akan menormalkan nama dengan menghapus default. Misalnya, SR_CYR_SRN_CS_AS menormalkan ke SR.

Untuk daftar kolatasi yang didukung, lihat Kolate yang didukung.

Contoh

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

Membandingkan UTF8_LCASE dan KOLADE UNICODE

Kolase UTF8_LCASE berbasis UNICODE seperti UNICODE_CI dan UNICODE_CI_AI mendukung perbandingan yang tidak peka huruf besar/kecil, tetapi berbeda dalam cara mereka menangani karakter beraksen dan aturan khusus lokal.

Perbedaan perilaku

UTF8_LCASE mengonversi string menjadi huruf kecil dan membandingkannya byte byte menggunakan UTF8_BINARY. Ini ringan dan cepat, tetapi memperlakukan karakter beraksen sebagai berbeda dari bentuknya yang tidak beraksen.

Kolaborasi berbasis UNICODE menggunakan pustaka ICU dan data lokal CLDR. Pengubah CI dan AI memperluas ini untuk memperlakukan karakter yang setara bahkan ketika mereka berbeda dalam kasus, aksen, atau keduanya. Kolase ini lebih menyeluruh tetapi memiliki biaya komputasi yang lebih tinggi.

Perilaku	UTF8_LCASE	UNICODE_CI	UNICODE_CI_AI
'A' = 'a'	true	true	true
'Café' = 'café'	true	true	true
'Cafe' = 'Café'	false	false	true
'ß' = 'ss' (Jerman tajam s)	false	false	false
'resume' = 'résumé'	false	false	true
Mendukung LIKE dan RLIKE	Yes	No	No
Mendukung aturan khusus lokal	No	Yes	Yes

Kapan menggunakan setiap kolagasi

• Gunakan UTF8_LCASE untuk perbandingan peka huruf besar/kecil yang cepat ketika karakter beraksen harus tetap berbeda. Kolas ini sangat cocok untuk migrasi gudang data dari sistem yang menggunakan pencocokan string yang tidak peka huruf besar/kecil.

• Gunakan UNICODE_CI untuk perbandingan tidak sensitif huruf besar/kecil yang mengikuti aturan Unicode, terutama ketika data menyertakan karakter dari beberapa bahasa di mana huruf kecil sederhana tidak mencukupi.

• Gunakan UNICODE_CI_AI saat perbedaan huruf besar/kecil dan aksen harus diabaikan. Misalnya, jika pencarian "resume" harus cocok "résumé"dengan . Ini umum dalam pencarian yang menghadap pengguna dan aplikasi multibahasa.

Contoh

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

Pengurutan bawaan

Kolase default berlaku untuk STRING literal, penanda parameter, fungsi tanpa STRING parameter yang menghasilkan string, dan kolom, bidang, atau definisi jenis variabel tanpa COLLATE klausul.

Kolase default diturunkan sebagai berikut:

• Untuk pernyataan DDL seperti ALTER TABLE, , CREATE VIEW, CREATE TABLEdan CREATE FUNCTION:

  • Kolase default adalah kolase default objek yang sedang dibuat atau diubah.
  • Jika tidak ada klausa DEFAULT COLLATION yang ditentukan, pengurutan default adalah UTF8_BINARY.

• Untuk pernyataan DML (UPDATE, , DELETE FROM, INSERT) MERGE INTOdan Kueri, kolase defaultnya adalah UTF8_BINARY.

Prioritas kolase

Azure Databricks menerapkan aturan prioritas kolase untuk menentukan kolase mana yang akan digunakan untuk string tertentu. Empat tingkat prioritas didefinisikan:

1. Eksplisit

   Kolase secara eksplisit ditetapkan menggunakan collate ekspresi.

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

   Kolase secara implisit ditetapkan oleh nama Kolom, Nama bidang, Alias kolom, Nama variabel, atau Referensi nama Parameter , termasuk hasil subkueri di mana kolase bukan Tidak Ada.

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

   STRING Penanda Parameter harfiah, bernama, atau tidak bernama, atau STRING diproduksi oleh fungsi dari jenis lain.

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

   Kolase yang ditetapkan adalah kolase default.

4. Tidak

   Hasil STRING dari fungsi, operator, atau operasi set seperti UNION yang mengambil lebih dari satu STRING argumen dengan kolase implisit yang berbeda.

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

Derivasi kolasi

Saat mengambil kolase untuk hasil STRING , aturan prioritas berlaku sebagai berikut:

1. Jika ekspresi cocok dengan salah satu definisi di atas, kolase dan prioritasnya adalah seperti yang didefinisikan.

2. Jika ekspresi adalah fungsi atau operator dengan parameter tunggal yang STRINGmengembalikan , kolase dan prioritasnya adalah salah STRING satu STRING parameter.

3. Jika ekspresi adalah fungsi atau operator dengan dua parameter atau lebih STRING :

   1. Jika semua parameter memiliki kolase dan prioritas yang sama, hasilnya menggunakan kolase dan prioritas tersebut.

   2. Jika parameter memiliki kolase atau prioritas yang berbeda, biarkan C1 dan C2 menjadi kolase yang berbeda dan D menjadi kolase default. Hasilnya ditentukan oleh tabel berikut:

Kolase dan Prioritas	C1 Eksplisit	C1 Implisit	D Pengaturan Awal	Tidak
C2 Eksplisit	Kesalahan	C2 Eksplisit	C2 Eksplisit	C2 Eksplisit
C2 Implisit	C1 Eksplisit	Tidak	C2 Implisit	Tidak
D Pengaturan Awal	C1 Eksplisit	C1 Implisit	D Pengaturan Awal	Tidak
Tidak	C1 Eksplisit	Tidak	Tidak	Tidak

Contoh

> 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

Keterbatasan

Banyak fungsi pencocokan pola dan regex hanya UTF8_BINARY mendukung dan UTF8_LCASE kolatasi. Azure Databricks menimbulkan kesalahan ketika fungsi-fungsi ini digunakan dengan kolase lain seperti UNICODE atau kolase khusus lokal seperti DE atau FR_CI_AI.

Fungsi yang terpengaruh meliputi LIKE, ILIKE, RLIKE, dan regexp_* keluarga fungsi. Untuk pencocokan string pada kolom dengan kolatasi lain, gunakan contains fungsi, startswith fungsi, atau endswith fungsi.

Terkait

• collate Ekspresi
• collation fungsi
• collations fungsi tabel