Kolacja bazy danych

Kolacja bazy danych to domyślne ustawienie sortowania znaków obowiązujące dla wszystkich jej obiektów.

Czym jest kolacja bazy danych

Kolacja bazy danych to zestaw reguł określających sposób porównywania i sortowania napisów. Wpływa między innymi na kolejność wartości zwracanych przez sortowanie, wynik operatorów porównania oraz działanie indeksów opartych na kolumnach tekstowych. W polskich opisach technicznych częściej spotyka się zapis „kolacja” jako odpowiednik angielskiego terminu collation; nie należy mylić go z „kolacją” oznaczającą posiłek.

Kolacja może określać alfabet lub zestaw reguł językowych, kodowanie znaków, rozróżnianie wielkości liter, rozróżnianie akcentów oraz sposób traktowania znaków równoważnych. Przykładowo w kolacji niewrażliwej na wielkość liter wartości „Jan” i „jan” mogą być uznawane za równe podczas porównania. W kolacji wrażliwej na wielkość liter takie wartości pozostają różne. Konkretne zachowanie zależy od nazwy kolacji i silnika bazy danych, dlatego sama informacja o użyciu Unicode nie opisuje jeszcze reguł porównywania.

Gdzie obowiązuje ustawienie kolacji

Domyślna kolacja może być przypisana na poziomie całej bazy danych, a następnie dziedziczona przez nowo tworzone obiekty, takie jak tabele i kolumny tekstowe. W wielu systemach można ją także określić osobno dla tabeli, kolumny albo pojedynczego wyrażenia w zapytaniu. Ustawienie niższego poziomu może zastąpić wartość odziedziczoną z poziomu bazy. Z tego powodu dwie kolumny tekstowe w tej samej bazie mogą mieć odmienne zasady porównywania.

Kolacja nie jest tym samym co typ danych. Kolumna może przechowywać tekst w typie Unicode, ale nadal korzystać z określonej kolacji decydującej o porównywaniu znaków. W Microsoft SQL Server kolacja może być ustalona między innymi dla serwera, bazy danych, kolumny i wyrażenia. W MySQL domyślne wartości są określane przez poziomy serwera, bazy danych, tabeli i kolumny. PostgreSQL wykorzystuje ustawienia lokalizacji, takie jak LC_COLLATE i LC_CTYPE, ustalane podczas tworzenia bazy, a dodatkowe zachowanie zależy od użytej konfiguracji lokalizacji i wersji systemu.

Wpływ kolacji na zapytania i indeksy

Reguły kolacji zmieniają wyniki sortowania wykonywanego przez ORDER BY oraz porównań używanych w warunkach WHERE, filtrach i złączeniach. Mogą również decydować o tym, czy wartości różniące się wyłącznie wielkością liter albo znakami diakrytycznymi są traktowane jako równe. W kolacji niewrażliwej na akcent słowa zawierające „a” i „ą” mogą być porównywane według reguł, które nie rozróżniają tych znaków, natomiast inna kolacja może zachować to rozróżnienie.

Kolacja ma znaczenie dla indeksów tekstowych, ponieważ indeks musi być zbudowany zgodnie z zasadami porównywania wartości. Zmiana kolacji kolumny może wymagać przebudowy indeksów albo utworzenia nowych indeksów zgodnych z oczekiwanym sposobem wyszukiwania. Jeżeli zapytanie wymusza inną kolację niż ta użyta przez indeks, optymalizator może nie wykorzystać istniejącej struktury w pełnym zakresie. W praktyce może to prowadzić do innego planu wykonania, większej liczby odczytów i dłuższego czasu odpowiedzi.

Problem może pojawić się również podczas łączenia kolumn o niezgodnych kolacjach. Niektóre silniki zgłaszają wtedy błąd konfliktu reguł porównywania, a inne wybierają kolację wynikającą z określonych zasad priorytetu. Bezpośrednie wymuszenie kolacji w zapytaniu bywa użyteczne diagnostycznie, lecz może ograniczyć użycie indeksu. Przy projektowaniu schematu warto więc ustalić jednolite zasady dla kolumn, które będą porównywane, sortowane lub używane jako klucze wyszukiwania.

Dobór i sprawdzanie kolacji w praktyce

Kolację należy dobierać na podstawie języka danych, oczekiwanego sortowania i wymagań dotyczących równości tekstu. Dla polskich danych istotne są między innymi zasady porządkowania liter z diakrytyką, takich jak „ą”, „ę”, „ł” i „ó”. Nie istnieje jedna uniwersalna kolacja poprawna dla każdego zastosowania. Kolumna przeznaczona do nazw wyświetlanych użytkownikom może wymagać reguł językowych, natomiast identyfikator techniczny często powinien korzystać z przewidywalnego, jednoznacznego porównywania.

Przed zmianą kolacji trzeba sprawdzić istniejące indeksy, ograniczenia unikalności, klucze obce oraz zapytania zależne od niewrażliwości na wielkość liter lub akcenty. Zmiana zasad równości może ujawnić wcześniej ukryte duplikaty, na przykład wartości „Kowalski” i „kowalski”, które po zmianie staną się równoważne. Należy także przetestować sortowanie na rzeczywistych danych oraz porównać plan wykonania najważniejszych zapytań.

W serwisie komputerowym kontrola kolacji ma sens przede wszystkim podczas diagnozowania aplikacji korzystających z lokalnej bazy danych, problemów z wyszukiwaniem oraz rozbieżności między środowiskami. Sprawdzenie typu kolumny, ustawień bazy, wersji silnika i definicji indeksów pozwala odróżnić błąd programu od prawidłowego działania reguł sortowania. Przy naprawie laptopa lub komputera PC trzeba dodatkowo zweryfikować, czy problem nie wynika z uszkodzonego nośnika, błędów systemu plików albo niekompletnej instalacji używanego oprogramowania.