Ordenações e diferenciação de maiúsculas e minúsculas

  • Artigo

O processamento de texto em bancos de dados pode ser complexo e requer mais atenção do usuário do que se suspeitaria. Por um lado, os bancos de dados variam consideravelmente na forma como lidam com o texto; por exemplo, enquanto alguns bancos de dados diferenciam maiúsculas de minúsculas por padrão (por exemplo, Sqlite, PostgreSQL), outros não diferenciam maiúsculas de minúsculas (SQL Server, MySQL). Além disso, devido ao uso de índice, a confidencialidade de maiúsculas e minúsculas e aspectos semelhantes podem ter um impacto de longo alcance no desempenho da consulta: embora possa ser tentador usar string.ToLower para forçar uma comparação que não diferencia maiúsculas de minúsculas em um banco de dados que diferencia maiúsculas de minúsculas, isso pode impedir que seu aplicativo use índices. Essa página detalha como configurar a confidencialidade de maiúsculas e minúsculas ou, em geral, ordenações e como fazer isso de forma eficiente sem comprometer o desempenho da consulta.

Introdução às ordenações

Um conceito fundamental no processamento de texto é a ordenação, que é um conjunto de regras que determina como os valores de texto são ordenados e comparados quanto à igualdade. Por exemplo, enquanto uma ordenação que não diferencia maiúsculas de minúsculas desconsidera diferenças entre letras maiúsculas e minúsculas para fins de comparação de igualdade, uma ordenação que diferencia maiúsculas de minúsculas não. No entanto, como a confidencialidade de maiúsculas e minúsculas diferencia a cultura (por exemplo i e I representam letras diferentes em turco), existem várias ordenações que não diferenciam maiúsculas de minúsculas, cada uma com seu próprio conjunto de regras. O escopo das ordenações também vai além da sensibilidade de maiúsculas e minúsculas, para outros aspectos dos dados de caractere; em alemão, por exemplo, às vezes é (mas nem sempre) desejável tratar ä e ae como idênticos. Por fim, as ordenações também definem como os valores de texto são ordenados: enquanto o alemão coloca ä depois a, o sueco o coloca no final do alfabeto.

Todas as operações de texto em um banco de dados usam uma ordenação, explicita ou implicitamente, para determinar como a operação compara e ordena cadeias de caracteres. A lista real de ordenações disponíveis e seus esquemas de nomenclatura é específica do banco de dados. Consulte a seção abaixo para obter links para páginas de documentação relevantes de vários bancos de dados. Felizmente, os bancos de dados geralmente permitem que uma ordenação padrão seja definida no nível do banco de dados ou da coluna e especifique explicitamente qual ordenação deve ser usada para operações específicas em uma consulta.

Ordenação do Banco de Dados

Na maioria dos sistemas de banco de dados, uma ordenação padrão é definida no nível do banco de dados; a menos que substituído, essa ordenação se aplica implicitamente a todas as operações de texto que ocorrem dentro desse banco de dados. Normalmente, a ordenação do banco de dados é definida no momento da criação do banco de dados (por meio da instrução DDL do CREATE DATABASE) e, se não for especificada, usa como padrão um valor de nível de servidor determinado no momento da instalação. Por exemplo, a ordenação padrão no nível do servidor no SQL Server para a localidade do computador "Inglês (Estados Unidos)" é SQL_Latin1_General_CP1_CI_AS, que não diferencia maiúsculas de minúsculas e diferencia acentos gráficos. Embora os sistemas de banco de dados geralmente permitam alterar a ordenação de um banco de dados existente, isso pode levar a complicações; É recomendável escolher uma ordenação antes da criação do banco de dados.

Ao usar migrações do EF Core para gerenciar o esquema do seu banco de dados, o seguinte no método OnModelCreating do seu modelo configura um banco de dados SQL Server para usar um agrupamento que diferencia maiúsculas de minúsculas:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Ordenação de colunas

As ordenações também podem ser definidas em colunas de texto, substituindo o padrão do banco de dados. Isso pode ser útil se determinadas colunas precisarem não diferenciar maiúsculas de minúsculas, enquanto o restante do banco de dados precisa diferenciar maiúsculas de minúsculas.

