照合順序と大文字と小文字の区別

  • [アーティクル]

データベースでのテキスト処理は複雑である可能性があり、ユーザーが考えているよりも多くの注意が必要です。 一例を挙げると、データベースではテキストの処理方法が大きく異なります。たとえば、既定で大文字と小文字が区別されるデータベース (例: Sqlite、PostgreSQL) もあれば、大文字と小文字が区別されないものもあります (SQL Server、MySQL)。 また、インデックスの使用により、大文字と小文字の区別や類似する側面がクエリのパフォーマンスに大きく影響する可能性があります。大文字と小文字が区別されるデータベースで、string.ToLower を使用して大文字と小文字を区別しない比較を強制したい気持ちになるかもしれませんが、それを行った場合、アプリケーションでインデックスを使用できなくなる可能性があります。 このページでは、大文字と小文字の区別、つまり、より一般的には照合順序を構成する方法と、クエリのパフォーマンスを低下させずにそれを効率的に行う方法について詳しく説明します。

照合順序の概要

テキスト処理の基本概念は "照合順序" です。これは、テキスト値の順序付けと等価性に関する比較方法を決定する一連のルールです。 たとえば、大文字と小文字を区別しない照合順序では、等価性の比較の目的で、大文字と小文字の違いは無視されますが、大文字と小文字を区別する照合順序ではされません。 ただし、大文字と小文字の区別はカルチャに依存する (たとえば、トルコ語では i と I は異なる文字を表す) ため、大文字と小文字を区別しない照合順序は複数存在しており、それぞれに独自のルール セットがあります。 また、照合順序のスコープは、大文字と小文字の区別を超えて、文字データの他の側面にまで及びます。たとえば、ドイツ語では、ä と ae は同一として扱うことが望ましい場合があります (常にではありません)。 最後に、照合順序では、テキスト値の "順序付け" 方法も定義されます。ドイツ語では、ä は a の後に配置されますが、スウェーデン語ではアルファベットの末尾に配置されます。

データベース内のすべてのテキスト操作では、明示的にも暗黙的にも、照合順序を使用して、操作での文字列の比較と順序付けの方法が決定されます。 使用可能な照合順序とその名前付けスキームの実際の一覧は、データベースに固有です。さまざまなデータベースの関連ドキュメント ページへのリンクについては、以下のセクションを参照してください。 幸いなことに、データベースでは、データベースまたは列レベルで既定の照合順序を定義することと、クエリの特定の操作に使用する照合順序を明示的に指定することが一般に許可されます。

データベース照合順序

ほとんどのデータベース システムでは、既定の照合順序はデータベース レベルで定義されています。オーバーライドしない限り、その照合順序が、そのデータベース内で発生するすべてのテキスト操作に暗黙的に適用されます。 データベースの照合順序は、通常、データベースの作成時に (CREATE DATABASE DDL ステートメントを使用して) 設定されます。指定されていない場合は、セットアップ時に決定されたサーバーレベルの値が既定で使用されます。 たとえば、"英語 (米国)" コンピューター ロケールの SQL Server の既定のサーバー レベルの照合順序は SQL_Latin1_General_CP1_CI_AS であり、大文字と小文字が区別されず、アクセントが区別される照合順序です。 データベース システムでは、通常、既存のデータベースの照合順序の変更が許可されていますが、そうした場合、複雑になる可能性があります。照合順序は、データベースの作成前に選択することをお勧めします。

EF Core の移行を使用してデータベース スキーマを管理している場合は、次のようにモデルの OnModelCreating メソッド指定すると、大文字と小文字を区別する照合順序を使用するように SQL Server データベースが構成されます。

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

列の照合順序

照合順序をテキスト列に定義して、データベースの既定値を上書きすることもできます。 これは、特定の列では大文字と小文字を区別しない必要があり、データベースの残りの部分では大文字と小文字を区別する必要がある場合に便利です。

