Ordenamiento natural

Cuando ordenas cadenas que contienen números en MySQL, el resultado no siempre es el que esperas. MySQL ordena las cadenas carácter por carácter usando la comparación lexicográfica, lo que produce resultados como item1, item10, item11, item2, item20, item3. Un ser humano esperaría item1, item2, item3, item10, item11, item20. El ordenamiento natural resuelve este problema tratando las secuencias numéricas dentro de las cadenas como números reales.

El problema

Considera una tabla de salas de reunión o ubicaciones:

CREATE TABLE salas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL
);
 
INSERT INTO salas (nombre) VALUES
('Sala 1'), ('Sala 2'), ('Sala 3'), ('Sala 10'),
('Sala 11'), ('Sala 20'), ('Sala 100'), ('Sala 5');

Si ordenas con ORDER BY nombre estándar:

SELECT nombre FROM salas ORDER BY nombre;

MySQL compara carácter por carácter. Cuando llega al primer dígito, compara 1 con 2, y como 1 < 2, "Sala 10" aparece antes de "Sala 2". Esto es correcto desde el punto de vista lexicográfico, pero no es intuitivo para un usuario.

Solución con conversión numérica (+0)

Si las cadenas siguen un patrón predecible con un prefijo de texto y un número, puedes extraer la parte numérica y usarla para ordenar:

SELECT nombre
FROM salas
ORDER BY nombre + 0;

Espera, esto no funciona directamente porque nombre + 0 intentará convertir toda la cadena a número, y "Sala 1" se convierte a 0. El truco es extraer solo la parte numérica:

SELECT nombre
FROM salas
ORDER BY CAST(REGEXP_SUBSTR(nombre, '[0-9]+') AS UNSIGNED);

REGEXP_SUBSTR(nombre, '[0-9]+') extrae la primera secuencia de dígitos de la cadena, y CAST(... AS UNSIGNED) la convierte a número para que la comparación sea numérica en lugar de textual.

Solución con LENGTH + nombre

Otra técnica clásica que no requiere expresiones regulares: ordenar primero por la longitud de la cadena y luego alfabéticamente. Esto funciona cuando las cadenas tienen la misma estructura y la diferencia está en la longitud del número:

SELECT nombre
FROM salas
ORDER BY LENGTH(nombre), nombre;

La lógica es simple: "Sala 1" tiene 6 caracteres, "Sala 10" tiene 7, y "Sala 100" tiene 8. Al ordenar primero por longitud, los números de un dígito quedan antes que los de dos dígitos, y estos antes que los de tres. Dentro del mismo grupo de longitud, el orden alfabético normal funciona correctamente.

Esta técnica tiene una limitación: falla si las cadenas tienen prefijos de longitud variable. Funciona bien para listas donde solo cambia la parte numérica.

Solución con LPAD

Si puedes modificar la consulta pero no los datos, LPAD rellena los números con ceros a la izquierda para que la comparación textual coincida con la numérica:

SELECT nombre
FROM salas
ORDER BY LPAD(
    REGEXP_SUBSTR(nombre, '[0-9]+'),
    10,
    '0'
);

LPAD rellena el número extraído con ceros hasta 10 caracteres de ancho. Así "1" se convierte en "0000000001" y "100" en "0000000100". La comparación textual de estas cadenas produce el orden numérico correcto.

Caso con múltiples números en la cadena

Cuando las cadenas tienen más de un grupo numérico, la situación se complica. Considera versiones de software o códigos compuestos:

CREATE TABLE versiones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(20) NOT NULL
);
 
INSERT INTO versiones (version) VALUES
('v1.0'), ('v1.1'), ('v1.2'), ('v1.10'), ('v1.9'),
('v2.0'), ('v2.1'), ('v10.0'), ('v2.10');
 
-- Ordenamiento incorrecto estándar
SELECT version FROM versiones ORDER BY version;

Para ordenar correctamente, necesitas separar y convertir cada componente numérico:

SELECT version
FROM versiones
ORDER BY
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, 'v', -1), '.', 1) AS UNSIGNED),
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, 'v', -1), '.', -1) AS UNSIGNED);

La primera expresión extrae el número mayor (antes del punto) y la segunda extrae el número menor (después del punto). Ambos se convierten a enteros para comparación numérica.

Caso práctico: nombres de archivo

Otro escenario común es ordenar nombres de archivos:

CREATE TABLE documentos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre_archivo VARCHAR(100) NOT NULL
);
 
INSERT INTO documentos (nombre_archivo) VALUES
('informe_1.pdf'), ('informe_2.pdf'), ('informe_10.pdf'),
('informe_3.pdf'), ('informe_100.pdf'), ('informe_20.pdf');
 
SELECT nombre_archivo
FROM documentos
ORDER BY
    SUBSTRING_INDEX(nombre_archivo, '_', 1),
    CAST(REGEXP_SUBSTR(nombre_archivo, '[0-9]+') AS UNSIGNED);

Primero ordena por el prefijo de texto y luego por el número extraído como entero.

Almacenar la parte numérica en una columna separada

Si necesitas ordenamiento natural frecuentemente y el rendimiento es importante, la mejor solución es almacenar el número en una columna separada:

ALTER TABLE salas ADD COLUMN numero INT;
 
UPDATE salas SET numero = CAST(REGEXP_SUBSTR(nombre, '[0-9]+') AS UNSIGNED);
 
-- Ahora el ordenamiento es simple y eficiente
SELECT nombre FROM salas ORDER BY numero;

Esta columna puede indexarse para consultas que necesiten ordenar por ella, lo que es mucho más eficiente que calcular la extracción del número en cada consulta.

Rendimiento

Las soluciones con REGEXP_SUBSTR, CAST y funciones de cadena impiden que MySQL use índices para el ordenamiento, ya que está aplicando una función sobre la columna. Para tablas pequeñas esto no es problema. Para tablas con millones de filas, considera la estrategia de columna separada o una columna generada:

ALTER TABLE salas ADD COLUMN nombre_sort INT
    GENERATED ALWAYS AS (CAST(REGEXP_SUBSTR(nombre, '[0-9]+') AS UNSIGNED)) STORED;
 
CREATE INDEX idx_nombre_sort ON salas(nombre_sort);
 
SELECT nombre FROM salas ORDER BY nombre_sort;

La columna generada se calcula automáticamente cuando insertas o actualizas datos, y al ser STORED puede tener un índice.

El ordenamiento natural no está incorporado de forma nativa en MySQL, pero con las técnicas presentadas puedes lograr el comportamiento esperado en cualquier escenario.