Ejemplo de restricción COLLATE

Ejemplos de restricción COLLATE

La restricción COLLATE define las reglas para comparar y ordenar caracteres en una base de datos. Determina aspectos como la sensibilidad a mayúsculas/minúsculas, acentos, orden alfabético específico del idioma y otros atributos relacionados con las operaciones de comparación de cadenas. Una colación adecuada es crucial para el correcto funcionamiento de consultas que involucran ordenamiento, agrupación y comparaciones de texto.

MySQL

En MySQL, podemos aplicar colaciones a nivel de base de datos, tabla o columna:

-- A nivel de base de datos
CREATE DATABASE biblioteca
CHARACTER SET utf8mb4
COLLATE utf8mb4_spanish_ci;

-- A nivel de tabla
CREATE TABLE libros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(100) COLLATE utf8mb4_spanish_ci,
    autor VARCHAR(100) COLLATE utf8mb4_spanish_ci
) COLLATE utf8mb4_spanish_ci;

También podemos usar COLLATE en consultas específicas:

-- Ordenar resultados según reglas de español
SELECT titulo, autor
FROM libros
ORDER BY titulo COLLATE utf8mb4_spanish_ci;

-- Búsqueda insensible a mayúsculas/minúsculas
SELECT * FROM libros
WHERE titulo COLLATE utf8mb4_general_ci = 'don quijote';

Para modificar la colación de una tabla o columna existente:

-- Modificar colación de una tabla
ALTER TABLE libros
COLLATE utf8mb4_spanish_ci;

-- Modificar colación de una columna
ALTER TABLE libros
MODIFY titulo VARCHAR(100) COLLATE utf8mb4_spanish_ci;

Colaciones comunes en MySQL:

• utf8mb4_general_ci: Insensible a mayúsculas/minúsculas, comparación rápida pero menos precisa
• utf8mb4_unicode_ci: Insensible a mayúsculas/minúsculas, según estándares Unicode
• utf8mb4_spanish_ci: Especial para idioma español (ñ, acentos, etc.)
• utf8mb4_bin: Binaria, sensible a mayúsculas/minúsculas, compara códigos binarios
• utf8mb4_0900_ai_ci: Estándar Unicode 9.0, insensible a acentos y mayúsculas/minúsculas (MySQL 8.0+)

PostgreSQL

En PostgreSQL, las colaciones se especifican principalmente al crear la base de datos, pero también se pueden aplicar a nivel de columna:

-- A nivel de base de datos
CREATE DATABASE biblioteca
WITH ENCODING 'UTF8'
LC_COLLATE = 'es_ES.utf8'
LC_CTYPE = 'es_ES.utf8';

-- A nivel de columna
CREATE TABLE libros (
    id SERIAL PRIMARY KEY,
    titulo VARCHAR(100) COLLATE "es_ES",
    autor VARCHAR(100) COLLATE "es_ES"
);

En PostgreSQL, también podemos usar COLLATE en consultas:

-- Ordenar usando colación específica
SELECT titulo, autor
FROM libros
ORDER BY titulo COLLATE "es_ES";

-- Filtrar con colación específica
SELECT * FROM libros
WHERE titulo COLLATE "es_ES" = 'García Márquez';

Para verificar las colaciones disponibles:

-- Listar colaciones disponibles en PostgreSQL
SELECT * FROM pg_collation;

SQL Server

En SQL Server, las colaciones determinan no solo el orden, sino también la sensibilidad a mayúsculas/minúsculas y acentos:

-- A nivel de base de datos
CREATE DATABASE biblioteca
COLLATE Latin1_General_CI_AI;

-- A nivel de columna
CREATE TABLE libros (
    id INT IDENTITY(1,1) PRIMARY KEY,
    titulo NVARCHAR(100) COLLATE Latin1_General_CI_AI,
    autor NVARCHAR(100) COLLATE Latin1_General_CS_AS
);

Los sufijos en nombres de colación de SQL Server indican comportamientos específicos:

• CI: Case Insensitive (insensible a mayúsculas/minúsculas)
• CS: Case Sensitive (sensible a mayúsculas/minúsculas)
• AI: Accent Insensitive (insensible a acentos)
• AS: Accent Sensitive (sensible a acentos)

Uso en consultas:

-- Búsqueda sensible a mayúsculas en una columna insensible
SELECT * FROM libros
WHERE titulo COLLATE Latin1_General_CS_AS = 'Cien años de soledad';

-- Ordenar ignorando acentos
SELECT titulo, autor FROM libros
ORDER BY autor COLLATE Latin1_General_CI_AI;

Oracle

En Oracle, las colaciones se manejan principalmente a través de la configuración NLS (National Language Support):

-- Crear una tabla con colación específica para una columna
CREATE TABLE libros (
    id NUMBER PRIMARY KEY,
    titulo VARCHAR2(100) COLLATE USING_NLS_COMP,
    autor VARCHAR2(100)
);

Para operaciones específicas, Oracle permite especificar colaciones:

-- Ordenar usando colación específica
SELECT titulo, autor
FROM libros
ORDER BY NLSSORT(titulo, 'NLS_SORT = SPANISH');

-- Comparar usando colación específica
SELECT * FROM libros
WHERE NLSSORT(titulo, 'NLS_SORT = SPANISH') = NLSSORT('Cien años de soledad', 'NLS_SORT = SPANISH');

Para modificar la configuración NLS a nivel de sesión:

-- Configurar colación a nivel de sesión
ALTER SESSION SET NLS_COMP = LINGUISTIC;
ALTER SESSION SET NLS_SORT = SPANISH;

SQLite

En SQLite 3.25.0 y versiones posteriores, se pueden usar colaciones predefinidas o definir colaciones personalizadas:

-- Crear tabla con colación predefinida
CREATE TABLE libros (
    id INTEGER PRIMARY KEY,
    titulo TEXT COLLATE NOCASE,
    autor TEXT COLLATE NOCASE
);

SQLite incluye las siguientes colaciones:

• BINARY: Comparación binaria (predeterminada)
• NOCASE: Insensible a mayúsculas/minúsculas para ASCII
• RTRIM: Ignora espacios finales

Uso en consultas:

-- Búsqueda insensible a mayúsculas/minúsculas
SELECT * FROM libros
WHERE titulo COLLATE NOCASE = 'don quijote';

-- Ordenar ignorando mayúsculas/minúsculas
SELECT titulo, autor FROM libros
ORDER BY autor COLLATE NOCASE;

Importancia y casos prácticos

1. Ordenamiento multilingüe: Las colaciones aseguran que los textos se ordenen correctamente según las reglas del idioma.

   -- En español, 'Ñ' se ordena después de 'N'
   SELECT nombre FROM clientes
   ORDER BY nombre COLLATE utf8mb4_spanish_ci;
   

2. Búsquedas insensibles a casos:

   -- Encontrará 'México', 'MÉXICO', 'mexico', etc.
   SELECT * FROM paises
   WHERE nombre COLLATE utf8mb4_general_ci = 'mexico';
   

3. Índices y rendimiento: La elección de colación afecta el rendimiento de los índices:

   -- Un índice con colación binaria es más rápido pero sensible a mayúsculas/minúsculas
   CREATE INDEX idx_titulo ON libros(titulo COLLATE utf8mb4_bin);
   

4. Agrupaciones: Las colaciones afectan cómo se agrupan los datos:

   -- Agrupará 'España' y 'ESPAÑA' como un solo grupo si se usa una colación insensible a mayúsculas
   SELECT UPPER(pais), COUNT(*) FROM clientes
   GROUP BY pais COLLATE utf8mb4_general_ci;