Ordenar campo varchar numéricamente en MySQL

Tengo un number de campo de tipo varchar . Aunque es de tipo varchar , almacena valores enteros con ceros iniciales opcionales. Un tipo los ordena lexicográficamente ( "42" viene antes de "9" ). ¿Cómo puedo ordenar por valores numéricos ( "9" antes de "42" )?

Actualmente uso la consulta:

 SELECT * FROM table ORDER BY number ASC 

Hay algunas maneras de hacer esto:

  1. Guárdelos como valores numéricos en lugar de cadenas. Ya lo ha descartado porque desea mantener las cadenas como 00100 intactas con los ceros a la izquierda.
  2. Ordenar por las cadenas emitidas como numéricas. Esto funcionará, pero tenga en cuenta que es un asesino de rendimiento para bases de datos de tamaño decente. Las funciones por fila realmente no se escalan bien.
  3. Agregue una tercera columna que sea el equivalente numérico de la cadena y el índice sobre eso. Luego use un disparador de inserción / actualización para asegurarse de que esté configurado correctamente cada vez que cambie la columna de cadena.

Dado que la gran mayoría de las bases de datos se leen con mucha más frecuencia que las escritas, esta tercera opción anterior amortiza el costo del cálculo (hecho en insertar / actualizar) sobre todas las selecciones. Sus selecciones serán deslumbrantemente rápidas ya que usan la columna numérica para ordenar (y no para funciones por fila).

Sus inserciones y actualizaciones serán más lentas, pero ese es el precio que paga y, para ser honesto, vale la pena pagar.

El uso del disparador mantiene las propiedades ACID de la tabla ya que las dos columnas se mantienen en el paso. Y es un idioma bien conocido que generalmente puede intercambiar espacio por tiempo en la mayoría de las optimizaciones de rendimiento.

Hemos utilizado este “truco” en muchas situaciones, como almacenar versiones de mayúsculas y minúsculas junto con los originales (en lugar de utilizar algo como tolower ), longitudes de cadenas de identificación para encontrar a todos los usuarios con 7 caracteres (en lugar de utilizar len ) y así sucesivamente.

Tenga en cuenta que está bien revertir desde la tercera forma normal para el rendimiento siempre que comprenda (y mitigue) las consecuencias.

Prueba esto

 SELECT * FROM table_name ORDER BY CAST(field_name as SIGNED INTEGER) ASC 

De hecho, he encontrado algo interesante:

 SELECT * FROM mytable ORDER BY LPAD(lower(mycol), 10,0) DESC 

Esto le permite ordenar el campo como:

 1 2 3 10 A A1 B2 10A 111 
 SELECT * FROM table ORDER BY number + 0 

Truco que acabo de aprender. Agregue ‘+0’ a la cláusula de orden de campo varchar:

 SELECT * FROM table ORDER BY number+0 ASC 

Ahora veo esta respuesta arriba. Me pregunto si esto está encasillando el campo y un número entero. No he comparado el rendimiento. Funciona bien

Para una tabla con valores como Er353, ER 280, ER 30, ER36 ordenar por defecto dará ER280 ER30 ER353 ER36

 select fieldname,substring(fieldname, 1, 2) as bcd, CONVERT(SUBSTRING(fieldname, 3, 9),UNSIGNED INTEGER) AS num from table_name order by bcd,num; 

los resultados serán en este orden ER30 ER36 ER280 ER353

usted puede obtener orden de acuerdo a su requerimiento mi uso siguiente consulta SQL

 SELECT * FROM mytable ORDER BY ABS(mycol) 

dado un username columna que contiene VARCHAR como estos:

 username1 username10 username100 

uno podría hacer:

 SELECT username, CONVERT(REPLACE(username, 'username', ''), UNSIGNED INTEGER) AS N FROM users u WHERE username LIKE 'username%' ORDER BY N; 

no es barato, pero cumple su función.

SELECT * FROM table ORDER BY number ASC

Debería mostrar lo que desea que se muestre … parece que lo está ordenando por id o el number no está definido como integer en este momento.

Áspero y listo: ordena por 1 * field_name