Compartir por

Intercalaciones y sensibilidad a 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 que uno sospecharía. Para una cosa, las bases de datos varían considerablemente en la forma en que controlan el texto; por ejemplo, aunque 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 sensibilidad a mayúsculas y minúsculas y aspectos similares pueden tener un impacto de gran alcance en el rendimiento de las consultas: aunque puede resultar tentador usar string.ToLower para forzar una comparación sin sensibilidad a mayúsculas y minúsculas en una base de datos sensible a mayúsculas y minúsculas, lo cual podría impedir que la aplicación use índices. En esta página se detalla cómo configurar la distinción entre mayúsculas y minúsculas, o más generalmente, 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 colación, que es un conjunto de reglas que determinan cómo se ordenan y comparan los valores de texto para determinar si son iguales. Por ejemplo, aunque una intercalación sin distinción entre mayúsculas y minúsculas omita las diferencias entre letras mayúsculas y minúsculas con fines de comparación de igualdad, una intercalación que distingue mayúsculas y minúsculas no. Sin embargo, dado que la distinción entre mayúsculas y minúsculas depende de las referencias culturales (por ejemplo, i y I representan letras diferentes en turco), existen varios conjuntos de ordenamiento insensibles a mayúsculas, cada uno con su propio conjunto de reglas. El ámbito de las intercalaciones también va más allá de la distinción entre mayúsculas y minúsculas, abarcando otros aspectos de los datos de caracteres; en alemán, por ejemplo, a veces (pero no siempre) es deseable tratar ä y ae como idénticos. Por último, las intercalaciones también definen cómo se ordenan los valores de texto: mientras que el alemán coloca la letra ä después de a, el sueco la coloca al final del 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 la operación compara y ordena las cadenas. La lista actual de intercalaciones disponibles y sus esquemas de nomenclatura es específica de la base de datos; consulte la sección a continuación para obtener vínculos a las páginas de documentación pertinentes de varias bases de datos. Afortunadamente, las bases de datos generalmente permiten 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 base de datos se establece normalmente en el momento de creación de la base de datos (a través de la CREATE DATABASE instrucción DDL) y, si no se especifica, el valor predeterminado es un valor de nivel de servidor determinado en el momento de la instalación. Por ejemplo, la intercalación de nivel de servidor predeterminada en SQL Server para la configuración regional del equipo "Inglés (Estados Unidos)" es SQL_Latin1_General_CP1_CI_AS, que es una intercalación que no distingue mayúsculas de minúsculas y que distingue acentos. Aunque los sistemas de bases de datos normalmente permiten modificar la intercalación de una base de datos existente, hacerlo puede provocar complicaciones; se recomienda elegir una intercalación antes de la creación de la base de datos.

Al usar migraciones de EF Core para administrar el esquema de la base de datos, el siguiente en el método del OnModelCreating modelo configura una base de datos de SQL Server para usar una intercalación que distingue mayúsculas de 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, reemplazando el valor predeterminado de la base de datos. Esto puede ser útil si ciertas columnas deben ser insensibles a mayúsculas y minúsculas, mientras que el resto de la base de datos deba ser sensible a mayúsculas y minúsculas.

Al utilizar migraciones de EF Core para gestionar el esquema de su base de datos, lo siguiente configura la columna para que la propiedad Name sea insensible a las mayúsculas en una base de datos que de otro modo estaría configurada para diferenciar entre mayúsculas y 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, es necesario consultar la misma columna con distintas intercalaciones en consultas diferentes. Por ejemplo, una consulta puede necesitar realizar una comparación con distinción entre mayúsculas y minúsculas en una columna, mientras que otra puede necesitar realizar una comparación sin distinción entre 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 = await context.Customers
    .Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
    .ToListAsync();

Esto genera una cláusula COLLATE en la consulta SQL, la cual aplica una intercalación sensible a mayúsculas y minúsculas independientemente de la intercalación definida en el nivel de columna o base 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 bases de datos: una consulta que se ejecuta de forma eficiente con un índice puede detenerse por completo 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. La especificación de 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 precaución 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, Sqlite). Esto permite definir varios índices en la misma columna, acelerando las operaciones con intercalaciones diferentes (por ejemplo, comparaciones sensibles a mayúsculas y minúsculas y no sensibles a ellas). Consulte la documentación del proveedor de bases de datos para obtener más información.

Advertencia

Inspeccione siempre los planes de consulta de las consultas y asegúrese de que los índices adecuados se usan en consultas críticas para el rendimiento que se ejecutan en grandes cantidades de datos. Invalidar la distinción entre mayúsculas y minúsculas en una consulta mediante EF.Functions.Collate o una llamada a string.ToLower puede tener un impacto muy significativo en el rendimiento de tu aplicación.

Traducción de operaciones de cadena de .NET integradas

En .NET, la igualdad de cadenas es sensible a las mayúsculas y minúsculas de forma predeterminada: s1 == s2 realiza una comparación ordinal que requiere que las cadenas sean idénticas. Dado que la intercalación predeterminada de las 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 con distinción entre mayúsculas y minúsculas de la base de datos: la igualdad de C# se traduce directamente a la igualdad de SQL, que puede o no distinguir entre mayúsculas y 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 de string.Equals aceptando una enumeración StringComparison, lo que permite especificar la sensibilidad a mayúsculas y minúsculas y una cultura para la comparación. Por diseño, EF Core se abstiene de traducir estas sobrecargas a SQL y, al intentar usarlos, se producirá una excepción. Para empezar, EF Core no sabe qué intercalación sensible o insensible a mayúsculas y minúsculas debe usarse. 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 comúnmente utilizada. Para forzar que una consulta use una comparación que distinga entre mayúsculas y minúsculas o que no lo haga, especifique una intercalación explícitamente mediante EF.Functions.Collate como se detalla arriba.

Recursos adicionales

Información específica de la base de datos

Otros recursos

  • Last updated on