Intercalaciones y confidencialidad de mayúsculas y minúsculas

El procesamiento de texto en las bases de datos puede ser complejo y requiere más atención del usuario de la que uno sospecharía. Por un lado, las bases de datos varían considerablemente en la forma en que controlan el texto. Por ejemplo, mientras algunas bases de datos distinguen mayúsculas de minúsculas de forma predeterminada (por ejemplo, Sqlite, PostgreSQL), otras no distinguen mayúsculas de minúsculas (SQL Server, MySQL). Además, debido al uso de índices, la confidencialidad de mayúsculas y minúsculas y aspectos similares pueden tener un impacto de gran alcance en el rendimiento de las consultas: aunque puede ser tentador usar para forzar una comparación sin mayúsculas de minúsculas en una base de datos que distingue mayúsculas de minúsculas, hacerlo puede impedir que la aplicación use string.ToLower índices. En esta página se detalla cómo configurar la confidencialidad de mayúsculas y minúsculas, o más generalmente, las intercalaciones, y cómo hacerlo de forma eficaz sin poner en peligro el rendimiento de las consultas.

Introducción a las intercalaciones

Un concepto fundamental en el procesamiento de texto es la intercalación, que es un conjunto de reglas que determinan cómo se ordenan los valores de texto y se compara la igualdad. Por ejemplo, aunque una intercalación que no distingue mayúsculas de minúsculas no tiene en cuenta las diferencias entre letras mayúsculas y minúsculas para fines de comparación de igualdad, una intercalación que distingue mayúsculas de minúsculas no lo hace. Sin embargo, dado que la confidencialidad de mayúsculas y minúsculas distingue la referencia cultural (por ejemplo, y representan letras diferentes en turco), existen varias intercalaciones que no distinguen mayúsculas de minúsculas, cada una con su propio conjunto de iI reglas. El ámbito de las intercalaciones también se extiende más allá de la confidencialidad de mayúsculas y minúsculas, a otros aspectos de los datos de caracteres; en alemán, por ejemplo, a veces es deseable (pero no siempre) tratar ä y ae como idéntico. Por último, las intercalaciones también definen cómo se ordenan los valores de texto: mientras que los lugares en alemán después de , el sueco los coloca al final del a alfabeto.

Todas las operaciones de texto de una base de datos usan una intercalación (ya sea explícita o implícitamente) para determinar cómo compara y ordena las cadenas la operación. La lista real de intercalaciones disponibles y sus esquemas de nomenclatura es específica de la base de datos; Consulte la sección siguiente para obtener vínculos a las páginas de documentación pertinentes de varias bases de datos. Afortunadamente, la base de datos generalmente permite definir una intercalación predeterminada en el nivel de base de datos o columna, y especificar explícitamente qué intercalación se debe usar para operaciones específicas en una consulta.

Intercalación de base de datos

En la mayoría de los sistemas de base de datos, se define una intercalación predeterminada en el nivel de base de datos; a menos que se invalide, esa intercalación se aplica implícitamente a todas las operaciones de texto que se producen dentro de esa base de datos. La intercalación de la base de datos se establece normalmente en el momento de creación de la base de datos (a través de la instrucción DDL) y, si no se especifica, el valor predeterminado es un valor de nivel de servidor determinado en el momento de la CREATE DATABASE instalación. Por ejemplo, la intercalación de nivel de servidor predeterminada en SQL Server es , que es una intercalación que distingue mayúsculas de minúsculas y que distingue SQL_Latin1_General_CP1_CI_AS acentos. Aunque los sistemas de base de datos normalmente permiten modificar la intercalación de una base de datos existente, hacerlo puede provocar complicaciones. Se recomienda seleccionar una intercalación antes de la creación de la base de datos.

Al usar EF Core para administrar el esquema de base de datos, lo siguiente en el método del modelo configura una base de datos SQL Server para usar una intercalación que distingue mayúsculas de OnModelCreating minúsculas:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Intercalación de columnas

Las intercalaciones también se pueden definir en columnas de texto, invalidando el valor predeterminado de la base de datos. Esto puede ser útil si determinadas columnas deben no tener en cuenta mayúsculas de minúsculas, mientras que el resto de la base de datos debe tener mayúsculas de minúsculas.

