Collation bepaalt hoe tekst in een database wordt gesorteerd en vergeleken. Het beinvloedt dus direct hoe queries met ORDER BY, WHERE en joins werken.
Niet elke database implementeert dit op dezelfde manier:
Het is belangrijk om de juiste collation te kiezen, omdat dit invloed heeft op zoekresultaten, sortering, indexen en zelfs of data als uniek wordt beschouwd.
Collation-namen bestaan vaak uit meerdere onderdelen die samen het gedrag bepalen. Hieronder de meest voorkomende afkortingen en betekenissen:
Voorbeelden:
In de praktijk gebruik je meestal een case-insensitive collation voor gewone gebruikersdata, en een strengere of binaire vergelijking voor technische waarden waarbij exacte overeenkomst belangrijk is.
Hieronder zie je per database hoe je de collation of het equivalent kunt opvragen, wat het resultaat betekent en hoe je die waarde moet interpreteren.
Query:
SELECT sys_context('USERENV','LANGUAGE');
Resultaat:
AMERICAN_AMERICA.AL32UTF8
Uitleg:
De query SELECT sys_context('USERENV','LANGUAGE'); geeft een samengestelde string terug zoals AMERICAN_AMERICA.AL32UTF8. Deze waarde bestaat uit taal, regio en character set. In dit voorbeeld is AL32UTF8 het belangrijkste deel; dat is de Unicode character set van de database. Oracle werkt niet met een centrale collation zoals MySQL en SQL Server, maar met een combinatie van character set en NLS-instellingen voor sorteren en vergelijken. Daardoor krijg je in Oracle niet alle collatie-informatie in een compacte naam terug.
Query:
SELECT @@collation_server;
Resultaat:
utf8mb4_0900_ai_ci
Uitleg:
De query SELECT @@collation_server; geeft direct de standaard collation van de server terug, zoals utf8mb4_0900_ai_ci. Deze naam kun je opsplitsen: utf8mb4 is de character set, 0900 verwijst naar de gebruikte Unicode-sorteerregels, ai betekent accent-insensitive en ci betekent case-insensitive. MySQL combineert dus de karakterset en de regels voor tekstvergelijking en sortering in een waarde. Dat maakt het relatief eenvoudig om het gedrag van tekstkolommen snel te begrijpen.
Query:
SELECT SERVERPROPERTY('Collation');
Resultaat:
SQL_Latin1_General_CP1_CI_AS
Uitleg:
De query SELECT SERVERPROPERTY('Collation'); retourneert de server collation, zoals SQL_Latin1_General_CP1_CI_AS. Ook deze naam bestaat uit meerdere onderdelen: SQL_ verwijst naar een oudere SQL Server-collation, Latin1_General geeft de taal- en sorteerregels aan, CP1 staat voor Code Page 1252, CI betekent case-insensitive en AS betekent accent-sensitive. Net als bij MySQL zit hier veel informatie in een string, zodat je in een oogopslag ziet hoe tekst wordt vergeleken en gesorteerd.
Bij het lezen van een collation-naam is het handig om steeds te letten op de onderdelen voor character set, case sensitivity en accent sensitivity. Daarmee kun je meestal snel inschatten hoe de database tekst zal behandelen.