Intercalaciones y distinción de mayúsculas y minúsculas

  • Artículo

El procesamiento de textos en bases de datos puede ser complejo y requiere más atención por parte del usuario de lo que cabría sospechar. Por un lado, las bases de datos varían considerablemente en su forma de manipular el texto; por ejemplo, mientras que algunas distinguen de forma predeterminada entre mayúsculas y minúsculas (por ejemplo, Sqlite, PostgreSQL), otras no lo hacen (SQL Server, MySQL). Además, debido al uso de índices, la distinción entre mayúsculas y minúsculas y aspectos similares pueden tener un gran impacto en el rendimiento de las consultas: aunque puede resultar tentador utilizar string.ToLower para forzar una comparación que no distinga entre mayúsculas y minúsculas en una base de datos que distinga entre mayúsculas y minúsculas, hacerlo puede impedir que la aplicación utilice índices. En esta página se detalla cómo configurar la distinción entre mayúsculas y minúsculas o, de forma más general, las intercalaciones, y cómo hacerlo de forma eficaz sin comprometer 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 y comparan los valores de texto para la igualdad. Por ejemplo, mientras que una intercalación que no distingue mayúsculas de minúsculas no tiene en cuenta las diferencias entre mayúsculas y minúsculas a efectos de comparación de la igualdad, una intercalación que distingue mayúsculas de minúsculas no lo hace. Sin embargo, dado que la distinción entre mayúsculas y minúsculas depende de la cultura (por ejemplo, i y I representan letras diferentes en turco), existen múltiples intercalaciones que no distinguen entre mayúsculas y minúsculas, cada una con su propio conjunto de reglas. El ámbito de las intercalaciones también se extiende más allá de la distinción entre mayúsculas y minúsculas a 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 del texto: mientras que el alemán coloca ä después de a, el sueco lo coloca al final del alfabeto.

Todas las operaciones de texto de una base de datos utilizan una intercalación, ya sea de forma explícita o implícita, para determinar cómo la operación compara y ordena las cadenas. La lista actual de intercalaciones disponibles y sus esquemas de nomenclatura depende de cada base de datos; consulte la sección siguiente para obtener vínculos a las páginas de documentación pertinentes de las distintas 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 instrucción DDL CREATE DATABASE) 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 predeterminada de nivel de servidor en SQL Server para la configuración regional de máquina "Inglés (Estados Unidos)" es SQL_Latin1_General_CP1_CI_AS, que es una intercalación que no distingue entre mayúsculas y minúsculas ni entre 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.

Cuando se utilizan migraciones de EF Core para administrar el esquema de la base de datos, lo siguiente en el método OnModelCreating del modelo configura una base de datos SQL Server para utilizar una intercalación que distingue entre mayúsculas y 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 determinadas columnas no deben distinguir entre mayúsculas y minúsculas, mientras que el resto de la base de datos debe distinguirlas.

Cuando se utilizan migraciones de EF Core para administrar el esquema de la base de datos, lo siguiente configura la columna de la propiedad Name para que no distinga entre mayúsculas y minúsculas en una base de datos que, de lo contrario, está configurada para distinguir 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 mediante intercalaciones diferentes mediante consultas diferentes. Por ejemplo, una consulta puede necesitar realizar una comparación que distinga entre mayúsculas y minúsculas en una columna, mientras que otra puede necesitar realizar una comparación que no distinga 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 = context.Customers
    .Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
    .ToList();

Esto genera una cláusula COLLATE en la consulta SQL, que aplica una intercalación que distingue mayúsculas de minúsculas independientemente de la intercalación definida a nivel de columna o de 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 una base de datos: una consulta que se ejecuta eficazmente con un índice puede detenerse gradualmente sin él. Los índices heredan implícitamente la intercalación de su columna; esto significa que todas las consultas de la columna pueden utilizar automáticamente los índices definidos en esa columna, siempre que la consulta no especifique una intercalación diferente. Si se especifica una intercalación explícita en una consulta, generalmente se impedirá que esa consulta utilice un índice definido en esa columna, ya que las intercalaciones dejarían de coincidir; por lo tanto, se recomienda tener cuidado al utilizar 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 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 información.

Advertencia

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

Traducción de operaciones de cadena de .NET integradas

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

Además, .NET proporciona sobrecargas de string.Equals que aceptan una enumeración StringComparison, que permite especificar la distinción entre mayúsculas y minúsculas y una referencia cultural para la comparación. Por diseño, EF Core se abstiene de traducir estas sobrecargas a SQL e intentar utilizarlas provocará una excepción. Por un lado, el núcleo de EF no sabe qué intercalación de mayúsculas y minúsculas o de minúsculas y minúsculas debe utilizarse. Y lo que es más importante, 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 .NET muy básica y usada habitualmente. Para forzar que una consulta use una comparación que distinga entre mayúsculas de minúsculas o que no distinga entre mayúsculas de minúsculas, especifique una intercalación explícitamente mediante EF.Functions.Collate, como se ha detallado anteriormente.

Recursos adicionales

Información específica de la base de datos

Otros recursos