EF Core の移行を使用してデータベース スキーマを管理している場合は、次のように指定することで、大文字と小文字を区別するように構成されているデータベースで、大文字と小文字を区別しないように Name プロパティの列を構成できます。

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

クエリでの明示的な照合順序

場合によっては、クエリごとに異なる照合順序を使用して、同じ列に対してクエリを実行する必要があります。 たとえば、あるクエリでは列に対して大文字と小文字を区別した比較を実行する必要がある一方で、別のクエリでは同じ列に対して大文字と小文字を区別しない比較を実行する必要がある場合があります。 これは、クエリ自体に照合順序を明示的に指定することで実現できます。

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

これにより、SQL クエリ内に COLLATE 句が生成され、列またはデータベース レベルで定義されている照合順序に関係なく、大文字と小文字を区別する照合順序が適用されます。

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

明示的な照合順序とインデックス

インデックスは、データベースのパフォーマンスで最も重要な要素の 1 つです。あるインデックスを使って効率的に実行されているクエリが、そのインデックスがないと徐々に停止する可能性があります。 インデックスは、列の照合順序を暗黙的に継承します。つまり、クエリに異なる照合順序が指定されていなければ、その列に対するすべてのクエリは自動的に、その列に定義されているインデックスを使用できます。 クエリに明示的な照合順序を指定すると、照合順序が一致しなくなるため、一般にそのクエリでは、その列に定義されているインデックスは使用されなくなります。そのため、この機能を使用する際は注意が必要です。 照合順序は、常に、列 (またはデータベース) レベルで定義することをお勧めします。これにより、すべてのクエリでは暗黙的にその照合順序が使用され、どのインデックスも利用できます。

一部のデータベースでは、インデックスの作成時に照合順序を定義できることに注意してください (PostgreSQL、Sqlite など)。 これにより、同じ列に複数のインデックスが定義され、異なる照合順序を使用して操作が高速化されます (大文字と小文字が区別される比較と大文字と小文字が区別されない比較の両方など)。 詳細については、データベース プロバイダーのドキュメントを参照してください。

警告

クエリのクエリ プランを常に検査し、大量のデータに対して実行される、パフォーマンスが重要なクエリで適切なインデックスが使用されるようにしてください。 EF.Functions.Collate を使用して (または string.ToLower を呼び出して) クエリの大文字と小文字の区別をオーバーライドすると、アプリケーションのパフォーマンスに非常に大きな影響を与える可能性があります。

組み込みの .NET 文字列操作の変換

.NET では、文字列の等価性では既定で大文字と小文字が区別されます。s1 == s2 では、文字列が同一である必要がある序数に基づく比較が実行されます。 データベースの既定の照合順序は異なるため、また単純な等価性ではインデックスを使用することが望ましいため、EF Core では、単純な等価性をデータベースの大文字と小文字を区別する操作に変換することは試行されません。C# の等価性は、直接 SQL の等価性に変換されます。これは、使用されている特定のデータベースとその照合順序の構成に応じて、大文字と小文字が区別される場合もされない場合もあります。

さらに、.NET では、StringComparison 列挙型を受け入れる string.Equals のオーバーロードが提供されます。これにより、比較のために大文字と小文字の区別とカルチャを指定できます。 EF Core では、仕様により、これらのオーバーロードは SQL に変換されず、それらを使用しようとすると例外が発生します。 一つには、EF Core には、大文字と小文字を区別する、または大文字と小文字を区別しない照合順序の中でどれを使用すべきかがわからないということがあります。 さらに重要なこととして、照合順序を適用すると、ほとんどの場合、インデックスの使用が妨げられ、非常に基本的で一般に使用される .NET コンストラクトのパフォーマンスに大きく影響します。 大文字と小文字を区別する、または大文字と小文字を区別しない比較をクエリで強制的に使用するには、前述のように、EF.Functions.Collate を使用して明示的に照合順序を指定してください。

その他のリソース

データベース固有の情報

その他のリソース