Kollationeringar och skiftlägeskänslighet

Textbearbetning i databaser kan vara komplex och kräver mer användarinformation än vad man misstänker. För det första varierar databaserna avsevärt i hur de hanterar text; Medan vissa databaser till exempel är skiftlägeskänsliga som standard (t.ex. Sqlite, PostgreSQL), är andra icke-skiftlägeskänsliga (SQL Server, MySQL). På grund av indexanvändning kan skiftlägeskänslighet och liknande aspekter dessutom ha en långtgående inverkan på frågeprestanda: även om det kan vara frestande att använda string.ToLower för att framtvinga en skiftlägeskänslig jämförelse i en skiftlägeskänslig databas kan det hindra ditt program från att använda index. Den här sidan beskriver hur du konfigurerar skiftlägeskänslighet, eller mer allmänt, sortering och hur du gör det på ett effektivt sätt utan att äventyra frågeprestandan.

Introduktion till sortering

Ett grundläggande begrepp i textbearbetning är sorteringen, som är en uppsättning regler som avgör hur textvärden sorteras och jämförs för likhet. Även om en skiftlägesokänslig sortering bortser från skillnader mellan versaler och gemener för likhetsjämförelse, gör inte en skiftlägeskänslig sortering det. Men eftersom skiftlägeskänslighet är beroende av kultur (t.ex. i och I representerar olika bokstäver på turkiska), finns det flera skiftlägesokänsliga sorteringar, var och en med sin egen uppsättning regler. Sorteringens omfattning sträcker sig också bortom skiftlägeskänslighet, till andra aspekter av teckendata. På tyska är det till exempel ibland (men inte alltid) önskvärt att behandla ä och ae som identiska. Slutligen definierar sorteringar också hur textvärden sorteras: medan tyska placerar ä efter a, placerar svenska den i slutet av alfabetet.

Alla textåtgärder i en databas använder en sortering – explicit eller implicit – för att avgöra hur åtgärden jämför och beställer strängar. Den faktiska listan över tillgängliga sorteringar och deras namngivningsscheman är databasspecifika. Se avsnittet nedan för länkar till relevanta dokumentationssidor för olika databaser. Som tur är tillåter databaser vanligtvis att en standardsortering definieras på databas- eller kolumnnivå och att uttryckligen ange vilken sortering som ska användas för specifika åtgärder i en fråga.

Databaskollation

I de flesta databassystem definieras en standardsortering på databasnivå. om den inte åsidosätts gäller den sorteringen implicit för alla textåtgärder som inträffar i databasen. Databassorteringen anges vanligtvis vid tidpunkten för databasskapandet (via DDL-instruktionen), och om den inte anges, används ett förinställt värde på servernivå som bestäms vid konfigurationstiden. Till exempel är SQL_Latin1_General_CP1_CI_AS standardsorteringen på servernivå i SQL Server för datorspråket "Engelska (USA)", vilket är en skiftlägesokänslig, accentkänslig sortering. Även om databassystem vanligtvis tillåter ändring av sortering av en befintlig databas, kan detta leda till komplikationer. Vi rekommenderar att du väljer en sortering innan databasen skapas.

När du använder EF Core-migreringar för att hantera databasschemat, konfigurerar följande i din modells OnModelCreating-metod en SQL Server-databas för att använda en skiftlägeskänslig kollation.

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Kolumnkollation

Sorteringsordningar kan också definieras på textkolumner och därmed åsidosätta databasens standardinställning. Detta kan vara användbart om vissa kolumner måste vara skiftlägesokänsliga, medan resten av databasen måste vara skiftlägeskänslig.

När du använder EF Core-migreringar för att hantera databasschemat, konfigurerar följande kolumnen för egenskapen Name så att den är skiftlägesokänslig i en databas som annars normalt är konfigurerad att vara skiftlägeskänslig.