Al usar EF Core para administrar el esquema de base de datos, lo siguiente configura la columna para que la propiedad no distingue mayúsculas de minúsculas en una base de datos que, de lo contrario, está configurada para que distingue mayúsculas de Name minúsculas:

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

Intercalación explícita en una consulta

En algunos casos, la misma columna debe consultarse mediante intercalaciones diferentes mediante consultas diferentes. Por ejemplo, una consulta puede necesitar realizar una comparación que distingue mayúsculas de minúsculas en una columna, mientras que otra puede necesitar realizar una comparación sin mayúsculas y minúsculas en la misma columna. Esto se puede lograr especificando explícitamente una intercalación dentro de la propia consulta:

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

Esto genera una cláusula en la consulta SQL, que aplica una intercalación que distingue mayúsculas de minúsculas independientemente de la intercalación definida en el nivel de columna o base COLLATE de datos:

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

Intercalaciones e índices explícitos

Los índices son uno de los factores más importantes en el rendimiento de la base de datos: una consulta que se ejecuta de forma eficaz con un índice se puede detener sin ese índice. Los índices heredan implícitamente la intercalación de su columna; Esto significa que todas las consultas de la columna son aptas automáticamente para usar índices definidos en esa columna, siempre que la consulta no especifique una intercalación diferente. Especificar una intercalación explícita en una consulta generalmente impedirá que esa consulta use un índice definido en esa columna, ya que las intercalaciones ya no coincidirían; Por lo tanto, se recomienda tener cuidado al usar esta característica. Siempre es preferible definir la intercalación en el nivel de columna (o base de datos), lo que permite que todas las consultas usen implícitamente esa intercalación y se beneficien de cualquier índice.

Tenga en cuenta que algunas bases de datos permiten definir la intercalación al crear un índice (por ejemplo, PostgreSQL o Sqlite). Esto permite definir varios índices en la misma columna, lo que acelera las operaciones con intercalaciones diferentes (por ejemplo, comparaciones que distinguen mayúsculas de minúsculas y no distinguen mayúsculas de minúsculas). Consulte la documentación del proveedor de bases de datos para obtener más detalles.

Traducción de operaciones de cadena de .NET integradas

En .NET, la igualdad de cadenas distingue mayúsculas de minúsculas de forma predeterminada: realiza una comparación ordinal que requiere que las s1 == s2 cadenas sean idénticas. Dado que la intercalación predeterminada de bases de datos varía y, dado que es deseable que la igualdad simple use índices, EF Core no intenta traducir la igualdad simple a una operación que distingue mayúsculas de minúsculas de la base de datos: la igualdad de C# se traduce directamente SQL la igualdad de SQL, que puede o no distingue mayúsculas de minúsculas, en función de la base de datos específica en uso y su configuración de intercalación.

Además, .NET proporciona sobrecargas para aceptar una enumeración, lo que permite especificar la referencia cultural y la confidencialidad de mayúsculas y minúsculas string.EqualsStringComparison para la comparación. Por diseño, EF Core evita traducir estas sobrecargas a SQL y al intentar usarlas se producirá una excepción. Por un lado, EF Core no sabe qué intercalación distingue mayúsculas de minúsculas o no distingue mayúsculas de minúsculas. Lo más importante es que aplicar una intercalación impediría en la mayoría de los casos el uso de índices, lo que afectaría significativamente al rendimiento de una construcción de .NET muy básica y de uso frecuente. Para forzar que una consulta use una comparación que distingue mayúsculas de minúsculas o que no distingue mayúsculas de minúsculas, especifique una intercalación explícitamente a través de como se ha EF.Functions.CollateEF.Functions.Collate

Recursos adicionales

Información específica de la base de datos

• SQL Server documentación sobre intercalaciones.
• Documentación de Microsoft.Data.Sqlite sobre intercalaciones.
• Documentación de PostgreSQL sobre intercalaciones.
• Documentación de MySQL sobre intercalaciones.

Otros recursos

• EF Core Community sesión de standup,que presenta intercalaciones y explora aspectos de la perf e indexación.