Ao usar migrações do EF Core para gerenciar seu esquema de banco de dados, o seguinte configura a coluna para que a propriedade Name não diferencie maiúsculas de minúsculas em um banco de dados configurado para diferenciar maiúsculas de minúsculas:

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

Ordenação explícita em uma consulta

Em alguns casos, a mesma coluna precisa ser consultada usando ordenações diferentes por consultas diferentes. Por exemplo, uma consulta pode precisar executar uma comparação que diferencia maiúsculas de minúsculas em uma coluna, enquanto outra pode precisar executar uma comparação que não diferencia maiúsculas de minúsculas na mesma coluna. Isso pode ser feito especificando explicitamente uma ordenação dentro da própria consulta:

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

Isso gera uma cláusula COLLATE na consulta SQL, que aplica uma ordenação que diferencia maiúsculas de minúsculas, independentemente da ordenação definida no nível da coluna ou do banco de dados:

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

Ordenações e índices explícitos

Índices são um dos fatores mais importantes no desempenho do banco de dados: uma consulta que é executada com eficiência com um índice pode parar sem esse índice. Índices herdam implicitamente a ordenação de sua coluna; isso significa que todas as consultas na coluna são automaticamente qualificadas para usar índices definidos nessa coluna, desde que a consulta não especifique uma ordenação diferente. Especificar uma ordenação explícita em uma consulta geralmente impedirá que essa consulta use um índice definido nessa coluna, pois as ordenações não corresponderiam mais. Portanto, é recomendável ter cuidado ao usar esse recurso. É sempre preferível definir a ordenação no nível de coluna (ou banco de dados), permitindo que todas as consultas usem implicitamente essa ordenação e se beneficiem de qualquer índice.

Observe que alguns bancos de dados permitem que a ordenação seja definida ao criar um índice (por exemplo, PostgreSQL, Sqlite). Isso permite que vários índices sejam definidos na mesma coluna, acelerando as operações com ordenações diferentes (por exemplo, comparações que diferenciam maiúsculas de minúsculas e maiúsculas de minúsculas). Consulte a documentação do provedor de banco de dados para obter mais informações.

Aviso

Sempre inspecione os planos de consulta de suas consultas e verifique se os índices adequados estão sendo usados em consultas críticas de desempenho em execução em grandes quantidades de dados. Substituir a confidencialidade de maiúsculas e minúsculas em uma consulta por meio de EF.Functions.Collate (ou chamando string.ToLower) pode ter um impacto muito significativo no desempenho do aplicativo.

Tradução de operações de cadeia de caracteres internas do .NET

No .NET, a igualdade de cadeia de caracteres diferencia maiúsculas de minúsculas por padrão: s1 == s2 executa uma comparação ordinal que exige que as cadeias de caracteres sejam idênticas. Como a ordenação padrão de bancos de dados varia e, como é desejável que a igualdade simples use índices, o EF Core não tenta traduzir a igualdade simples para uma operação que diferencia maiúsculas de minúsculas do banco de dados: a igualdade em C# é traduzida diretamente em igualdade de SQL, que pode ou não diferenciar maiúsculas de minúsculas, dependendo do banco de dados específico em uso e de sua configuração de ordenação.

Além disso, o .NET fornece sobrecargas de aceitação de string.Equals aceitando uma enumeração StringComparison, o que permite especificar a confidencialidade de maiúsculas e minúsculas e uma cultura para a comparação. Por design, o EF Core se abstém de traduzir essas sobrecargas para o SQL e tentar usá-las resultará em uma exceção. Por um lado, o EF Core não sabe qual ordenação que diferencia maiúsculas de minúsculas ou maiúsculas de minúsculas deve ser usada. E o mais importante: a aplicação de uma ordenação evitaria, na maioria dos casos, o uso do índice, afetando significativamente o desempenho de um constructo .NET muito básico e comumente usado. Para forçar uma consulta a usar uma comparação que diferencia maiúsculas de minúsculas ou que não diferencia maiúsculas de minúsculas, especifique uma ordenação explicitamente por meio de EF.Functions.Collate conforme detalhado acima.

Recursos adicionais

Informações específicas do banco de dados

Outros recursos