modelBuilder.Entity<Customer>().Property(c => c.Name)
    .UseCollation("SQL_Latin1_General_CP1_CI_AS");

Explicit kollationering i en sökfråga

I vissa fall måste samma kolumn frågas med olika sorteringar av olika frågor. En fråga kan till exempel behöva utföra en skiftlägeskänslig jämförelse på en kolumn, medan en annan kan behöva utföra en skiftlägesokänslig jämförelse på samma kolumn. Detta kan åstadkommas genom att uttryckligen ange en sortering i själva frågan:

var customers = await context.Customers
    .Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
    .ToListAsync();

Detta genererar en COLLATE sats i SQL-frågan, som tillämpar en skiftlägeskänslig sortering oavsett sortering som definierats på kolumn- eller databasnivå:

SELECT [c].[Id], [c].[Name]
FROM [Customers] AS [c]
WHERE [c].[Name] COLLATE SQL_Latin1_General_CP1_CS_AS = N'John'

Explicit sorteringsordningar och index

Index är en av de viktigaste faktorerna i databasprestanda – en fråga som körs effektivt med ett index kan stoppas utan indexet. Index ärver implicit sortering av sin kolumn; Det innebär att alla frågor i kolumnen automatiskt är berättigade att använda index som definierats i kolumnen , förutsatt att frågan inte anger någon annan sortering. Om du anger en explicit sortering i en fråga förhindrar det vanligtvis att frågan använder ett index som definierats i den kolumnen, eftersom sorteringarna inte längre skulle matcha. Vi rekommenderar därför att du är försiktig när du använder den här funktionen. Det är alltid att föredra att definiera sorteringen på kolumnnivå (eller databasnivå), så att alla frågor implicit kan använda sorteringen och dra nytta av alla index.

Observera att vissa databaser tillåter att sortering definieras när du skapar ett index (t.ex. PostgreSQL, Sqlite). Detta gör att flera index kan definieras på samma kolumn, vilket påskyndar åtgärder med olika sorteringar (t.ex. både skiftlägeskänsliga och skiftlägesokänsliga jämförelser). Mer information finns i databasleverantörens dokumentation.

Översättning av inbyggda .NET-strängåtgärder

I .NET är strängjämlikhet versalkänslig som standard: s1 == s2 utför en ordinal jämförelse som kräver att strängarna är identiska. Eftersom standardsortering av databaser varierar, och eftersom det är önskvärt för enkel likhet att använda index, gör EF Core inget försök att översätta enkel likhet till en databas skiftlägeskänslig åtgärd: C#-likhet översätts direkt till SQL-likhet, vilket kan vara skiftlägeskänsligt, beroende på den specifika databas som används och dess sorteringskonfiguration.

Dessutom tillhandahåller .NET överlagringar av string.Equals som accepterar en StringComparison uppräkning, vilket gör det möjligt att ange skiftlägeskänslighet och en kultur för jämförelsen. Ef Core avstår avsiktligt från att översätta dessa överlagringar till SQL, och försök att använda dem resulterar i ett undantag. För det första vet EF Core inte vilken skiftlägeskänslig eller skiftlägesokänslig sortering som ska användas. Ännu viktigare är att en sortering i de flesta fall skulle förhindra indexanvändning, vilket avsevärt påverkar prestanda för en mycket grundläggande och vanlig .NET-konstruktion. Om du vill tvinga en fråga att använda skiftlägeskänslig eller skiftlägesokänslig jämförelse anger du en sortering explicit via EF.Functions.Collatesom beskrivet ovan.

Ytterligare resurser

Databasspecifik information

• SQL Server-dokumentation om kollektioner.
• Microsoft.Data.Sqlite-dokumentation om sorteringsordningar.
• PostgreSQL-dokumentation om sortering.
• MySQL-dokumentation om sortering.

Andra resurser

• .NET Data Community Standup-möte, en introduktion till sorteringsordningar och utforskning av prestanda- och indexeringsaspekter.